Return to schedule
SHARE

Business Analytics in Excel

New Call-to-action



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
  • SMART 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.

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:
    • 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

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 follow.

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

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:

1. Roll up financials in data set one to company level and use trend analysis to interpret the success or decline of business units.

2. 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.

 

schedule

No dates currently scheduled

have a question?

Please enter your first name.
Please enter your last name.
Please enter your valid email.
Please enter your valid contact number.
Please enter your company
Please select your country
Please enter your enquiry