Understanding Leveraged Buyout Modeling

Hey everyone and welcome to an LBO streaming tutorial type video and a lot of you guys have asked for something different than just the DCFs and so this is going to be the start of a lot more kind of different types of modeling streaming videos and stuff like that. That's pretty much the introduction. Let me go into a few more things a little bit later but before I do wanted to let you guys also know about today's sponsor for the video WallStreetPrep and Wharton. Wharton is where I just got my MBA a few weeks ago, just graduated. But if you're watching this video, I'm guessing you may be interested in private equity. You know, why else would you really want to build an LBO? Maybe also if you're interested in banking, I guess. But they have this private equity certificate program where you learn at your own pace for eight weeks through this online program. And as you scroll down here, you'll see that there are a lot of companies that sponsor and kind of work with you in this program like Carlyle, KKR. Blackstone, etc., top private equity firms. It's been around for I think about a year now and they had over 500 students and people come from various different backgrounds and countries. You can see that a lot of people who have taken this program in the past are actually in investment banking or private equity and they just want to kind of like brush up on their skills. So you can kind of go onto my website or on to the site in the description but just to go into what you'll learn here. You'll see that you get an intro to private equity. You'll get more information on how to think about investing in like a framework, the private equity deal process, deal structure, valuation and modeling, of course, and all this good stuff. In terms of speakers, you can see you have Wharton professors and then Scott Roman, who's the private equity program director for Wall Street Prep. And then keynote speakers from pretty established. Private equity investors like David Rubenstein-Carlisle, he's probably the most famous one here. Martin Brands, also a speaker, a panelist, works at Blackstone. And then if you scroll all the way down, here is what I think is also super helpful. So peer-to-peer networking, you kind of join your cohort and then also are able to interact with people who are now alumni of this program, I guess. It's kind of like a LinkedIn invite-only group. You get a certificate and then recruiting events from the top headhunters. So feel free to check it out. I know it is pricey. You can use my code rareliquid to get $300 off. And if you sign up by the early registration deadline, then you get another $200 off. And this program runs three times a year. So pretty much anytime when you're watching this video, you could probably check that program out. But now let's move on to a few more things that I had in mind that I wanted to share before going into the LBO. So one is, so this is going to be the first. like just a simple LBO tutorial video that we're going to do completely from scratch. And this is kind of more so to provide a good foundation for what's to come in the future versus just starting like an advanced LBO tutorial video like right away, right? It's going to be probably too complicated for most people. And so today is going to be relatively simple. If you want to get email notifications for when I do these streams and or notifications for when I have the links ready to download the models because you can download this model for free once I get the link ready and everything. There's a link in the description for you to sign up for those notifications. And also I'm thinking of starting some kind of discord. And so I will be sending that out via email as well. So I'm thinking on that discord, maybe what I'll do is I'll just like also post these models and you can just download them on there. So Anyway, if you're interested, wait for that or sign up via email there. It's in my description. You can also look at related videos for LBO and Excel tutorials that I've linked in the description. And then also I have the private equity videos that I made in a playlist in case you're interested in that. I also realized I never really plug my Instagram and TikTok. And now that I've graduated from my MBA and have much more free time, I'm hoping to make more short-form content about my MBA life or MBA lessons, also just about finance in general. So feel free to check those out. And then I've recently launched an MBA course called Liquid MBA for how to get into MBA and working on ones for banking and consulting as well. So I know a lot of like plugs here and everything, but I thought a lot of these might be of interest to you guys. A lot of free resources and some of these, of course, are ones that I built that will be paid for or need to be paid for. So I will, as usual, just answer questions as you guys have them in the chat. But with that said, let's go into building this LBO. And so there are two ways I was thinking about building this out. First is like maybe we have some financials like for a real company. And then I just build out an LBO based on the numbers. And I think I'll do that in the future. But for today's LBO, I was thinking it'd be easier and simpler to just start off. with let's say you're given these assumptions and you need to build out an LBO. And this is typically kind of like it may be similar to if you ever get a private equity interview, then this might be kind of similar to what you may have to do. They just give you a bunch of text and then you have to build a model out of it. Other times they'll give you financials for you to work with. And, you know, I'll provide examples of that in the future as well. So I'm just going to format things a bit to how I want it to look. And all right, so let's kind of read through this. You are a private equity firm looking to potentially buy out an insurance company. In the last 12 months, the company has generated $2 billion in sales, $900 million in COGS, $500 million in OPEX, $100 million in DNA, $200 in networking capital, and $140 million in CapEx. For simplicity, your firm assumes it can grow the company revenue at 10% for the next five years, assumes a 21% tax rate, and assumes all margins will remain flat. So this really simplifies a lot of things. Your firm also believes it can enter and exit at a 10 times EBITDA multiple at the end of year five. Fees and expenses are estimated to amount to $100 million, and there will be three tranches of that $200 million for a revolver, 7.5% interest. $2.5 billion from a bank loan, 9.5% interest, and $1.5 billion from senior notes, 12.5%. Calculate the firm's IRR. All right, so what we're just going to start off with is building out a section for our assumptions. And let me also actually zoom in so you guys can see everything. If things are too small later on, let me know, and I will... I can increase the size or zoom in more. So assumptions are going to be here. I'm going to make this white. Oops, let's make this five. So we're going to start off by including our financials here. So let's make this this blue color and make... this white so what assumptions do we have here right everything is kind of laid down for us so we're gonna assume or we're gonna label this as ltm financials or ltm we have financials up here anyway so we're gonna have revenue cogs opex dna networking capital um 140 million oops capex what And then, so that's this line here. For simplicity, 10% revenue growth. So let's say we call these operating assumptions, revenue growth. And then we also have here tax rate. All margins will be flat. So now we're just going to need to kind of include the numbers here. And so let me build this. in the style I used to, I usually like where it's kind of dotted. I think it's this one. Yep. Okay. I'm going to move this a little bit to the right. Okay. So revenue here is 2 billion. We're going to do everything in millions. And I'm also going to... Make this... I like using this one with the parentheses for negative. Also need to make... or use commas. So, and I'm also gonna turn this into blue. Because blue just represents... Let's see which one is good. Blue represents that it's a hard code. And there's one that's like really commonly used, but it's not in the color wheel for some reason. Okay, I cannot tell if my eyes are... Okay that looks okay. We'll just stick with this one. I'm going to copy the formatting alt-est. Cogs here 900 million. OpEx is 500 million. DNA 100. Networking capital 200. CapEx 140. Revenue growth rate 10% and going to need to format this as well. I also need to make this blue. This blue. Okay. Tax rate, 21%. So that covers... So let's just put an X here for what we've covered so far. Your firm also believes it can enter and exit at a 10X multiple. at the end of year five. So let's now do our entry and exit multiple stuff. So entry, we're going to need to get calculate our last 12 months EBITDA and then get that and then get our EBITDA multiple. That's going to get us to our enterprise value. Um, so we're gonna connect to that. Okay, actually so first thing what I want. Uh, well, let's see. Um Okay, let's just build this part out first and we can fill stuff in later because there's gonna be some things we can't do right now Okay, it's being weird Okay, so we're going to need to calculate enterprise value, then get our existing net debt that get us to our equity value. And then we have, let's see, fees and expenses. Fees and expenses copy here it's just going to be 100 million and then let's also have our a section for our exit and that'll just entail our um well let's see let's call this entry multiple actually and then we'll call this exit multiple And this we actually have already, which is 10. And let's format this so that it has an X next to it. I know I did that really fast, but to do that, what you can want to do if you're on a PC, I don't know what the shortcuts are for Macs, Control 1, then Tab. That allows you to press the up or down arrow key. And then you can press Tab again. 0.0x so basically changing the type press ok and then we can do that again here so uh well actually yeah well yeah we would need to do it again here so we're gonna in our assumptions here it says that your firm believes it can enter and exit at a 10x multiple and it's very common to have the same multiple for when you enter and exit because you can't always bank on a company in the future having multiple expansion, right? So that's why a lot of assumptions, you know, to be kind of conservative or... realistic you want to assume it's the same but hope that the multiple expands over time okay so we need to calculate calculate our EBITDA later and then existing net debt we may be able to do let's see actually I think I realized that as I was creating these assumptions one thing I forgot to include was existing net debt. So I'm gonna actually add in here one more assumption. Let's just end here. The company has existing net debt of $1 billion. all right and we'll calculate the rest later so now we need to think about our capital structure so we have a revolver and so a revolver is just like a credit card but for companies literally that's what you can think of think of it's like it's like short term usually less than one year in terms of repayment so it's a little bit longer like credit cards usually we pay off every month or you should at least For Revolver, it's usually within the year, but you can obviously pay it off sooner. And then bank loan, also often referred to as term loans. And then senior notes. So I also got a question on the last one of my LBO tutorials about like why in later on, actually, it'll make more sense after I build this out. But here, let me do this first. And then. talk about it. So amount and then interest kind of for interest rate. So let's see. Let's copy paste this again amount. So there's three tranches of debt, 200 million from a revolver, 2.5 billion from. a bank loan and 1.5 billion from senior notes the revolver has 7.5 percent interest bank loan 9.5 and then the senior notes 12.5 so in my one of my lbo tutorial videos someone asked me when i built the debt waterfall which we'll do later why i paid off bank loans before senior notes When senior notes have a higher interest rate? Great question. And the reason is because a lot of times bank loans, they'll have a clause or something that says, you got to pay us back first. That's why we'll give you this like kind of lower interest rate or whatever. I actually did respond in some comments that I thought hadn't made a mistake. But then after kind of rethinking about it, I was like, oh, actually, I didn't make a mistake. So that's why oftentimes even with a lower interest rate, you'll pay it off first just because you have to. All right, so let's build out the other sections. So we're going to need our sources and uses table. Make this five, row height five. We're going to need some of our... We're going to need like EBITDA and stuff. So what I'm going to do first is actually build out our financials and start off with our operating model before we go into the sources and uses table. Okay, so before I go into this though, let me see if there are any questions here. So regarding LB on DCF, how do you think investment banking will change with AI in terms of models in general? I can't predict way into the future, but I think in the immediate future, it'll just be another tool that bankers use to make their processes more efficient. So what I often kind of talk about when this question is asked is back in, I don't know, 30, 40 years ago. pre-internet my mds would say that way back when or whatever you had to go to the library to photocopy annual like 10ks or 10qs or whatever bring them back to the office and then build your models and whatever do stuff by hand etc now you can get like all of that information in an instant from fax at cap iq bloomberg but bankers still work super super long hours right so i think also it's going to be really hard to build out really good i don't know like i think you can get the you can get the foundations for a model but then you're going to have to make a lot of adjustments anyway and i think the new norm will just make it so that you're producing a lot more work for clients versus like taking you know giving analysts like 20 hours of their week back so they work less um okay subscriber i see you're a subscriber since i was at 100 thank you I have a grateful for an answer. The question is more about replaceability. How do you think everyday life will be changing? Everyday life, I think you'll just start. I mean, I use Chachi PT all the time for like everything. And I think there will just be more and more tools that come along for financial modeling as well. I can also give you another example. So when I was an investment banking analyst, I had to build these things called strips. and profiles of companies so let's say that we were trying to tell one of our customer one of our clients that they should consider buying you know any of these like 30 companies we would have to build out these profiles of companies and they're like powerpoint like one one slide per um per company or it would be like five in one slide and those would be called strips because there'd just be like one row each but a lot of times we had to Find all the information, enter it all in and do a lot of just like grunt work that really, really was so annoying. Hated doing those. And those kinds of things, I feel like nowadays I would just go to Chachapiti and be like, hey, give me give me some information about this company and like give me the management information, like give me a link. And I just like it to make my life like so much easier. So that's what I think. Analysts will be faster, but I don't know if it'll like really replace analysts or anything for a while. Okay, so I see another question about revolvers and senior notes. I already explained revolvers, so I don't want to repeat myself too much, but it's just like a credit card for companies, essentially. Less than one year, you can take out money. Senior notes are just usually a little bit, like you get them from sometimes different sources instead of just a standard bank, and they offer a higher interest rate. And sometimes you just need to take that out. at a higher interest rate because you can't just get it from other places right so okay let's build out our operating model a bit so starting with revenue and then percent growth and add a few spaces here then we're going to have our cogs and this will we want to see percent of sales we're going to use this format quite a bit so i'm just going to copy paste this down This gets us to our gross profit and then we have our OpEx that gets us to our EBIT. And let's see here. So EBIT then let's say we get our DNA then our EBITDA. And then your interest, which we will have to use as a plug for later. That gets us to our earnings before taxes. Then we get our taxes. Let's use tax rate. And then that gets us to our net income. So a lot of rows, but it's going to be pretty easy. Let's see. So let's, let's call this. I'm just going to, I'm not going to use fake years. I'm just going to call it year five. Oops. Call. And then oops, year four. Actually we can make it. So this is three, two, one. So let's use custom again. So we're going to say year and then space. Cool. So now we have our years, year zero to five. Year zero is basically going to be LTM for us. So we have our revenue, we can just link to up here. And when you're linking to a number in the same sheet, then At JPMorgan, the industry standard was making that purple. Percent growth, we don't have that for this year. So it's going to leave a blank. But we do have revenue growth here. So again, going to make it purple. And we can just copy paste it. You can do one of two things. You can either anchor this and make it all anchored to that or you can just make it equal to that. And remember our assumptions here. It says all margins stay flat. I guess I'll add here. I'll assume all growth rates and margins will remain flat just to make sure that it's covering all our bases. So multiply this times one plus our growth rate. Do that. Okay, so we have our revenue growth and then our cost of goods sold, 900% of our sales. And remember we said that... or the assumption is that the margins will stay flat so we're going to keep it at 45 and then multiply our cost of goods sold margin by our sales and since we're gonna be using a similar format so i'm just gonna copy paste the format so what i did here is control shift right arrow key down arrow key control copy down arrow key a few times and then alt est and that copies the formatting here so this will be purple this will be black gross profit is our revenue minus our cogs and then percent of sales or sorry yeah for our margin um yeah i guess we needed to calculate it so no need for it to be needs to be purple too. All right. Next, our OpEx, it will be this format. So I'm going to copy paste that there, connect this to our OpEx, which is 500 percentage of sales. So we want this to be flat and then multiply our margin to our sales. And then that gets our OpEx. Now to calculate our EBIT, pretty simple, I'm going to copy paste this. So gross profit minus our OpEx and then percentage of sales is our EBIT divided by our sales. 30% remains flat. And one thing you want to do as you're building out your model, like if something generally speaking, and this is true on the job as well, When you're building out your model and then the numbers look a little weird, you always want to be kind of checking for that. So, for example, you know, I'm building this out relatively quickly, but I can easily just, you know, get accidentally, you know, get my gross cost of goods sold minus like my OPEX or something like that. And then that number would look way too low. Or a lot of times a mistake I make is sometimes I will be, I'll need my... percentage of sales but then let's say like i try to copy paste it and then it's like a you know large weird number right it shouldn't be larger than your like or very similar to your gross profit if you have 25 of sales for your opex so just check the trends and stuff for me when i look at this it's like okay everything's at 30 this looks like it's probably right which is why i thought of bringing that up okay so Now let's do our... Actually, I think I can just copy paste both of these. Because our DNA is going to be similar to our OpEx where we link to it. And then we'll need our percentage of sales. I'm going to say stay flat. And then this times our sales. If I wanted to be more efficient, I could definitely anchor this row. and then multiply this and instead of constantly going back to our sales like i could just copy paste this instead um probably should have started doing that earlier uh ebitda is our ebit plus our dna and then ebit uh over our sales so again i could have just anchored this and then done that for this one as well. But yeah, 35% makes sense. You know, this 30% plus the 5% gets you to 35%. And now we have our EBITDA for year zero. So before we go into all this other stuff, which requires a lot more, let's finish out our sources and uses. So EBITDA is 700. Let's connect to that. Make this purple. Enterprise value is 700 times our entry multiple. So that means we're buying the company for 7 billion. Equity value is our enterprise value minus our net debt. So 6 billion in equity value. And now we can build out our sources and uses. So to do that, what I'm going to do is add some more space here. Okay, so we're going to have our sources and then our uses. The lights are like super bright of where I'm at right now. Okay, uses. And then we're going to, for our sources here. Oops. Sources is, you can think of it as like, where are we getting the money? And we're getting the money from two sources. One is like the debt that we're going to take on, the new debt. And then one is these private equity firms cash and their money, basically. So that's going to be the sources of where we're getting the money to purchase. And then where are we going to use that money? We're going to use it to buy the company, refinance the existing debt, and we're going to use it to pay off fees and expenses for lawyers, for when we're taking out new debt, we have to pay banks, all that stuff. So what we're going to have here, I can just link to our debt. So we're going to have Revolver, bank loans, senior notes, and you also... You don't just want to have like the amount you also want to have the times EBITDA and then percentage of capital. These are helpful to just have as reference. So revolver amount is 200, bank loan 2.5 billion etc and then we can see what like the multiple of EBITDA that it's that it is. So I think this will be a little bit more clear once I do it for all of them. So I anchored the EBITDA so I can just copy paste it down and then we want the total here for reference. These are going to be in percentages. Do that a little bit later. And then sponsor equity. So the sponsor is actual money basically. And we're gonna... that is always a plug. So I'll tell you what that means later. But we will want our total sources here. And I guess what I need to do is fill out our uses first actually. So here we're going to need debt refinancing, equity payment for the actual company, fees and expenses. And a lot of times when you're building out like a proper DCF, or sorry LBO, you're going to need a lot more. But in our situation, it's pretty simple. So this is all we're really going to need. I'm going to add a little bit more spacing here just to make this model a little bit aesthetic, more aesthetic. All right. Debt financing, $1,000. Equity payment, $6,000. Fees and expenses, $100. Percentage of EBITDA, you can just copy paste this formula. And then our total uses. Your total uses always have to equal your total sources. And that's super, super important to remember. Percentage of capital. Now we can calculate this. This is just simply. your line item divided by the total. This is percentage. Okay, 14.1%. And for this as well, you want the sum. This, you always want to make sure it's 100%, right? Everything that you're using, all the percentages should equal 100%. If not, then that's just a check. You know, it's an easy check that you have in there to see if you did anything wrong. Let's add a border. Okay, so now we have our total uses. And our total sources always have to equal our total uses. And you just simply add or make this line equal to your total uses. That way they will always equal each other. You'll never get that. You'll never... have a misbalance in any sort. You can't even have a 0.00000001 difference. It always needs to equal exactly. Otherwise, the money's not matching, right? You need to pay exactly what you need to pay in order to acquire what you want to acquire. So sponsor equity here is the plug, meaning that what you're simply doing is getting the total sources and then subtracting out what you're financing with. And so then what you're left with is $2.9 billion. That's what the sponsor needs to pay. What you see here in terms of multiplied by EBITDA, you're taking on six times EBITDA of debt in order to fund the acquisition of this company. And obviously I created all these assumptions and so I made it exactly six because at least when I was at JPM, six times EBITDA was a generally rough kind of almost max-ish leverage multiple that you would want to use. I don't know if things have changed. They likely could have. But I just want it to be at six basically. And then we can also see what it is for sponsor equity for X and total sources. And then here we can do the same thing as we did for our uses, which is. getting our percentages. All right so now everything looks good for our sources and uses table. Pretty simple stuff right? It's not like it's not like too complicated. Now that we have a lot of the basics really built out what we have left is we need to calculate our interest. or our leverage-free cash flow, calculate like our debt waterfall, which gets us to our interest. And once we do that, our job's pretty much done because after that, we can calculate our IRR. Before I do that, let me look at the chat box to see if there are any questions here. Okay, I see a bunch of advice you guys are giving each other. This Clement asks, is this pre-recorded or live? It's live. I'm talking right now. Okay, so it seems like no questions as I was skimming through. I will, or let's go down here. And then we're going to now start building out our levered free cash flow. And then, or sorry, our cash flow items and then our debt schedule. So let's start here with our cash flow items. I see a question of what's the company. There is no company. It's a fake company. DNA, percentage of sales, networking capital. Oh, so I should also add later on, like this, if you missed the intro of this, this is going to be the start of like a LBO tutorial type series where things are going to get more and more advanced in the future. And later I'll do... an LBO of a real company. I'll also do an LBO like a three statement model LBO. We'll do like we'll add sensitivities for the different scenarios just like we I do for my DCFs. This is just the start of the series and so I wanted to keep things pretty simple to begin with. All right, just realize you may not be even asking about that, but okay. Anyways, what are we doing here? DNA, we have this, so we can copy to there, percentage of sales. You can always either recalculate it or just take it from what you've already calculated and I actually usually err towards the side of recalculating just because, but let's just not do that this time. Okay, networking capital, we haven't calculated yet. So let's go up here. Networking capital was 200. And that is... 10% of sales. Remember, everything is staying flat for this model. Oops. So percentage of sales, multiply this by sales. I guess I could have copy-paste that. Wait, could I have? No, I couldn't have. OK. And then we can calculate our change in networking capital, which is our current year minus our previous year. Then we're going to be doing the same thing for capex. So let's copy this formatting and let's go to the top to get our capex 140. Again, percentage of sales. We're going to make things flat and then multiply this by our sales. Okay, so those are our cash flow items. Pretty simple stuff. And then what we're going to need next is to calculate our leveraged free cash flow. This is a key difference between the DCF and the LBO. So the DCF, if you have watched a lot of those videos, we always calculate our unlevered free cash flow. And then, yeah, we don't include any like interest expense costs or anything like that. For a leverage free cash flow, we do. So we start off with net income and that already takes into account your debt because... you net income has already paid off your interest expense versus an unlevered free cash flow you don't take that into account because unlevered free cash flow formulas ebit times ebit times one minus tax rate plus dna minus networking capital minus capex ebit is earnings before interest and taxes right before anything is taken before any interest is paid so you're not taking into account your your debt or your interest versus your net income. You've already paid your interest. So that's how that works. Um, Leverage free cash flow is a similar formula to your unlevered. It's just that you start with net income. Then you add DNA, subtract out CapEx, subtract out change in networking capital, and then mandatory debt repayments. But we're not going to have that for this model. So we're going to calculate all this stuff a little bit later. Actually, it's kind of weird when it's blank. So let me kind of calculate some of this stuff. I'll just highlight this part for now, noting that we need to calculate this later. But our EBIT or earnings per for taxes is our, wait a minute. Oh, it's simply our EBIT minus our interest. This again needs percentage of sales and then tax rate is 21%. Oops. And let's just make that equal. Keep it flat. So taxes, earnings before taxes times our tax rate. And then net income, earnings before taxes minus your taxes. Oops. All right. So we have a 23.7 net income margin or profit margin. But of course things will change once we calculate our interest. So in here what we can do is connect to our net income for now. DNA let's make all these purple actually. So DNA, capex. change in networking capital and then you can just copy paste these across that gets us to our leveraged free cash flow what you want to do is get your net income plus your dna minus your capex minus your change in networking capital And usually you have mandatory debt repayments as well. Very important not to forget that. But in our model, we don't have that. So we don't need to worry about it. All right. So that concludes our financials section. And now we need our debt waterfall, also known as your debt schedule. So. What we're going to start off with is the kind of quote unquote like highest tranche of debt, which is the revolver. Basically what you need to pay off first, because you're going to see as we build this out, it's called the debt waterfall because you start off with your leverage free cash flow, aka the cash you have that you can spend or pay back debt. And then it flows down to what you first need to pay off, which is your revolver. And then it's going to be your bank debt and then the senior notes. And it's going to waterfall down, basically. And you'll see what I mean. So we're going to start off with our revolver. And then we want our beginning balance. And then our interest that we'll calculate. And then our pay down. That leads us to our ending balance. indent this. Maybe best way might be six. Okay. So in year zero, we have this figure, right? You can either connect it to your sources and uses or up here to your assumptions. They should technically be the same thing. I also realized I should have made this purple. Okay, I think also these need to be purple. Yep. Okay, anyway, so you can connect to this or you can connect up there. Honestly, it doesn't really matter as long as everything's linked correctly. But I'm going to link to up here. So that's your ending balance, right? At the end of year zero, basically when you decide to do the transaction. And then your beginning balance is the same as your ending balance. Now, this is where I guess things can get a little bit more on the tricky side. And where you kind of where, yeah, this is the trickiest part of the model, I guess, but it's not like super, super tricky, I think. So interest for our scenario is very simple. We're just assuming flat interest rate. A lot of times you need to like take LIBOR into account or something like that. And that'll change depending on the day really. But in our case, we're just simply going to get our beginning balance and then multiply it by our interest rate. And then our pay down is going to be the minimum of either our leverage free cash flow, the cash we have, or our beginning balance. So in this case, the minimum is our beginning balance, right? So we're going to have $257 million in cash left over that we can use to pay off other debt. What we do here is beginning balance minus our paydowns, which is zero. And then from here on out, you can actually just copy paste these formulas. And it should be zero because we paid down our revolver, right? So, okay. That's our revolver and then I'm just going to copy paste this and make adjustments but it'll just save some time because we're going to have to do the same thing for our bank debt and then our senior notes. Except for here we want to now connect to our bank loan which is 2.5 billion and then our interest we want to multiply to the row let's see it's to below. which is 15 yeah 9.5 percent and this is the whole waterfall part so now we can't just first of all this is like linking to the wrong thing anyway what we want to do here is the minimum of either the debt balance or the amount of cash you have on hand to pay right which is your leverage free cash flow the amount of cash you have in that year in total. And then you want to subtract what you have already paid, which is a 200 here. And so what you can see here is that, so here's our interest payment or interest. Pay it down, just double checking right now to make sure that everything looks correct. All right, 503, 553, 609, 577. Okay, I guess we pay off our bank debt here at the end. Yep. And then do the same thing for our senior notes. Is this correct? Yep. So this, you want to multiply that to 16 now because we want to get our 12.5% you see here. And then now what we want to do here is the minimum of either your beginning balance or now your leverage-free cash flow minus what you've already paid for your revolver and or what you paid for your bank debt. Which should end up with zero. Yep. And then you can just copy paste this across. And you can see basically, this is why you can see the waterfall part now, right? You're paying off your revolver first. And then with the remaining amount of money, you're paying off your bank debt. And then with the remaining, you're paying off your senior notes. So you don't have any cash to pay off your senior notes until year five, when you've paid off your bank debt. That's your debt schedule. And now we just want to calculate just like a summary of like the totals. So what I want to do here is call it total debt. Let me just actually copy this. So our ending balance are these numbers here. Beginning balance is also here. Total interest is here. Total pay down is here. Is our pay down? Yep. It matches that. So now we have our figures here for our total debt. That's pretty much the LBO actually. We're pretty much done. So now all we need to do is link to our interest payment. So let's go up here. My interest payment looks really high but things are gonna be circular so we'll see how things turn out. Boom. So The numbers basically will decrease. And beforehand we had in here leverage-free cashflow of like 457. But once you pay off your interest, then it's gonna lower your net income, which is gonna lower your leverage-free cashflow, which lowers how much you can pay debt. So there's like circularity essentially. So just to explain that again, so you have your interest expense here, right? Your interest expense will lower your net income, which means that your leverage for cash flow is lower. If your leverage for cash flow is lower, that means your pay down is lower. And if your pay down is lower, that means your interest expense is, I guess, higher than if we weren't kind of... Kind of explained that wrong. But your interest expense affects your net income, which affects your leverage-free cash flow, which affects your pay down, which affects your net interest expense, essentially. And it's in a loop like that over and over again. Let me just double check to see that, see if I did all this correctly though. So we have our leverage-free cash flow of 110. We have... Beginning balance 200. Interest that we pay here. You're paying down 110. Then you can't pay down any here. Not paying down any at all here. Interest. Yep. Keep paying down. Max is 111, 264, 335, 415. So it looks right to me. So now what we need to do is just calculate our IRR. Before I did do that, I guess since I finished off a section, I can see if there are any questions, although it doesn't really seem like there are many. Isn't Revolver a debt instrument which you can use for short-term cash, i.e. when a leverage for cash flow is negative? One is. When it is negative, the revolver is drawdown rather than needing an equity injection. Yeah, that is how you can use it as well, like just like a credit card. But I would say for our scenario, we want to keep things simple. I don't know necessarily. I don't know the details of how a company would use it and if like for a private equity company if you're looking to buy another company and their cash flow is negative I don't even see the point of them really doing an LBO for the company if they have negative leverage cash flow. So in our situation and for an LBO, I would imagine that it makes more sense to just treat a revolver as a standard, more just typical debt tranche, at least for this model. Any other questions? Seems like no. Okay. All right. So let's calculate our IRR now. What we need is our LTM EBITDA at exit, then our exit multiple. That gets us to our enterprise value. And then we can subtract out our net debt. That gets us to sponsor equity value. Then we can compare that against sponsor equity at entry. and then we can calculate our money over invested capital and our IRR. So ltm evita at exit let's go up here 1127 exit uh let's actually make this purple as usual exit multiple connect to here Enterprise value is our simply our LTM EBIT dot times our exit multiple net that oops It's down here actually, 2875. And then sponsor equity is our enterprise value minus our net debt. Sponsor equity at entry is down here, 29, 2.9 billion. So money over invested capital is just simply, what do you end that divided by? what do you start at oops so 2.9 x your money essentially and then in terms of calculating our irr we can do this by getting our moic to the power of one to five one over five because that's how many years and then minus one that gets us to our irr of 24 okay so let me add Yeah, okay, so 23.7% is our IRR here. And I always like to add something here for reference. Can I have our MOIC and then our IRR? and link down to MOIC and our IRR. Make these... oops. Okay, so why do we have this here? It's because you can just play around with your assumptions and see how your... assumptions all change. So entry multiple of 10 versus like 11, you can see like if you have 15% growth rate or like 5% growth rate, you know, what it does to your IRR, increase in taxes to lower your IRR. And you can kind of like do it, you know, some checks, right? Like if you have really high expenses that lowers It should lower your IRR, which it does, right? But if it's not doing that as you're kind of building out the model, after you build out the model, then you should check why it's not working correctly, right? If you pay a higher interest rate, then that lowers your returns. If it's like 50%, then it'll lower your returns and everything. So that pretty much wraps up the LBO. And as I mentioned, you will... be able to download this for free. To go over a few other things before I kind of sign off here, check out Wall Street Prep and Wharton, their private equity certificate program, if you're interested. This is going to be the start of a lot of more videos for LBO tutorials because it seemed like there was a lot of interest in them. So later on, we'll be using real financial figures from real companies. build more sensitivity around like different cases and then also be adding like three statement modeling like a lot more complicated advanced stuff you can sign up for email notifications for when i do these streams and also for these uh models just to be like sent to you and also i'm thinking of starting a discord so i'll also send that link out if you want to sign up links for all of this stuff will be in this video's description and if you're If you want to learn more about LBO stuff and start with the basics, then you can look in the description for related videos. I also have Excel and Excel tutorials and private equity videos. I'm starting to post more more on Instagram and TikTok. So feel free to check that out. And then I have a few courses you can check out my MBA one I already recently released. And then I'm building out one for banking, which is probably most relevant for you. And then one for consulting as well. So I hope this was helpful. I know. I made a lot of DCF videos and I wanted to make some other types of ones and kind of need to brush up on some of my skills and prepare you know all this stuff before I could actually do so but now that I've graduated from school have more time so look forward to more of these in the future and thanks as always for joining the stream hope to catch you guys in the next one see ya

