MULTIPLE EXPANSION

• Home
• Archives
• About
• Library
• Join

Training LBO (Model Included)

2017-08-31
1. Introduction

This guide will provide basic instruction on modeling LBOs. Specifically, you will learn:

o How to build a full-featured LBO model

o Some key Excel functions
o Core financial concepts and terminology
o How to model different types of debt

This guide will not serve as:

o An introductory accounting course

o A “from-the-ground-up” Excel course
If this model is too complicated, start with our Easy LBO Guide.
LBO Overview
LBO means “Leveraged BuyOut.” Two main characteristics differentiate LBOs from other types of
acquisitions:
o Instead of a “corporate” buying another “corporate,” e.g., Amazon buying Whole Foods, in a
LBO, a Financial Sponsor (a.k.a. private equity firm) is the buyer
o The sponsor uses a mix of debt (hence “Leveraged”) and equity to buy the company. The
equity is the money that the financial sponsor invests, and sponsors raise debt financing
from banks / the capital markets.
Do sponsors buy the whole company?
In LBOs, the sponsor generally buys a controlling stake in the company. Management and/or the seller
may retain some equity.
Why does a sponsor use debt?
A sponsor uses debt so that they can buy the company with less of their own cash (equity). Over time, as
the company pays down its debt, the equity value will grow as a percentage of the total enterprise value,
thereby increasing the value of the sponsor’s investment.
Total enterprise value (TEV) = equity value + net debt.

If enterprise value is increasing and net debt is shrinking, the sponsor is simultaneously using two levers
to increase their equity value:

1. growing TEV and

2. increasing the percentage of TEV owned by equityholders.
Debt Primers

If you don’t have a decent understanding of the different types of debt used in LBOs, spend ~15 minutes
perusing the guides below. A quick read will give you the background knowledge you need.

o [Link]
o S&P Guide - Leveraged Loans
o [Link]
o S&P Guide - High-Yield Bonds
2. Getting Started
Download the following files:
o Blank Model - you’ll build your own model with this template.
o Completed Model - for your reference. This Excel file includes a tab for each step of the
tutorial. Note: the numbering starts at 3; the first step is the tab labeled “LBO_3” - “3. Filling
in Transaction Assumptions.”

You should try to follow along, step-by-step, building your own model from the blank file.

Our model template is built vertically within a single tab, and is divided into the following sections:
 o Transaction Assumptions
o Income Statement
o Working Capital Schedule
o Balance Sheet
o Statement of Cash Flows
o Debt Schedule
o Interest Expense Schedule
o Credit Metrics
o Returns Calculations

For simpler LBOs, like this one, we prefer a single-tab, vertical format, because it makes it easier to link
between sections and trace dependencies.

The working capital schedule and balance sheet are often unnecessary, but we’re including them here so
you can see how all the pieces fit together. Likewise, the debt schedule and interest expense calculations
are usually bundled together, but since they’re a bit longer here, we split them up.

3. Filling in Transaction Assumptions

At the top of the “LBO” tab, you can find the Transaction Assumptions section. This is where we’re
going to start, and there’s a lot going on, so we’ll go through a brief overview:
o The Sources & Uses section is one of the key parts of any financial model. Quite literally, it
describes what your sources of cash are, and what you’re using them for. In LBOs, your
sources will be a mix of debt and equity financing, and your main use will be purchasing
the target company (“Purchase Equity”)
o The Transaction Assumptions section is where we’ll calculate the transaction value /
equity value. In an acquisition, “transaction value” is equivalent to “total enterprise value”
(TEV). Therefore, equity value = transaction value - net debt. We’re going to assume a cash-
free, debt-free transaction, which means the sponsor is buying the company with no debt
to refinance and no excess cash (there will be a little bit of operating cash).
o The Financing Assumptions section contains financing assumptions for various tranches
of debt (we provide these). We’ll show you how to use these inputs to perform the
financing calculations required in a LBO.
Transaction Value & Equity Value
 1. Link the LTM Adj. EBITDA from the income statement (use 2016A - we’re assuming an
illustrative transaction close at FYE 2016).
2. Calculate the Transaction Value (LTM Adj. EBITDA x Transaction Multiple).
3. Link the “Cash On-Hand” from Sources as the cash balance.
4. Link “Refinance Existing Debt” from Uses as the debt balance.
5. Equity Value = Transaction Value - Debt + Cash
Hint: transaction value and equity value should be the same in this case (cash-free, debt-
free).
Management Rollover
In a management rollover, the management team agrees to “roll over” part of their equity stake as an
investment alongside the financial sponsor. A management rollover is not uncommon, and sponsors like
it, because it keeps the management team even more incentivized. If check size (how much money a
sponsor is able to invest in a single deal) is a concern, a management rollover can help mitigate that.

