LBO Modeling [PDF]

Zitiervorschau

se



Co ur

Public Course

AM T

Pu

bli c

LBO Modeling

Scan to access your A

online class

© AMT Training 2008–2021

AM T

Pu

bli c

Co ur

se



© AMT Training 2008–2021

B



Public Courses

For over 20 years, we have been providing financial modeling and valuation training to investment banks, private equity firms, business schools and management consultancies.

Co ur

se

• we have trained over 70,000* finance professionals globally • our trainers are ex-investment bankers and other finance professionals • we turn ‘know how’ into ‘can do’, using practical application and real life examples • our training is fast paced, information intensive and hands on • we make number crunching, fun, lively and relevant to your work

bli c

*Figures taken from 2012 to 2017

Pu

Once you’ve attended our courses, you’ll be able to build financial models with ease, work through financing options with colleagues, structure transactions and deliver pitches confidently.

AM T

We offer open enrolment public courses (non-exam) that run throughout the year in our global training centres. Frankfurt | Hong Kong | London | New York | Singapore

C

For further information on our public courses, please visit amttraining.com/public-courses

© AMT Training 2008–2021



Contents

Edition 2021

AM T

Pu

bli c

Co ur

se

LBO Short Form Modeling1

Copyright Adkins Matchett & Toy Limited (“AMT”) 2008–2021. All rights reserved

Adkins Matchett & Toy Limited is part of Wilmington plc.

AMT owns legally and beneficially all of the

Intellectual Property Rights in the content of this document.

No reproduction, copy or transmission

whatsoever of any part of the document may be made without prior written permission. Please be advised AMT will vigorously

Disclaimer

Whilst every effort has been made to ensure accuracy regarding the content of these slides/notes, Adkins Matchett & Toy cannot be held responsible in any way for consequences arising from the information given. No decision should be taken on the basis of information included in the slides/notes without reference to specialist advice.

prosecute any unauthorised use.

© AMT Training 2008–2021

D



Bookstore

We have a range of books, shortcut guides and interactive workbooks to help develop your skills and support for your career.

Co ur

se

Bestsellers

Our Crunch The Numbers series describe key financial topics in a succinct fashion so you have access to the information wherever and whenever you need it. Essential Series

Ultimate Set

A set of 3 lightweight A5 sized books covering the following topics:

• • • • • •

• Accounting Fundamentals • Modeling • Valuation

bli c

A set of 6 lightweight A5 sized books covering the following topics:

AM T

Pu

Accounting Fundamentals Modeling Valuation Advanced Accounting Power Modeling (Excel 2007/2010) Merger Modeling and Analysis

Find out more by visiting www.amttraining.com

Expert Series

A set of 3 lightweight A5 sized books covering the following topics: • Advanced Accounting • Power Modeling (Excel 2007/2010) • Merger Modeling and Analysis

E

© AMT Training 2008–2021

AM T

Pu

bli c

Co ur

se



© AMT Training 2008–2021

F

se

LBO Short Form Modeling

AM T

Pu

bli c

Co ur

LBO Short Form Modeling

1

© AMT Training 2008–2021

AM T

Pu

bli c

Co ur

se

LBO Short Form Modeling

© AMT Training 2008–2021

2

LBO Short Form Modeling

Summary

LBO overview LBO valuation LBO short form model (lite) LBO financing

AM T

• • • •

Pu

bli c

Co ur

se

LBO analysis fundamentals

2

3

© AMT Training 2008–2021

LBO Short Form Modeling

Pu

bli c

Co ur

se

LBO overview

What is a leveraged buyout? Overview

Characteristics

AM T

Objective

Financial buyers purchase assets, with the objective to sell them in the short to medium term, for a substantial profit

Transaction is typically highly leveraged, hence LBO Returns driven by deleveraging and improving operating performance Investment horizon 3 to 5 years 4

© AMT Training 2008–2021

4

LBO Short Form Modeling

Impact of high leverage

High leverage gives additional equity returns but at an additional risk

Advantages

Disadvantages

Fiscal

Interest expense is generally a taxdeductible expense

Higher fixed interest costs increase

Volatility earnings and cash flow volatility

se

Equity returns can be enhanced by high leverage

Default risk

Higher leverage results in higher default risk

Co ur

Returns

Reduced Less strategic and financial flexibility flexibility due to stressed balance sheet

High leverage increases default risk, Discipline forcing business efficiency

Pu

bli c

5

Drivers of the LBO market

AM T

• Asset valuations

– When companies are highly valued, it is harder to invest today and make a high return over the investment period

• Amount of private equity funding available – PE firms are more active in the market, and may pay higher prices, when investors are willing to contribute more to funds

• Availability of credit

– The appetite of debt investors drives the leverage available for transactions. This in turn can drive equity returns higher

• Interest rates – Lower interest rates mean companies can support higher levels of debt

6

5

© AMT Training 2008–2021

LBO Short Form Modeling

LBO valuation

LBO analysis The steps

Pu

bli c

Co ur

se

How to turn an LBO analysis into a valuation method

AM T

• Calculate offer enterprise and equity value – Frequently done using an EBITDA multiple

• Sources and uses of funds at entry

– Identify the potential financing structure for the transaction

• Forecast operating performance

– Forecast cash flows available for debt repayment – Enables estimation of the amount of debt at exit

• Sources and uses of funds at exit – Estimated sale price and debt at exit – Estimate exit equity value

• Calculate the returns to investors

– Calculate the annualized ROE using an internal rate of return (IRR) calculation

8

© AMT Training 2008–2021

6

LBO Short Form Modeling

LBO analysis Example

Answer

Assumptions:

EBITDA EV Debt Equity

Case 2 Improve margins too

7.0x

7.0x

7.0x

se

EBITDA multiple

Case 1 Repay debt

100

100

120

700

700

840

490

350

350

210

350

490

