Advanced Data Analysis Techniques

Introduction

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 Behaviour and outcomes.

Objectives

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 optimisation 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
• To provide delegates with a working vocabulary of analytical terms to enable them to converse with people who are experts in the areas of data analysis, statistics and probability, and to be able to read and comprehend common textbooks and journal articles in this field.  
• To provide delegates with both an understanding and practical experience of a range of the more common analytical techniques and data representation methods, which have direct relevance to a wide range of analytical problems.  
• To give delegates the ability to recognize which types of analysis are best suited to particular types of problems.  
• To give delegates sufficient background and theoretical knowledge to be able to judge when an applied technique will likely lead to incorrect conclusions.  
• To provide delegates with an overview of the main data analysis applications within engineering systems.

Training Methodology

This is an interactive course. There will be open question and answer sessions, regular group exercises and activities, videos, case studies, and presentations on best practices. Participants will have the opportunity to share with the facilitator and other participants what works well and not so well for them, as well as work on issues from their own organizations. The course is conducted online using MS-Teams/ClickMeeting.

Who Should Attend?

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.

Course Outline

Day 1: 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 2: Newtonian and Genetic Optimization 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 3: 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 4: 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 5: 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