Excel financial modelling training course – 3 days

Day 1 of the course: core financial model build up

Course delegates complete their own financial model build up 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 model) plus refresher notes supplied by the lecturer.

• Planning assumptions
  • Obtaining source data
  • Coding inputs
  • Structuring assumptions and anticipating scenario analysis
  • Modelling and formatting best practice
  • Good model structure
  • Good model design

Financial modelling exercise. 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

Financial modelling exercise.  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

Financial modelling exercise.  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

Financial modelling exercise.  Course delegates use their financial model to forecast a balance sheet for the case study.

Day 2 of the financial modelling course: working with a model

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

• Modelling debt
  • Forecasting a simple debt schedule
  • Linking to other statements
  • Tools for resolving circularity
  • Setting debt paydown

Financial modelling exercise.  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

Financial modelling exercise.  Using their model, delegates forecast levered and unlevered free cash flow.

• More advanced Excel exercise: modelling 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

Day 3 of the financial modelling course: determining outputs, scenario analysis, Excel modelling help, Excel modelling best practice

• 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

Financial modelling exercise.  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

Financial modelling exercise.  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?

Financial modelling exercise.  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”

Book the Excel financial modelling course

To book on the Excel financial modelling training program, please click on the red "book course" button below.

Other financial modelling course training

For full details of our Excel training, please see our please see our financial modelling course program.