The purpose of the course is for participants to learn how to build a financial model using best practice principles. This will include the development of financial statements and the mastering of the main Excel techniques (such as forecasting, risk issues, differing scenarios and optimisation).

Attendees will learn how to deal with more problematic modelling issues and gain hands-on experience of applying these skills.  By building a complete model, participants will gain the confidence to build and revise their own models.

---

Key Points

Many models fail due to inadequate structures and methodology and this course offers the opportunity to build a comprehensive financial model using international best-practice principles.  The course commences with a discussion of objectives and common mistakes and then develops the case model in stages.  On completion, participants will have built a complete model for future use and review.

Methodology

The course is taught using demonstrations combined with practical and interactive case studies.  The exercises in each session reinforce the concepts covered in each of the units.

Emphasis is placed on delegates gaining practical, hands-on experience of the design and construction of financial models in Excel.

Comprehensive product notes and modelling software are provided for future reference.

---

Day One

Introduction and Overview

Financial Modelling and Model Planning

• Overview of the course – building a model from a blank sheet
• Case study outline – desired outcomes
• Review of available financial data
• Avoiding Excel modelling errors
• Starting initial template from a blank file
• Required Excel framework
• Template sheets
• Basic information e.g. timelines and version control
• Styles, colours and number formats

Exercise: setting up model framework

Income Statement and Balance Sheet

• Extracting data from annual reports
• Adding income and balance sheet
• Model checks on consistency

Exercise: completing and checking the initial statements

Model Auditing

• Essential auditing methods
• Consistency and other checks
• Checking the initial model

Exercise: finding errors in model

Questions and answers

---

Day Two

Forecasting and Equity Valuation

Forecasting Methods

• Review of forecasting methods
• Macro forecasting
• Linear methods
• Review of historical performance
• Relationship between company and financial strategy
• Identifying and forecasting key drivers
• Linkages and modelling problems

Exercise: reviewing performance and adding a forecast to the case template

Free Cash Flows

• Derivation of free cash flow
• Checking model results and redefining forecast
• Reasonableness of forecast and results

Exercise: adding free cash flow to the model

Cost of Capital

• Alternative theories – bonds and arbitrage pricing theory
• Capital Asset Pricing Model constituents
• Discussion and agreement on WACC inputs
• Asset and equity betas and risk
• Mathematical derivation

Exercise: deriving beta from data and adding WACC to case

Initial Equity Valuation

• Methods of adding terminal value
• Producing an equity valuation
• Comparison to existing share price

Exercise: adding equity valuation to the template

Questions and Answers

 

---

Day Three

Risk, Targeting and Other Layers

Alternative Cash Flow Valuation

• Time varying WACC
• Adjustments to value
• Adjusted present value (APV) method
• Existence and addition of financial and operational synergies
• Reconciliation to economic profit

Exercise: adding APV to the template

Risk and Sensitivity

• Understanding of risk and multiple answers
• Sensitivity and matrices
• Creating sensitivity graphs
• Scenario planning based on economic climate
• Alternative scenario methods

Exercise: testing the models with different sensitivity and risk matrices 

Optimum Structure and Targeting

• Achieving a target valuation
• Goal seek method
• Testing valuation with leverage and structure

Exercise: targeting the case to improve results 

Management Reporting and Model Review

• Management reporting and summary
• Exercise: adding reporting to the valuation model
• Suggestions for other improvements to the valuation model
• Final audit
• Help and user assistance
• Securing and protection
• Documentation and maintenance

Questions and Answers