13.6%

23.6%

Co ur

– A PE firm purchases Rex Co for 7.0x its EBITDA of 100 – The deal is financed with 30% equity and 70% debt – Exit year 4, no multiple expansion – Case 1 – repay debt down to 350 – Case 2 – improve EBITDA to 120 and repay debt down to 350

Entry

ROE / IRR

ROE / IRR

LBO analysis Drivers of return

Pu

bli c

9

AM T

• Debt repayment

– Enterprise value stays constant, but equity value rises as debt is repaid

• Margin enhancement (cost cutting and efficiency improvements) – Enterprise value increases, debt stays constant or maybe repaid and equity value rises

• Acquisition and sale multiples are assumed constant (no multiple expansion) – Entry and exit multiples are constant

10

7

© AMT Training 2008–2021

LBO Short Form Modeling

LBO analysis as a valuation method

LBO valuation indicates the maximum price a financial buyer can pay

Assume offer price

se

The offer Enterprise value (EV) is calculated

Adjust offer price to reach minimum IRR for financial sponsor

• EV / EBITDA typically used

Co ur

Sponsor returns measured

The higher the offer value, the lower the IRR for the financial sponsor

• IRR • Money multiple

Pu

bli c

11

Impact of entry value on investor returns As entry price increases, investor returns decrease

Exit analysis

AM T

Entry analysis Entry price

Low

Medium

High

Entry price

Low

Medium

High

Entry multiple

6.0x

7.0x

8.0x

Exit multiple (1)

7.0x

7.0x

7.0x

EBITDA (Year 0)

200

200

200

EBITDA (Year 3) (2)

220

220

220

5.0x

5.0x

5.0x

800

800

800

Enterprise value Debt / EBITDA

Enterprise value Debt value (3)

Debt financing

Equity value

Equity financing

IRR

As the price paid at entry increases, it is unlikely that the amount of debt available will increase. As a result the investor needs to increase their amount of equity financing at entry

At exit, the IRR of the investment is driven by the exit enterprise value and the equity financing at entry. The higher the entry equity financing the lower the investment IRR

(1) (2) (3)

Exit multiple benchmarked against trading and transaction multiples Exit EBITDA increase driven by sales growth and efficiency improvements Cash generation and debt repayment not affected by price paid at entry 12

© AMT Training 2008–2021

8

LBO Short Form Modeling

What makes a target firm a good LBO candidate? • •

Asset at ‘reasonable’ price Realistic exit route

Price Cash • •

• •

Growth

Stable, predictable cash flows support leverage Good cash conversion ratio Limited investment required

Co ur

•

Opportunity for operational improvements Good management team Strong market position

se

•

AM T

Exit routes

Pu

bli c

13

Sale to strategic buyer (‘trade sale’)

Initial Public Offering (IPO)

• Typically 100% is sold • Sold post turnaround initiatives implemented by the financial sponsors • Strategic buyers can typically pay for synergies

• Often less than 50% is sold • Often run as part of a ‘dual track’ process where an M&A deal is pursued at the same time • Does not allow liquidation of the control premium

Recapitalization • Business is refinanced and re-leveraged • Allows partial cash-out for the equity investors via special dividend

Secondary / Tertiary exit • Business is sold to another financial sponsor

14

9

© AMT Training 2008–2021

LBO Short Form Modeling

LBO short form model (lite)

Pu

bli c

Co ur

se

Simple operating forecast Basic financing structure

LBO short form model output Key outputs

AM T

• How much debt can the target business support? – A given set of operational forecast will determine the cash flows available to repay debt, hence identifying the maximum debt capacity

• What is the likely capital structure for the transaction? – Having established the total debt capacity, the financing instruments used to fund the deal need to be identified and quantified

• What is the maximum price that the buyer might be willing to pay? – Identifying a minimum internal rate of return (IRR) for the equity investors will establish the maximum price that can be paid

16

© AMT Training 2008–2021

10

LBO Short Form Modeling

LBO short form model output

Co ur

se

The financial investor will normally target a return of between 20% and 25%

Pu

bli c

17

LBO short form model steps

AM T

Step 1: Calculate offer enterprise and equity value Step 2: Sources and uses of funds at entry Step 3: Forecast operating performance – – – – –

Operating forecast Cash flow statement Debt schedule Wiring up interest Debt analysis

Step 4: Sources and uses of funds at exit Step 5: Calculate the returns to investors – Equity returns and MOIC – Sensitivity analysis 18

11

© AMT Training 2008–2021

LBO Short Form Modeling

Circularity settings

Danger! Building a model with the circular switch on and iterations active can result in unintentional circularities!

Model construction

Enable iterative calculations

Circular switch

se

Enable iterative calculations

Off

Off

On

Set to 0

No tick

Co ur

Switch

Financial model analysis

Tick

On

Set to 1

Pu

bli c

19

AM T

Step 1: Calculate offer enterprise and equity value

All outstanding options Latest available shares outstanding

LTM numbers provide the latest available historical benchmark

20

© AMT Training 2008–2021

12

LBO Short Form Modeling

Step 2: Sources and uses of funds at entry The table of sources and uses of funds shows the capital structure for the transaction

• •

se

– The uses of funds are the purchase price, the refinanced debt and the fees – The sources of funds are made up of the financing of the deal (debt and equity) – The amount of debt financing depends on the cash flow generation of the business, and on the availability of credit: Start with a preliminary assumption on the amount of senior and subordinate debt The % of total calculation and the D / EBITDA multiple can be used as sanity-checks

•

Co ur

– The equity contribution is the plug that makes the table balance – The interest rate on debt can be calculated at this stage Floating rate debt is calculated as a margin over the reference rate

Pu

bli c

21

AM T

Step 2: Sources and uses of funds at entry

22

13

© AMT Training 2008–2021

LBO Short Form Modeling

Step 3: Forecast operating performance

