2 Hour 3-Statement LBO Case Study - Private Equity - Built from Scratch

[@georgepatterson5167]

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.

[@praveen111989]

Amazing content, cannot believe someone will post something like this for free on the internet.

[@asunagilfoyle]

i love how this is just...free for anyone to watch.

[@lskee4179]

Randomly stumbled across this, this is awesome! New Sub

[@martinsafarian4404]

Video starts at 14:50

[@Nomad8188]

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.

[@cameronweber2043]

He catches this error like 5 minutes after

[@inspired_by_happy_living]

Thanks for the video. Please prepare an interview/career guide for private equity so that we can study it and Ace interviews. Thank you.

[@adrilife2709]

Joshua thanks for the explanation while going through the model, very valuable material.

[@ballsyclan7913]

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?

[@felipedebaere2185]

Valid questions which I would also like to see answered

[@financeinterviewcoach-josh5963]

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.

[@ballsyclan7913]

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

[@avarma618]

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

[@GOAT352u]

@@financeinterviewcoach-josh5963 Well, shouldn't we use the cash than to pay down some of the existing debt. The problem I see now is that 25M just disappeared into goodwill and PP&E. I would have just subtracted the 25M from the existing debt, or would that be a mistake?

[@SouthsideWing]

Great stuff. Have been practicing several times

[@akinwunmizainab6070]

Can you pls recommend website to learn investment banking from scratch, majorly practicals that involves excel

[@kellenjemeyson7072]

Show the part where you lay off all the front line staff to cover the debt service

[@simondechoisy779]

Josh - would it ve possible to get hold of a copy of the case study instructions?

[@saudarshi6916]

I wish I saw this video 5 years ago, Great job!

[@financialrecovery453]

Great to watch. Well done!

[@georgestewartjr]

Extremely helpful video! Definitely worth watching multiple times.

[@migueltavarez23]

Awesome video, it would be good if you also put the keyboard entries so we can see the shortcuts you use in excel 👍

[@harlansmith7193]

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 :)!!

[@thechanagal396]

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

[@DH0518]

1:28:35 Did you deduct Term Loan Mandatory Amortization twice?

[@Mr3921151]

yes agreed i believe he did...i had the same question.

[@alexandervalladares2501]

This is an excellent video … Thank you

[@felipedebaere2185]

Thank you for this content boss!

[@financeinterviewcoach-josh5963]

Anytime! Cheers. More to come :)

[@frankyohance4625]

This is awesome! Can we download the case study and the xls sheet somewhere?

[@financeinterviewcoach-josh5963]

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

[@benjaminpowell5376]

Loved the walkthrough, can we download this anywhere? thanks

[@financeinterviewcoach-josh5963]

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

[@lindsaybok9002]

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!

[@Fact-Flingofficial]

Plzzzzz try to upload dcf i like your videos too much man

[@cristianoronaldo-lr2mw]

What is the revolver

[@nicolashassoun1731]

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?

[@FIN-u7i]

1:19:42 those values of D&A have been changed in the start we put it as 2% of revenue but its different now that made my calculations different than yours is it a mistake or did I miss something

[@beyondalpha2437]

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

[@hrishikeshshinde5150]

I dont think so. First one is correct

[@eamonnarasaratnam7213]

no, neither depreciation nor amortization are included in SG&A

[@lukasfinster7890]

great stuff!! Have learned a lot - i have tried to replicate it but noticed that cash - interest is not considered. Could you please explain?

[@sarahfasson3055]

are you not double counting the mandatory amortization at 1hour 29min mark?

[@Justmyopinion_]

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?

[@thomasjp95]

I have the same doubt. I think the max function is just standard practice

[@원도훈-m4w]

​@@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! :)

[@sambreakwell884]

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?

[@safeershahid9150]

Hi! its an amazing tutorial. please share the practice file so we practice along with the tutorial.

[@ahmedosama5205]

Amazing Video ! Thanks

[@financeinterviewcoach-josh5963]

No problem! I'm glad you liked it.

[@TokyoTaisu]

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?

[@financeinterviewcoach-josh5963]

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!

[@godismyway7305]

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.

[@davidmartins5320]

No it’s not….if you want I’d put you through

[@ajaydhameliya5793]

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 !

[@georgewood00]

Amazing thank you so much

[@paronzoda]

Really interesting video, tho could you open a few more tabs pls ;)

[@filipeiller986]

What is the short cut to copy paste cell references that he uses frequently?

[@SBMRadzwa]

Hi Josh, @ 1:22:15 When doing the Debt schedule, your income statement numbers change, is there something I missed?

[@financeinterviewcoach-josh5963]

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.

[@AdanSheikh-uq1tg]

Why are financing fees considered Non-current assets instead of Non-Current Liabilities?

[@davidxieus]

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!

[@financeinterviewcoach-josh5963]

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.

[@원도훈-m4w]

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

[@desijiga2002]

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.

[@Rock321333]

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?

[@davidxieus]

Have you ever figured it out? I'm getting 181, 180, 182, 178, 173....

[@annajones6866]

What's the shortcut to duplicate an excel file, ALT W N? Tried that but didn't work

