This training course provides the opportunity for delegates to practise and improve their ability to model LBOs using Excel.
On this course delegates imagine working with a business that is contemplating taking on extra debt as part of an LBO.
LBO modelling course – day 1 of 3
Building the core of an LBO 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 to start 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
- Forecasting depreciation
Modelling. Delegates analyse and forecast fixed assets, depreciation and capital expenditure.
- Completing the balance sheet
- Key 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
- Forecasting a more complex debt structure
- Modelling a debt waterfall
- Using “max”, “min” and “if” functions to model a debt waterfall
Modelling. Delegates forecast a debt pay-down schedule for their case study.
LBO course module 2
Getting to cash flow, modelling a new deal
- 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
- The link to valuation
Modelling. Using their model, delegates forecast levered and unlevered free cash flow.
- 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
- What scenarios make the most sense?
- What inputs should we be flexing?
- How can we structure the model to run those scenarios easily?
- What happens to our outputs as the business is stressed?
- How can we best present the information?
Modelling. 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.
- Structuring a deal – sources & uses of funds
- Introduction to the fundamental principles of deal structuring
- Exploring “sources & uses” – a key learning concept for the course
- 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
- Concentrating on the key levers without getting bogged down in complex models
- The impact on the model: calculating goodwill and the pro-forma balance sheet
Case study. Delegates develop their own deal structure for a transaction conducted by the case business.
LBO course – module 3
More advanced topics, modelling LBO debt and equity structure
- The link to valuation
- 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?
Exercise. 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
- Understanding the nature of different financial instruments and risk profiles
- Modelling waterfall structures
- Estimating and optimising debt capacity
Modelling – debt structure. Delegates develop a debt structure for the 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?
- Key considerations for debt holders – keeping finance providers happy
- Typical covenant tests
- Conditions of default
- Covenant trends
- Trends across different businesses
- Typical tests employed by rating agencies
Exercise. Delegates consider how S&P would rate debt in a real business.
- Structuring equity
- The nature of equity instruments used in buy out structures
- The different risks and rewards accruing to different parties
- Incentivising management
- Key drivers for equity investors
- The impact of loan stock & preference shares
- The impact of mezzanine
- Iterating to optimise rewards to key participants
Case study. Delegates iterate with a “back of the envelope” deal structure to optimise returns.
Download the online outline for the LBO course
Here you can download the online LBO modelling course outline.