Excel is the world’s leading tool for data manipulation, analysis, management and reporting. The breadth of functions and tools available in Excel offers users wide-ranging flexibility, but it also means usage can be haphazard, ill-informed and inefficient when used without proper guidance.
Business Analytics in Excel brings an end to ineffective Excel usage by providing a structured step-by-step approach to the analytical process; from the initial importing of raw data to Excel to the final stage of extracting and reporting results.
Key learning
Elevate your results through ‘what-if analysis’, professional charts and statistical analysis
Master pivot tables and other filter and sorting tools for quick and powerful data arrangement
Command all the functions and tricks to ‘cleanse’ your data into Excel-ready form
Know how to select the right method of data import with a choice matrix
What you will take away from this course
A structured step-by-step approach to analysing and interpreting data for business analysis
Key exercises workbook with course case-studies and benefits
Mazars Financial Modelling best practice methodology rules and techniques
All the keyboard short-cuts and tricks required to work in Excel with speed
Is this programme for you?
Yes, this course is for you if you need to analyse or make decision based on data in Excel. Typical attendees include accountants, analysts, consultants, managers and other industry professionals.
Upcoming
Location:Date:
Course Outline
Getting data into Excel and understanding its form
Learn about the typical import sources of data (e.g. accounting software, ERP systems and common database programmes)
Understand the pros and cons of different importing methods with walkthrough examples
Discuss the form of received information and its implications (e.g. dating, text and number formats)
Clarify what data aims to represent in column and row arrangements, in spatial and duration terms
Focused Case-Study Exercise: PART 1
Develop a choice matrix and import data
This exercise will lead you to produce a reference choice matrix so you can quickly assess the correct import method based on import source, form of information and its imported arrangement.
Use this choice matrix to identify the correct import methods for two sets of case-study data which will be cleansed, manipulated, analysed and reported upon through the course.
Case study data set 1: Highly granular financial information imported from accounting software.
Case study data set 2: Sale and demographic survey data from a multi-national car vendor.
Converting data to make it Excel-ready
Identify how Excel understands your data and make data Excel compliant
Learn key Excel functions, tips and tricks to assist quick conversion including:
tips – e.g. use of custom formatting to cleanse data
tricks – e.g. converting specific elements of data blocks fast by changing between number and text forms
Understand when data is corrupted or simply unreadable for Excel
Focused Case-Study Exercise: PART 2
Cleanse data for use
Working in small teams, use the functions, tips and tricks presented to arrange imported data sets so that they are Excel compliant for purpose and visually accessible.
The trainer will assess the different methods for efficiency of process, clarity of presentation and appropriate use of commands.
Data mining and manipulation
Discuss and define analytical goals for the data by posing the correct questions
Identify what data may be omitted in light of goals and learn how to remove this efficiently
Define the parameters of your data and manipulate it into useful blocks for analysis, using:
Dating and time controls
Lookup and reference formulas including LOOKUP, MATCH, INDEX, CHOOSE, OFFSET
Sum controls including SUMPRODUCT and SUMIF
Arrangement controls including TRANSPOSE, FILTER and SORT
Focused Case-Study Exercise: PART 3
Master powerful data manipulation tricks
During this exercise your group will discuss and determine an analytical goal for the first set of data. The trainer will then work through the appropriate manipulation of this data set with the class.
For the second data set the trainer will conversely pose a complex analytical goal and manipulation will be in your hands to determine. Full review, best-practice solution and Q&A to fllow.
Data analysis and powerful Excel tools for quick results
Answer your data queries by understanding a wide range of tools at your command including:
What if analysis tools, including Goal Seek and data tables
Moving averages
Regression analysis
Finalise your investigations by presenting your findings:
Use advanced conditional formatting commands and tricks
Develop professional presentation charts for reporting to senior management
Focused Case-Study Exercise: PART 4
Interpreting data
Working through goals for both data sets your group will discuss and assess the best way to:
Roll up financials in data set one to company level and use trend analysis to interpret the success or decline of business units.
Analyse the sales and demographic data for a car vendor case study and determine the best location for a new strategic expansion office using statistical analysis to support your decision.
Some of these cookies are necessary, while others help us analyse our traffic, serve advertising and deliver customised experiences for you. For more information on the cookies we use, please refer to our Privacy Policy.
This website cannot function properly without these cookies.
Analytical cookies help us enhance our website by collecting information on its usage.
We use marketing cookies to increase the relevancy of our advertising campaigns.
Follow us
Corality is dedicated to exceptional financial modelling. As part of our commitment to raising the bar in financial modelling, we want to ensure the financial modelling community is kept up to date with the latest events, tips, techniques and training.
By registering for email updates from Corality you will be kept up to date about:
