Advanced Data Analysis Techniques

What are the Goals?

This Advanced Data Analysis Techniques training course aims to provide those involved in monitoring, managing and controlling complex business processes with the understanding and practical capabilities needed to convert data into meaningful information via a range of very powerful modelling, simulation and predictive analytical methods.

The specific objectives are as follows:

• To teach delegates how to solve a wide range of complex business problems which require modelling, simulation and predictive analytical approaches
• To show delegates precisely how to implement a range of modelling, simulation and predictive analytical methods using Microsoft Excel 2016 (or 365)
• To provide delegates with both a conceptual understanding and practical experience of advanced data analysis methods including: Bayesian models, conventional and genetic optimization methods, Monte Carlo models, Markov models, What If analysis, Time Series models, Linear Programming, and more
• To engage delegates for the entire 3 days in the exploration and use of modelling and simulation methods within Microsoft Excel, to develop complete solutions to the 8 totally realistic business problems that are presented
• To enable delegates to make the shift from intuition-based to information-based decision making in complex situations, hence enabling them to enhance their forecasting and future behavior predictions, increase their proficiency in risk assessment and risk-informed decision making, and to exploit to a much greater extent the wealth of information contained in Big Data
• To provide a clear understanding of why the best companies in the world see modelling, simulation and predictive analytics as being essential to delivering the right quality products and optimized services at the lowest possible costs

Who is this Training Course for?

This training course has been designed for professionals whose jobs involve the manipulation, representation, interpretation and/or analysis of data. The training course involves extensive modelling and analysis using Excel 2010 (or higher) and therefore delegates must not only be numerate, but must enjoy detailed working with numerical data to solve complex problems.

Full familiarity with Microsoft Excel (version 2007 or higher), and the ability to analyse data using common statistical methods, are fundamental prerequisites for attendance on this course. Only delegates who have attended the Data Analysis Techniques course will be eligible to attend this programme, because without mastery of the capabilities taught in the Data Analysis Techniques course a delegate will not be able to succeed on this training course.

Daily Agenda

Day One: 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 optimising the products from a refinery, and minimising the manufacturing and delivery costs for a complex supply chain (with and without batch manufacturing, and with and without warehousing)

Day Two: Newtonian and Genetic Optimisation Methods

• Linear and non‐linear optimisation problems; Stochastic search strategies; Introduction to genetic algorithms; Biological origins; Shortcomings of Newton‐type optimisers; 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 optimising the motion trajectory of a large manufacturing robot, both with and without forced constraints

Day Three: 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

Day Four: 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

Day Five: 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 problems; How many iterations are enough?; Defining complex problems; Modelling the variables; Analysing the data; Freezing the model; Manual recalculation; "Paste Values" function; Basic statistical functions; 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