The private equity house / management will prepare an operational forecast

Balance sheet

• Last twelve months (LTM) earnings are an important historical benchmark • Operating improvements (e.g. profitability increases) are often included in the forecasts • The interest calculation is left blank at this stage

• A full balance sheet forecast is not necessary • Only the operating items that have a material cash flow impact are needed • Off-balance sheet financing might have to be considered (e.g. pension deficits) • Improvements to OWC ratios and ‘capex holidays’ (in the early years post deal) should be considered

Co ur

se

Income statement

Pu

bli c

23

Step 3: Forecast operating performance

AM T

Build operating model forecast

Operating assumptions drive operating model

24

© AMT Training 2008–2021

14

LBO Short Form Modeling

Step 3: Forecast operating performance Cash flow statement

Co ur

se

• To establish the debt capacity of the target business, the cash flow available for debt repayment has to be quantified • The operations generate cash flows, which are used to make operating investments (working capital, capex, etc.) and to pay tax and interest. Any excess cash will be used to repay debt • The interest expense lines should be left blank at this stage

Pu

bli c

25

Step 3: Forecast operating performance Debt schedule

AM T

• The debt schedule calculates the ending balance of the debt for each year. The initial amounts are anchored to the sources of funds • The repayment line is to be left blank, until the cash flow statement is complete • Interest should be calculated on average balances

26

15

© AMT Training 2008–2021

LBO Short Form Modeling

Step 3: Forecast operating performance Debt repayment calculation

Co ur

se

• The debt repayments can now be calculated • MIN functions must be used to avoid over-repaying • The senior debt takes priority over the subordinated debt • Once calculated in the cash flow statement, link the repayments to the debt schedule

Pu

bli c

27

Step 3: Forecast operating performance Wire up interest: cash flow statement and income statement

AM T

• Wiring up the interest into the income statement and cash flow statement creates a circularity • At this stage, the iteration box in Excel's settings must be switched on • The interest calculations must be linked into the cash flow statement first, using an IF statement that references the circularity switch • Lastly the interest can be linked into the income statement

28

© AMT Training 2008–2021

16

LBO Short Form Modeling

Debt analysis Ratios

Co ur

se

• Leverage and coverage ratios should be calculated • Debt ratios are used to analyse the development of the financial structure in time, and are also useful to compare this transaction to other deals • Debt ratios are used to establish covenants designed to protect the lenders • The IFERROR function should be used to prevent the ratio calculations showing errors

Debt analysis

AM T

Average life

Pu

bli c

29

• The repayment of the senior debt is not ‘straight line’. The average life calculates the maturity of an equivalent ‘bullet’ loan • Each repayment is multiplied by the year number and the sum of the weighted repayments is then divided by the original loan amount • The = SUMPRODUCT (range of years,range of repayments) function simplifies this calculation

30

17

© AMT Training 2008–2021

LBO Short Form Modeling

Debt analysis Repayment year

Co ur

se

• Term A amortizes over 7 years, term B must be repaid in year 8 and term C in year 9 • If the debt is not repaid in full before its maturity, the firm is taking refinancing risk • The subordinated debt in this model has a 10-year maturity • The COUNTIF function will check if the debt is repaid timely

Pu

bli c

31

Step 4: Sources and uses of funds at exit Equity value calculation

AM T

• The enterprise value at the end of each forecast year can be calculated by multiplying the assumption for the exit EV / EBITDA multiple by the EBITDA forecast • The equity value is calculated at the end of each forecast year using the forecast EV and the bridge from EV to equity value for that date

32

© AMT Training 2008–2021

18

LBO Short Form Modeling

Step 5: Calculate the returns to investors: IRR • The internal rate of return for equity holders depends on the initial investment, and on the value of the equity at exit

se

– Use the cash flows to equity holder’s line to show these values

Co ur

• The IRR is calculated using the IRR function in Excel • The XIRR function can also be used if you wish to specify the dates of the cash flows

The negative cash flow in year 0 is the initial equity investment (from the sources of funds). For years 1 to 10, an IF statement is used to show a positive cash flow in the exit year

Pu

bli c

33

Step 5: Calculate the returns to investors: MOIC

AM T

• Money on money or multiple on invested capital (MOIC), is one of the most fundamental performance measurements for private fund investing • Sometimes called TVPI (total value to paid-in) or the investment multiple, MOIC is calculated by dividing the sum of a fund's realized and unrealized value by the total amount invested

34

19

© AMT Training 2008–2021

LBO Short Form Modeling

Step 5: Calculate the returns to investors Sensitivity analysis

Overview

One input, multiple output data table

Co ur

se

• For a comprehensive analysis of the deal, the model outputs should be sensitized against changes in the main inputs • Build a data table that shows the sensitivity range for the input variable, and the corresponding values for the desired outputs • The offer price or premium paid is one of the inputs that should always be sensitized

AM T

Pu

bli c

35

LBO financing

The financing structure

© AMT Training 2008–2021

20

LBO Short Form Modeling

Setting up the financing structure Sources and uses of funds

Sources

Uses

• Cash

• • • •

• Debt • Equity

se

– Excess cash from target balance sheet

Purchase of targets equity Retire existing target debt Transaction fees and expenses Cash to target balance sheet

Pu

bli c

Co ur

– Working capital requirements

Setting up the financing structure

AM T

• Financial sponsors aim to maximize the amount of debt used in the acquisition, and minimize the equity • This is achieved through: – Creating debt products whose terms are tailored to the needs of debt investors – Tapping into different segments of the credit market – Allocating debt within the corporate structure according to a ‘structural subordination’ logic – Liquidity to operate the business

• Reach out to capital markets and internal credit teams for input on likely financing structure

38

21

© AMT Training 2008–2021

LBO Short Form Modeling

The term structure of LBO financing • Extending the term (maturity) of the financing increases the debt capacity of a business – Borrow against cash flows further and further out in time