Here we’re assuming a 10% rollover. This means that the management team owns at least 10% of the
existing equity and agrees to keep 10% invested alongside the sponsor. 10% is probably a bit high, but
not unheard of. Multiply the rollover % by the calculated equity value.

4. Purchase Accounting
We’re using overly simplified purchase accounting, because frankly, purchase accounting isn’t very
important for LBOs.

The core concept of purchase accounting is that the value paid for an asset must be capitalized on the
balance sheet. In the case of an acquisition, the asset being acquired is the company itself and its bundle
of assets and liabilities.

In most acquisitions (including this one), the price paid exceeds the existing book value of net assets.
Therefore, the purchase price is allocated to the net assets by writing them up to fair value.

Example: you buy a manufacturing company for $500mm. The net assets have a book value of $100mm,
so there’s a $400mm delta, but the fair value of the company’s manufacturing PP&E is actually $200mm.
On the balance sheet of the post-acquisition company, the PP&E is written up to $200mm. But that
doesn’t cover the full delta - so what next? Any remaining purchase price is allocated to an indefinite-
lived intangible asset called goodwill (“indefinite-lived” is just an accounting term that means it doesn’t
have a finite life. Theoretically, it could last forever).

Here we’re assuming that the book value of net assets (excluding the existing goodwill) is equal to fair
value, so the entire excess purchase price is allocated to goodwill.

1. Set Total Equity Value equal to the Equity Value you just calculated above.
2. Link the appropriate asset & liability fields from the balance sheet.
o Remember to subtract out the existing goodwill (goodwill functions as the plug; we
don’t care about the prior plug – we’re calculating it again here).

5. Debt Financing
Now we’ll fill out the Debt Financing section below the provided financing assumptions. Here we’ll
calculate the actual amount of debt raised and associated fees.
1. For the Revolver, link to the hard-coded “Revolver Draw” assumption under Sources. The
assumed draw is $0, which is standard practice. It would be unusual to fund the
transaction with the Revolver. The Revolver functions as a safety net, and it is used to fund
any short-term cash shortages.
2. Calculate the $ raised for the Term Loan B, Senior Notes and Subordinated Notes using the
supplied leverage multiples and LTM Adj. EBITDA.
o Simply multiplying the leverage multiple by Adj. EBITDA can result in oddly specific
debt balances (e.g., $179.4mm of senior notes), whereas companies raise round
numbers of debt.
o Use the mround function and the “Minimum Debt Increment” assumption found
under Other Assumptions to get nice, round debt balances for each tranche:
=mround(leverage * EBITDA, minimum debt increment)

3. Calculate the financing fees for each tranche by multiplying the calculated debt balance by
the fee % from the Financing Assumptions table.
o For the revolver fee, multiply the financing fee % by the revolver commitment.

6. Sources & Uses

Now we’ll fill out the Sources & Uses using our work from the preceding sections.
1. Under Sources, link the individual debt tranches from the Debt Financing section.
2. Link Management Rollover from Transaction Assumptions.
3. Under Uses, set Purchase Equity equal to Equity Value from Transaction Assumptions.
4. Link Transaction Expenses from Other Assumptions.
 5. Link the Total Financing Fees from the Debt Financing section.
6. Now the only remaining line item is Sponsor Equity; set it equal to Total Uses - the sum of
all other sources.
Sponsor Equity = Total Uses - Sum(All other sources)

Your Sources & Uses should now balance.

7. Income Statement

Let’s fill out the PF Income Statement. The historical revenue, EBITDA and D&A are already in place, and
we’ve included all the assumptions you need in a memo below the income statement.
 1. Grow Revenue by multiplying the prior year by (1 + growth rate).
