Advanced Data Analysis and Statistics for Information Workers using Excel

To equip information workers and other non-technical professionals with advanced data analysis, statistical and forecasting skills using the Excel Analysis ToolPak and other advanced features of Excel. To enable them to validate and interpret their results, and to fit them into an organisation’s strategic management plans.

Part 1 – Basic Data Theory and Manipulation (1.5 days)
This part of the course introduces students to basic data concepts, how to summarise data in various ways, how to present it and how to produce reliable data.

1. Understanding Data
   
   • Data Types
   • Appropriate graphs for the different types
2. Descriptive Statistics
   
   • Analysis ToolPak Descriptives
   • Complex Pivot Tables
   • Conditional Aggregate Functions
3. Advanced Graphics
   
   • Enhancing our graphs
   • Graphics for complex pivots
4. Producing reliable data
   
   • Data Cleaning
   • Data Validation
   • Data Filtering
5. Exercise – Undertake an initial analysis of the test data and interpret it in the light of organisational goals

Part 2 – The Normal Distribution & Estimation (1 day)
This part of the course introduces more sophisticated analysis; examining distributions, using them to make estimations of the key variables affecting the organisation’s performance and beginning to analyse the causal links between these variables.

1. The Normal Distribution – Analysis ToolPak Histogram
2. Probabilities and the Normal Distribution
3. Estimation and Confidence Intervals
4. Basic Hypothesis testing using Excel functions and the Analysis ToolPak
5. Analysis ToolPak ANOVA
6. Exercise – developing the analysis from the first exercise, estimate probable values for key performance variables, validate them and start to analyse the factors driving them.

Part 3 – Probability and Regression (1 day)
This part of the course uses probability and regression to derive further estimates for our key performance variables and perform further causal analysis on them.

1. Probability
   
   • Basic Probability Arithmetic
   • Categorical Data Analysis
   • Probability distributions and expected values
2. Regression and Correlation
   
   • Simple regression
   • Correlation
   • Multiple regression with the Analysis ToolPak
   • Hypothesis testing for regression
3. Exercise – perform further estimation and causal analysis for our key performance variables.

Part 4 – Time Series and Forecasting (1 day)
This part of the course enables students to make predictions as to the future success of the organisation’s plans using various forecasting methods.

1. Additive Time Series Model
2. Multiplicative Time Series Model
3. Moving Averages
4. Exponential Smoothing
5. Holt’s Method
6. Winter’s Method
7. Exercise – based on previous estimates for our organisation’s key performance variables, produce forecasts for the performance of its strategic plans

Part 5 – Final Report and Conclusions (0.5 day)
In this part of the course, students finalise their analysis, followed by a discussion. We re-visit any topics needing further elucidation and discuss further ways in which this sort of analysis can be conducted.

1. Exercise – finalise analysis and come to overall conclusions about the organisation’s strategic plans, and their chances of success
2. Discussion about student’s conclusions
3. Introduction to further data analysis and business intelligence tools
   
   • Power Pivot (Tabular OLAP)
   • Multidimensional OLAP
   • Data Mining
   • Big Data
4. Review of topics covered in the course

Data Analysts and other non-technical professionals.

Good basic and intermediary Excel skills.

More demanding data analysis roles and the ability for further study in business intelligence.

Certificate of completion

Digital certificate - Included