Course Overview
 
Financial modelling techniques allow business users to improve the reliability, quality and timeliness of their decision making. Modelling techniques are now used in many different areas, such as capital budgeting, investment appraisal, business valuation, and financial analysis and forecasting.

With a model-by-model approach, this course will guide you through the various techniques, providing you with a comprehensive understanding of modelling, covering such issues as scenario analysis, forecasting and Monte Carlo analysis, as well as how each technique can practically be applied through the use of Excel.

Participants will acquire key skills necessary to build and use Excel models in practical business environments.

Teaching Method

This programme will be delivered through practical model-building case studies and worked examples, demonstrating how and why each technique is used. Emphasis is placed on participants gaining practical, hands-on experience of the design and construction of financial models in Excel, not the acquisition of needless spreadsheet skills for their own sakes.

Participants will take away a range of completed financial models applicable to different businesses and modelling challenges.

Participants will be required to bring a scientific calculator and a laptop with USB port to the course.

Course Content

Day 1

Revision of Excel
Key financial functions
Common financial uses
Charts and reporting functions
Review of Excel functions and their use
Targeting a specific output
Goal seeking
Optimisation
Circularity and how to resolve it
Working with range names
Limitations of “choose” and “if” functions

Exercise: Determining which Excel functions are useful in financial modelling and for what purpose

Financial Models
The fundamentals of model design:
Objectives of financial models
Typology
Design, testing and feedback
Model sensitivity and auditing
The role of macros

Exercise: Analysing a financial model

Mechanics of Financial Models
Working sheet
Debt issues sheet
Financial Statements
Model Outputs

Model Value Drivers
Demand and Capacity Driven Forecasts
Consistency of Capital Expenditures and Volume
Operating Revenue and Expense Drivers

Cash Flow Modelling
Modelling cash flow and ratios:
Allowing for accountancy - depreciation, tax and capital allowances
Modelling ratios


Forecasting Models
Review of forecasting methods
Macro forecasting
Linear/trend methods
Smoothing techniques
Seasonal forecasts
Multiple regression

Example: Constructing and using a forecasting model

Risk Techniques
Understanding of risk
Sensitivity table
Scenario analysis
Dynamic what-if
Standard deviation and coefficient of variation
Certainty equivalents
Building a simulation model
Monte Carlo simulations

Example: Using Excel for risk management modelling

Valuation Models
Cash flow DCF-IRR valuation models
XNPV, XIRR, MIRR
Ratio analysis and modelling
EVAtm models
CFROI (Cash Flow Return on Investment) models
Modelling real options
Integrated models

Case Studies: Numerous valuation models of a range of companies and valuation techniques

Types of Financial Transaction Models
Modelling for project finance:
Acquisitions modelling
New investments
Disposals & divestments
Leasing projects


Acquisition Modelling
Synergy Modelling
Analysis of Completed M&A Models
Model Layout and Structuring Issues
Financial Statement Review for M&A

Case Study: Acquisition models

Introduction to Modelling of Private Equity
Theory behind leveraged buyouts
Cash flow waterfall
Modelling the impact of covenants
Ratios and valuation analysis


Management Models
Financial Control
Standards and flexible budgeting for control
Budget and profit planning
Marginal costing and limiting factors
Break-even and profit-volume analysis
Control of working capital
Decision trees, binomials and linear programming

Day 2

Building a Corporate Model.

Based on a real example, provided by an analyst in reviewing a listed company, delegates will construct and use a model for the valuation and analysis. The exercise will include:

Company Review
Analysing the inputs
Reviewing and analysing the Annual Statements
Vertical Analysis
Ratio Analysis
Fundamental Market Analysis
Loan and amortization assessment
Determining Free Cash Flow
IRR, NPV and other valuation analysis

Course Conclusion: The Value of Models