LBO Model Case Study: Assessment Center
Вставка
- Опубліковано 13 чер 2024
- In this tutorial, part 1 of a 2-part series on LBO case studies, we’ll look at what you might expect in a case study modeling test given at an assessment center or at the end of your internship at a bank or PE firm.
By breakingintowallstreet.com/ "Financial Modeling Training And Career Resources For Aspiring Investment Bankers"
Table of Contents:
1:59 Case Study Overview
5:00 Strategy for Tackling the Case Study
9:39 Step 1: Assumptions and Sources & Uses
12:22 Step 2: Income Statement
13:12 Step 3: Cash Flow Statement and Debt Repayments
16:42 Step 4: Interest Expense and Linking the Statements
21:22 Step 5: IRR and Cash-on-Cash Multiple
Lesson Outline:
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).
Key Points:
With a time-pressured case study like this, you have to SIMPLIFY and cut corners where needed.
Here, for example, we can simplify by only including 3 years of projections, skipping a complex debt schedule and Balance Sheet Projections, and consolidating the assumptions and Sources & Uses schedules.
However, there are also a few tricky parts to be aware of - first off, despite the instructions, you do need to assume a purchase EBITDA multiple so that the model works initially.
Also, you need to include more of a “real” investor returns calculations area, and you have to go a bit out of order because of the specific assumptions here.
Step 1: Assumptions and Sources & Uses
Assume a baseline purchase multiple just to get a price to use in the model, and then base the Uses side on this purchase price and the associated fees.
Then, fill in Sources using the x EBITDA assumptions given for each tranche of debt, and back into Investor Equity by taking Total Uses and subtracting the debt sources so far.
Step 2: Income Statement
On the Income Statement, project revenue, EBITDA, and D&A according to the given assumptions, and calculate Pre-Tax Income and Net Income based on the tax rate assumption.
Step 3: Cash Flow Statement and Debt Repayments
Then, start the CFS with Net Income, add back the non-cash charges (just D&A for now), and factor in the changes in Inventory, AR, and AP.
Since you don’t have a Balance Sheet, take a shortcut and just use the change in revenue times the relevant percentages to get these. Inventory and AR increasing will decrease cash flow, and AP increasing will increase cash flow.
Calculate FCF by taking all of that and subtracting CapEx, and then include the amortization of Term Loans A, B, and C to determine the Net Change in Cash.
At the bottom, the Cash Balance each year equals the Cash Balance from the prior year plus the Net Change in Cash.
Step 4: Interest Expense and Linking the Statements
First, track how Term Loans A through C change each year by subtracting the annual amortization. Hold the Second Lien constant. Keep the PIK Loan blank for now.
Calculate interest expense using the BEGINNING balance (i.e. the one from the end of the previous year), not the average, to avoid circular references.
Do this for all the debt tranches, as well as the interest earned on cash. Use LIBOR and the LIBOR spread, or the fixed interest rates for the Second Lien and the PIK Loan, to do this.
Then, go back and make the PIK Loan increase by the PIK Interest each year, and then go back and link the Net Interest Expense on the Income Statement (it should be a negative there).
Finally, make sure you include the PIK Interest as a non-cash add-back on the Cash Flow Statement now.
Step 5: IRR and Cash-on-Cash Multiple
Calculate the IRR and cash-on-cash multiple the normal way at the bottom; Investor Equity should always be a negative in Year 0, and you calculate the Exit Enterprise Value in Year 3 using the EBITDA in Year 3 and the EBITDA exit multiple we were instructed to use.
Subtract Net Debt to calculate the Exit Equity Value. For now, Investor Equity = Exit Equity Value.
Sum up all positives in the Investor Equity line and divide by the sum of all negatives there to calculate the cash-on-cash multiple; use the built-in IRR function in Excel to calculate that.
In part 2 of this series, we’ll complete the case study by building sensitivity tables and using Goal Seek to turn the LBO model into a valuation methodology. We will also answer the case study questions.
RESOURCES:
youtube-breakingintowallstreet...
youtube-breakingintowallstreet...
youtube-breakingintowallstreet...
youtube-breakingintowallstreet...
Brian, excellent work, well explained, especially thinking about what one is about to do and why one should be doing it. Now looking forward to 2nd part!
Thanks for watching! The 2nd part is already here if you look at the LBO modeling playlist and go to the one right after this case study.
@@financialmodeling Sure thing dude, on it, much appreciated.
This one was hard but I've sort of overcome it. My brain is boiling up. Up for part 2. Thanks man!
Thanks for watching!
Very impressive!! I'm gonna rewatch it at 0.25x speed next time lol but It's really impressive. Thank you
Thanks!
Oh boy, that was a comlex one. Neverthelless great video!
Thanks for watching!
Thank you for the great video! A quick question on Cash on Cash multiple at the end. What do we need such a formula instead of just simply inserting "=-Year 3 Return/Year 0 Return"? Many thanks.
The exit year can change, so you can't just link to two specific cells.
Thank you so much
Thanks for watching!
Thanks for the tutorial Brian, much appreciated!
Q: Shouldn't we use the Equity Value instead of EV as the purchase price (for fees calculation) ?
Yes, but we simplified it here because we only had the Purchase Enterprise Value, not the Purchase Equity Value.
Perfect, thanks for the reply. Just wanted to be sure as I derived the EqV assuming a debt free basis and a $20m cash balance.
Have a great week end.
Awesome content! Perfect delivery. Although I can't find any links to download the assessment file or the excel template
Thanks. If you click "More" or "Show More" and scroll to the bottom, you can find the links there.
Thanks for the case study! When will the second part be released?
Thanks for watching. Part 2 will be available in the next 1-2 weeks.
Thanks for the great tutorial . how did we calculate libor units? Plus can you explain L+350 terms, what do they mean?
"350" refers to basis points, so L+350 means "LIBOR plus 3.50%." LIBOR Units is just a hard-coded 10,000 so that 350 / LIBOR_Units = 3.50%, or 100 / LIBOR_Units = 1.00%. It's just a way to convert to percentages.
LIBOR is supposed to be 2.25%, not 2.5%. That is the reason for a negative cash position at the end of Y3. Great video, helped me A LOT!
Thanks for that correction.
Hello Brian. Thanks again for that brilliant video.
Just one question though. Shouldn't the variation in Inventory and Payables be calculated as a percentage of COGS (If there had been any line in the Income Statement). I believe it would be more consistent in accordance with DIS and DPO ratio calculation. Thank you very much.
This is such a small point that it's not even worth thinking about. Yes, in theory, It's better to tie Inventory and Payables to COGS... but we don't even know what type of company this is, nor do we have assumptions for those ratios. So we use what is available.
Thanks Brian for the quick reply.Do you have any tutorials for how to use Factiva, Bloomberg or Cap IQ terminals for valuation analysis? How are they different from your assessment center or paper case studies? I need to learn this asap as I will be given a valuatiob assignment to use one of those in few days.Any help would be appreciated
We do not at this time, but there are a few examples throughout our courses of how to set Capital IQ screens for different tasks such as finding debt comps.
Hello Brian,
Thanks for the tutorial. One quick question. I have seen in your full-blown Dell LBO that you include the cash balance as a "Source" in the Source and Uses table. I note that in this tutorial you've not included the existing $20million cash balance in the "Sources" side (which ended up being the only difference between your model and my model when I was going through the tutorial). What is the logic or reason behind including or excluding the cash balance in the "Sources" side of the Sources / Uses table?
Thanks!
If the company has excess cash, you can use it to fund the deal. If it does not have excess cash, you cannot use it to fund the deal. It is subjective and depends somewhat on what the company and sponsor agree to as well.
Thank you for sharing. Disagree on not building in an RCF though, even if time pressed: at my firm they bombed candidates for that.
Feel free to disagree. My experience is that actually finishing on time is the most important point, and that most people fail to do it by getting caught up in minutiae. If the instructions say to build a Revolver, sure, OK, but otherwise, finishing on time and getting to a yes/no decision is the most important part, even if the model is not as flexible as it could be. And if the company's cash flow can easily meet the mandatory repayments during the holding period, the Revolver doesn't make a difference.... if not, then it's a lot more important.
Thank a lot for the video. Quick question - about the PIK loan, the definition of the loan is that the interest accumulates and then paid in the maturity period, I saw that you add back the interest expense in the cash flow statement, but why do you, in the beginning, subtract the interest payment of this loan in the income statement? is it because of accounting rules?
No, the definition of a PIK loan is that the interest accrues to the principal rather than being paid in cash in the period shown. And then the entire loan principal will come due in the future. Accounting rules state that all interest should appear on the IS because it is tax-deductible regardless of whether it is cash or non-cash interest.
man's brilliant
Thanks! But I actually think this example isn't great, so we're probably going to replace it this year (the newer LBO examples are better).
Excellent gyes. Super
Thanks for watching!
Thanks for the video. Quick question - why loan amortisation is not recorded as an expense in income statement? Amortisation of intangible assets are recorded as an expense (usually paired with depreciation), but why not loan amortisation?
Because principal repayments of a loan do not affect a company's Book or Cash Taxes. Unlike interest, which counts as a tax deduction and therefore affects the business income available to common shareholders.
Hi Brian, thanks a lot for the great video! Could I please kindly ask you where I can download the excel template? The last 2 links on the video description do not work for me. Thanks in advance!
Try:
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-Before.xlsx
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-After.xlsx
@@financialmodeling thanks a lot!
Great Video Thank you! QQ Financing fees on total debt, should.t those be amortised over time and go as cash outflow under cfo? Not sure how to understand when to do this and when not! thanks :) Also qq we assume no debt but we take the 20mn cash as initial balance shouldn't this also go into uses as cash on B/S if we are assuming a debt free-cash free transaction? If only debt free the wont we have net cash so that would also affect sources and uses cals? Thank you!
Yes, but we are simplifying and skipping that step here because there is almost no cash flow impact. The cash-free/debt-free treatment here is not quite right due to unclear instructions, but it makes a marginal difference on the end results. Take a look at the cash-free/debt-free LBO tutorial.
Did you guys get the template through the 3rd and 4th links under "Resources"? It seems that I can't access those two links....
Try:
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-After.xlsx
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-Before.xlsx
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case.pdf
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-09-LBO-Model-Assessment-Center-Case-Answers.pdf
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case-After.xlsx
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case-Before.xlsx
youtube-breakingintowallstreet-com.s3.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case.pdf
If the company has 20m cash on the balance you have to pay for that. In this case, the purchase price is 20m higher than the EV (assuming no debt). To account for the access to this 20m, you can either reduce the uses of funds by 20m and start with a 0m cash balance in year 0 or you do not adjust the uses of funds and start with a cash balance of 20m. Either way, the purchase price increases.
Yes, we probably should have set the initial Cash balance to 0 here if the deal was really done on a cash-free/debt-free basis (which is implied if the purchase price is quoted in terms of Enterprise Value). But it doesn't really make a difference in the end results because the Cash balance is so low. We prefer to either use Purchase Equity Value + Existing Debt Refinanced in Uses and then maintain the company's existing Cash balance, or to use Purchase Enterprise Value and make it cash-free/debt-free. The Debt part here is fine but I agree that the Cash treatment should be changed.
Dont we need to do something with the financing and M&A fees? Wouldnt they sit on the balance sheet initially and decrease on the income statement?
In a more complex case study, yes, but they make such a small difference that you can simplify and ignore them here.
Hmm you didn't cover the bit on the IPO exit, I realise
Why is the term A-C amortization subtracted and not added back to get net change in cash since its non-cash exp?
Amortization of Term Loans is an actual cash outflow. "Amortization" here just means "Principal Repayments." Since you are repaying loan principal using cash, it is a a cash outflow.
Many Thanks, Brian.
I have a quick question to clarify with you
When calculating 'Increase in Inventory' is (Year 0 - Year 1) * Inventory % Revenue
whereas 'Increase in Payables' is (Year 1 - Year 0) * Payables % Revenue.
Which is opposite way. Could give me a detailed explanation about when to use (Year 1 - Year 0) and (Year 0 - Year 1)?
Please see: ua-cam.com/video/tMgty8jwmHI/v-deo.html
Hey Brian, great video ! I just have one question, why do we subtract the annual amortization to the loan ? (beginning step 4)
Not sure what you mean - some loans amortize over time, which means the company must use cash flow to repay the loan principal...
hey bryan, i cant seem to be able to load the excel files. is there a problem with them?
I just tried the links, and they seemed to work... maybe try the direct URLs and remove the parts that UA-cam adds at the end?
I don't know what happened but I think the link for spreadsheets is not available anymore
We don't recommend using this example because the newer LBO models in this channel are better / more consistent. But the links are here:
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case.pdf
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-After.xlsx
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-08-LBO-Model-Assessment-Center-Case-Before.xlsx
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-09-LBO-Model-Assessment-Center-Case-Answers.pdf
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case.pdf
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case-After.xlsx
youtube-breakingintowallstreet-com.s3.us-east-1.amazonaws.com/109-09-LBO-Valuation-Assessment-Center-Case-Before.xlsx
I think you can actually solve without having to initially input the "made up" 8x multiple or use goal seek. By backsolving from the exit EV, less the exit debt, less the exit cash, you get to the exit equity, and by dividing that by the target MOC multiple of 2.5x you get to the max equity you can bid initially.
To the initial equity we have calculated to meet the 2.5x MOC requirement we can add the acquisition debt, less the cash on balance sheet, and get to the entry EV. This entry EV divided by the entry EBITDA gives a 7.18x EV/EBITDA multiple.
In this specific case, yes, that works. But it won't work in the generalized case where the company might issue dividends to the PE firm, raise additional equity capital, do a dividend recap, etc., which is why we chose to use goal seek for the added flexibility.
Why do we have to deduct the financing cash flows (i.e. amortization amounts) here - I have often seen that simple LBOs only use the LFCF for the cash accumulation?
You don't necessarily have to. You could simplify it by ignoring the financing fees and amortization altogether since they barely affect the model. We only did it because the instructions asked us to.
Hey, isn't Libor 2.25% and not 2.5% like in the model? did I miss something where you explain the difference?
+sd asd Yes, you're right, it should be 2.25%. Feel free to correct it yourself - it makes a difference of about 0.000001% (i.e., not worth expanding brain cells on). Think the assumptions changed between the initial and final versions here.
+Mergers & Inquisitions / Breaking Into Wall Street
Alright. Thanks for the response!
Where can i get an excel template?
Click on "Show More" under the video and see the links under "RESOURCES" at the bottom.
Where is the file to download it?
Click "Show More" and scroll to the bottom.
Can I get this Excel Sheet ?
Click "Show More" and scroll to the bottom.
the Libor rate in the model was 2.5%, however, in the case study, it was 2.25%
Yes, there are minor inconsistencies in these videos because I am only human. I would urge you to focus on the big picture rather than 0.25% discrepancies.
I get it, thanks for sharing the knowledge, I was just stating the fact :)
Brian , you are a great Human. Thanks is not enough , still Many thanks ...