• The training seminar shows by example how to build on the method learned in the Data Analysis Techniques training seminar and to create a variety of powerful modeling, simulation, and predictive analytical methods.
• The methods introduced include Bayesian models, Newtonian and genetic optimization methods, Monte Carlo simulation, Markov models, advanced What If analysis, Time Series models, Linear Programming, and more.
• The training seminar uses advanced features of Microsoft Excel throughout, and it is important that all delegates are fully competent in both Excel and the statistical analysis of data.

Module 1: Data Collection, Presentation and Analysis

• Data and Methods of Collecting Data
• Presentation of Data using Excel
• Statistical Analysis of Data – Mean; Median and Mode; Variance and Standard Deviation
• Probability – Binomial; Normal and Poisson Distribution
• The Limitations of Data and Data Analysis
• Confidence Intervals and Hypothesis Testing

Module 2: Pivot Tables and Data Analysis Tools in Excel

• Pivot Tables – developing and using Pivot Tables to aid Decision Making
• Cross Tabulation and Chi-squared
• Data Analysis Tools in Excel – a detailed understanding
• ANOVA; t-tests and Z scores
• Moving Average and Exponential Smoothing
• Advanced and Applied Solver and Goal Seek

Module 3: Advanced Correlation and Regression

• Co-variance and Correlation
• Linear Regression
• Non-linear regression
• Multiple Regression using Excel
• Statistically Testing Results
• Making Decisions – Project Planning, Estimating Costs; Reducing Risk

Module 4: Project Management and Applied Data Analysis

• Forecasting Costs and Production levels
• Estimating Project Duration using Statistical estimates and P.E.R.T
• Network and Critical Path Analysis
• Economic Quantity Order Analysis – to optimize inventory levels
• Linear Programming and Optimisation Techniques to maximize resource allocation, e.g., inventory and capital
• Earned Value Analysis – to identify Project Cost and Schedule Variances

Module 5: Financial Management and Applied Data Analysis

• Analysing and Forecasting Volatility in the Market, e.g., Oil Prices
• Evaluating Suppliers, Competitors and Acquisition Targets using Data Analysis
• Developing Financial Models
• Cost, Volume, Profit Analysis
• Risk Management using Data Analysis
• Assessing the impact of decisions on Return on Investment and Return on Equity

Module 6: Linear Programming

• Introduction to Optimisation; Multi‐variate Optimisation Problems; Determining the Objective Function; Constraints to Problems; Sign Restrictions; The ‘feasibility region’; Graphical Representation; Implementation using Solver in Excel
• Using Linear Programming to Solve Production and Supply Chain / Logistics Problems, such as optimizing the products from a refinery, and minimizing the manufacturing and delivery costs for a complex supply chain (with and without batch manufacturing, and with and without warehousing)

Module 7: Newtonian and Genetic Optimization Methods

• Linear and Non‐linear Optimisation Problems; Stochastic Search Strategies; Introduction to Genetic Algorithms; Biological Origins; Shortcomings of Newton‐type optimizers; How to Apply Genetic Algorithms; Encoding; Selection; Recombination; Mutation; How to Parallelise. Implementation using Solver in Excel
• How to Solve a range of Optimisation Problems, Culminating in the classic ‘travelling salesman problem’ by optimizing the motion trajectory of a large manufacturing robot, both with and without forced constraints

Module 8: Scenario Analysis

• Introduction to Scenario Analysis; A What‐If example in Excel; Types of What‐If analysis; Performing manual what‐if analysis in Excel; One-Variable Data Tables; Two‐variable data tables
• Using Scenario Manager in Excel; Using scenario analysis to predict business expenses and revenues for an uncertain future

Module 9: Markov Models

• Understanding Risk; Introduction to Markov Models; 5 Steps for Developing Markov Models; Manipulating Arrays and Matrices inside Excel; Constructing the Markov Model; Analysing the Model; Roll Back and Sensitivity Analysis; First‐order Monte Carlo; Second‐order Monte Carlo
• Decision Trees and Markov Models; Simplifying Tree Structures; Explicitly Accounting for Timing of Events
• Using Markov Chains to simulate an insurance no claims discount scheme, and Modelling the Outcomes of a Healthcare System

Module 10: Monte Carlo Simulation

• Introduction to Monte Carlo Simulation; Monte Carlo building blocks in Excel; Using the RAND() function; Learning to model the problem; Building worksheet‐based simulations; Simple issues; How many iterations are enough?; Defining complex issues; Modelling the variables; Analysing the data; Freezing the model; Manual recalculation; “Paste Values” function; Basic statistical purposes; PERCENTILE() function
• Monte Carlo Simulation solutions to problems of traffic flow in a city, dealing with uncertainty in the sale of product, predicting market growth and assessing risk in currency exchange rates

• 120 hours
• Sunday to Wednesday

This training seminar is intended for delegates who have already attended the Data Analysis Techniques training seminar (this is a necessary prerequisite for this training) and hence who already have a solid understanding of conventional data analysis methods.