2. Calculate projected EBITDA by multiplying margin by Revenue.
3. Calculate D&A with the % of sales.
4. Subtract D&A from EBITDA to arrive at EBIT.
5. Leave Net Interest Expense blank for now, but calculate EBT as EBIT + blank Net Interest
Expense row.
o EBT equals EBIT for now, but we will add Net Interest Expense in the future.
6. Calculate tax expense by multiplying the tax rate found in Other Assumptions by EBT.
7. Calculate Net Income by adding the tax expense (negative) and EBT.

8. Working Capital Schedule

We’re going to use Revenue, COGS and historical working capital to build out the Working Capital
Schedule.

1. Set Sales equal to the Revenue line in the Income Statement.

2. Calculate COGS by multiplying Sales by the COGS memo line in the Income Statement.
3. Link the 2016A working capital line items from the Balance Sheet:
o Net Accounts Receivable
o Inventory
o Other Current Assets
o Accounts Payable
 o Accrued Expenses
o Other Current Liabilities
We’ll use these to calculate the historical ratios, which will be the basis for our
projections.
4. Calculate the historical DSO, DIH, DPO and % of sales metrics. For metrics based on days,
use the provided 360-days cell.
o DSO = Net Accounts Receivable / Sales * 360 Days
o DIH = Inventory / COGS * 360 Days
o DPO = Accounts Payable / COGS * 360 Days
Cash conversion cycle is a common metric for working capital efficiency. Cash
conversion cycle = DIH + DSO - DPO. Time that cash is invested as inventory + time
that the accounts receivable are outstanding - the time that the company can delay
paying its account payables.

5. Once you’ve calculated the historical ratios for 2016A, set the ratios in all the future years
equal to 2016A. This is a simplifying assumption - in a real LBO, it would be important to
diligence all these assumptions and have a detailed view on how/why a company’s
working capital profile might change.
o By holding these ratios (and COGS as % of Sales) constant, we’ve made working
capital a linear function of Sales.
6. Use the projected working capital ratios (held constant) and the projected Sales & COGS to
calculate the projected working capital line items.
o Example: Projected Inventory = DIH / 360 * COGS
7. Calculate Net Working Capital (Current Assets - Current Liabilities) in each year.
8. Calculate the (Increase) Decrease in NWC as: prior year Net Working Capital - current year
Net Working Capital. We do it this way (so that increases are negative), because we’re
going to link this line directly to the Statement of Cash Flows. Increases in NWC are a use of
cash, and decreases are a source of cash.
9. PF Balance Sheet

We’re assuming that the transaction closes at Fiscal Year End 2016. FYE 2016 has already come and
gone, but best practice is to use the latest available balance sheet. For a public company, you’d use the
latest 10-K/10-Q.

The historical 2016A balance sheet that we have here is the company’s standalone balance sheet
(assuming no transaction). We’re going to calculate the PF 2016 balance sheet - what the balance sheet
would look like immediately after closing the transaction.

When preparing a PF balance sheet, incorporate all line items from the Sources & Uses. You can set
balance sheet line items that aren’t affected by the Sources & Uses equal to the standalone balance
sheet.
1. Set all working capital line items equal to 2016A:
o Net Accounts Receivable
o Inventory
o Other Current Assets
o Accounts Payable
o Accrued Expenses
o Other Current Liabilities
2. Set Net PP&E and Other Noncurrent Liabilities equal to 2016A.
 3. The PF Shareholders’ Equity = Sponsor Equity + Management Rollover - Transaction
Expenses. You can completely ignore the historical equity balance.
4. Debt = sum(all sources of debt financing).
o Make sure to include the line for the revolver draw even though it’s 0.
5. Other Noncurrent Assets = 2016A balance + Financing Fees.
o Financing Fees are capitalized and then amortized over the term of the debt
instruments, and best practice is to stick the capitalized fees in Other Noncurrent
Assets.
6. Goodwill = PF Goodwill, which you calculated in the Purchase Accounting section.
7. Set Cash & Cash Equivalents = 2016A cash balance - “Cash On-Hand” + “Fund Cash
Balance.” Since these latter two are both $0, PF cash = 2016A cash.

Your PF balance sheet should now balance.

Getting the balance sheet to balance can be tricky. A good way to think about it is that if the historical
balance sheet balances and the sources and uses section balances, the combination should also balance.
Every line item in sources & uses must be accounted for in the PF balance sheet. The sources are
generally straightforward. Goodwill and any purchase accounting write-ups are the bridge between the
equity purchase price and historical net assets.