se

• For the lender, extending the term increases risk • The term profile of an LBO financing structure is built in order of seniority

Co ur

– The more senior the debt, the earlier the cash flow claim, the shorter the debt maturity

LBO financing

Pu

bli c

39

Sources of funds in detail; term structure

• Term Loans Can be split into tranches (e.g. A, B, C), Maturity 7 years

Senior secured debt

• Second lien Maturity 8 years

Senior unsecured debt

• High yield bonds Maturity 8 years

AM T

Senior secured debt

Subordinated debt Other debt Equity

Maturities are illustrative

• Mezzanine finance Maturity 8 years • Other subordinated debt Maturity 8 years • LBO sponsor, Existing shareholders, Management Targeted return c. 20% IRR 40

© AMT Training 2008–2021

22

LBO Short Form Modeling

Debt instruments General characteristics

• Amount borrowed and principal • Cost: interest – Cash interest vs PIK interest

• Repayment date: maturity date – Bullet repayment vs amortizing – Secured vs unsecured – 1st lien vs 2nd lien

• Priority of payment: seniority

– Senior vs junior; subordinated vs unsubordinated

se

• Security: collateral

Co ur

• Covenants • Op Co. vs. Hold Co. • Other features (convertible, callable, cum-warrants etc)

Pu

bli c

41

Common covenants

Credit ratios act as early warning system; Ratios monitored

Leverage

• EBITDA / Interest expense • (EBITDA – Capex) / Interest expense • FCF / Total debt

• Senior debt / EBITDA • Net debt / EBITDA • Total debt / EBITDA

AM T

Coverage ratios

Whilst these covenants are typically used, remember that EBITDA does not equal FCF

42

23

© AMT Training 2008–2021

LBO Short Form Modeling

Senior secured debt Cost of senior debt

Typically split into tranches

Cost of senior debt

Term

Maturity

Repayment

Spread

A

6 yrs

Amortizing

300

B

7 yrs

Bullet

400

C

8 yrs

Bullet

500

se

– Security – Covenants – Ranking

• Floating interest rate

– Often partly swapped into fixed rate

Co ur

Indicative spread over reference rate

• Lowest cost debt because of lower risk due to:

Pu

bli c

43

Senior secured debt Second lien

AM T

• Helps to extend secured debt and therefore reduces the overall cost of funding • Floating interest rate, bullet repayment • Secured via a second ranking fixed charge – ‘Lien’ is a legal word that means a security charge like a mortgage – The lender only gets what is left over after the first ranking charge has been satisfied (the revolving credit facility and term loans)

• Minor financing source • Investor base is primarily hedge funds

44

© AMT Training 2008–2021

24

LBO Short Form Modeling

Senior secured debt

Establishing the senior debt capacity of the target

Cash flow lending

EBITDA multiple as a proxy

• The maximum debt capacity can be estimated using cash flow projections and some financing assumptions • Cash flow available for debt servicing is calculated

• EBITDA is often used as a quickand-dirty benchmark for cash flow

se

– Debt financing is often quoted as a multiple of EBITDA

• LTM EBITDA used as key reference point • EBITDA measures earnings not cash flow

Co ur

– The cash flow that can be used to repay the principal plus the interest – Equal to the free cash flow (FCF)

– Check cash conversion ratio: FCF / NOPAT

Pu

bli c

45

Senior unsecured debt High yield debt

Advantages / Disadvantages

• High yield debt sold

• Advantages

AM T

Features

– In the public markets via bonds; or – Can be placed via private placement to specific bond investors (under rule 144A)

– More flexible – Less restrictive covenants (incurrence basis) – No amortization

• Typically minimum size of $100m / £100m / €100m

• Disadvantages – Higher cost – Usually cannot be called until after 3 – 5 years

46

25

© AMT Training 2008–2021

LBO Short Form Modeling

Subordinated debt Mezzanine debt

Features

Advantages / Disadvantages

• Interest may be PIK • Sometimes with warrants attached • Return measured as an internal rate of return

• Advantages

se

• Disadvantages:

– Significantly higher cost – Dilution of equity returns

Co ur

– a blend of the return on the loan plus the value of the warrants – typically mid-teens, but will vary through the cycle

– Few covenants – Maximizes leverage – PIK allows cash to be conserved

•

Via warrants

Pu

bli c

47

Equity

The equity contribution is split between two components

AM T

• 25% – 40% of total capital (1) • Split is primarily driven by the need to incentivize management – Preferred stock (or shareholder / vendor loan notes) • •

Institutional investors Usually PIK dividends

– Common stock • •

Institutional investors and management Often pays no dividends (prevented by debt covenants)

• Target IRR for institutional investors c. 20% (1)

(1) Market currently sees deals with equity up to 50% and target IRR at 15 to 20% 48

© AMT Training 2008–2021

26

LBO Short Form Modeling

Structural subordination Debt push down

• Debt investors want to be as close as possible to the operational cash flows • Prefer to lend to operating companies if possible • If not possible will lend to holding companies which receive dividends from the operational business • Equity investors will invest at the parent company level

Parent company • Sponsor equity • Management equity

se

Holding company • Subordinated debt

Co ur

Operating company • Senior unsecured debt • Senior secured debt

AM T

Pu

bli c

49

LBO overview Appendix

27

© AMT Training 2008–2021

LBO Short Form Modeling

Solution: Impact of entry value on investor returns As entry price increases, investor returns decrease

Entry analysis

Exit analysis

Entry price

Low

Medium

High

Entry price

Low

Medium

High

Entry multiple

6.0x

7.0x

8.0x

Exit multiple (1)

7.0x

7.0x

7.0x

EBITDA (Year 0)

200

200

200

EBITDA (Year 3) (2)

220

220

220

Enterprise value

1,200

1,400

1,600

1,540

1,540

