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

Подписаться 8 тыс.

Просмотров 238 тыс.

50% 1

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.

Опубликовано:

6 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 127

@georgepatterson5167 9 месяцев назад

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.

@martinsafarian4404 20 дней назад

Video starts at 14:50

@praveen111989 8 месяцев назад

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

@kellenjemeyson7072 8 месяцев назад

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

@migueltavarez23 9 месяцев назад

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

@harlansmith7193 9 месяцев назад

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

@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 8 дней назад

He catches this error like 5 minutes after

@lskee4179 Год назад

Randomly stumbled across this, this is awesome! New Sub

@saudarshi6916 2 месяца назад

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

@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 7 месяцев назад

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

@farhanashaik238 7 месяцев назад

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

@thechanagal396 2 месяца назад

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

@alexandervalladares2501 Месяц назад

This is an excellent video … Thank you

@lindsaybok9002 2 месяца назад

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!

@financialrecovery453 3 месяца назад

Great to watch. Well done!

@SouthsideWing 4 месяца назад

Great stuff. Have been practicing several times

@akinwunmizainab6070 4 месяца назад

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

@lukasfinster7890 4 месяца назад

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

@safeershahid9150 4 месяца назад

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

@georgestewartjr 8 месяцев назад

Extremely helpful video! Definitely worth watching multiple times.

@felipedebaere2185 Год назад

Thank you for this content boss!

@financeinterviewcoach-josh5963 Год назад

Anytime! Cheers. More to come :)

@Fact-Flingofficial 7 месяцев назад

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

@paronzoda 9 месяцев назад

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

@simondechoisy779 Год назад

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

@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

@DH0518 2 месяца назад

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

@Mr3921151 2 месяца назад

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

@beyondalpha2437 5 месяцев назад

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 5 месяцев назад

I dont think so. First one is correct

@eamonnarasaratnam7213 4 месяца назад

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

@benjaminpowell5376 2 года назад

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

@nicolashassoun1731 6 месяцев назад

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?

@ahmedosama5205 Год назад

Amazing Video ! Thanks

@financeinterviewcoach-josh5963 Год назад

No problem! I'm glad you liked it.

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

@krishnasinghalCFA 7 часов назад

Can I get this final template, Please?

@filipeiller986 3 месяца назад

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

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

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

@user-sd7ho9dp2y 7 месяцев назад

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

@sambreakwell884 4 месяца назад

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?

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

@AdanSheikh-uq1tg 2 месяца назад

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

@Julia-sz1xn 20 дней назад

Can you provide the template?

@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

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

@alexanderkent2597 11 месяцев назад

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 9 месяцев назад

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.

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

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

@Justmyopinion_ 11 месяцев назад

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 9 месяцев назад

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

@user-sd7ho9dp2y 7 месяцев назад

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

@samuel.andermatt 4 месяца назад

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

@yunjiebao6367 2 месяца назад

because it has the highest seniority

@SBMRadzwa Год назад

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

@financeinterviewcoach-josh5963 9 месяцев назад

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.

@user-yk6dt6td7z Год назад

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.

@grynda123 8 месяцев назад

Guys why entry and ex8t multiple the same?

@timrose3460 9 месяцев назад

Where can I get the final Spread sheet?

@zohabashir2797 Год назад

Where can I find the full question and solution excel?

@philipbunford6947 8 месяцев назад

Is there a way to download the material used here?

@natashasajnani4352 3 месяца назад

Could you please share the excel file?

@martinsafarian4404 20 дней назад

You forgot to substract cash in purchase price

@nothingiseverperfect 9 месяцев назад

We went from Hunter gather society to this

@DolaerAhommed 2 дня назад

Young Daniel Lee Brian Garcia William

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

@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

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

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

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

@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

@AgnesEsther-n5r 18 часов назад

Young Daniel Rodriguez Elizabeth Thompson Timothy

@oSJmee 9 месяцев назад

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

@scottm8212 9 месяцев назад

Wow

@MarjorieAdams-c4u День назад

Hernandez Richard Lewis Susan Young Angela

@닝닝닝 7 месяцев назад

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

@lugas2267 8 месяцев назад

Omg voice twin of Sam Sulek

@TrumanKim-m2w 15 часов назад

Gonzalez Frank Walker Anna Gonzalez Brian

@javedalam6115 Год назад

Hii

@timpoos4635 9 месяцев назад

Looks pretty boring

@AliRethrbuck 9 месяцев назад

Go work in a mine then.

@ezekielchristiannnn 9 месяцев назад

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

@Sebastian-hg3xc 9 месяцев назад

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