Excel financial modelling training course – 3 days
Day 1 – core model build up
Delegates complete their own Excel 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
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. Delegates add to their 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. Delegates use their model to forecast a balance sheet for the case study.
- 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.
Day 2 – working with a model
Delegates look at how to, for example, use the model to analyse a new and revised deal structure, value a business or determine debt capacity. Again, delegates finish the course with a detailed record of their own modelling work plus refresher notes in a slide pack provided by the course director.
- Developing deal structure – 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
- Determining the equity gap
- The impact of equity rollover
- The impact on the model: calculating goodwill and the pro-forma balance sheet
Modelling. Participants develop their own deal structure for a transaction conducted by the case business.
- Valuation and its link with deal structure
- Absolute vs. relative valuation techniques
- Defining and refining firm value: enterprise vs. equity value
- What is debt free cash free? What’s the link to deal structure?
- Relative valuation – typical valuation metrics
- Which multiples should we use?
- What are the pros and cons of different multiples?
Case work. Relative valuation of a bid target.
- Determining debt capacity and structuring debt
- Clear, simple and concise explanation of different 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. Participants develop a debt structure for a case study and start to flex the structure within given constraints. How much debt could the business support? How big a target could it contemplate acquiring? What impact does changing the debt structure have on debt capacity?
Day 3 – determining outputs, scenario analysis, Excel modelling help, Excel modelling best practice
- Defining key outputs
- 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 model - something that contains key outputs and credit statistics and is quickly and easily readable.
- Scenario analysis
- Modelling scenarios
- Building a model framework that will accommodate multiple scenarios
- Instant scenario switching with drop down boxes
- How can we stress test the model?
Modelling. Delegates vary their model so that it can accommodate multiple scenarios.
- 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”
Class discussion. Good modelling practice.
- Other modelling topics
- Modelling revolving credit facilities and more complicated debt structures
- Modelling debt instruments
- Modelling tax losses
- Using conditional formatting to, for example, highlight covenant breaches
- Modelling returns to debt and equity providers
- Time spent on these topics depends on the extent of participants’ interest
For further details regarding our financial modelling training courses, please click here: