1. Introduction
This guide will provide basic instruction on modeling LBOs. Specifically, you will learn:
- How to build a full-featured LBO model
- Some key Excel functions
- Core financial concepts and terminology
- How to model different types of debt
2. Getting Started
Download the following files:
- Blank Model – you’ll build your own model with this template.
- 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:
- Transaction Assumptions
- Income Statement
- Working Capital Schedule
- Balance Sheet
- Statement of Cash Flows
- Debt Schedule
- Interest Expense Schedule
- Credit Metrics
- 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:
- 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”)
- 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).
- 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
- Link the LTM Adj. EBITDA from the income statement (use 2016A – we’re assuming an illustrative transaction close at FYE 2016).
- Calculate the Transaction Value (LTM Adj. EBITDA x Transaction Multiple).
- Link the “Cash On-Hand” from Sources as the cash balance.
- Link “Refinance Existing Debt” from Uses as the debt balance.
- 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.
- Set Total Equity Value equal to the Equity Value you just calculated above.
- Link the appropriate asset & liability fields from the balance sheet.
- 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).
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.
- Set Total Equity Value equal to the Equity Value you just calculated above.
- Link the appropriate asset & liability fields from the balance sheet.
- 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).
评论0