[Music] [Applause] [Music] hello and welcome to another tutorial video this time around we're going to go through a quick lbo modeling test or as it sometimes called a peer lbo model now these are very very common in private Equity interviews and really in all types of Finance interviews where this topic could come up and what they'll often do is instead of having you sit down and create a full cell model with a lot of complicated details that takes you several hours or a week or something like that they may give you a very simple scenario and say here is our scenario here's what the company's evitat is here's how much debt we're using here's what we expect for Capital expenditures here's what you should assume for the debt repayment in the cash here's the multiple that we're aiming for and then based on this what should we be paying for this company upfront in the beginning so they could easily give you a scenario like this and it tests not only your understanding of these modeling Concepts and how an lbo works but also if you can condense and simplify information and if you can figure out from the clues they give you what different LMS should be so let's go to our Excel file for this as you can see it's going to be very very simple because it is a very simple scenario intended to test in a few minutes whether or not you can figure this out so what we're going to do here is pretty much start at the top and start filling out our evida and some of the other metrics up here then go down to the cash flow section and see if we can use this to perhaps flesh out some more of the items at the top then we are going to look at the exit at the end and then based on some of the numbers here we're going to calculate what the initial price for this company should be in this case the private Equity Firm ABC capital is targeting a 3X multiple of invested Capital it plans to sell the company after 5 years at an Enterprise Value to eidon multiple of 6X so we're going to go through this exercise and see what's required to do that now the first two parts here are actually irrelevant the fact that the companies had poor operating results revenue and EA have declined that doesn't matter because with lbo models you're only worried about future period so you can sort of ignore these first two paragraphs here the first relevant part they tell you is that opco the company they're going to be acquiring has ibav 250 million and they expect eidat to stay flat for the next 5 years what does that mean in terms of our model what it means is that in year zero we have ebit of 250 million and each year after that it stays flat notice how we're not even bothering with formatting anything else like that to make this look pretty we just want to get to the correct answer because it's a speed test here what else is relevant there're also telling us that ABC Capital has obtained debt financing 750 million and they expect working capital to be a source of funds at $6 million per year so let's go in and enter these assumptions the beginning debt balance is going to be 750 million the interest rate is going to be 10% and then working capital they said source of funds at $6 million per year that means that the change in working capital I have actually disabled it working capital here I should really call it change in working capital it's going to be a positive and it's going to add to our cash flow each year because they say source of funds right here there's a lot of confusion about this topic but in this case it's very very simple so this might be a company where for example they collect a lot of deferred revenue upfront and so they're getting more cash than what you'd expect from just looking at their income statement it could be something like that it could be that they're taking a while to pay suppliers but they're collecting cash from customers very quickly so there are a number of reasons why working cap could be a source of funds but those are some guesses in this case so I'm going to say six in each year here year 1 through year five and we have that assumption entered right here let's keep going down so opco requires Capital expenditures of 35 million per year and has a tax rate of 40% so let's think about those assumptions and going into them now the capital expenditure assumptions are going to be on the cash flow statement or really this mini cash flow area that we have really all we're doing is starting with our net income and then we're making some of the usual adjustments you on you see on the cash flow statement like adding back non-cash charges subtracting capex but we're not going through a full cash flow statement because it doesn't it's not required by the question and it would make it take way too long to do this so for capex it's going to be a use of cash because they are spending money on that so we're going to take this copy this across that's $35 million per year we also know that the tax rate as they stated was 40% so we have that information let's go back to the case study and see what else we can enter so we should assume no transaction fees zero minimum cash required that simplifies some of this treatment and that PPN on the balance sheet remains constant for the next 5 years that is very important because what that tells you is that depreciation must equal Capital expenditures if the net PP number is staying constant for the next 5 years so they're not telling us directly what depreciation here should be but we know from the question prompt that it has to be equal to the capex as I say over here capex has to equal DNA which equals 35 million because the pp stays constant for it to stay constant depreciation will have to be a positive of course capex will be a negative and so they'll cancel each other out so for the depreciation I'm going to link to our capex and just flip the sign for this one we have that and then I'm also going to go back to the income statement and enter our depreciation figures right here and remember these are going to be negative on the income statement because when you subtract DNA from ebit you get to your ebit your operating income then when you subtract interest from that you get to your pre-tax income or earnings before taxes EBT down here so we have that now let's go back and keep going through these assumptions assume that excess cash is not used to repay debt and instead simply accumulates on the balance sheet in real life you wouldn't necessarily do this but I'm just trying to give you some round numbers here that you can work with with these numbers the advantage is that these are so simple and such clean numbers that you could conceivably do this in your head or write it out on paper which is why it's called a paper lbo model sometimes and that's why we're making this assumption here so let's think about this what does this actually mean so to figure out how much cash actually accumulates and what our debt balance is over time we have to do a couple things so first off for the debt balance what we're going to do here is link to our beginning debt balance up the top and then for the debt balance each year after that if we actually had cash that was generated that we can use for debt repayment we could use it to repay that debt but in this case we don't so this cash is just going to build up on the balance sheet over time how do we figure out what this cash flow is and then what the debt balance is well the debt balance is easy because they're telling us that nothing is repaid so this 750 million is going to stay the same through all five years here in this model now the cash generated to get this we have to figure out what the company's net income is and some of these other metrics now we already have our DNA so we can take that and our ebit DOT and get to our operating income or ebit but we need to get to the interest first to do that we can take our interest up here and I'm going to Anchor this with F4 and then we're going to multiply by the beginning debt balance so in other words the debt balance from the prior year year zero here this is what's going to determine our interest in year One have that and let's copy this across we have 75 million in interest now we can get to our pre-tax income so I would take our ibida and our DNA and our interest those all have negative signs so we can just add these so our EBT our pre-tax income is 140 million each year what about the taxes well for these we can take our tax rate I'm using a negative sign here we can take our tax rate anchor this with F4 multiply by EBT and then copy this across then for our net income just take our pre-ex income and our taxes so we get to net income of 84 million per year right here now what about the cash that's generated well remember how the cash flow statement works we start at the top with net income and then we adjust for non-cash charges working capital and then subtract Capital expenditures that gets us to our free cash flow technically our levered free cash flow or something like that because we are including interest expense here so for the cash generated we're going to take our net income and then we're going to add our depreciation our working capital is going to add to our cash flow here because this sends a source of funds and then our Capital expenditures we're adding that because it's already negative that of course is going to reduce our cash flow so we have this let's copy it across and you can see that they generate 90 million of cash each year here because we're assuming no debt repayment what really happens is that we have our net income of 84 the DNA and the capex cancel each other out and so all that really happens here is that our change in working capital loose our net income of 84 million and takes that up to 90 million instead so we have that and now we can with this done we can sort of we can move into the last step of this process which is figuring out the required purchase price to get a 3X multiple of invested Capital at an EV to eitaa multiple of 6X so let's work backwards and think about how you do this we know what our eitaa at the end of this period is it's just the 250 million that we had from the start we also know that they're telling us to assume a 6X exit multiple so we have that what is the exit Enterprise Value then well we take our exit multiple of 6X we multiply by our IAT of 250 million now to figure out how much actually goes to the private Equity Firm you pretty much always assume that they have to repay any outstanding debt and then that any excess cash generated can be put toward that debt so it's going to increase how much they get back at the end the cash Genera is going to increase that the debt remaining that they have to repay is going to decrease that amount so that's one you can think about it you can also think about it like this that to go from Equity value to Enterprise Value you have to subtract cash and add debt if you're going from Enterprise Value to equity value you do the opposite so you would subtract debt and add cash instead so there are a couple ways to think about it but that is what you do in this case now for the debt we want to take the remaining balance at the end so I'm going to use a negative sign and have this negative for the 750 million right here and then for the cash generated so what we can say for this is we can just add up the cash flow each year because remember we assume that this just stays on the company's balance sheet we assumed basically that they didn't have any cash to begin with OR that the cash they had was the bare minimum they needed so we're just going to have this for our total cash generated comes out to 450 million the equity proceeds let's add up all these numbers so we have our exit Enterprise Value minus our debt plus our cash this gets us to 1.2 billion for the exit proceeds now to figure out what the initial investment was this part is a little bit tricky remember per the case study instructions that they are targeting a 3X multiple of invested capital and they plan to sell this company after 5 years so let's enter this 3x multiple right here and what we can then do is take our Equity proceeds this is what goes to the private Equity Firm at the end divide by our multiple of invested capital and that'll give us the initial Equity investment that the PE Firm made now this is not the total amount that they paid for the company because remember they use the 750 million of debt in the beginning as well but this is going to get us the equity portion of what they contributed in the beginning let's take this divide by the multiple invested Capital right there so we have that and then for the initial price what I'm going to say is that we will take the 400 million that they must have contributed in the beginning and then we're going to add the debt that gives us the total price they paid their Equity contribution plus the debt they use to buy the company 1.15 billion what is this as a multiple of evida we can just take that price and divide by the year zero ebat here or the year one ebot doesn't even matter because they stay the same each year in this case and so we get to a multiple of 4.6x and so that is really it you can see some of the logic over here I've just written it out in words that the initial investment is 400 million to get to a 3X multiple of invested Capital they use 750 million of debt so the total price is 1.15 billion representing a 4.6x IA purchase multiple so that's it this gets you to the correct answer now and in most cases that's it you're done one other quick thing to point out is that in this case one conclus clusion we might draw is that on the surface this doesn't necessarily seem like a great deal because to get the multiple of invested Capital they targeting we actually need multiple expansion we need the exit multiple to go up from the 4.6x in the beginning up to 6X here at the end which is expansion of about 30 33 35% something like that so that is one quick conclusion we can draw based on this very very simple math but that is really it for our peer lbo model tutorial our quick lbo model tutorial hope you understand some more about this concept now and are better prepared to do this in case study interviews it is really not that difficult you just need to work quickly forget about formatting and get to the math and the final answers as quickly as [Music] possible [Music]