[@financeinterviewcoach-josh5963]

Yes. make sure to hold alt down, only works on PC

[@annajones6866]

@@financeinterviewcoach-josh5963 Fab thanks!

[@pvzlkjvfna]

Is there a way to download the material used here?

[@krishnasinghalCFA]

Can I get this final template, Please?

[@timrose3460]

Where can I get the final Spread sheet?

[@GeorgiosZaphiropoulos]

Great video! Is there a reason you don’t depreciate the scheduled CAPEX?

[@financeinterviewcoach-josh5963]

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.

[@zohabashir2797]

Where can I find the full question and solution excel?

[@mdkhaledhasan8059]

*Multiplies revenue by 20% to find receivables* "I dont know how to explain this, maths is honestly one of the hardest" 01:02:00

[@Julia-sz1xn]

Can you provide the template?

[@samuel.andermatt]

Why is the revolver (lowest interest) paid off first?

[@yunjiebao6367]

because it has the highest seniority

[@grynda123]

Guys why entry and ex8t multiple the same?

[@martyf3279]

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?

[@vaishalijain8902]

I am facing the same issue

[@vaishalijain8902]

Did you find the way around it?

[@martyf3279]

@@vaishalijain8902 You need to enable iterative calculation via Excel Options > Formulas > Enable Iterative Calculation

[@JoshOlawale]

Unused fees and commitment fees are different no? 1:36:57

[@tw430]

You need to pay commitment fees on unused balances at a lower interest rate

[@JoshOlawale]

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

[@jd5787]

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!

[@nothingiseverperfect]

We went from Hunter gather society to this

[@TheKangoeroe]

How would you deal with the deferred tax liability? Do you get higher D&A in the P&L?

[@financeinterviewcoach-josh5963]

Yes, you amortize it over the lifetime of the incremental PP&E

[@maximopara6814]

Where can someone learn about this?

[@natashasajnani4352]

Could you please share the excel file?

[@alexanderkent2597]

Pretty good, except Gross Profit - SG&A = operating profit (EBIT)… he subtracts EBITDA from Gross Profit to get SG&A, which is incorrect.

[@financeinterviewcoach-josh5963]

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.

[@jci95]

Hi Josh, quick question. How do you calculate the cash from exercised options? Does it link to the equity value of the management options?

[@financeinterviewcoach-josh5963]

Yes, it's just the original equity value x option %.

[@TheGuillotineKing]

I did this in under 5 minutes with AI my how times have changed

[@rahmanonimisi2917]

What AI did you use?

[@haykgalstyan9116]

would not your EV include cash, so you have to exclude to get to equity value.

[@financeinterviewcoach-josh5963]

The excess cash is already reflected in the sources (cash from balance sheet) and uses (minimum cash balance)

[@rileycacamacdonald9690]

1607 Brody Field

[@nicolasguerreroruiz7652]

why dont you subtract net debt from EV? you have 50m in cash at the beginning..

[@nicolasguerreroruiz7652]

you subtracted gross debt

[@financeinterviewcoach-josh5963]

The excess cash is already reflected in the sources (cash from balance sheet) and uses (minimum cash balance)

[@AnthonyThomas-n3w]

274 Armstrong Expressway

[@martinsafarian4404]

You forgot to substract cash in purchase price

[@CroninLynn]

3515 Gennaro Island

[@WrightGracie]

3093 Lonie Parkways

[@lugas2267]

Omg voice twin of Sam Sulek

[@spak4319]

15:10

[@YkgjfhFhfhjr]

8692 Loyal Roads

[@oSJmee]

Just curious, does knowing this has a future? I guess a KI could do this task in seconds.

[@timpoos4635]

Looks pretty boring

[@ezekielchristiannnn]

Yea that's our job, is it boring? No if you like but to most people? Yes it is

[@Sebastian-hg3xc]

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

[@lcandothat]

@@Sebastian-hg3xc so you think if all of the finance industry stopped working nothing would happen?

[@JohnFare]

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

[@pectenmaximus231]

@@Sebastian-hg3xcthe mine potentially gets capital from the types of firms that do this boring modelling. These boring models figure out the efficient pricing and tries to identify risks. This modelling seems superfluous but it really does contribute to making sound financial decisions which underpin all the other more ‘obviously’ or ‘conventionally’ productive activity in the world.

[@닝닝닝]

As I get closer to the end, it gets messy....

[@maguirakonwell]

550 Bryon Well

[@scottm8212]

Wow

[@DonaldMartinez-z6k]

Kristian Route

[@AnjelicaBiagini]

8804 Felton Shoals

[@GarfieldGlander-t5h]

Kshlerin Ranch

[@닝닝닝]

59:34 alt e s l

[@LyndDaphne]

699 Kunze Place

[@javedalam6115]

Hii

[@OmerLints-m3s]

Powlowski Loaf

[@FudgeBondu]

Walker Jeffrey Jones Brian Walker Jennifer

[@JimmyShea-d6s]

Zieme Points

[@Sebastian-hg3xc]

what fantasy world full of nonsense did i just enter.

[@MacaulayKelly]

8862 Geovany Hollow