1,540

Debt / EBITDA

5.0x

5.0x

5.0x

Debt value (3)

800

800

800

Debt financing

1,000

1,000

1,000

Equity value

740

740

740

200

400

600

54.7%

22.8%

7.2%

As the price paid at entry increases, it is unlikely that the amount of debt available will increase. As a result the investor needs to increase their amount of equity financing at entry (1) (2) (3)

se

IRR

Co ur

Equity financing

Enterprise value

At exit, the IRR of the investment is driven by the exit enterprise value and the equity financing at entry. The higher the entry equity financing the lower the investment IRR

Exit multiple benchmarked against trading and transaction multiples Exit EBITDA increase driven by sales growth and efficiency improvements Cash generation and debt repayment not affected by price paid at entry

AM T

Pu

bli c

51

London | New York | Hong Kong

52

Please note: All materials are the intellectual property of AMT Training.

© AMT Training 2008–2021

Please visit us on: www.amttraining.com

28

LBO Short Form Modeling

Contents

Pu

bli c

Co ur

se

LBO short form model

– – – – – –

AM T

• LBO short form model overview • Short form model steps

Sources and uses of funds at entry Operating scenarios Cash sweep modeling Debt ratios Sources and uses of funds at exit Disaggregating returns

• Appendix: LBO Financing

2

29

© AMT Training 2008–2021

LBO Short Form Modeling

Pu

bli c

Co ur

se

LBO short form model overview

Short form or long form analysis? Long form

• Preliminary assessment of the maximum price that a financial buyer can pay for the target • Typically used for an initial analysis (e.g. pitching) • Can use a simplified or more complex financing structure • Only key items are forecasted (the model does not have complete financial statements)

• Full and detailed analysis of

AM T

Short form

– Maximum price that a financial buyer can pay – Deal financing structure – Investors returns – Likely covenants structure

• Typically used for live deals • Complex and time-consuming

4

© AMT Training 2008–2021

30

LBO Short Form Modeling

LBO analysis The steps

• Calculate offer enterprise and equity value – Frequently done using an EBITDA multiple

• Sources and uses of funds at entry

– Identify the potential financing structure for the transaction – Forecast cash flows available for debt repayment – Enables estimation of the amount of debt at exit – Estimated sale price and debt at exit – Estimate exit equity value

• Calculate the returns to investors

Co ur

• Sources and uses of funds at exit

se

• Forecast operating performance

– Calculate the annualized ROE using an internal rate of return (IRR) calculation

AM T

Pu

bli c

5

LBO short form model

Operating forecast scenarios Detailed financing structure

31

© AMT Training 2008–2021

LBO Short Form Modeling

LBO short form model output Key outputs

• How much debt can the target business support? – A given set of operational forecast will determine the cash flows available to repay debt, hence identifying the maximum debt capacity

• What is the likely capital structure for the transaction?

se

– Having established the total debt capacity, the financing instruments used to fund the deal need to be identified and quantified

• What is the maximum price that the buyer might be willing to pay?

Co ur

– Identifying a minimum internal rate of return (IRR) for the equity investors will establish the maximum price that can be paid

Pu

bli c

7

LBO short form model steps

AM T

Step 1: Calculate offer enterprise and equity value Step 2: Sources and uses of funds at entry Step 3: Forecast operating performance – – – – –

Operating forecast scenarios Cash flow statement Detailed debt schedule Cash sweep modeling Debt analysis

Step 4: Sources and uses of funds at exit Step 5: Calculate the returns to investors – Equity returns and MOIC – Disaggregating returns 8

© AMT Training 2008–2021

32

LBO Short Form Modeling

Circularity settings

Danger! Building a model with the circular switch on and iterations active can result in unintentional circularities!

Model construction

Set to 0

Enable iterative calculations

Circular switch

se

Off

Enable iterative calculations

On

Off

On

Co ur

Switch

Financial model analysis

Tick

No tick

Set to 1

Pu

bli c

9

Step 1: Calculate offer enterprise and equity value

AM T

• Offer price assumption may be based on a % offer premium above the stock market price, or on a multiple of earnings – Public deals are likely to be priced using a premium – Private deals will be priced based on an EV multiple – A ‘switch’ cell can be used to switch between these two methods

• Up-to-date financial information for the target should be used – Number of shares outstanding and dilutive instruments (e.g. options) – Last twelve month’s earnings

10

33

© AMT Training 2008–2021

LBO Short Form Modeling

Step 1: Calculate offer enterprise and equity value

se

Switch cell can be used to change between premium and multiple driven pricing

Co ur

IF function used to change valuation calculation

Conditional formatting is used to grey out unused sections of the model

Pu

bli c

11

Step 2: Sources and uses of funds at entry

AM T

The table of sources and uses of funds shows the capital structure for the transaction – The uses of funds are the purchase price, the refinanced debt and the fees – The sources of funds are made up of the financing of the deal (debt and equity) – The amount of debt financing depends on the cash flow generation of the business, and on the availability of credit: • •

Start with a preliminary assumption on the amount of senior and subordinate debt The % of total calculation and the D / EBITDA multiple can be used as sanity-checks

– The equity contribution is the plug that makes the table balance – The interest rate on debt can be calculated at this stage •

Floating rate debt interest is calculated using a margin over the reference rate

12

© AMT Training 2008–2021

34

LBO Short Form Modeling

Co ur

se

Step 2: Sources and uses of funds at entry

Pu

bli c

13

Step 2: Sources and uses of funds at entry Why have two types of equity?

AM T

• If only one type of equity is used, management may be incentivised to induce a fire sale • By using two types of equity, the institutional investor ensures it makes a minimum return before the management benefit from their investment – Management would not be incentivised to induce a fire case

14

35

© AMT Training 2008–2021

LBO Short Form Modeling

Step 2: Sources and uses of funds at entry Multiple financing scenarios