Wharton is where I just got my MBA a few weeks ago, just graduated. But if you're watching this video, I'm guessing you may be interested in private equity. You know, why else would you really want to build an LBO?

Maybe also if you're interested in banking, I guess. But they have this private equity certificate program where you learn at your own pace for eight weeks through this online program. And as you scroll down here, you'll see that there are a lot of companies that sponsor and kind of work with you in this program like Carlyle, KKR. Blackstone, etc., top private equity firms.

It's been around for I think about a year now and they had over 500 students and people come from various different backgrounds and countries. You can see that a lot of people who have taken this program in the past are actually in investment banking or private equity and they just want to kind of like brush up on their skills. So you can kind of go onto my website or on to the site in the description but just to go into what you'll learn here. You'll see that you get an intro to private equity. You'll get more information on how to think about investing in like a framework, the private equity deal process, deal structure, valuation and modeling, of course, and all this good stuff.

In terms of speakers, you can see you have Wharton professors and then Scott Roman, who's the private equity program director for Wall Street Prep. And then keynote speakers from pretty established. Private equity investors like David Rubenstein-Carlisle, he's probably the most famous one here.

Martin Brands, also a speaker, a panelist, works at Blackstone. And then if you scroll all the way down, here is what I think is also super helpful. So peer-to-peer networking, you kind of join your cohort and then also are able to interact with people who are now alumni of this program, I guess. It's kind of like a LinkedIn invite-only group. You get a certificate and then recruiting events from the top headhunters.

So feel free to check it out. I know it is pricey. You can use my code rareliquid to get $300 off. And if you sign up by the early registration deadline, then you get another $200 off.

And this program runs three times a year. So pretty much anytime when you're watching this video, you could probably check that program out. But now let's move on to a few more things that I had in mind that I wanted to share before going into the LBO.

So one is, so this is going to be the first. like just a simple LBO tutorial video that we're going to do completely from scratch. And this is kind of more so to provide a good foundation for what's to come in the future versus just starting like an advanced LBO tutorial video like right away, right?

It's going to be probably too complicated for most people. And so today is going to be relatively simple. If you want to get email notifications for when I do these streams and or notifications for when I have the links ready to download the models because you can download this model for free once I get the link ready and everything.

There's a link in the description for you to sign up for those notifications. And also I'm thinking of starting some kind of discord. And so I will be sending that out via email as well. So I'm thinking on that discord, maybe what I'll do is I'll just like also post these models and you can just download them on there.

So Anyway, if you're interested, wait for that or sign up via email there. It's in my description. You can also look at related videos for LBO and Excel tutorials that I've linked in the description. And then also I have the private equity videos that I made in a playlist in case you're interested in that.

I also realized I never really plug my Instagram and TikTok. And now that I've graduated from my MBA and have much more free time, I'm hoping to make more short-form content about my MBA life or MBA lessons, also just about finance in general. So feel free to check those out. And then I've recently launched an MBA course called Liquid MBA for how to get into MBA and working on ones for banking and consulting as well. So I know a lot of like plugs here and everything, but I thought a lot of these might be of interest to you guys.

