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
