Simple LBO Model - Case Study and Tutorial

you then welcome to another tutorial video this time around we're going to go through a quick LBO modeling test or is it sometimes called a peeper 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 Excel model with a lot of complicated details that takes you several hours or week or something like that they may give you a very simple scenario and say here is our scenario here's where the company's Evita 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 light em 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 IVA da 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 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 five years at an enterprise value to keep it on multiple of six X 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 of revenue and epitaphs decline that doesn't matter because with LBL models you're only worried about future periods you can sort of ignore these first two paragraphs here the first relevant part they tell you is that OpCo become they're going to be acquiring his ebay top 250 million and they expect eBay dot to stay flat for the next five years what does that mean in terms of our model what it means is that in year zero we've a bid of 250 million and each year after that it stays flat notice how we're not even bothering with formatting or anything else like that to make this look pretty we just want to get to the correct answer because they speed test here what else is relevant they're also telling us that ABC Capital has obtained debt financing 750 million and they expect working capital be a source of funds at six million dollars 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 percent and then working capital they said source of funds at six million dollars per year that means that the change in working capital I have actually disabled working capital here I should really call it change in working capital it's going to be 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 would 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 capital 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 your one through your 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 as a tax rate of 40 percent 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 to on that 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 cross that's negative 35 million per year we also know that the tax rate as they stated was 40 percent 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 PP&E on the balance sheet remains constant for the next five years that is very important because what that tells you is that depreciation must equal capital expenditures if the net PP&E number is staying constant for the next five 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 PPE 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 with that and then I'm also going to go back to the income statement and enter our depreciation figures right here and remember this is going to be negative on the income statement because when you subtract DNA from eBay 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 the 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 Venge is that these are so simple and such clean numbers that you could conceivably do this in your head or write 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 linked 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 balances well the debt balance is easy because they're telling us that nothing is repaid so the 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 da 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 your zero here this is what's going to determine our interest in your one if that and let's copy this across we have 75 million in interest now we can get to our pre-tax income so take our EBIT da and our DNA and our interest those all have negative signs so we can just add these so our EBT or 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 Anker this with f4 x eb t and then copy this across then for our net income just take our pre-tax 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 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 they 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 80 for the DNA and the capex cancel each other out and so all that really happens here is that our change in working capital boosts 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 add an e V to e Badal multiple of 6 X so let's work backwards and think about how you do this we know what our EBIT da at the end of this it 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 we take our exit multiple of 6 X we multiply by our y batata 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 Turner is going to increase that the debt remaining that they have to repay is going to decrease that amount so that's one we 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 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 gonna 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 assumed 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 / the case study instructions that they are targeting a 3x multiple of invested capital they plan to sell this company after five 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 divided 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 divided 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 used to buy the company 1.15 billion what is this as I multiple of evita we can just take that price and divide by the year zero e pizza here or the you're wanting but that doesn't even matter because they stay the same each year in this case and so we get to a multiple of four point six x 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 four hundred million to get to a 3x multiple invest capital to use 750 million of debt so the total price is 1.15 billion representing a four point six x EBIT ah 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 conclusion 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're targeting we actually need multiple expansion we need the exit multiple to go up from the four point six X in the beginning up to six X here at the end which is expansion of about thirty thirty three thirty five percent 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 paper lvl model tutorial our quick LBL 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 possible you

Channel: Mergers & Inquisitions / Breaking Into Wall Street

Views: 367,466

Rating: 4.9369283 out of 5

Keywords: simplified lbo model, simple lbo model, quick lbo model, paper lbo model, leveraged buyout, leveraged buyout model, internal rate of return, IRR, calculating IRR, private equity, MOIC, multiple of invested capital, simple lbo, quick lbo, LBO Model, Breaking Into Wall Street, Mergers And Inquisitions, Wall Street Prep, Excel Model Tutorial, TheStreet, WallStreetOasis, #Investment, paper lbo, lbo case study, investment, lbo, m&a, investment banking, financial modeling

Id: 8VULm4T1jFI

Length: 13min 24sec (804 seconds)

Published: Mon Jun 16 2014