Multiple capital structures can be incorporated into an LBO model when considering different financing scenarios

Co ur

se

Enter as many capital structure choices as you need. This example shows four capital structures

Use INDEX to return the specified choice.

Pu

bli c

15

Step 3: Forecast operating performance

The private equity house / management will prepare an operational forecast

Balance sheet

• Last twelve months (LTM) earnings are an important historical benchmark • Operating improvements (e.g. profitability increases) are often included in the forecasts • The interest calculation is left blank at this stage

• A full balance sheet forecast is not necessary • Only the operating items that have a material cash flow impact are needed • Off-balance sheet financing might have to be considered (e.g. pension deficits) • Improvements to OWC ratios and ‘capex holidays’ (in the early years post deal) should be considered

AM T

Income statement

16

© AMT Training 2008–2021

36

LBO Short Form Modeling

Step 3: Forecast operating performance Build operating model forecast

CHOOSE OFFSET INDEX MATCH

Co ur

– – – –

se

• Operating scenarios allow assumptions and methodologies to be changed quickly in models • There are various switch functions that allow this to be set up. The main ones are:

Pu

bli c

17

Step 3: Forecast operating performance

AM T

Build operating model forecast

Selected case assumptions drive operating model

18

37

© AMT Training 2008–2021

LBO Short Form Modeling

Step 3: Forecast operating performance Cash flow statement

Co ur

se

• To establish the debt capacity of the target business, the cash flow available for debt repayment must be quantified • The operations generate cash flows, which are used to make operating investments (working capital, capex, etc.) and to pay tax and interest. Any excess cash will be used to repay debt • The interest expense and debt repayment lines should be left blank at this stage

Pu

bli c

19

Step 3: Forecast operating performance Cash sweep definitions

What does it mean?

How is it calculated?

Cash flow available for debt repayment

The total cash generated by the business during the year that is available to repay debt principal

CFO plus CFI plus Any equity cash flows included in CFF

Cash available for debt repayment

The total cash available to repay debt at the end of the period, after holding back any cash for operating purposes

Beginning cash, plus cash flow available for debt repayment, minus minimum cash needed for operations

Mandated repayments

Debt repayments that are required this year

Forecast as part of a cash sweep

Accelerated repayments

Optional and early repayments of debt

Forecast as part of a cash sweep

AM T

Item

20

© AMT Training 2008–2021

38

LBO Short Form Modeling

Step 3: Forecast operating performance The waterfall: Cash sweep with mandated debt repayments Cash flow available for debt repayment

Cash available for debt repayment Mandated repayments of long-term debt

Beginning cash

se

Less operating cash requirement

Revolver

Co ur

Accelerated repayments of long-term debt

Excess cash

Pu

bli c

21

Step 3: Forecast operating performance

AM T

Cash available for debt repayment

The total cash available to repay debt at the end of the period

Cash available for debt repayment

Cash flow available for debt repayment

Beginning cash

Operating cash requirement

22

39

© AMT Training 2008–2021

LBO Short Form Modeling

Step 3: Forecast operating performance Calculating the long-term debt mandated repayments

se

• The mandated repayments are tested against the actual debt balances to check whether they need to be made • Cash is held back to make these payments first

Actual mandated repayment made

Lower of Beginning LT debt balance

Co ur

Mandated LT debt repayment

Pu

bli c

23

Step 3: Forecast operating performance Cash available after mandated debt repayments

AM T

Cash available after mandated debt repayments is calculated by deducting the mandated repayments made from the cash

24

© AMT Training 2008–2021

40

LBO Short Form Modeling

Step 3: Forecast operating performance Calculating the short-term debt requirement

se

The revolver balance is forecast using a BASE analysis Beginning Add Issuance Subtract Repayment Ending

Change in revolver balance

Lower of Beginning revolver balance

Co ur

Cash available after mandated debt repayments

Pu

bli c

25

Step 3: Forecast operating performance Calculating the long-term debt accelerated repayments

AM T

Each tranche of LT debt balance is forecast using a BASE analysis Beginning Subtract mandated repayment Subtract accelerated repayment Ending

Cash available after accelerated repayment of previous layer

Accelerated LT debt repayment

Lower of Beginning debt balance after mandated repayment 26

41

© AMT Training 2008–2021

LBO Short Form Modeling

Excess cash

Excess cash can accumulate once all debt repaid

Calculation

Once all debt is repaid, then excess cash balances can start to accumulate

The excess cash balance will automatically be calculated by the cash flow statement

Co ur

se

Method

Pu

bli c

27

Step 3: Forecast operating performance Calculate interest expense and interest income

AM T

• Senior debt pays cash interest. Since these are amortizing loans (i.e. the principal may be repaid in installments over the forecast period), interest is calculated on the average balance • Bullet repayment loans (e.g. high yield) pay cash interest. Since there is no repayment during our forecast period, interest can be calculated on the beginning balance • PIK interest is paid in kind (rolled up on the balance of the loan) and calculated on the beginning balance

28

© AMT Training 2008–2021

42

LBO Short Form Modeling

Step 3: Forecast operating performance Link up the debt balances to cash flow statement

Co ur

se

• Once the cash sweep is completed the debt balances based on the forecast mandated and accelerated repayments are known and can be summarised • The changes in the debt balances can now be linked up to the cash flow statement

Pu

bli c

29

Step 3: Forecast operating performance Wire up interest: Cash flow statement and income statement

AM T

• Wiring up the interest into the income statement and cash flow statement creates a circularity • At this stage, the iteration box in Excel's settings must be switched on • The interest calculations must be linked into the income statement first, using an IF statement that references the circularity switch • Lastly the interest from the income statement can be linked into the cash flow statement

30

43

© AMT Training 2008–2021

LBO Short Form Modeling

Debt analysis Ratios

Co ur

se