10. Statement of Cash Flows

 1. Link Net Income from the income statement. You can ignore the historical years; we only
care about the cash flows going forward.
2. Add back D&A (noncash expense).
3. Link to the (Increase) Decrease in NWC from the Working Capital Schedule.
4. Leave Noncash Interest Expense blank for now.
5. Calculate projected CapEx as a % of Revenue (assumption found in the memo section
below the Income Statement).
6. Add up the Cash from Operating Activities and Levered Free Cash Flow lines.
7. Link the 2016 Ending Cash Balance from the PF 2016 cash from the balance sheet.
8. Set the 2017E Beginning Cash = to the 2016 Ending Cash (in statement of cash flows).
9. Calculate Cash Available for Debt Repayment:
o Add Levered Free Cash Flow.
o Subtract the Minimum Cash Balance (from “Other Assumptions” section).
o The Minimum Cash Balance ensures that a minimum amount of cash remains - the
cash needed for ordinary operations.
10. Leave the Mandatory Amortization and Optional Prepayment lines blank.
11. Add back the Minimum Cash Balance above Ending Cash.
12. Then calculate Ending Cash:
Ending Cash = sum(Cash Available for Debt Repayment, Mandatory Amortization, Optional
Prepayment, Minimum Cash Balance).
We add back the Minimum Cash Balance, because it was never spent. It was just taken out
of Cash Available for Debt Repayment.

13. Carry over these formulas through the rest of the projection period.
Interest Income
Now we’ll calculate Cash Interest Income. Although it’s immaterial, it’s still best practice to include this.
We’re going to use an “if” statement to manage circular references.
Average Cash Balance = if (CIRC = 1, average(Beginning Cash, Ending Cash), Beginning Cash)
The circ is found under “Other Assumptions.”

