LBO modelling – 3 day course

Section 1 of the LBO modelling course: cash flows for the core business

• 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
  • Depreciation
  • 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
  • Iterating
  • 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.

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

More advanced course topics: scenario analysis, modelling a new deal, LBO valuation

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

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

Section 3 of the course - key factors in reaching conclusions in LBO modelling: debt and equity structuring

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

Book the LBO modelling course

The LBO modelling training course is next running June 2012. To book on this program, please click on the red "book course" button below.

Other Excel financial modelling course training

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