• Leverage and coverage ratios should be calculated • Debt ratios are used to analyze the development of the financial structure in time, and are also useful to compare this transaction to other deals • Debt ratios are used to establish covenants designed to protect the lenders • In some jurisdictions there are limitations on the deductibility of interest (e.g. interest deductibility of no more than 30% of EBITDA)

Debt analysis Average life

Pu

bli c

31

AM T

• The repayment amortizing debt is not ‘straight line’. The average life calculates the maturity of an equivalent ‘bullet’ loan • Each repayment is multiplied by the year number and the sum of the weighted repayments is then divided by the original loan amount • The = SUMPRODUCT (range of years,range of repayments) function simplifies this calculation

32

© AMT Training 2008–2021

44

LBO Short Form Modeling

Debt analysis Repayment year

Co ur

se

• Term A amortizes over 7 years, term B must be repaid in year 8 and term C in year 9 • If the debt is not repaid in full before its maturity, the firm is taking refinancing risk • The subordinated debt in this model has a 10-year maturity • The COUNTIF function will check if the debt is repaid timely

Pu

bli c

33

Step 4: Sources and uses of funds at exit Equity value calculation

AM T

• The enterprise value at the end of each forecast year can be calculated by multiplying the assumption for the exit EV / EBITDA multiple by the EBITDA forecast • The equity value is calculated at the end of each forecast year using the forecast EV and the bridge from EV to equity value for that date

34

45

© AMT Training 2008–2021

LBO Short Form Modeling

Step 5: Calculate the returns to investors: IRR

Co ur

se

• The institutions invested in 100% of the shareholder loans and in part of the common stock • The internal rate of return of the investment depends on 1) the initial investment, and on 2) the value of the investment at exit • The calculation of the value of the investment at exit may need to reflect that the % ownership of the institutional investors could be diluted by the mezzanine warrants

Pu

bli c

35

Calculate the returns to investors: MOIC

AM T

• Money on money or multiple on invested capital (MOIC), is one of the most fundamental performance measurements for private fund investing. • Sometimes called TVPI (total value to paid-in) or the investment multiple, MOIC is calculated by dividing the sum of a fund's realized and unrealized value by the total amount invested

36

© AMT Training 2008–2021

46

LBO Short Form Modeling

Step 5: Calculate the returns to investors: Drivers Drivers of return

• Debt repayment

– Enterprise value stays constant, but equity value rises as debt is repaid

• Margin enhancement (cost cutting and efficiency improvements)

– Enterprise value increases, debt stays constant or maybe repaid and equity value rises

• Multiple expansion

Co ur

se

– Entry and exit multiples are often assumed to be constant (no multiple expansion)

Pu

bli c

37

Step 5: Calculate the returns to investors Sensitivity analysis

– – – – –

AM T

• For a comprehensive analysis of the deal, the model outputs should be sensitized against changes in the main inputs • Key inputs to flex in an LBO model Financing structure Operating scenario Exit date Exit multiple Offer price

• Key outputs

– IRR – MOIC – Debt metrics

38

47

© AMT Training 2008–2021

LBO Short Form Modeling

Step 5: Calculate the returns to investors Sensitivity analysis

Co ur

se

A master and clone cell structure allows data tables to be located on a different sheet to the input cells. These clone cells drive the data tables as well as the rest of the model.

Pu

bli c

39

Step 5: Calculate the returns to investors

AM T

Master and clone driven data tables

40

© AMT Training 2008–2021

48

LBO Short Form Modeling

Cell links Formulae links Data table links

Step 5: Calculate the returns to investors Master and clone driven data tables set up Assumptions sheet

Sensitivity sheet

Master

Rest of model Clone

8.0%

WACC

Link to master

Growth

3.0%

Growth

Link to master

WACC Master - 1

Master

Master +1

Output

Output

Output

Master

Output

Output

Master + 1

Output

Output

Growth

Output

Output

Co ur

Formula Master – 1

Output driven by clones

se

WACC

Output Output

Pu

bli c

41

Step 5: Calculate the returns to investors Master and clone set up for a one input, multi output data table Sensitivity sheet

AM T

Scenarios sheet

Clone

Master

Scenario

Scenario

1

Scenario

Link to master

Formula

Formula

Formula

1

Output

Output

Output

2

Output

Output

Output

3

Output

Output

Output

Cell links Formulae links Data table links Rest of model Output driven by clones Output

Output 42

49

© AMT Training 2008–2021

LBO Short Form Modeling

LBO financing

Pu

bli c

Co ur

se

The financing structure

Setting up the financing structure Sources and uses of funds

Uses

• Cash

• • • •

AM T

Sources

– Excess cash from target balance sheet

• Debt • Equity

© AMT Training 2008–2021

Purchase of targets equity Retire existing target debt Transaction fees and expenses Cash to target balance sheet – Working capital requirements

50

LBO Short Form Modeling

Setting up the financing structure • Financial sponsors aim to maximize the amount of debt used in the acquisition, and minimize the equity • This is achieved through:

se

– Creating debt products whose terms are tailored to the needs of debt investors – Tapping into different segments of the credit market – Allocating debt within the corporate structure according to a ‘structural subordination’ logic – Liquidity to operate the business

Co ur

• Reach out to capital markets and internal credit teams for input on likely financing structure

Pu

bli c

45

The term structure of LBO financing

AM T

• Extending the term (maturity) of the financing increases the debt capacity of a business – Borrow against cash flows further and further out in time

• For the lender, extending the term increases risk • The term profile of an LBO financing structure is built in order of seniority – The more senior the debt, the earlier the cash flow claim, the shorter the debt maturity

46

51

© AMT Training 2008–2021

LBO Short Form Modeling

LBO financing

Sources of funds in detail; term structure Senior secured debt

• Term Loans Can be split into tranches (e.g. A, B, C), Maturity 7 years

Senior secured debt

• Second lien Maturity 8 years