A lot of free resources and some of these, of course, are ones that I built that will be paid for or need to be paid for. So I will, as usual, just answer questions as you guys have them in the chat. But with that said, let's go into building this LBO. And so there are two ways I was thinking about building this out. First is like maybe we have some financials like for a real company.

And then I just build out an LBO based on the numbers. And I think I'll do that in the future. But for today's LBO, I was thinking it'd be easier and simpler to just start off.

with let's say you're given these assumptions and you need to build out an LBO. And this is typically kind of like it may be similar to if you ever get a private equity interview, then this might be kind of similar to what you may have to do. They just give you a bunch of text and then you have to build a model out of it. Other times they'll give you financials for you to work with. And, you know, I'll provide examples of that in the future as well.

So I'm just going to format things a bit to how I want it to look. And all right, so let's kind of read through this. You are a private equity firm looking to potentially buy out an insurance company. In the last 12 months, the company has generated $2 billion in sales, $900 million in COGS, $500 million in OPEX, $100 million in DNA, $200 in networking capital, and $140 million in CapEx. For simplicity, your firm assumes it can grow the company revenue at 10% for the next five years, assumes a 21% tax rate, and assumes all margins will remain flat.

So this really simplifies a lot of things. Your firm also believes it can enter and exit at a 10 times EBITDA multiple at the end of year five. Fees and expenses are estimated to amount to $100 million, and there will be three tranches of that $200 million for a revolver, 7.5% interest.

