Financial Modelling School: Module 1 - Best Practice Financial Modelling in Excel

Day 1

Introduction & Course Objectives

• Overview of course objectives
• Review of models and their objectives

Overall Model Structure & Design

• Best financial modelling practice
• Overall structure of the model
• Logic flow within the model
• Separation of inputs, calculations and outputs
• Defining desired outputs
• Setting-up required inputs
• Use of switches to allow option selection
• Use of flags to control timing factors
• Set-up for flexibility
• Consistency in the model
• Accommodating multiple options
• Building assumptions off term sheets or other external inputs
• Using the assumptions sheets as a sign-off document
• Restricting ranges of inputs and validation criteria
• Version control
• Use of the corkscrew technique
• Tracking changes
• Documentation
• Exercise – creating a simple model with an assumptions / input sheet with built-in flexibility

Modelling Techniques for Revenue and Cost Forecasts

• Translating assumptions & inputs into a model forecast:
  • Overall growth
  • Split volume and price
  • Split volume into market growth and market share
  • Top-down market sizing, market share and price
• Build-up of construction or other capital costs
• Correct matching of units
• Modelling pricing & revenue assumptions
• Use of lookup and INDEX/MATCH functions to change expenditure timings
• Building in sensitivities into the model
• Exercises:
  • build-up revenue forecasts with different techniques and granularity levels
  • build capex forecast with flexible timing

Day 2

Inflation / Escalation Factors

• Creating inflation indices
• Controlling start time of inflationary pattern
• Applying multiple rates to different cost & revenue items
• Varying inflation rates over life of the model
• Exercise – model multiple, variable inflation rates, with different starting-points and changing rates over the life of the model

Cash Flow Modelling

• Differences between income statement and cash flow
• Organising cash flow statements
• What is working capital?
• Modelling changes in receivables, inventory, payables
• Income statement items that are not cash: adjustments needed
• Cash items not in the income statement: adjustments needed
• Financing cash flows: changes in debt & equity
• Revolving facilities and their role in cash flow forecasting
• Exercise: create a cash flow model to forecast cash inflows and outflows for a trading business, and drawing on revolving facilities as needed.

Comparing a Model to Previous Versions of the Model

• Separate runs and variation of inputs
• Comparison of actuals to forecast
• Comparing results of different versions of same model
• Reviewing future implications of variances
• Example – from different versions of a modelled forecast, calculate variances and review future assumptions

Day 3

Sensitivity Analysis in a Model

• Stress-testing the model
• Varying inputs to assess effect on results
• Use of built-in sensitivity inputs
• Use of goal seek & solver
• Version control to allow comparison of outputs
• Use of Excel tools to support sensitivity analysis:
  • watch window function
  • data tables
  • scenario manager
• Exercise – from a given model of cash flows, P&L and balance sheet, calculate effect of varying inputs to a given degree, and stress-test model to break-even.

Reporting Outputs

• Design techniques to enable optimisation
• Consolidation techniques:
  • Add-through
  • Sum-through
  • Sum & Group
  • Consolidate function in Excel
• Use of charts & graphics
• Using pivot tables:
  • Pivot table wizard
  • Setting-up tables
  • Changing parameters
  • Analyzing pivot table data
  • Drill-down function
• Exercise – from a given set of outputs, create output tables, pivot tables and consolidations to enable flexible reporting

Advanced Excel Functions

• Advanced financial mathematics – PMT, PPMT, IPMT,NPER
• Summation & counting functions in Excel: COUNT, COUNTA, COUNTIF, AVERAGE, AVERAGEIF, SUMIF, SUMIFS
• Statistical functions in Excel
• Depreciation functions in Excel
• Date and Time functions in Excel
• Lookup and Reference formulae: Lookups, OFFSET and MATCH formulae
• Logical functions – using TRUE, FALSE, IF, AND & OR in combination
• Text functions in Excel: LEFT, RIGHT, MID, LEN and wildcard
• Exercises:
  • 1) model a loan using some of the advanced functions learned
  • 2) summarise messy data without cleaning it, using text functions

Wrap-Up

• Overall review
• Key points to re-iterate
• Brief introduction to further exercises & reading
• Final questions and issues to discuss