LBO Valuation (19:36)
In this follow-up LBO Valuation tutorial, you’ll learn how to use an LBO model for valuation purposes, and how to tweak the model so that you can use built-in Excel functions such as Goal Seek to value companies.
For Aspiring Investment Bankers Only:
Nearly there: please tell us where to send your...
Free DCF Video Tutorial Series
Master Financial Modeling, As It Is Performed In Real Life.
Download the documents linked to under this video to see the case study instructions and understand this lesson (and the follow-up lesson after this one).
Requirements to Use an LBO Model for Valuation Purposes
First, you need to reduce the purchase assumption and exit assumptions to single cells, ideally an EBITDA multiple for both, or perhaps a premium to the current share price for the purchase assumption.
Second, you must DISABLE circular references and use the beginning debt and cash balances to calculate interest.
Otherwise, Goal Seek will not work properly.
Finally, your IRR and CoC multiple cells should stay in the same position regardless of the exit year. This just makes it easier to set up sensitivities and to use Goal Seek.
Using Goal Seek to Value a Company in an LBO
You can use Goal Seek (Alt + A + W + G) to determine how much a private equity firm could pay for a company, if it exits at a specific multiple and is targeting a specific IRR or cash-on-cash multiple.
You can also use it to check for multiple conditions, such as what EBITDA margin would be necessary if the targeted multiple is X and the purchase price is Y.
In Excel, select the CoC multiple or IRR cell, press Alt + A + W + G, set it to the value you want, and then select the EBITDA purchase multiple cell for the one you’re changing.
You will then get a solution that indicates the multiple the PE firm could pay to achieve the returns they are targeting.
Setting Up Sensitivity Tables
You can also set up sensitivity tables to look at these values over broader ranges. We recommend going up and down by around 20-30% over the baseline purchase and exit multiples.
You MUST hard-code the rows and columns of the sensitivity tables and you CANNOT link them to actual parts of the LBO model.
Link to the figure you want to calculate in the top-left corner of the table, and then highlight the whole table and press Alt + D + T or Alt + A + W + T to calculate the entire range of values.
Answering the Other Case Study Questions
With all of this set up, the rest of the case study questions are straightforward to answer: just use Goal Seek to determine the appropriate purchase/exit multiples, EBITDA margins, or revenue growth rates.
Modeling an IPO Exit
You can use the same analysis for an IPO exit as well, and if you wanted you could even tie it together with the LBO valuation by determining the amount a private equity firm could pay if they take the company public at a certain valuation in the future.
In an IPO exit, you assume that only part of the firm’s stake is sold initially, and that the rest is sold over time. The share price may also fluctuate, so the firm may end up with a stake that is worth more or less in future years
Finally, the instructions here state that senior debt should be maintained in the IPO exit, so we assume that only the Second Lien and PIK Loan are repaid.
The actual assumptions in real life vary, but often at least some debt is maintained even after the company goes public.
Recap and Summary
The key points with this case study are:
- Set up the assumptions properly, simplify, and reduce everything down to a few key inputs to save time.
- Avoid a complex debt schedule since there are no optional repayments anyway.
- Make sure the investor returns calculations section is flexible enough to work with both an M&A and an IPO exit, and that it supports Goal Seek.
- For Goal Seek and the case study questions, avoid circular references, simplify the purchase and exit assumptions down to 2 cells, and make sure the IRR and CoC multiple cells stay in the same place.