$2.5 billion from a bank loan, 9.5% interest, and $1.5 billion from senior notes, 12.5%. Calculate the firm's IRR. All right, so what we're just going to start off with is building out a section for our assumptions.

And let me also actually zoom in so you guys can see everything. If things are too small later on, let me know, and I will... I can increase the size or zoom in more. So assumptions are going to be here. I'm going to make this white.

Oops, let's make this five. So we're going to start off by including our financials here. So let's make this this blue color and make...

this white so what assumptions do we have here right everything is kind of laid down for us so we're gonna assume or we're gonna label this as ltm financials or ltm we have financials up here anyway so we're gonna have revenue cogs opex dna networking capital um 140 million oops capex what And then, so that's this line here. For simplicity, 10% revenue growth. So let's say we call these operating assumptions, revenue growth.

And then we also have here tax rate. All margins will be flat. So now we're just going to need to kind of include the numbers here.

And so let me build this. in the style I used to, I usually like where it's kind of dotted. I think it's this one. Yep.

Okay. I'm going to move this a little bit to the right. Okay.

So revenue here is 2 billion. We're going to do everything in millions. And I'm also going to... Make this... I like using this one with the parentheses for negative.

Also need to make... or use commas. So, and I'm also gonna turn this into blue.

Because blue just represents... Let's see which one is good. Blue represents that it's a hard code.

And there's one that's like really commonly used, but it's not in the color wheel for some reason. Okay, I cannot tell if my eyes are... Okay that looks okay.

We'll just stick with this one. I'm going to copy the formatting alt-est. Cogs here 900 million. OpEx is 500 million.

DNA 100. Networking capital 200. CapEx 140. Revenue growth rate 10% and going to need to format this as well. I also need to make this blue. This blue.

Okay. Tax rate, 21%. So that covers...

So let's just put an X here for what we've covered so far. Your firm also believes it can enter and exit at a 10X multiple. at the end of year five.

So let's now do our entry and exit multiple stuff. So entry, we're going to need to get calculate our last 12 months EBITDA and then get that and then get our EBITDA multiple. That's going to get us to our enterprise value. Um, so we're gonna connect to that. Okay, actually so first thing what I want.

Uh, well, let's see. Um Okay, let's just build this part out first and we can fill stuff in later because there's gonna be some things we can't do right now Okay, it's being weird Okay, so we're going to need to calculate enterprise value, then get our existing net debt that get us to our equity value. And then we have, let's see, fees and expenses.

Fees and expenses copy here it's just going to be 100 million and then let's also have our a section for our exit and that'll just entail our um well let's see let's call this entry multiple actually and then we'll call this exit multiple And this we actually have already, which is 10. And let's format this so that it has an X next to it. I know I did that really fast, but to do that, what you can want to do if you're on a PC, I don't know what the shortcuts are for Macs, Control 1, then Tab. That allows you to press the up or down arrow key.

And then you can press Tab again. 0.0x so basically changing the type press ok and then we can do that again here so uh well actually yeah well yeah we would need to do it again here so we're gonna in our assumptions here it says that your firm believes it can enter and exit at a 10x multiple and it's very common to have the same multiple for when you enter and exit because you can't always bank on a company in the future having multiple expansion, right? So that's why a lot of assumptions, you know, to be kind of conservative or... realistic you want to assume it's the same but hope that the multiple expands over time okay so we need to calculate calculate our EBITDA later and then existing net debt we may be able to do let's see actually I think I realized that as I was creating these assumptions one thing I forgot to include was existing net debt. So I'm gonna actually add in here one more assumption.

