2 Hour 3-Statement LBO Case Study - Private Equity - Built from Scratch
ฝัง
- เผยแพร่เมื่อ 5 ก.ย. 2024
- Watch me build a 3-statement LBO model from scratch. Great practice and review for private equity case study interviews!
About me: My name is Josh Jia and I have 9 years of investment banking and private equity experience. Since founding Finance Interview Coach, I have helped place over 100 people in finance related roles at top banks like Goldman Sachs, Morgan Stanley, and JP Morgan, as well as top PE firms like Apollo, Onex, and Blackstone.
We also provide interview coaching for corporate development, equity research, hedge funds, venture capital, corporate / commercial banking, private debt, and FP&A.
If you'd like to learn more, check out my website at www.financeinterviewcoach.com and sign up for a free consultation. Thanks so much for watching this video and please SUBSCRIBE!
About Apex Advisory: Apex Advisory specializes in advising companies, venture capital funds, and private equity firms. Consultation services include financial modeling, investment memorandums, due diligence, strategy, and data science. Please visit apexadvisory.ca/ for more information.
Video starts at 14:50
interesting to watch the spreadsheet take shape - i have no idea whats involved here but seems like qualified folks would be able to follow along well.
Show the part where you lay off all the front line staff to cover the debt service
In your NWC Schedule you had a miscalculation. Time stamp, 1:01:20. You multiplied cogs twice rather than days. It should be 61 for days payable outstanding.
He catches this error like 5 minutes after
Amazing content, cannot believe someone will post something like this for free on the internet.
A couple of questions:
1) Should the Minimum cash amount be included under "Uses" given that should be the minimum operating cash needed to operate the business and therefore included
2) Why is OID not included as part of the Uses?
3) How come the existing cash is not used to net off the refinanced debt portion?
Valid questions which I would also like to see answered
Great questions. I simplified this model a bit for the audience who are just undergrad university students so that's why there are some inconsistencies.
1) Yes minimum cash should be a use here
2) OID was not included here to simplify the model since I figured this would be hard enough to understand already for the audience
3) Normally cash on balance sheet for a public company or large private company can be used as a source. For a mid-market private company, it's often cash-free. It wouldn't net off though. But I assumed cash free transaction in this model to simplify things for the audience.
@@financeinterviewcoach-josh5963 Got it, thanks for sharing and totally understand that models can become very granular and complex so there is a need to simplify. Thank you!
@@financeinterviewcoach-josh5963 A little confused about min cash. Isn't that only accounted for as a use in a cash-free debt-free deal? I would assume that it is inherently going to be in the offer value under uses otherwise.
Awesome video, it would be good if you also put the keyboard entries so we can see the shortcuts you use in excel 👍
He probably learned those shortcuts through countless hours of excel though. It's not impossible to do but you'd need like some key record software real time showing this or something that he'd have to systematically post through his vid, I think. There is another way to do super basic shortcuts though, (might not be what he's doing), just by holding/pressing [alt], it will shortcut you to any specific part of the "excel UI interface" i.e. file, home, insert, page layout, etc... That's probably a big part of how he has his incredible speed also while he's masterfully using excel :)!!
When you stepped up PP&E by 15% when you did the purchase price allocation, how come it didn’t create a deferred tax liability
Randomly stumbled across this, this is awesome! New Sub
1:28:35 Did you deduct Term Loan Mandatory Amortization twice?
yes agreed i believe he did...i had the same question.
Thanks for the video. Please prepare an interview/career guide for private equity so that we can study it and Ace interviews. Thank you.
I wish I saw this video 5 years ago, Great job!
Josh - would it ve possible to get hold of a copy of the case study instructions?
Hi, firstly thank you for the wonderful video. Just one question
1/ you put in as "Gross profit - SG&A = EBITDA"
2/ isn't that supposed to be"Gross profit - SG&A = EBIT"?
I dont think so. First one is correct
no, neither depreciation nor amortization are included in SG&A
This video is amazing and really on point even for a current analyst myself! Is there any way you can send the answer sheet? I could not find the link for it and would be hugely appreciated if you could share the link!
great stuff!! Have learned a lot - i have tried to replicate it but noticed that cash - interest is not considered. Could you please explain?
This is awesome! Can we download the case study and the xls sheet somewhere?
2 days ago (edited)
Thanks so much! I appreciate the kind comment.
I don't offer the Excel files, as they are proprietary files that I have spent countless hours developing and are part of my paid interview prep services, but feel free to check out my links for more free interview prep and financial modeling tips: linktr.ee/financeinterviewcoach
This is an excellent video … Thank you
Really interesting video, tho could you open a few more tabs pls ;)
Plzzzzz try to upload dcf i like your videos too much man
Hi! its an amazing tutorial. please share the practice file so we practice along with the tutorial.
Extremely helpful video! Definitely worth watching multiple times.
Great to watch. Well done!
Great stuff. Have been practicing several times
Can you pls recommend website to learn investment banking from scratch, majorly practicals that involves excel
What is happening to cash that you adjusted in 2012? Either that should have been spent on transaction or remained on the balance sheet. You have adjusted that in balance sheet but not spent it on transaction fees or pass it through cash flow statement !
Loved the walkthrough, can we download this anywhere? thanks
Thanks so much! I don't offer the Excel files, as they are proprietary files that I have spent countless hours developing and are part of my paid interview prep services, but feel free to check out my links for more free interview prep and financial modeling tips: linktr.ee/financeinterviewcoach
Great video, super helpful, I just have a question regarding the CFI at the end, why don't you include the revolver commitment fee to the Borrowing / (repayment) of Revolver line in the CFI?
Thank you for this content boss!
Anytime! Cheers. More to come :)
Thanks - I put in a late nighter but of course my balance sheet doesn't add up. The gap is widening after y1. Any tips how to debug?
I would recommend going through each line of the cash flow statement one by one and matching it with changes in the balance sheet, as everything in the cash flow statement should be found somewhere in the balance sheet as well. Make sure to check your sum formulas as well!
Should the $25m that you took out of the existing cash balance, to get to the minimum cash balance, not be included in the sources?
Why are financing fees considered Non-current assets instead of Non-Current Liabilities?
Hi Josh, Just subscribed. Can you do a three-statement financial modeling for a real life company from yahoo finance your future videos? I feel like it is the hardest one compared to doing DCF and LBO. Thank you.
No it’s not….if you want I’d put you through
What is the short cut to copy paste cell references that he uses frequently?
This is an amazing video. Quick question - don't you also need to depreciate the existing and incremental capex as well as the incremental PP&E from the transaction in the D&A schedule? Thank you!
Thanks! The capex is already being automatically being depreciated since depreciation is driven as a % of the opening PP&E balance which capex increases every year. But the incremental PP&E from the write-up should also be depreciated, that's an oversight in this video.
@@financeinterviewcoach-josh5963 Hi thank you so much for this walk through it was hugely insightful. Might be a beginner question but might as well ask as others may have the same question. In timestamp "1:28:41" you pay the manda term loan amortisation in the term loan schedule and include the same amortisation in the RCF schedule.
Understood that it must be a mandatory payment upfront otherwise tap into default but then why do we need to pay them off twice? As its included in both RCF schedule and Term Loan Schedule. Thanks! :)
Amazing Video ! Thanks
No problem! I'm glad you liked it.
Pretty cool. Got to make an appointment with myself to finish this video this week. Quick question: would you know good resources on value creation plan / operational value/growth plan by any chance please? We need to support the forecasts with an operational plan once we acquire a company (unlike you are Elon Musk, have access to a cool 44B in cash and loan and can "experiment" with the product while antagonizing 100% of your workforce 😂). Thanks!
What's the shortcut to duplicate an excel file, ALT W N? Tried that but didn't work
Yes. make sure to hold alt down, only works on PC
@@financeinterviewcoach-josh5963 Fab thanks!
Can you provide the template?
where is the excel version of this file? It would be good to play around and get a feel for the actual file. thanks much.
Pretty good, except Gross Profit - SG&A = operating profit (EBIT)… he subtracts EBITDA from Gross Profit to get SG&A, which is incorrect.
Thanks for the comment. SG&A was not provided in this case. So you have to backsolve for SG&A because you are given EBITDA. All expenses are negative in this model (as is best practice) so actually this is the correct way to arrive at SG&A in this case.
*Multiplies revenue by 20% to find receivables* "I dont know how to explain this, maths is honestly one of the hardest" 01:02:00
Hi Josh, @ 1:22:15 When doing the Debt schedule, your income statement numbers change, is there something I missed?
LBO models are circular: debt drives interest expense which drives free cash flow which drives debt paydown which drives...you guessed it, interest expense.
So before you finish the debt schedule properly, Excel is constantly recalculating as you make changes.
Thanks so much for the video - really really helpful. One question - at 1:42:25, when you paste your total interest expense, your numbers change from the originals (ending year at 157) to new numbers (ending year at 188), I know it's an iterative calculation but when I do the same, my numbers still change but they're different. Mine go 181, 179, 180, 181, 180... Not sure what I've done wrong along the way but any ideas?
Have you ever figured it out? I'm getting 181, 180, 182, 178, 173....
Why is the revolver (lowest interest) paid off first?
because it has the highest seniority
Thanks a lot for this great video! Quick q: At 1:27:48, should the discretionary term loan repayment be min(cash flow available for term loan, opening term loan - mandatory amortization) as we've already accounted for that amortization in CF available for revolver? Also, no need for the max as the value would always be >=0 here?
I have the same doubt. I think the max function is just standard practice
@@thomasjp95 Hey might be a beginner question but might as well ask as others may have the same question. In timestamp "1:28:41" he pays the manda term loan amortisation in the term loan schedule and also included the same amortisation in the RCF schedule.
Understood that it must be a mandatory payment upfront otherwise tap into default but then why do we need to pay them off twice? As its included in both RCF schedule and Term Loan Schedule. Thanks! :)
Where can I find the full question and solution excel?
Is there a way to download the material used here?
Guys why entry and ex8t multiple the same?
Where can I get the final Spread sheet?
Great video! Is there a reason you don’t depreciate the scheduled CAPEX?
Thanks! The capex is already being automatically being depreciated since depreciation is driven as a % of the opening PP&E balance which capex increases every year.
Hi Josh, quick question. How do you calculate the cash from exercised options? Does it link to the equity value of the management options?
Yes, it's just the original equity value x option %.
You forgot to substract cash in purchase price
Could you please share the excel file?
How would you deal with the deferred tax liability? Do you get higher D&A in the P&L?
Yes, you amortize it over the lifetime of the incremental PP&E
why dont you subtract net debt from EV? you have 50m in cash at the beginning..
you subtracted gross debt
The excess cash is already reflected in the sources (cash from balance sheet) and uses (minimum cash balance)
Where can someone learn about this?
I'm getting issues with circular references in the Mezz section of my debt schedule. How are you not getting errors on that end? the Ending balance is based off the sum of Opening Balance and PIK accretion which is based on the PIK interest expense, calculated on the average of the ending and opening balance... any ideas here?
I am facing the same issue
Did you find the way around it?
@@vaishalijain8902 You need to enable iterative calculation via Excel Options > Formulas > Enable Iterative Calculation
We went from Hunter gather society to this
would not your EV include cash, so you have to exclude to get to equity value.
The excess cash is already reflected in the sources (cash from balance sheet) and uses (minimum cash balance)
Unused fees and commitment fees are different no? 1:36:57
You need to pay commitment fees on unused balances at a lower interest rate
Got it; just different naming convention. I’m used to referring to upfront fees as commitment fees and unused fees as standby… but speaking with many US bankers who call unused fees, commitment fees. Good to know
Young Daniel Lee Brian Garcia William
Wow
Just curious, does knowing this has a future? I guess a KI could do this task in seconds.
As I get closer to the end, it gets messy....
Hernandez Richard Lewis Susan Young Angela
Looks pretty boring
Go work in a mine then.
Yea that's our job, is it boring? No if you like but to most people? Yes it is
@@AliRethrbuck working in a mine actually does something useful and so do lots of other jobs out there that don't involve physical labor. this stuff here on the other hand is just a boring waste of time. you're not contributing anything useful to society.
@@Sebastian-hg3xc so you think if all of the finance industry stopped working nothing would happen?
@@AliRethrbuck just because someone doesn’t want to work 80hrs a week in a office with this stuff doesn’t mean there gonna work in a mine 😭😭😭
15:10
Hii
Omg voice twin of Sam Sulek
59:34 alt e s l
what fantasy world full of nonsense did i just enter.