Football Field Case Study - LBO Summary
- 03:11
How to use the offset and match functions in Excel to calculate the acquisition multiple for a leveraged buyout (LBO) model that will give a certain internal rate of return (IRR) range. How to use the LTM EBITDA and the acquisition multiple to derive the implied enterprise value and the valuation range for the target company.
Transcript
So that's most of the analysis that we've got. The exception is the LBO. We want to know how much the private equity house we are able to pay. So then what we can do is we've got an IRR range for the year four exit. So what I want to do here is I want to pull in the relevant multiple for that IRR number. And if you remember we go back to the LBO sheet. You can see that we've got our ratio here and we've got our multiple. So what I want to be able to do here is build a little formula that will say, okay, given that IRR, what would be the corresponding multiple be? Okay, so I now need to calculate what multiple we need to pay to give us that IRR. So I'm gonna do an offset function and I'm gonna come up to my LBO and I'm gonna start above the entry multiples. Okay? I'm start above the entry multiples and I'll absolutely reference that. Then what I want to do is I want to use the match function to calculate how many rows I need to offset. So I'm gonna use the match function and what we want to do is look up the value, which is in the valuation summary, that 22%. So we're gonna look up that value in cell C39 comma, and then I'm gonna go to the LBO sheet because I want the range of percentages here and I'm gonna use the year four range of percentages. And then I'm gonna absolute reference that because I want to be able to copy this, right? Then it's gonna ask me do I want an exact match or do I want a greater than or less than? Okay, so as I actually kind of want a greater than, because I want to make sure that the multiple is greater than the 22% because we don't want it to be below that. So I'm gonna do minus one close parentheses for the match function and then comma, it's going to ask me how many columns I want to offset, which is zero for the offset function and then hit enter and I get 14.3 there. So what this is saying, if I go back to my model or my LBO sheet, the 22% IRR is about here and that corresponds to a 14.3 multiple and that's what that little formula is doing. It's giving us the multiple, the acquisition multiple. That will give us an IRR of 22% and 20%. So they've obviously tightened is to make the multiple a little tighter here. So the implied enterprise value is going to be the LTM EBITDA for Red Bull, multiply by that multiple and that acquisition price, the valuation range, which will give us a IRR range of 20 to 22%.