Let's just end here. The company has existing net debt of $1 billion. all right and we'll calculate the rest later so now we need to think about our capital structure so we have a revolver and so a revolver is just like a credit card but for companies literally that's what you can think of think of it's like it's like short term usually less than one year in terms of repayment so it's a little bit longer like credit cards usually we pay off every month or you should at least For Revolver, it's usually within the year, but you can obviously pay it off sooner.

And then bank loan, also often referred to as term loans. And then senior notes. So I also got a question on the last one of my LBO tutorials about like why in later on, actually, it'll make more sense after I build this out.

But here, let me do this first. And then. talk about it. So amount and then interest kind of for interest rate.

So let's see. Let's copy paste this again amount. So there's three tranches of debt, 200 million from a revolver, 2.5 billion from. a bank loan and 1.5 billion from senior notes the revolver has 7.5 percent interest bank loan 9.5 and then the senior notes 12.5 so in my one of my lbo tutorial videos someone asked me when i built the debt waterfall which we'll do later why i paid off bank loans before senior notes When senior notes have a higher interest rate?

Great question. And the reason is because a lot of times bank loans, they'll have a clause or something that says, you got to pay us back first. That's why we'll give you this like kind of lower interest rate or whatever. I actually did respond in some comments that I thought hadn't made a mistake.

But then after kind of rethinking about it, I was like, oh, actually, I didn't make a mistake. So that's why oftentimes even with a lower interest rate, you'll pay it off first just because you have to. All right, so let's build out the other sections.

So we're going to need our sources and uses table. Make this five, row height five. We're going to need some of our...

We're going to need like EBITDA and stuff. So what I'm going to do first is actually build out our financials and start off with our operating model before we go into the sources and uses table. Okay, so before I go into this though, let me see if there are any questions here. So regarding LB on DCF, how do you think investment banking will change with AI in terms of models in general? I can't predict way into the future, but I think in the immediate future, it'll just be another tool that bankers use to make their processes more efficient.

So what I often kind of talk about when this question is asked is back in, I don't know, 30, 40 years ago. pre-internet my mds would say that way back when or whatever you had to go to the library to photocopy annual like 10ks or 10qs or whatever bring them back to the office and then build your models and whatever do stuff by hand etc now you can get like all of that information in an instant from fax at cap iq bloomberg but bankers still work super super long hours right so i think also it's going to be really hard to build out really good i don't know like i think you can get the you can get the foundations for a model but then you're going to have to make a lot of adjustments anyway and i think the new norm will just make it so that you're producing a lot more work for clients versus like taking you know giving analysts like 20 hours of their week back so they work less um okay subscriber i see you're a subscriber since i was at 100 thank you I have a grateful for an answer. The question is more about replaceability.

How do you think everyday life will be changing? Everyday life, I think you'll just start. I mean, I use Chachi PT all the time for like everything.

And I think there will just be more and more tools that come along for financial modeling as well. I can also give you another example. So when I was an investment banking analyst, I had to build these things called strips.

and profiles of companies so let's say that we were trying to tell one of our customer one of our clients that they should consider buying you know any of these like 30 companies we would have to build out these profiles of companies and they're like powerpoint like one one slide per um per company or it would be like five in one slide and those would be called strips because there'd just be like one row each but a lot of times we had to Find all the information, enter it all in and do a lot of just like grunt work that really, really was so annoying. Hated doing those. And those kinds of things, I feel like nowadays I would just go to Chachapiti and be like, hey, give me give me some information about this company and like give me the management information, like give me a link. And I just like it to make my life like so much easier.

So that's what I think. Analysts will be faster, but I don't know if it'll like really replace analysts or anything for a while. Okay, so I see another question about revolvers and senior notes. I already explained revolvers, so I don't want to repeat myself too much, but it's just like a credit card for companies, essentially. Less than one year, you can take out money.

Senior notes are just usually a little bit, like you get them from sometimes different sources instead of just a standard bank, and they offer a higher interest rate. And sometimes you just need to take that out. at a higher interest rate because you can't just get it from other places right so okay let's build out our operating model a bit so starting with revenue and then percent growth and add a few spaces here then we're going to have our cogs and this will we want to see percent of sales we're going to use this format quite a bit so i'm just going to copy paste this down This gets us to our gross profit and then we have our OpEx that gets us to our EBIT. And let's see here.

So EBIT then let's say we get our DNA then our EBITDA. And then your interest, which we will have to use as a plug for later. That gets us to our earnings before taxes. Then we get our taxes. Let's use tax rate.

And then that gets us to our net income. So a lot of rows, but it's going to be pretty easy. Let's see.

So let's, let's call this. I'm just going to, I'm not going to use fake years. I'm just going to call it year five.

Oops. Call. And then oops, year four.

Actually we can make it. So this is three, two, one. So let's use custom again. So we're going to say year and then space.

Cool. So now we have our years, year zero to five. Year zero is basically going to be LTM for us.

So we have our revenue, we can just link to up here. And when you're linking to a number in the same sheet, then At JPMorgan, the industry standard was making that purple. Percent growth, we don't have that for this year.

So it's going to leave a blank. But we do have revenue growth here. So again, going to make it purple.

And we can just copy paste it. You can do one of two things. You can either anchor this and make it all anchored to that or you can just make it equal to that.

And remember our assumptions here. It says all margins stay flat. I guess I'll add here. I'll assume all growth rates and margins will remain flat just to make sure that it's covering all our bases. So multiply this times one plus our growth rate.

Do that. Okay, so we have our revenue growth and then our cost of goods sold, 900% of our sales. And remember we said that...

or the assumption is that the margins will stay flat so we're going to keep it at 45 and then multiply our cost of goods sold margin by our sales and since we're gonna be using a similar format so i'm just gonna copy paste the format so what i did here is control shift right arrow key down arrow key control copy down arrow key a few times and then alt est and that copies the formatting here so this will be purple this will be black gross profit is our revenue minus our cogs and then percent of sales or sorry yeah for our margin um yeah i guess we needed to calculate it so no need for it to be needs to be purple too. All right. Next, our OpEx, it will be this format. So I'm going to copy paste that there, connect this to our OpEx, which is 500 percentage of sales. So we want this to be flat and then multiply our margin to our sales.

And then that gets our OpEx. Now to calculate our EBIT, pretty simple, I'm going to copy paste this. So gross profit minus our OpEx and then percentage of sales is our EBIT divided by our sales.

30% remains flat. And one thing you want to do as you're building out your model, like if something generally speaking, and this is true on the job as well, When you're building out your model and then the numbers look a little weird, you always want to be kind of checking for that. So, for example, you know, I'm building this out relatively quickly, but I can easily just, you know, get accidentally, you know, get my gross cost of goods sold minus like my OPEX or something like that. And then that number would look way too low. Or a lot of times a mistake I make is sometimes I will be, I'll need my...

percentage of sales but then let's say like i try to copy paste it and then it's like a you know large weird number right it shouldn't be larger than your like or very similar to your gross profit if you have 25 of sales for your opex so just check the trends and stuff for me when i look at this it's like okay everything's at 30 this looks like it's probably right which is why i thought of bringing that up okay so Now let's do our... Actually, I think I can just copy paste both of these. Because our DNA is going to be similar to our OpEx where we link to it. And then we'll need our percentage of sales. I'm going to say stay flat.

And then this times our sales. If I wanted to be more efficient, I could definitely anchor this row. and then multiply this and instead of constantly going back to our sales like i could just copy paste this instead um probably should have started doing that earlier uh ebitda is our ebit plus our dna and then ebit uh over our sales so again i could have just anchored this and then done that for this one as well.

But yeah, 35% makes sense. You know, this 30% plus the 5% gets you to 35%. And now we have our EBITDA for year zero.

So before we go into all this other stuff, which requires a lot more, let's finish out our sources and uses. So EBITDA is 700. Let's connect to that. Make this purple.

Enterprise value is 700 times our entry multiple. So that means we're buying the company for 7 billion. Equity value is our enterprise value minus our net debt.

So 6 billion in equity value. And now we can build out our sources and uses. So to do that, what I'm going to do is add some more space here.

