Modelling

This Excel modelling training course is designed as a first program for people who would like an opportunity to practise and improve their ability to model cash flows in Excel. The course is conducted in a work shop format, with the emphasis on “learning by doing”. During the program delegates work as a team to create a cash flow model from scratch.

Financial modelling build up – course day 1 of 3

Course delegates start their Excel modelling by building up financial accounts in pre-set “stages”. They save their work in clear steps as they go. At the end of the course they have a record of their own work (each completed stage of their Excel model) plus refresher notes supplied by the lecturer.

Building a financial model

  • Planning assumptions
    • Obtaining source data
    • Coding inputs
    • Structuring assumptions and anticipating scenario analysis
    • Modelling and formatting best practice
    • Good model structure
    • Good model design
Modelling. Delegates are introduced to a case study and a set of financial statements.  Participants use that starting point to begin creating their own model.
  • Starting to forecast the income statement
    • Starting to forecast the P&L from key assumptions
    • How far can we progress?
    • What’s stopping us from continuing?
    • Key drivers for modelling
    • Key ratios driving the forecast
    • Drivers on revenues
    • Drivers on costs
    • Sources of data
Modelling.  Course delegates add to their financial model and forecast out the income statement as far as pre-tax earnings.
  • Modelling fixed assets
    • Forecasting assets
    • Key drivers on asset intensity
    • Capital expenditure
    • Depreciation
    • Forecasting depreciation
Modelling.  Delegates analyse and forecast fixed assets, depreciation and capital expenditure.
  • Completing the balance sheet
    • Drivers for balance sheet items
    • Which creditors can we stretch, and by how much?
    • How quickly can we collect debtors?
    • Forecasting the balance sheet
    • Impacts on cash flow
    • Is growth good?
    • Linking to other statements
    • Balancing the balance sheet
Modelling.  Course delegates use their financial model to forecast a balance sheet for the case study.

Course day 2 – getting to cash flow, modelling a new deal

Course delegates complete their model. Then they start to look at how to, for example, use the financial model to analyse a new and revised deal structure.

Completing a financial model

  • Modelling debt
    • Forecasting a simple debt schedule
    • Linking to other statements
    • Tools for resolving circularity
    • Setting debt paydown
Modelling.  Delegates forecast a debt pay-down schedule for their case study.
  • Cash flow
    • Modelling the cash flow statement
    • Key linkages to other statements
    • Presenting the cash flow statement
    • Forecasting cash flow to equity
    • Forecasting unlevered cash flow
Modelling.  Using their model, delegates forecast levered and unlevered free cash flow.
  • Excel modelling exercise: a new deal
    • Developing a new structure for a new deal the client is contemplating
    • Modelling sources & uses of funds
    • Developing a “first cut” debt structure
    • Calculating refinancing needs
    • The role of working capital and extra cap ex requirements
    • Typical financing and transaction fees
    • The impact on the model: calculating goodwill and the pro-forma balance sheet

Course day 3 – select topics in financial modelling

Determining outputs, scenario analysis, Excel modelling help, Excel modelling best practice

Working with a financial model

  • Modelling detailed debt structures: determining debt capacity and structuring debt
    • High level explanation of different debt instruments, with a focus on how you might actually model more complicated debt instruments
    • Senior debt
    • High-yield debt
    • Mezzanine
    • Payment-in-Kind
    • Understanding the nature of different financial instruments and risk profiles
    • Modellng waterfall structures
    • Estimating and optimising debt capacity
Modelling.  Course delegates look at debt structuring work completed by a major investment bank. Working in teams, can you see what this analyst has done wrong? Delegates work together to do a better job of modelling a complicated debt structure. How can this work be used to test debt capacity for the transaction?
  • Performing scenario and sensitivity analysis in the financial model
    • What scenarios make the most sense?
    • How can we structure the financial model to run those scenarios easily?
    • What happens to our outputs as the model is stressed?
    • How can we best present the information?
    • Instant scenario switching with drop down boxes
Modelling.  Course delegates develop a suite of scenarios for their model, setting the model up so that it contains a full record of scenarios and the user can switch very quickly between them using drop down boxes
  • Defining key outputs in financial modelling
    • What are the most important outputs?
    • How can they be presented clearly?
    • How can we put for example, anticipated sales, capital expenditure and working capital plans into context?
Modelling.  Delegates complete a new sheet within their financial model – something that contains key outputs and credit statistics and is quickly and easily readable.
  • Excel modelling help
    • Delegates are provided with the opportunity to ask for help with particular Excel functions
    • Use of these functions is demonstrated in class and supplemented with exercises which the class works through together
Spreadsheet exercises.  Useful functions in Excel.
  • Lessons in good modelling practice
    • During the course delegates work to create their own models, establishing and observing spreadsheeting best practice as they progress
    • Good modelling techniques are observed throughout the course, discussed in groups and demonstrated during the program
    • At the end their time participants finish with their own modelling work (which they have created following modelling best practice) plus a hard copy and permanent record of “modelling tips”

Download the online outline for the financial modelling course

Here you can download the financial modelling course online outline.