LBO Case Study - Review of Model
- 04:05
How to do a sensitivity analysis of the internal rate of return (IRR) of a private equity deal for buying Red Bull, based on different entry and exit multiples. How to use data tables and recalculate formulas in Excel.
Glossary
IRR PE Deal sensitivity analysisTranscript
So we've done our initial IRR calculation and you can see that's a little bit low. So probably what we should do is we should sensitize the entry and exit multiple in each year. Now in this case we can see that we've got a really wide range of entry multiples here and that's probably a little unrealistic. So we just need to consider actually what would be reasonable. And the way I do that is if I go to the trading comps and I just look at where these companies are trading, you can see that Monster is trading at 29 times LTM EBITDA. There's no way that you're going to be able to buy Red Bull for less than that. So because you're going to have a control premium as well. So what I'm going to do for the entry multiple, I'm actually gonna change this. So we start with 29 times, but then what I'm gonna do is I'm going to just reduce the variance. So rather than plus 1, I'll do plus 0.25. And that means we've got a slightly tighter multiple range here. And then I'm gonna pick up the internal rate of return and I've got 16.6 and then I'm going to do a data table alt DT. Now the row input cell here is the top of the data table. So that's the exit year, which is year four. And then the column input cell is the multiple, the entry multiple that we are paying, which is that 25 times there. So I come down here, you can see that we've got zeros. Why do we have zeros? If I do alt FT, you'll notice that if I go to formulas, I've got automatic except for data tables activated. So just up here. Now I could change this to automatic, but sometimes in a big model like this, it's better just to hit the F9 key. But if I do that, I'll stop my recording. So I have to actually do this using the menu bar, but you can just hit F9 and I'm going to calculate now, which is B to calculate. And you can see we pull in these numbers. Now on this basis you can see actually this deal doesn't work. So what I'm going to do, I'm actually going to reduce the multiple. Let's make it say 25. Let's see what happens if we start 25 and I need to reactivate my calculation.
Yeah, you can still see this still doesn't really work. So I'm gonna reduce it to say to 20 times and then I'm going to do my recalculation. Even at 20 times, this is looking pretty dicey. So maybe I'll do it say to 17 times. Let's see if we can get a deal out of that. Yeah, then you can start to see that 17 times it becomes reasonable. So this is gonna be a very difficult deal for the private equity firms to actually fund because they're just not gonna get returns at the kind of prices that people will expect to pay. Now normally what we'll do is we'll want to range in between 20 and 25% IRR. So let me reduce this a little bit further to 15 times recalculate. Again, I probably need to make it say 10 times, and this is just a crazily low multiple. Yeah, that's probably a little exaggerated. But let's say we use a year four. What we've effectively got in this range here, that's our valuation range. So what we are saying, private equity firm buying Red Bull in a traditional buyout mechanism would only be able to pay between 12 and 16 times L LTM EBITDA. Otherwise the deal just doesn't get the returns that they would expect.