Okay, so we're going to have our sources and then our uses. The lights are like super bright of where I'm at right now. Okay, uses.

And then we're going to, for our sources here. Oops. Sources is, you can think of it as like, where are we getting the money?

And we're getting the money from two sources. One is like the debt that we're going to take on, the new debt. And then one is these private equity firms cash and their money, basically.

So that's going to be the sources of where we're getting the money to purchase. And then where are we going to use that money? We're going to use it to buy the company, refinance the existing debt, and we're going to use it to pay off fees and expenses for lawyers, for when we're taking out new debt, we have to pay banks, all that stuff.

So what we're going to have here, I can just link to our debt. So we're going to have Revolver, bank loans, senior notes, and you also... You don't just want to have like the amount you also want to have the times EBITDA and then percentage of capital.

These are helpful to just have as reference. So revolver amount is 200, bank loan 2.5 billion etc and then we can see what like the multiple of EBITDA that it's that it is. So I think this will be a little bit more clear once I do it for all of them.

So I anchored the EBITDA so I can just copy paste it down and then we want the total here for reference. These are going to be in percentages. Do that a little bit later. And then sponsor equity. So the sponsor is actual money basically.

And we're gonna... that is always a plug. So I'll tell you what that means later.

But we will want our total sources here. And I guess what I need to do is fill out our uses first actually. So here we're going to need debt refinancing, equity payment for the actual company, fees and expenses.

And a lot of times when you're building out like a proper DCF, or sorry LBO, you're going to need a lot more. But in our situation, it's pretty simple. So this is all we're really going to need.

I'm going to add a little bit more spacing here just to make this model a little bit aesthetic, more aesthetic. All right. Debt financing, $1,000.

Equity payment, $6,000. Fees and expenses, $100. Percentage of EBITDA, you can just copy paste this formula. And then our total uses.

Your total uses always have to equal your total sources. And that's super, super important to remember. Percentage of capital.

Now we can calculate this. This is just simply. your line item divided by the total. This is percentage. Okay, 14.1%.

And for this as well, you want the sum. This, you always want to make sure it's 100%, right? Everything that you're using, all the percentages should equal 100%. If not, then that's just a check. You know, it's an easy check that you have in there to see if you did anything wrong.

Let's add a border. Okay, so now we have our total uses. And our total sources always have to equal our total uses. And you just simply add or make this line equal to your total uses. That way they will always equal each other.

You'll never get that. You'll never... have a misbalance in any sort.

You can't even have a 0.00000001 difference. It always needs to equal exactly. Otherwise, the money's not matching, right?

You need to pay exactly what you need to pay in order to acquire what you want to acquire. So sponsor equity here is the plug, meaning that what you're simply doing is getting the total sources and then subtracting out what you're financing with. And so then what you're left with is $2.9 billion. That's what the sponsor needs to pay. What you see here in terms of multiplied by EBITDA, you're taking on six times EBITDA of debt in order to fund the acquisition of this company.

And obviously I created all these assumptions and so I made it exactly six because at least when I was at JPM, six times EBITDA was a generally rough kind of almost max-ish leverage multiple that you would want to use. I don't know if things have changed. They likely could have.

But I just want it to be at six basically. And then we can also see what it is for sponsor equity for X and total sources. And then here we can do the same thing as we did for our uses, which is. getting our percentages. All right so now everything looks good for our sources and uses table.

Pretty simple stuff right? It's not like it's not like too complicated. Now that we have a lot of the basics really built out what we have left is we need to calculate our interest. or our leverage-free cash flow, calculate like our debt waterfall, which gets us to our interest.

And once we do that, our job's pretty much done because after that, we can calculate our IRR. Before I do that, let me look at the chat box to see if there are any questions here. Okay, I see a bunch of advice you guys are giving each other.

This Clement asks, is this pre-recorded or live? It's live. I'm talking right now.

Okay, so it seems like no questions as I was skimming through. I will, or let's go down here. And then we're going to now start building out our levered free cash flow.

And then, or sorry, our cash flow items and then our debt schedule. So let's start here with our cash flow items. I see a question of what's the company.

There is no company. It's a fake company. DNA, percentage of sales, networking capital.

Oh, so I should also add later on, like this, if you missed the intro of this, this is going to be the start of like a LBO tutorial type series where things are going to get more and more advanced in the future. And later I'll do... an LBO of a real company.

I'll also do an LBO like a three statement model LBO. We'll do like we'll add sensitivities for the different scenarios just like we I do for my DCFs. This is just the start of the series and so I wanted to keep things pretty simple to begin with. All right, just realize you may not be even asking about that, but okay.

Anyways, what are we doing here? DNA, we have this, so we can copy to there, percentage of sales. You can always either recalculate it or just take it from what you've already calculated and I actually usually err towards the side of recalculating just because, but let's just not do that this time.

Okay, networking capital, we haven't calculated yet. So let's go up here. Networking capital was 200. And that is...

10% of sales. Remember, everything is staying flat for this model. Oops.

So percentage of sales, multiply this by sales. I guess I could have copy-paste that. Wait, could I have?

No, I couldn't have. OK. And then we can calculate our change in networking capital, which is our current year minus our previous year. Then we're going to be doing the same thing for capex.

So let's copy this formatting and let's go to the top to get our capex 140. Again, percentage of sales. We're going to make things flat and then multiply this by our sales. Okay, so those are our cash flow items.

Pretty simple stuff. And then what we're going to need next is to calculate our leveraged free cash flow. This is a key difference between the DCF and the LBO.

So the DCF, if you have watched a lot of those videos, we always calculate our unlevered free cash flow. And then, yeah, we don't include any like interest expense costs or anything like that. For a leverage free cash flow, we do. So we start off with net income and that already takes into account your debt because... you net income has already paid off your interest expense versus an unlevered free cash flow you don't take that into account because unlevered free cash flow formulas ebit times ebit times one minus tax rate plus dna minus networking capital minus capex ebit is earnings before interest and taxes right before anything is taken before any interest is paid so you're not taking into account your your debt or your interest versus your net income.

You've already paid your interest. So that's how that works. Um, Leverage free cash flow is a similar formula to your unlevered. It's just that you start with net income.

Then you add DNA, subtract out CapEx, subtract out change in networking capital, and then mandatory debt repayments. But we're not going to have that for this model. So we're going to calculate all this stuff a little bit later.

Actually, it's kind of weird when it's blank. So let me kind of calculate some of this stuff. I'll just highlight this part for now, noting that we need to calculate this later. But our EBIT or earnings per for taxes is our, wait a minute.

Oh, it's simply our EBIT minus our interest. This again needs percentage of sales and then tax rate is 21%. Oops.

And let's just make that equal. Keep it flat. So taxes, earnings before taxes times our tax rate.

And then net income, earnings before taxes minus your taxes. Oops. All right. So we have a 23.7 net income margin or profit margin.

But of course things will change once we calculate our interest. So in here what we can do is connect to our net income for now. DNA let's make all these purple actually.

So DNA, capex. change in networking capital and then you can just copy paste these across that gets us to our leveraged free cash flow what you want to do is get your net income plus your dna minus your capex minus your change in networking capital And usually you have mandatory debt repayments as well. Very important not to forget that. But in our model, we don't have that. So we don't need to worry about it.

All right. So that concludes our financials section. And now we need our debt waterfall, also known as your debt schedule.

So. What we're going to start off with is the kind of quote unquote like highest tranche of debt, which is the revolver. Basically what you need to pay off first, because you're going to see as we build this out, it's called the debt waterfall because you start off with your leverage free cash flow, aka the cash you have that you can spend or pay back debt. And then it flows down to what you first need to pay off, which is your revolver.

And then it's going to be your bank debt and then the senior notes. And it's going to waterfall down, basically. And you'll see what I mean. So we're going to start off with our revolver. And then we want our beginning balance.

And then our interest that we'll calculate. And then our pay down. That leads us to our ending balance. indent this.

Maybe best way might be six. Okay. So in year zero, we have this figure, right? You can either connect it to your sources and uses or up here to your assumptions. They should technically be the same thing.

