LBO Modeling Test: Full Walkthrough of a 60-Minute Test (Blank Sheet)
HTML-код
- Опубликовано: 19 июл 2024
- Learn more: breakingintowallstreet.com/co...
In this tutorial, you’ll learn how to complete a full 60-minute LBO modeling test starting from a blank sheet in Excel, and you’ll learn how to simplify and work efficiently so you finish on time.
NOTE TO VIDEO: At 42:50 - 43:10, we make a mistake calculating the Ending Cash balance. We fix this mistake at 45:42 - 46:02.
Table of Contents:
For all the files and resources, please see:
www.mergersandinquisitions.co...
0:00 Introduction
2:24 Part 1: Likely Requirements in Modeling Tests
7:43 Part 2: Transaction Assumptions and Sources & Uses
17:24 Part 3: Model Drivers and Income Statement
32:14 Part 4: Free Cash Flow and Debt Schedule
46:04 Part 5: Returns and Sensitivities
56:22 Part 6: Answers to the Case Study Questions
1:03:12 Recap and Summary
![Working Capital and the Change in Working Capital in Valuation and Financial Modeling [REVISED]](http://i.ytimg.com/vi/tMgty8jwmHI/mqdefault.jpg)
For the files and resources, please see:
www.mergersandinquisitions.com/lbo-modeling-test/
Hey man, This is great! thank you so much for this video! Will do it in full. Love your channel!
Thanks for watching!
Excellent tutorial, thanks a lot. I definitely need to dig a bit deeper into these mgt options' and optional repayments' formulas - these aren't easy to grasp when I try to do the case by myself :)
Cheers from France
Yup, it takes some time and effort to learn. But it gets easier once you have completed many examples.
This looks really cool!
Thanks for watching!
Your videos are awesome and I’m learning a lot. Also, I’m planning to do a quite short thesis on an LBO case. Do you have any suggestions on what could be/ has historically been a relatively simple LBO I could talk about?
Thanks. I don't really look at recent LBOs unless it's part of a new course or something similar, and we just re-did all our LBO models last year, so I don't have a specific recommendation. But try simple companies in standard industries (industrials, retail, consumer, etc.) first.
Keep it up👍
This is so valuable work. A big thanks
Thanks for watching!
Nice one!
Thanks for watching!
Thank you!
Thanks for watching!
If I go back to school, it will be to learn how to do this.
Hello, Brian. I noticed that you showed treasury method in videos when calculating dilution. Could you please make separate video about options and their implication in LBO/Valuation models? Thanks for this video!
There really isn't much to say about it. Options slightly affect a company's value in a transaction scenario, but in 99% of cases they are not a major driver. This would be like analyzing a book and focusing on the average amount of space between a period and the first letter of the next sentence...
Hey Brian, absolutely love your videos. Is there any shortcut for making the indents in cells like you do, or do you just have to manually hit space. Also any chance you could explain how to move around the menu when I press CTRL 1 using only the keyboard.
Alt, H, 6 and Alt, H, 5 to insert and remove indents. We don't cover Excel shortcuts extensively in this channel (see the dedicated course), but there are several videos on how to set up the QAT in Excel and the more important shortcuts.
Thanks you very much
Thanks for watching!
Very Good video thks m8
Thanks for watching!
Your video is really helpful! A little question. In case study question 3, why should we implement a dividend recap and cash dividend distribution before Year 5 so as to boost returns? Equity value = Enterprise Value + Cash - Debt, so the two methods I mentioned will decrease equity value (and investor returns), I guess?
A dividend recap will result in an early cash distribution before the final exit in Year 5. The total amount received by the PE firm won't necessarily change by much, but the time value of money means that the IRR will increase due to the earlier distribution.
@@financialmodeling That's helpful. Thanks a lot!
Thank you for this great video!
Why doesn't the excel have any circular reference with the cash sweep?
A cash sweep does not create circular references. It's a simple linear calculation, and as long as we use the beginning cash and debt to calculate interest expense for use in the FCF calculation, no circular reference is created.
Thanks
Thanks for watching!
Hey, brilliant stuff. Could you explain why the "mandatory repayment" is min(term loan amortization, term loan)? Given that there is a cash sweep, should it not be If(fcf>min cash balance, term loan amortisation + 0.5*(fcf-min cash balance))??
Apologies if this is a stupid question but would appreciate any feedback on this
The cash sweep is the optional repayment part, so it's separate ("CF Used for Debt Repayment"). Mandatory repayments refer only to the fixed principal repayments that exist in each period as long as a sufficient loan balance exists. The MIN checks to make sure that the remaining balance is greater than the scheduled principal repayment.
Why don't we integrate the 2% ($24m) advisory & management fees? I assumed that I should at least subtract that figure our return calculation (from the Equity Value). Thank you very much!
The Investor Equity is higher due to these fees, so it is reflected in the model. Delete the fees, and the Investor Equity in D20 goes down, so the IRR and multiple increase.
Hi I've a doubt, while valuing the target for LBO using dcf , will v take post LBO values( where v use the new capital structure where new debt will be taken to fund the deal) or do we use the pre LBO values ( where v value the company with values as it is before the LBO is to be done)
Pre-LBO values.
What program would I want to purchase on your website if I wanted to be an equity analyst? I assume the financial modeling one?
Yes, either Financial Modeling or Excel + Financial Modeling.
Nice video, many thanks! One question: why do you link the mandatory term loan repayments (5%, 10%, 10% etc.) to the initial debt amount of €300m (i.e. you anchor this €300m figure in row 67)? I always linked the mandatory repayments to the beginning debt balance of the term loan in each year - is that wrong?
That is incorrect in most cases. The principal repayment percentages are based on the initial debt balance - otherwise, you might get into a situation where the loan never amortizes completely (if the percentages add up to 100%, for example).
Why is the CapEx (factory maintenance expense) based on last year's number of factories and not the number of factories we have this year? Thank you very much
Maintenance CapEx relates to existing factories that have been operating for at least a year. New factories constructed in the current year do not yet need maintenance.
@@financialmodeling That makes sense, thank you
On one of these more simple tests, would you ever have to do something like limitations on interest expense (30% of EBIT)?
Probably not, especially since it's specific to certain regions and not a global rule.
Hello guys I have a question concerning LBO analysis. Is it useful in an LBO to consider the degree of financial leverage to assess how much debt the acquirer can sustain?
There is no "acquirer" in an LBO, just a target company, because it continues to operate independently after acquisition by the PE firm. If you're asking about whether the target company's leverage pre-deal affects how much debt can be used in the deal, no, not really except in very minor ways (e.g., a company known to existing lenders might be able to raise debt from those lenders more easily).
Loved the content, though Im more just an excel user and not a financial analyst... I mean, I had to look up what LBO meant haha. I thought for sure you would mention it at some point. Also, it would be great if you showed the keyboard shortcuts on the screen as you record.... sometimes its hard to follow what you are doing since you are doing it on the keyboard and we cant see that. (and now I see you did it later in the video haha)
This tutorial assumes a certain level of prior knowledge because it's designed for people searching for the keyword "LBO modeling test." Anyone searching for that presumably knows what "LBO" means, what private equity is, etc. We tend not to focus on Excel shortcuts in financial modeling tutorials because pausing to state each shortcut slows down the pace. There are separate tutorials for Excel shortcuts and features here.
Hi i have another question at 42:09. I don't understand why we should use D71 when multiplying cash sweep % instead of free cash flow (e.g. D70)? Thank you
Because the company's entire FCF is not necessarily available for debt repayment. The available FCF is reduced by mandatory debt repayments and the minimum cash requirements.
Thank you. Two quick questions: Can you explain a little more why you do "Option % / (1 + Option %)"? Also, how would the layout of the returns analysis change if there was an equity roll? Would the % owned by the Mgmt. include the cash from their options? Or would it be multiplied against just the Exit Equity Value?
The additional shares issued create additional equity, but the initial option grant was based on the company's equity before the share count expanded, so the option holders receive a slightly lower percentage. We've covered equity rollovers in other examples if you look at some of the Sources & Uses videos.
@@financialmodeling Got it, that makes sense. Maybe to clarify my second question, let’s say the original Equity Ownership was 80% Sponsor / 20% Mgmt when accounting for the initial roll. When doing the returns analysis with a Mgmt. Option Pool, would you create another line, after normal exit equity value (row 94) called, for example, “Diluted Equity Value”, which includes the new cash from the exercise of the options and the subtracted equity to option holders, and then do the 80%/20% split from there? Or would the 80%/20% split be calculated on the normal exit equity value (row 94), and then we do all the options calculations? Essentially which comes first: options calculations or equity roll calculations? Apologies for the long-winded question thank you!
@@trebitda9443 So this is really beyond the scope of Q&A on this free RUclips channel, but it depends on the priority/hierarchy as defined in the legal agreements. It could go either way, which is why the contracts would have to specify the order of operations/calculations.
@@financialmodeling Hello, so just to confirm I'm thinking of this correctly... So since assets increases due to managers exercising their options that caused the equity to increase. Since equity increased, we now have to slightly decrease the % of equity the managers will receive(options pool %) to in-order to take into account the increase in equity due to the increase in cash from excercising their options?
@@nyokabi123 Yes. But you could easily skip this step and still pass the case study. They mostly just want to see that you get it "roughly correct" in the allowed time.
Hey, shouldn't you include the min-cash balance in the ending cash balance calculation? The way I see it, is that you need to keep it as a minimum for your work cap outflows/changes so it's always on the BS and should also earn cash interest? Thanks
The standard practice in LBO models is to include the entire remaining cash balance at the end, so we follow that here. If the company's cash balance is too low upon exit, the next acquirer will add funds to the deal to bring it up to the minimum level.
38:40 Hi I have one question. Why do we need to add the cash used for Debt Repayment when calculating Term Loans?
"Cash Used for Debt Repayment" means "Cash the company uses to *repay* Term Loans." It's added here because it's already a negative in Excel, and adding a negative is the same as subtracting the number.
@@financialmodeling Understood, thanks!
Hello! Apologies if this has been asked already but is there a reason why the Ending Cash of the Term Loan is calculated with mandatory repayments instead of CF Used for Debt Repayment?
I'm not sure which line item you are referring to here. The "Ending Cash" in row 74 is always based on the Beginning Cash, Mandatory Repayments, FCF, and the CF used for optional repayments (row 73), as these represent the starting point plus the major changes in the cash balance for that year.
53:44 Hi do you have a relevant video for calculating of return on invested capital? Thank you
Yes: ruclips.net/video/0wTR2Up7RKA/видео.html
Thanks for your video! When calculating the tax-deductible interest expense on the P&L, you included the entire interest expense (included the interest you didn't pay, like the PIK), not just the coupons you paid out to debt holders. Is this correct? Is the entire interest expense tax deductible?
Also, why not include mandatory principal repayments in the interest line in the P&L? Are they not treated the same?
For LBO model purposes, the entire interest expense, including both Cash and PIK Interest, is tax-deductible unless otherwise specified. There are some special cases where this is not true, and some regions limit the % of Interest that is tax-deductible, but in a 60-minute case study, you can't really think about these points. See the PIK Interest tutorial for more.
Debt principal repayments are never tax-deductible in any region or industry. They appear on the Cash Flow Statement and are not a tax deduction because they represent the simple repayment of borrowed capital, not the expenses of using that capital over time.
What's your keyboard and screen set-up?
Not sure what you mean. If you're asking about the QAT in Excel at the top, we cover that in an Excel video elsewhere in this channel.
Doing this in '60 is for pros :)
Hey, it's not that hard when you also create the test...
why doesn't ending cash balance factor in cash used to pay interest charges on debt and instead only principle pay down?
The Net Income that starts the FCF calculation already has a deduction for interest expense paid on the debt since it flows in from the Income Statement. So it is factored in, but in the line items above.
Can you make a video on formatting?
There are some older videos in the Excel section on this topic. It's a fairly boring topic that most people aren't really searching for, and it's well-covered in our actual Excel course already. So... maybe, but it's not exactly the highest priority topic here.