Advanced Excel and Risk Modelling - A Practical Workshop

Course Overview:

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

Who will benefit?

Analysts and other 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

Programme outline

This two day workshop explores:

• Rules and standards governing model design and construction
• Building models while 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
• The workshop is highly practical and involves building a complete model. Delegates start with a template and construct a complete model in stages.

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 CD-Rom to the seminar.

Module 1 – Spreadsheet Best Practice

• Financial modelling objectives
• Examples of Excel models
• Useful Excel features and techniques
• Systematic Excel standards
• Case outline – introduction

Exercise: reworking existing skeleton model

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

Module 3 – Forecasting Models

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

Exercise: developing forecast free cash flow and ratios

Exercise: producing a forecast with time series analysis

Module 4 – Risk and Sensitivity

• Risk and multiple answers
• Different scenario techniques
• Advanced financial functions

Exercise: adding sensitivity to the case model

Module 5 – Quantifying Risk

• Review of downside risk
• Quantifying risk – tornado and spider methods

Example: quantifying risk in the case model

Module 6 –Simulation Techniques

• Elements and building blocks of a simulation model
• Monte Carlo simulation methods in Excel

Example: using simulation in the case model

Module 7 – Optimisation and Targeting

• Overview of optimisation and targeting
• Goal seek and Solver methods

Example: targeting and optimisation

Module 8 – Management Reporting

• Dashboard techniques
• Management summaries
• Model completion

Exercise: producing a management report