Financial Modelling School: Module 2 - Advanced Financial Modelling in Excel

Day 1 and Day 2

Session 1 – Revision of Principles of Financial Modelling

• Best financial modelling practice
• Overall structure of the model
• The need for flexibility, and the required model structure to accommodate change
• Separation of inputs, calculations and outputs
• Use of flags for control of timing factors
• Multi-sheet formulas and nested IF statements
• Switches to allow options to be selected, and use of choice boxes
• Use of the corkscrew technique
• Check balances and error controls, use of conditional formatting
• Ease of hand-over to / use by another user
• Exercise – creating a cashflow from an assumptions input sheet with built-in flexibility

Session 2 – Review of Financial Functions in Excel

• Revision of financial mathematics and discounted cashflow principles
• Review of the most commonly-used financial functions:
  • PV, FV and financial maths functions
• Statistical functions in Excel
• Depreciation functions in Excel
• Date and Time functions in Excel
• Logical functions – using TRUE, FALSE, IF, AND & OR in combination
• Lookup and Reference formulae: Lookups, OFFSET and MATCH formulae
• Using INDEX and MATCH together
• Error trapping functions
• Exercise – use the newly-learned functions to analyse data flexibly

Session 3 - Modelling Taxes

• Distinction between value-added taxes, customs duties and corporate tax on profits
• Recognition of revenue for tax purposes
• Tax treatment of costs
• Non-deductible costs
• Interest deductibility restrictions
• Capital allowances and depreciation
• Tax loss carry-forwards and how to model them
• Restrictions on use of tax losses
• Example - Review of an example of tax modelling for an investment project
• Exercise – add corporate tax costs and payments into a pre-prepared income statement and cash flow model

Session 4 – More Advanced Cashflow Analysis

• Brief revision of:
  • Time value of money
  • WACC
  • Capital Asset Pricing Model
• Concept of funding from sequential sources
• Modelling debt drawdowns, headroom limits and constraints
• Modelling debt repayments in a hierarchy
• Dividend distributions under constraints
• Cash flow metrics: NPV, IRR, XIRR, XNPV, MIRR
• Exercise – create a cashflow forecast drawing multiple sources of funding, repaying out of future cashflows and calculating returns

Session 5 – Modelling the Balance Sheet

• Link between cashflow and income statement
• Key balance sheet items and the assumptions required to be made
• Non-cash items: depreciation, deferred tax
• Use of existing figures & opening balance sheets
• Ensuring the result always balances and always makes sense
• Exercise – create a balance sheet from cashflow and income statement figures, with flexible assumptions

Session 6 - Model Auditing

• Use of the formula auditing toolbar
• Checks, totals and error reporting
• Logic trees & flowcharts
• Non-Excel tools available
• Exercise – from a given model, audit and uncover errors and inconsistencies

Session 7 - Using the Model for Valuations

• Valuation of project / enterprise and valuation of equity:
  • Asset-based valuations
  • Cash flow valuation techniques:
    • Discounted cash flow
    • Building in synergies for M&A
    • Discounted dividend model
    • Gordon’s growth model
  • Valuation using comparable measures:
    • Price – Earnings Ratios
    • Dividend Yield Method
    • Transaction Comparables: Exit multiples
• Exercise – from a given cash flow and balance sheet, calculate valuations on different bases

Session 8 - Creating Simple Macros

• What is a macro?
• How macros help in financial modelling
• Best practice in creating and using macros
• Use of range names
• Documentation of macros
• Exercise – creating a macro without using VBA

Session 9 – Wrap-Up

• Overall review
• Key points to re-iterate
• Introduction to further reading and exercises

Final questions and issues to discuss