Business Analytics Course In Excel

• 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

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:
  • functions – e.g. RIGHT, LEFT, CLEAN, TRIM, PROPER, MID, LOWER, UPPER
  • 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

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

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:
  • Pivot tables
  • Statistical functionality (e.g. MEDIAN, PERCENTILE, CORREL)
  • 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

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.