Course details

Overview ?

Why choose this Training Course?

The statistical analysis of numerical information is proven to be a powerful tool, providing everyday insight into matters like corporate finance, production processes and quality control. However, the advent of the Internet of Things, the consequential growth in Big Data, and the ever-increasing requirements to model and predict, mean that many of the analytical opportunities and needs of a modern, high performing company cannot be met using conventional statistical methods alone.

More and more companies are wrestling with complex modelling and simulation problems, addressing matters like trying to optimize production systems, to maximize performance efficiency, to minimize operating costs, to combat risk, to detect fraud and to predict future behavior and outcomes.

Prerequisite

This intensive training course is intended for delegates who already have a solid understanding of conventional data analysis methods. This advanced training course shows by example how to build on the method learned in the Data Analysis Techniques course and to create a variety of powerful modelling, 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 course 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.

What are the Goals?

This training course aims to provide those involved in analyzing numerical data with the understanding and practical capabilities needed to convert data into meaningful information via the use of 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 business problems which require modelling, simulation and predictive analytical approaches
To show delegates how to implement a wide range of the more common modelling, simulation and predictive analytical methods using Microsoft Excel 2010 (or higher) and in particular the Solver tool
To provide delegates with both a conceptual understanding and practical experience of a range of the more common modelling, simulation and predictive analytical techniques, including Bayesian models, conventional and genetic optimization methods, Monte Carlo models, Markov models, What If analysis, Time Series models, Linear Programming, and more
To give delegates the ability to recognize which modelling, simulation and predictive analysis methods are best suited to which types of problems
To give delegates sufficient background and situation experience to be able to judge when an applied technique will likely lead to incorrect conclusions
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 optimised 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 analyze data using common statistical methods, are fundamental prerequisites for attendance on this course. 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.

How will this Training Course be Presented?

This training course adopts a problem‐based learning approach, in which delegates are presented with a series of real problems drawn from the widest possible range of applications – they range from insurance to supply chain logistics, from chemistry to engineering, and from production optimization to financial risk assessment. Each problem presents and exemplifies the need for a different modelling or analytical approach.

The training course is entirely applications‐oriented, minimizing the time spent on the theory and mathematics of analysis and maximizing the time spent on the use of practical methods from within Excel, along with the understanding of how and why such methods work.

Delegates will spend almost all of their time exploring the use of modelling and simulation methods using Microsoft Excel, to develop solutions to the totally realistic problems that are presented.

Organizational Impact

Organizations which are able to make optimum decisions, and which can reliably predict future trends and behaviors, are able to enhance substantially their ability to compete on the global stage; as a result of sending their employees on this course, organizations can expect to benefit from:

A shift from intuition-based to information-based decision making
The provision of accurate solutions to complex problems
Enhanced forecasting and future behavior prediction
Advanced modelling and simulation of business processes
More capable risk assessment and risk-informed decision making
Improved capitalization on the wealth of information contained in Big Data

Personal Impact

Participants will each gain extensive understanding and lots of practical experience of a wide range of the more common modelling, simulation and predictive analytical techniques, all of which will have direct relevance to a wide range of business issues; specifically, delegates will acquire:

New insights into the use of optimization, modelling and prediction using Microsoft Excel
Experience of Linear Programming
An understanding of how and when to use Newtonian and Genetic Optimization Methods
Knowledge of Scenario Analysis, Markov Modelling and Monte Carlo Simulation
The ability to recognize which types of analysis are relevant to particular types of issues
Sufficient situational knowledge to judge when a technique will lead to incorrect conclusions

Outline

DAY ONE: LINEAR PROGRAMMING

Introduction to optimization
Multi‐variate optimization 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)

DAY TWO: NEWTONIAN AND GENETIC OPTIMISATION METHODS

Linear and non‐linear optimization 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 parallelize
Implementation using Solver in Excel
How to solve a range of optimization problems, culminating in the classic ‘travelling salesman problem’ by optimizing 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
Analyzing 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
Analyzing 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