Calculate Cash Interest Income by multiplying the provided interest rate by the Average Cash Balance
for each year.
11. Debt Schedule
1. To start, link the PF 2016 ending debt balances from the Sources & Uses. Sum them up in
the total line.
2. Calculate the Undrawn Revolver Commitment:
Undrawn revolver commitment = max(0, revolver commitment - the drawn balance)
Include “max(0, “ because it should never be a negative number. You can drag this formula
across.

3. In 2017E - 2024E, link the Cash Available for Debt Repayment from the Statement of Cash
Flows.
Mandatory Amortization
We’ve already included the amortization schedule for you, but generally, term loans are the only debt
instruments with mandatory amortization. Mandatory amortization consists of regular scheduled
principal payments. Senior notes and bonds pay interest every year, but principal is only repaid at
maturity.

Calculating the mandatory amortization is not as simple as multiplying the % by the beginning principal
amount.

Mandatory Amortization = -max(0, min(PF balance * annual schedule %, prior year ending balance))
The “max(0, min(“ clause is a great way to constrain the value of a cell - never falls below 0.
In English, mandatory amortization is the lesser of the scheduled amortization or the remaining balance
at the beginning of the year (the beginning of this year = the end of the prior year). Remember to lock
the PF 2016E debt column when multiplying by the annual amortization %.

Cash Available for Optional Prepayment = Cash Available for Debt Repayment + Total Mandatory
Amortization

Optional Prepayment
Calculating Optional Prepayment can be tricky, especially for the Revolver. Remember that the Revolver
functions as a safety net. Therefore, if Mandatory Amortization exceeds the Cash Available for Debt
Repayment (resulting in negative Cash Available for Optional Prepayment), the company will draw on
the Revolver to make up the gap. On the other hand, if there’s cash left over, the company should pay
down any outstanding Revolver balance.

This is best represented using an “if“ statement:

Revolver Optional Prepayment = if(Cash Available for Optional Prepayment > 0, -max(0, min(last year’s
ending Revolver balance, Cash Available for Optional Prepayment)), -min(0, Cash Available for Optional
Prepayment))

The Optional Prepayment for the other debt tranches is more straightforward. And by stacking debt
tranches like this, we can use one formula to apply to all tranches.

The debt tranches are organized in order of priority – the Revolver gets paid down first, then the Term
Loan B, and then the notes. So the Optional Prepayment for the Term Loan B depends on if there’s
anything left over after paying down the Revolver. The notes have the same relationship with the Term
Loan B, etc.

You can apply this formula to the Optional Prepayment of all remaining tranches (excluding the
Revolver):

Tranche Optional Prepayment = -max(0, min(prior year ending balance + Mandatory Amortization, Cash
Available for Optional Prepayment - all prior Optional Prepayments)))
Let’s explain this. The Optional Prepayment for a given tranche must be the lesser of the remaining
balance (last year’s ending balance + this year’s Mandatory Amortization) and the Cash Available for
Optional Prepayments - all preceding (higher priority) Optional Prepayments this year.

Compare your formula with the completed version and drag across all Optional Prepayment cells.

Ending Debt Balances

Now that we have the Mandatory Amortization & Optional Prepayment for each tranche, we can
calculate the ending debt balances for each year.
1. Go back up to the ending debt balance section and set each 2017 ending balance:
Tranche balance= max(0, prior year ending balance + Mandatory Amortization + Optional
Prepayment).

2. Carry this formula across for all tranches and projected years.
3. Finally, link the Total Mandatory Amortization and Total Optional Prepayments back to the
Statement of Cash Flows.
Note: typically, you can’t repay outstanding notes as soon as you’ve paid down the Term Loan B, as
we’re doing here. Debt investors who buy high-yield bonds generally don’t want to be repaid early
without a fee. If the company is doing well enough to repay the debt early, that means that the company
is less risky, and investors would probably want to keep their capital deployed. Early repayment of
notes is a simplifying assumption, and it’s typical to model LBOs this way. Alternatively, you could
accrue a cash balance and/or refinance the notes.

12. Interest Expense Schedule

We’ve provided an illustrative LIBOR curve. If you’re unsure what this is, or what it’s used for, you
should pause and read some of the debt primers provided at the beginning of this tutorial.

We include the Undrawn Revolver as a debt balance, because the company must pay a small amount of
interest expense for the unused commitment.

For the Interest Rate Schedule, we’ve used a custom number format to achieve the “FIXED” vs. “FLOAT”
formatting. You can google “Excel custom number format” to learn more. Here, where it says “FLOAT,”
the cell value = 1, and where it says “FIXED,” the cell value = 0.

1. Calculate the debt balances. For each tranche, the debt balance should be the average
balance from the beginning to the end of a given year. Use the following formula, similar to
what we did when calculating Cash Interest Income:
Debt balance = if (CIRC = 1, average(prior year balance, this year balance), prior year
balance).
If the circ switch is off, then the interest expense will be calculated using the beginning
debt balance, which removes the circular reference.

2. Calculate the interest rate for each tranche.

o For FIXED tranches, the interest rate should just be the stated rate.
o For FLOAT tranches, the interest rate should be the sum of LIBOR and the stated
rate. We could include more advanced functionality, such as a LIBOR floor, but it
would be immaterial. You can read more about the intricacies of the various debt
instruments in the primers.
3. Calculate interest expense for each tranche by multiplying the debt balance by the
scheduled interest rate.
o When the CIRC switch = 1, interest expense is calculated with the average debt
balance.
4. Next we’re going to calculate the “PIK” interest for the notes. For each year, compare the
number of years elapsed to the PIK term, and if the number of years elapsed is less than or
equal to the PIK term, we’ll include that debt tranche’s interest expense as PIK interest.
o For the first 3 years, the subordinated notes “PIK.”
o Since PIK interest is not paid in cash, it’s deemed part of noncash interest expense,
which is added back in the statement of cash flows.
Although Payment-In-Kind (PIK) interest is uncommon in the current financing
environment, it pops up occasionally, and it’s an important concept to understand.
When notes, or other debt, have a PIK feature, interest expense is not paid in cash
for a number of years. Instead, the interest expense is added to the debt balance, so
the debt balance accrues over time. PIK interest is a way for LBO investors to
manipulate when cash payments occur. An investor might use a debt tranche with
PIK interest expense to layer on additional debt, while keeping the interest expense
manageable in the early years. The LBO investor would be counting on paying down
more senior tranches of debt, alleviating some of the interest expense, before the
PIK interest flips back to cash payment. This is a more aggressive, riskier technique.
Generally speaking, notes and mezzanine debt can have PIK interest.

5. Calculate financing fee amortization - the other type of noncash interest expense.
Capitalized financing fees are amortized over the life of the corresponding debt tranche.
This annual amortization is included in Net Interest Expense, but is a noncash expense. Use
the following formula:

Tranche fee amortization = max(0, min(total fee - sum(prior amortization), total fee / debt
term))

o In English, take the lesser of (the total fee minus cumulative prior amortization) and
the calculated annual amortization.
 o By locking the appropriate columns, you can drag this formula across all years and
all tranches. Try to get it on your own, but compare with the completed sheet if
you’ve having trouble.

Now we have all the components of interest expense. Interest Expense (including PIK interest) +
Financing Fee amortization = Total Interest Expense; less Cash Interest Income = Net Interest Expense.

Likewise, we can calculate Total Noncash Interest Expense as PIK Interest + Financing Fee Amortization.

13. Putting the Pieces Together

Fixing the Debt Schedule
After calculating PIK Interest Expense, you may have realized we’re not properly accounting for it in the
Debt Schedule. We’re going to fix that now. In the ending debt balance formula for the notes, add the
corresponding PIK Interest Expense each year. For the first 3 years, the Subordinated Notes Ending Debt
Balance should be increasing.

Adding Net Interest Expense to the Income Statement

Now, we’ll add Net Interest Expense to the Income Statement, and we’re going to include CIRC
protection. This is redundant, because we already included a CIRC statement in our debt balance /
Interest Expense calculation, but redundancy is better than debugging a broken model.

Use the following formula:

Net Interest Expense = if(circ=1, -Net Interest Expense from Interest Expense Schedule, 0)
Make sure you’re using net interest expense, which includes the Noncash Interest Expense and the Cash
Interest Income.

Finalizing Statement of Cash Flows

Now let’s link Noncash Interest Expense into the Statement of Cash Flows as an add-back to Net Income.
We do this, because Net Interest Expense reduces Net Income, but this portion of Net Interest Expense is
a noncash expense. For the first 3 years, Noncash Interest Expense will be relatively high, because it
includes the PIK Interest Expense.

Projected Balance Sheet

Next, we’re going to finish the Balance Sheet. The most important thing to remember when building out
a projected Balance Sheet is that every line item in the Statement of Cash Flows must be reflected in the
balance sheet. If there’s a single line item from the Statement of Cash Flows that is not properly
incorporated in the Balance Sheet, your Balance Sheet won’t balance.

1. Link Cash & Cash Equivalents in each future year to the Ending Cash on the Statement of
Cash Flows.
2. Link working capital line items from the Working Capital Schedule:
 o Net Accounts Receivable
o Inventory
o Other Current Assets
o Accounts Payable
o Accrued Expenses
o Other Current Liabilities
These line items are accounted for in the (Increase) Decrease in NWC line on the
Statement of Cash Flows.
3. Keep Goodwill constant in all future years. Just set it equal to the prior year.
4. Keep Other Noncurrent Liabilities constant in all future years.
5. Shareholders’ Equity = prior year Shareholders’ Equity + Net Income.
6. Link Debt to the corresponding total debt balance in the Debt Schedule.
o The changes in the debt balance include the following line items: Mandatory
Amortization, Optional Prepayment and PIK interest (included in Noncash Interest
Expense).
7. Other Noncurrent Assets = prior year balance - Financing Fee Amortization for the given
year.
o Now that we’re including Financing Fee Amortization, we’re fully incorporating the
Noncash Interest Expense line from the Statement of Cash Flows.
8. Net PP&E = prior year Net PP&E + CapEx - D&A.
o Basically, it grows every year by the amount of new capitalized assets less D&A. It’s
constant here, because as a simplifying assumption, we’ve made CapEx = D&A.

The balance sheet should now balance, because we’re incorporating all line items from the statement of
cash flows.
14. Credit Metrics

Every LBO model should include basic credit metrics, because these metrics and the credit profile will
inform debt investors’ appetite to fund the transaction. They indicate the potential credit risk.

Credit Ratios
The two main types of credit ratios are leverage ratios and interest coverage ratios:
o Leverage ratios - compare the amount of debt and the company’s cash flow (EBITDA).
o Interest coverage ratios - compare the company’s cash flow (EBITDA) and the annual
interest expense burden.
1. Link to the corresponding debt balances along with the EBITDA and CapEx.
o Senior Secured Debt = Revolver + Term Loan B
o Senior Debt = Revolver + Term Loan B + Senior Notes
2. Cash Interest Expense = Total Interest Expense (before Financing Fee Amortization and
Cash Interest Income) - PIK Interest Expense.
3. For 2016 Cash Interest Expense, I generally set it equal to 2017 Cash Interest Expense as a
shortcut. A better way is to calculate the full amount of Interest Expense (i.e., the starting
debt balances multiplied by the applicable interest rates, less any PIK’d interest).
o 2017 Cash Interest Expense should be lower than that amount, because debt is paid
down over the course of the year, which leads to a lower average debt balance.
4. Fill out leverage and interest coverage ratios.
 o The ratios should be pretty self-explanatory, but refer to the completed sheet in case
you have any issues.

15. Sponsor Returns

MoIC and IRR
When performing a LBO analysis, we measure sponsor returns using the Multiple of Invested Capital
(MoIC) and the Internal Rate of Return (IRR). Sponsors get paid based on MoIC, but their investors
care about IRR, so both are important. Even if a particular investment has a lackluster IRR, a decent
MoIC is good for the sponsor, because they get a share of the dollar profits (not based on IRR). That
being said, the fund IRR will be important for marketing their next fund to investors.

MoIC is literally the multiple of money made, e.g., the sponsor and its investors made 3x their money.
IRR is the discount rate that would be required in order for the sponsor to achieve a $0 NPV from the
investment. These are closely related concepts - the difference is that IRR takes into account the timing
of cash flows, whereas MoIC measures only the dollars. You can Google to learn more.

Exit Multiple Sensitivity

Using line items from the rest of the model, and a range of exit multiples, we’ll calculate the sponsor’s
returns (MoIC and IRR). Showing a range of exit multiples is helpful, because it highlights the impact
that the exit multiple can have on returns. PE firms often hold investments 5+ years, and the valuation of
a particular asset can shift dramatically over that time frame. In the best case scenario, the sponsor buys
an asset at the bottom of the cycle, and valuations subsequently skyrocket. An exit multiple that’s
substantially higher than the entry multiple is a great way to make money, but the opposite can happen,
too.
When a sponsor sells an investment for an exit multiple that’s higher than the entry multiple, that’s
called Multiple Expansion. It’s an elusive, mythical beast, and you can read more about it here.

1. Start by linking Adj. EBITDA, Debt and Cash.

2. Calculate Net Debt.
3. Calculate Enterprise Value based on the exit multiples on the left (Enterprise Value = exit
multiple x LTM Adj. EBITDA).
o The highlighted row corresponds to the entry multiple (i.e., constant entry and exit
multiples).
4. Equity Value = corresponding Enterprise Value (which varies by exit multiple) - Net Debt.
5. Set Investment equal to the total equity invested: Sponsor Equity + Management Rollover.
o In a real transaction, there would be a complicated split of investment proceeds
between management and the PE firm. A higher-level analysis, like this one,
generally just lumps them all into one bucket.
6. Calculate MoIC by dividing Equity Value by Investment.
7. Above the IRR section, add a row for the number of years elapsed. This will make it easier
to calculate the IRRs. You can enter the years elapsed manually or you can use
the Year function:
Years Elapsed = Year(current column date) - Year(PF 2016 date).
(You can refer to the date row at the top of the returns schedule).

8. Calculate IRR with the following formula:

IRR = MoIC ^ (1/ Years Elapsed) - 1

Next Steps

Congrats, you’ve completed the training LBO!

Try the rest of our PE modeling guides:

1. Ability-To-Pay Analysis
2. LBO Value Creation
 3. Dividend Recap Guide
NEWER
Ability-To-Pay Analysis

Contents
1. 1. Introduction

1. LBO Overview

2. Debt Primers

2. 2. Getting Started

3. 3. Filling in Transaction Assumptions

4. 4. Purchase Accounting

5. 5. Debt Financing

6. 6. Sources & Uses

7. 7. Income Statement

8. 8. Working Capital Schedule

9. 9. PF Balance Sheet

10. 10. Statement of Cash Flows

11. 11. Debt Schedule

12. 12. Interest Expense Schedule

13. 13. Putting the Pieces Together

1. Projected Balance Sheet

14. 14. Credit Metrics

15. 15. Sponsor Returns

16. Next Steps

Copyright © 2020 [Link]. All Rights Reserved. | About | Library | Privacy Policy | Terms of Service