Preparing for your Scenario Demonstration with Excel & Power BI | by Leatitia Hudson

Leatitia Hudson is a Data Literacy/Data & Insights for Business Decisions Coach at Multiverse.

The Scenario Demonstration is one part of the assessment for the Data Technician Level 3 qualification.

There are two separate exercises for the Scenario Demonstration that enable you to demonstrate specific Knowledge, Skills & behaviours (KSBs) from the Data Technician Level 3 Apprenticeship Standards. For a full list of the standards covered in the Scenario demonstration, click here. 

What you need to know:

• Each scenario demonstration is 45 minutes in duration (90 minutes total).
• Exercise 1 (45 mins) will be on Data Gathering where you clean, join and visualise the data to identify trends and explain the relevance of data to the business.
• Exercise 2 (45 mins) will be on Data Analysis and Validation where you will analyse the data you gathered in the first exercise and extract insights through normalisation, interpreting patterns or trends, identifying opportunities for automation and performing more advanced calculations. 
• The data will be provided in a CSV file format.
• You can use the tool/s of your choice as the assessment is tool inclusive. You can use one or multiple tools and swap between at any point.

Please see your EPA preparation 1: An introduction to your End Point Assessment on Appli.ed for more details.

Tool: Microsoft Excel 

Why Excel?

'Microsoft Excel enables users to format, organise and calculate data in a spreadsheet.’

Excel is a powerful Microsoft spreadsheet software tool that you can use to quickly summarise and gain insights from large datasets. It is the most widely used spreadsheet management tool in the industry due to the range of tools available that increase productivity and increase efficiency. 

Pros of Excel:

• Intuitive to use: A popular spreadsheet software that is widely adopted. Excel is a tool that is aligned with other Microsoft tools, making the product offering intuitive to use.
• Advanced Cleaning Functions: Excel has advanced data cleaning features, ensuring that your data quality is high! You can easily remove duplicates, correct any spelling mistakes, filter the data, identify outliers and much more!
• Aggregate data using pivot tables.
• Join and reference data with advanced LOOKUP functions (XLOOKUP, VLOOKUP, INDEXMATCH etc.).
• Use Power Query to transform, integrate and enrich data in Excel.
• Visualising data: Select from a number of different visualisations.
• Automation: Automate tasks by creating macros, using advanced formulas and using add-ins!
• Analyse data provides high level visual summaries, trends and patterns.

Cons of Excel:

• Time Consuming: Excel has many features including hundreds of formulas and advanced features that require an investment in time and effort to master. 
• Manipulation of visualisations: Navigating to different areas to manipulate the visualisation can take a lot of time to learn and there can be complexities with certain visualisations.

Tool: Power BI 

Why Power BI?

Power BI is a powerful tool that enables you to dynamically create visualisations and interactively explore data to gain insights.

According to Gartner Power BI is the leading BI tool with over 97% of Fortune 500 companies using it.

Pros of the chosen tool:

• Data Modelling: Prep and model your data with ease using the data modelling tools.
• Advanced functions: Use DAX to create calculated columns, measures and custom tables.
• Use Power Query (a tool familiar to Excel users) to transform, integrate and enrich data in Power BI.
• Create dynamic and interactive dashboards.
• Cross filtering & Slicers: providing a dynamic way to view your data and gain deeper insights.
• Visualising data: Power BI has a large range of visualisations. You can create insightful visualisations using just a few clicks!
• Applied Steps: Any transformations applied to your data will be visible in the Applied Steps pane, making it easy to review your steps and correct any errors.
• Automation: Leverage the automation tools available in Power BI such as the identification of relationships between tables and the refresh button that refreshes reports when new data is added. 

Cons of the chosen tool:

• Advanced Data cleaning limitations: requiring the use of an alternative tool such as Excel to assist with the data cleaning!
• Intuitive visualisations: PowerBI’s automatic decisions can occasionally to complexities’ in visualisations that can be difficult to figure out ways to fix (such as not ordering my months in line graphs but by the values).

​​​​​​​How to best prep for the Scenario Demonstration

• Practice: Practice the skills that you have been learning to solidify your knowledge and build confidence.
• Prepare! Ensure that you have completed the e-learnings on Appli.ed in the EPA Preparation Module.
• Read the brief carefully! What is the context of the problem? What tools/skills are needed? What do you  need to produce? How will you do it?
• Plan: You only have 45 minutes per scenario demonstration! Have a plan in mind on how you are going to complete each scenario. Spend the first few minutes of the assessment, writing down your plan. 
• Justify your reasoning: What is your rationale for choosing the specific tool/tools? Be clear about the pro’s and con’s of the tools and methods that you will be using. 
• Visualise the data: When choosing visualisations, think about what you want the visualisation to display. Are you showing a change over time? Looking at geographical data? Observing relationships between variables? Looking at how data is distributed? Comparing values between groups? These questions will help you choose the right visualisation.
• Demonstrate Problem-solving skills: Ensure that you are able to provide some insights from your findings and are able to contextualise the data to answer the scenario brief given. 

Believe in yourself! Have confidence and trust in your preparation and efforts. You’ve got this!

What to expect on the day of the assessment 

An assessor will be with you throughout both of the Scenario Demonstrations and will ask you questions throughout. The assessor is not there to trip you up! They are there to witness and provide opportunities’ for you to evidence the Data Technician Level 3 standards.

On the day, you will need:

• A computer with access to the internet.
• Access to email and any other software tools appropriate to the task.
• The ability to share your desktop via online conferencing software.
• Photo ID.