Advanced Excel and Risk Modelling

• Build a risk model incorporating different methods
• Learn how to sensitise models
• Learn how to quantify risk in spreadsheet model risk management
• Methods for stress testing risk models

• Basic standards governing model design and construction
• Building models to minimising errors
• Understanding and avoiding common modelling pitfalls
• Exploration of forecasting techniques
• Running sensitivities and what-if analysis to gain information about performance
• Advanced techniques to quantify risk

Module 1 - Spreadsheet Best Practice

• Financial modelling objectives
• Examples of poor Excel risk models
• Useful Excel features and techniques
• Systematic Excel standards: 20 Principles of Good Practice
• Case outline – introduction

Exercise: Review of existing simple model to detect instances of poor practice

Module 2 - Auditing and Testing

• Examples of common spreadsheet errors
• Essential testing and auditing techniques

Example: testing a financial analysis model

Exercise: debugging and checking an existing financial model in excel

Module 3 – Developing Cash Flow Models

• Components of cash flow models
• Building up calculations
• Quantifying results & metrics to use

Exercise: developing forecast free cash flow and ratios

Module 4 - Forecasting Models in Excel

• Review of Excel forecasting methods
• Linear methods
• Smoothing and seasonality
• Macro forecasting and regression
• Understanding value drivers in cash flow models

Exercise: producing a forecast with time series analysis

Module 5 - Risk and Sensitivity

• Risk and multiple answers
• Advanced scenario techniques
• Using advanced financial functions

Exercise: adding sensitivity to the case model

Module 6 – Quantifying Risk

• Review of downside risk
• Quantifying risk – tornado and spider methods
• Principles of Monte Carlo simulation methods in Excel

Example: quantifying risk in the case model

Module 7 - Optimisation and Targeting

• Overview of optimisation and targeting
• Goal seek and Solver methods
• Using data tables for multiple result scenarios

Example: targeting and optimisation

Module 8 - Management Reporting

• Dashboard reporting techniques
• Management summaries
• Model completion

Exercise: producing a management report

The trainer has over 30 years of experience in a wide range of roles in finance. He has delivered training courses on behalf of a number of international training companies since 2005.

He trained as a Chartered Accountant at KPMG in South Africa and New Zealand before moving into industry with Ford Motor Company. He held various positions there in financial analysis, budgeting and forecasting, until he was appointed sales planning manager, responsible for forecasting models, production planning and supply logistics. He joined a multinational private consultancy group in Australia as their general manager (finance); in this role, he guided the group through a period of major change and financial turnaround.

For the past 21 years, he has worked as a freelance financial modeller, analyst and trainer for a range of blue-chip clients. Assignments have included financial modelling using excel for two major LNG projects in the Middle East, numerous renewable energy projects in both Europe and developing countries, structured financing for a large-scale property development, restructuring and outsourcing projects in the retail gas sector, and PPP transactions in the utilities, health and support services sectors.

With an extensive accounting background, he brings accounting knowledge and analytical skills to transactions and financial modelling. He has built up a great deal of experience in financial modelling in numerous sectors, including gas production, electricity generation, energy retailing, waste processing and property development, as well as building financial models in central government departments, retail enterprises and high-volume online environments. He has built, developed and used models to support commercial negotiations, analyse risk, test scenarios and forecast results.

He has trained others in his specialization of financial modelling, and runs public courses on behalf of major training companies all around the world.

A two-day hands-on workshop for developing and constructing an advanced risk-based financial model.

The Advanced Excel and Risk Modelling course is highly practical and involves building a complete model.  Delegates start with a template and construct a complete model in stages

Who will benefit?

Analysts and others using Excel today are required to use Excel to review, construct, specify or design complex financial models.  Modern financial analysis demands models that are flexible and can deal efficiently with downside risk and sensitivity.

What you can gain

• Ability to develop financial models using a tried and tested methodology
• Tools for reviewing, auditing and testing models
• How to add risk and sensitivity to models to improve analysis and reporting
• How to quantify downside risk
• Concise reporting and dashboards

Delegates receive a full pack of Excel software and templates for future reference as part of the course materials

Each participant will be required to bring a laptop running Microsoft Office with a CD-ROM to the seminar.