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
- 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
- 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.