I also realized I should have made this purple. Okay, I think also these need to be purple. Yep. Okay, anyway, so you can connect to this or you can connect up there. Honestly, it doesn't really matter as long as everything's linked correctly.

But I'm going to link to up here. So that's your ending balance, right? At the end of year zero, basically when you decide to do the transaction. And then your beginning balance is the same as your ending balance. Now, this is where I guess things can get a little bit more on the tricky side.

And where you kind of where, yeah, this is the trickiest part of the model, I guess, but it's not like super, super tricky, I think. So interest for our scenario is very simple. We're just assuming flat interest rate. A lot of times you need to like take LIBOR into account or something like that. And that'll change depending on the day really.

But in our case, we're just simply going to get our beginning balance and then multiply it by our interest rate. And then our pay down is going to be the minimum of either our leverage free cash flow, the cash we have, or our beginning balance. So in this case, the minimum is our beginning balance, right?

So we're going to have $257 million in cash left over that we can use to pay off other debt. What we do here is beginning balance minus our paydowns, which is zero. And then from here on out, you can actually just copy paste these formulas.

And it should be zero because we paid down our revolver, right? So, okay. That's our revolver and then I'm just going to copy paste this and make adjustments but it'll just save some time because we're going to have to do the same thing for our bank debt and then our senior notes. Except for here we want to now connect to our bank loan which is 2.5 billion and then our interest we want to multiply to the row let's see it's to below. which is 15 yeah 9.5 percent and this is the whole waterfall part so now we can't just first of all this is like linking to the wrong thing anyway what we want to do here is the minimum of either the debt balance or the amount of cash you have on hand to pay right which is your leverage free cash flow the amount of cash you have in that year in total.

And then you want to subtract what you have already paid, which is a 200 here. And so what you can see here is that, so here's our interest payment or interest. Pay it down, just double checking right now to make sure that everything looks correct. All right, 503, 553, 609, 577. Okay, I guess we pay off our bank debt here at the end.

Yep. And then do the same thing for our senior notes. Is this correct?

Yep. So this, you want to multiply that to 16 now because we want to get our 12.5% you see here. And then now what we want to do here is the minimum of either your beginning balance or now your leverage-free cash flow minus what you've already paid for your revolver and or what you paid for your bank debt.

Which should end up with zero. Yep. And then you can just copy paste this across. And you can see basically, this is why you can see the waterfall part now, right?

You're paying off your revolver first. And then with the remaining amount of money, you're paying off your bank debt. And then with the remaining, you're paying off your senior notes. So you don't have any cash to pay off your senior notes until year five, when you've paid off your bank debt.

That's your debt schedule. And now we just want to calculate just like a summary of like the totals. So what I want to do here is call it total debt. Let me just actually copy this.

So our ending balance are these numbers here. Beginning balance is also here. Total interest is here. Total pay down is here. Is our pay down?

Yep. It matches that. So now we have our figures here for our total debt.

That's pretty much the LBO actually. We're pretty much done. So now all we need to do is link to our interest payment. So let's go up here.

My interest payment looks really high but things are gonna be circular so we'll see how things turn out. Boom. So The numbers basically will decrease.

And beforehand we had in here leverage-free cashflow of like 457. But once you pay off your interest, then it's gonna lower your net income, which is gonna lower your leverage-free cashflow, which lowers how much you can pay debt. So there's like circularity essentially. So just to explain that again, so you have your interest expense here, right?

Your interest expense will lower your net income, which means that your leverage for cash flow is lower. If your leverage for cash flow is lower, that means your pay down is lower. And if your pay down is lower, that means your interest expense is, I guess, higher than if we weren't kind of...

Kind of explained that wrong. But your interest expense affects your net income, which affects your leverage-free cash flow, which affects your pay down, which affects your net interest expense, essentially. And it's in a loop like that over and over again. Let me just double check to see that, see if I did all this correctly though.

So we have our leverage-free cash flow of 110. We have... Beginning balance 200. Interest that we pay here. You're paying down 110. Then you can't pay down any here.

Not paying down any at all here. Interest. Yep.

Keep paying down. Max is 111, 264, 335, 415. So it looks right to me. So now what we need to do is just calculate our IRR.

Before I did do that, I guess since I finished off a section, I can see if there are any questions, although it doesn't really seem like there are many. Isn't Revolver a debt instrument which you can use for short-term cash, i.e. when a leverage for cash flow is negative? One is.

When it is negative, the revolver is drawdown rather than needing an equity injection. Yeah, that is how you can use it as well, like just like a credit card. But I would say for our scenario, we want to keep things simple.

I don't know necessarily. I don't know the details of how a company would use it and if like for a private equity company if you're looking to buy another company and their cash flow is negative I don't even see the point of them really doing an LBO for the company if they have negative leverage cash flow. So in our situation and for an LBO, I would imagine that it makes more sense to just treat a revolver as a standard, more just typical debt tranche, at least for this model.

Any other questions? Seems like no. Okay. All right. So let's calculate our IRR now.

What we need is our LTM EBITDA at exit, then our exit multiple. That gets us to our enterprise value. And then we can subtract out our net debt. That gets us to sponsor equity value.

Then we can compare that against sponsor equity at entry. and then we can calculate our money over invested capital and our IRR. So ltm evita at exit let's go up here 1127 exit uh let's actually make this purple as usual exit multiple connect to here Enterprise value is our simply our LTM EBIT dot times our exit multiple net that oops It's down here actually, 2875. And then sponsor equity is our enterprise value minus our net debt.

Sponsor equity at entry is down here, 29, 2.9 billion. So money over invested capital is just simply, what do you end that divided by? what do you start at oops so 2.9 x your money essentially and then in terms of calculating our irr we can do this by getting our moic to the power of one to five one over five because that's how many years and then minus one that gets us to our irr of 24 okay so let me add Yeah, okay, so 23.7% is our IRR here. And I always like to add something here for reference. Can I have our MOIC and then our IRR?

and link down to MOIC and our IRR. Make these... oops.

Okay, so why do we have this here? It's because you can just play around with your assumptions and see how your... assumptions all change. So entry multiple of 10 versus like 11, you can see like if you have 15% growth rate or like 5% growth rate, you know, what it does to your IRR, increase in taxes to lower your IRR. And you can kind of like do it, you know, some checks, right?

Like if you have really high expenses that lowers It should lower your IRR, which it does, right? But if it's not doing that as you're kind of building out the model, after you build out the model, then you should check why it's not working correctly, right? If you pay a higher interest rate, then that lowers your returns. If it's like 50%, then it'll lower your returns and everything.

So that pretty much wraps up the LBO. And as I mentioned, you will... be able to download this for free. To go over a few other things before I kind of sign off here, check out Wall Street Prep and Wharton, their private equity certificate program, if you're interested. This is going to be the start of a lot of more videos for LBO tutorials because it seemed like there was a lot of interest in them.

So later on, we'll be using real financial figures from real companies. build more sensitivity around like different cases and then also be adding like three statement modeling like a lot more complicated advanced stuff you can sign up for email notifications for when i do these streams and also for these uh models just to be like sent to you and also i'm thinking of starting a discord so i'll also send that link out if you want to sign up links for all of this stuff will be in this video's description and if you're If you want to learn more about LBO stuff and start with the basics, then you can look in the description for related videos. I also have Excel and Excel tutorials and private equity videos.

I'm starting to post more more on Instagram and TikTok. So feel free to check that out. And then I have a few courses you can check out my MBA one I already recently released. And then I'm building out one for banking, which is probably most relevant for you. And then one for consulting as well.

So I hope this was helpful. I know. I made a lot of DCF videos and I wanted to make some other types of ones and kind of need to brush up on some of my skills and prepare you know all this stuff before I could actually do so but now that I've graduated from school have more time so look forward to more of these in the future and thanks as always for joining the stream hope to catch you guys in the next one see ya

Transcript for:Understanding Leveraged Buyout Modeling

Transcript for:
Understanding Leveraged Buyout Modeling