Senior unsecured debt

• High yield bonds Maturity 8 years

Co ur

• Other subordinated debt Maturity 8 years

se

• Mezzanine finance Maturity 8 years

Subordinated debt

Maturities are illustrative

Other debt

• LBO sponsor, Existing shareholders, Management Targeted return c. 20% IRR

Equity

Pu

bli c

47

Debt instruments General characteristics

AM T

• Amount borrowed and principal • Cost: interest – Cash interest vs PIK interest

• Repayment date: maturity date – Bullet repayment vs amortizing

• Security: collateral

– Secured vs unsecured – 1st lien vs 2nd lien

• Priority of payment: seniority

– Senior vs junior; subordinated vs unsubordinated

• Covenants • Op Co. vs. Hold Co. • Other features (convertible, callable, cum-warrants etc)

48

© AMT Training 2008–2021

52

LBO Short Form Modeling

Common covenants

Credit ratios act as early warning system; Ratios monitored

Leverage

• EBITDA / Interest expense • (EBITDA – Capex) / Interest expense • FCF / Total debt

• Senior debt / EBITDA • Net debt / EBITDA • Total debt / EBITDA

se

Coverage ratios

Co ur

Whilst these covenants are typically used, remember that EBITDA does not equal FCF

Pu

bli c

49

Senior secured debt Cost of senior debt

Term A B C

Cost of senior debt

AM T

Typically split into tranches Maturity

Repayment

Spread

6 yrs

Amortizing

300

7 yrs

Bullet

400

8 yrs

Bullet

500

• Lowest cost debt because of lower risk due to: – Security – Covenants – Ranking

Indicative spread over reference rate

• Floating interest rate – Often partly swapped into fixed rate

50

53

© AMT Training 2008–2021

LBO Short Form Modeling

Senior secured debt Second lien

• Helps to extend secured debt and therefore reduces the overall cost of funding • Floating interest rate, bullet repayment • Secured via a second ranking fixed charge

se

– ‘Lien’ is a legal word that means a security charge like a mortgage – The lender only gets what is left over after the first ranking charge has been satisfied (the revolving credit facility and term loans)

Co ur

• Minor financing source • Investor base is primarily hedge funds

Pu

bli c

51

Senior secured debt

Establishing the senior debt capacity of the target

EBITDA multiple as a proxy

• The maximum debt capacity can be estimated using cash flow projections and some financing assumptions • Cash flow available for debt servicing is calculated

• EBITDA is often used as a quickand-dirty benchmark for cash flow

AM T

Cash flow lending

– Debt financing is often quoted as a multiple of EBITDA

• LTM EBITDA used as key reference point • EBITDA measures earnings not cash flow

– The cash flow that can be used to repay the principal plus the interest – Equal to the free cash flow (FCF)

– Check cash conversion ratio: FCF / NOPAT

52

© AMT Training 2008–2021

54

LBO Short Form Modeling

Senior unsecured debt High yield debt

Features

Advantages / Disadvantages

• High yield debt sold

• Advantages – More flexible – Less restrictive covenants (incurrence basis) – No amortization

se

– In the public markets via bonds; or – Can be placed via private placement to specific bond investors (under rule 144A)

• Typically minimum size of $100m / £100m / €100m

• Disadvantages

Co ur

– Higher cost – Usually cannot be called until after 3 – 5 years

Pu

bli c

53

Subordinated debt Mezzanine debt

Advantages / Disadvantages

• Interest may be PIK • Sometimes with warrants attached • Return measured as an internal rate of return

• Advantages

AM T

Features

– Few covenants – Maximizes leverage – PIK allows cash to be conserved

• Disadvantages:

– a blend of the return on the loan plus the value of the warrants – typically mid-teens, but will vary through the cycle

– Significantly higher cost – Dilution of equity returns •

Via warrants

54

55

© AMT Training 2008–2021

LBO Short Form Modeling

Equity

The equity contribution is split between two components

• 25% – 40% of total capital (1) • Split is primarily driven by the need to incentivize management – Preferred stock (or shareholder / vendor loan notes) Institutional investors Usually PIK dividends

– Common stock • •

se

• •

Institutional investors and management Often pays no dividends (prevented by debt covenants)

Co ur

• Target IRR for institutional investors c. 20% (1)

(1) Market currently sees deals with equity up to 50% and target IRR at 15 to 20%

Pu

bli c

55

Structural subordination Debt push down

• Debt investors want to be as close as possible to the operational cash flows • Prefer to lend to operating companies if possible • If not possible will lend to holding companies which receive dividends from the operational business • Equity investors will invest at the parent company level

AM T

Parent company • Sponsor equity • Management equity

Holding company • Subordinated debt

Operating company • Senior unsecured debt • Senior secured debt 56

© AMT Training 2008–2021

56

Co ur

se

LBO Short Form Modeling

London | New York | Hong Kong

Please visit us on: www.amttraining.com

AM T

Pu

bli c

57

Please note: All materials are the intellectual property of AMT Training.

57

© AMT Training 2008–2021

AM T

Pu

bli c

Co ur

se

LBO Short Form Modeling

© AMT Training 2008–2021

58

AM T

Pu

bli c

Co ur

se

LBO Short Form Modeling

G

© AMT Training 2008–2021

bli c

Co ur

se

LBO Short Form Modeling

Asia Pacific Office Units 503-04 5th floor Haleson Building 1 Jubilee Street Central Hong Kong Tel. number: +852 3905 3059 Email: [email protected]

Pu

EMEA 5th floor Whitechapel Building 10 Whitechapel High Street London E1 8QS Office: +44 20 7324 2385 Email: [email protected]

AM T

Americas 31 W. 34th Street Suite #8080 8th floor Manhattan New York NY 10001 Office: +1 347 325-0525 Email: [email protected]

© AMT Training 2008–2021

Social media:

H