LBO Modeling Test: Full Walkthrough of a 60-Minute Test (Blank Sheet)

Рет қаралды 102,137

Mergers & Inquisitions / Breaking Into Wall Street

Күн бұрын

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

Пікірлер: 79

@financialmodeling 2 жыл бұрын

For the files and resources, please see: www.mergersandinquisitions.com/lbo-modeling-test/

@LsDrones 2 жыл бұрын

Hey man, This is great! thank you so much for this video! Will do it in full. Love your channel!

@financialmodeling 2 жыл бұрын

Thanks for watching!

@roro-dr3qc Жыл бұрын

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

@financialmodeling Жыл бұрын

Yup, it takes some time and effort to learn. But it gets easier once you have completed many examples.

@covfefehamberder5277 2 жыл бұрын

This looks really cool!

@financialmodeling 2 жыл бұрын

Thanks for watching!

@obaialhasson9576 2 жыл бұрын

Keep it up👍 This is so valuable work. A big thanks

@financialmodeling 2 жыл бұрын

Thanks for watching!

@sidmoitra007 2 жыл бұрын

Nice one!

@financialmodeling 2 жыл бұрын

Thanks for watching!

@gmtwows6090 2 жыл бұрын

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?

@financialmodeling 2 жыл бұрын

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.

@williamronald3857 2 жыл бұрын

Thank you!

@financialmodeling 2 жыл бұрын

Thanks for watching!

@lelioshmelio 2 жыл бұрын

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!

@financialmodeling 2 жыл бұрын

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...

@ibrahimshamshad6451 2 жыл бұрын

Thanks you very much

@financialmodeling 2 жыл бұрын

Thanks for watching!

@veljkopetkovic5256 2 жыл бұрын

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.

@financialmodeling 2 жыл бұрын

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.

@spak4319 9 ай бұрын

Very Good video thks m8

@financialmodeling 9 ай бұрын

Thanks for watching!

@HandsOnRealEstate 3 ай бұрын

If I go back to school, it will be to learn how to do this.

@user-zl9iz8zx4d 2 жыл бұрын

Thank you for this great video! Why doesn't the excel have any circular reference with the cash sweep?

@financialmodeling 2 жыл бұрын

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.

@sidharthmohanty6434 2 жыл бұрын

Thanks

@financialmodeling 2 жыл бұрын

Thanks for watching!

@YueningSmith Жыл бұрын

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?

@financialmodeling Жыл бұрын

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.

@YueningSmith Жыл бұрын

@@financialmodeling That's helpful. Thanks a lot!

@techgod3670 2 жыл бұрын

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)

@financialmodeling 2 жыл бұрын

Pre-LBO values.

@MakingaStink 2 ай бұрын

On one of these more simple tests, would you ever have to do something like limitations on interest expense (30% of EBIT)?

@financialmodeling 2 ай бұрын

Probably not, especially since it's specific to certain regions and not a global rule.

@gargaditya6804 2 жыл бұрын

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

@financialmodeling 2 жыл бұрын

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.

@carterfinance2376 2 жыл бұрын

What program would I want to purchase on your website if I wanted to be an equity analyst? I assume the financial modeling one?

@financialmodeling 2 жыл бұрын

Yes, either Financial Modeling or Excel + Financial Modeling.

@gmtwows6090 2 жыл бұрын

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?

@financialmodeling 2 жыл бұрын

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).

@닝닝닝 Жыл бұрын

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

@financialmodeling Жыл бұрын

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.

@levikern370 Жыл бұрын

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?

@financialmodeling Жыл бұрын

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).

@ytube9118 27 күн бұрын

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!

@financialmodeling 27 күн бұрын

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.

@닝닝닝 Жыл бұрын

53:44 Hi do you have a relevant video for calculating of return on invested capital? Thank you

@financialmodeling Жыл бұрын

Yes: kzfaq.info/get/bejne/Zt2EhZWG1Z22e3U.html

@user-be8gt9yo4u 4 ай бұрын

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?

@financialmodeling 4 ай бұрын

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.

@닝닝닝 Жыл бұрын

38:40 Hi I have one question. Why do we need to add the cash used for Debt Repayment when calculating Term Loans?

@financialmodeling Жыл бұрын

"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!

@klaraklarowicz6211 2 жыл бұрын

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

@financialmodeling 2 жыл бұрын

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.

@ytube9118 28 күн бұрын

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

@financialmodeling 27 күн бұрын

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.

@ytube9118 27 күн бұрын

@@financialmodeling That makes sense, thank you

@trebitda9443 2 жыл бұрын

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?

@financialmodeling 2 жыл бұрын

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.

@trebitda9443 2 жыл бұрын

@@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!

@financialmodeling 2 жыл бұрын

@@trebitda9443 So this is really beyond the scope of Q&A on this free KZfaq 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.

@nyokabi123 Жыл бұрын

@@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?

@financialmodeling Жыл бұрын

@@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.

@Ddub1083 2 жыл бұрын

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)

@financialmodeling 2 жыл бұрын

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.

@alfonsolopc 2 жыл бұрын

What's your keyboard and screen set-up?

@financialmodeling 2 жыл бұрын

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.

@madebyzona Ай бұрын

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?

@madebyzona Ай бұрын

Also, why not include mandatory principal repayments in the interest line in the P&L? Are they not treated the same?

@financialmodeling Ай бұрын

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.

@financialmodeling Ай бұрын

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.

@keithleyjarvis5663 2 жыл бұрын

Can you make a video on formatting?

@financialmodeling 2 жыл бұрын

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.

@RemoteSound-NatureSoundscapes 6 ай бұрын

why doesn't ending cash balance factor in cash used to pay interest charges on debt and instead only principle pay down?

@financialmodeling 6 ай бұрын

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.