M&A Case Study - Key Numbers
- 10:21
How to do an accretion dilution and ROIC analysis for an acquisition. Understand the assumptions, inputs, and calculations involved in the analysis, as well as how to deal with the currency conversion and the synergies.
Glossary
Acquisition Dilution ROICTranscript
We're going to do an analysis of Coca-Cola acquiring Red Bull. This is relatively straightforward. We're going to do a accretion dilution model and we're gonna take a look at the return on invested capital of the transaction and the present value of the synergies compared to the premium paid. The only complexity here here is the fact that one company is in Euros and another company, Coca-Cola, the acquirer is in dollars, so we're going to dollarize everything. So the first thing we're going to do is company name obviously is Red Bull and we've got an FX rate between Euros and US dollars of 1.1. So that's 1.1 Euros per US dollar, and we need to pull in the marginal tax rate. Now, the marginal tax rate, we could make an assumption it's the acquirer or the target. It's really gonna depend where the synergies sit and where the financing sits. Now in the answer model is taking from the Red Bull model, could argue that it should be the acquirers marginal tax rate for the tax shield on interest, but the synergies, if they're in the targets operations, then it would be their marginal tax rate of the target company. We are going to assume that the maximum multiple paid is going to be 25 times. So that means pull in the LTM EBITDA. And the reason we're doing this, this is a private company and it's likely to be priced off the LTM EBITDA because it's a private corporation. I'm gonna go to the Red Bull model and I'm gonna go to the EBITDA number in the last historical year and I'm gonna pull that in. You could actually just have an enterprise value here rather than a multiple, but often people will talk about multiples. Then what I want to do is I want to put in the key items to be able to do the bridge. So I'm gonna go and get the debt and debt equivalence in the historical balance sheet for Red Bull, assuming that this deal happens just after the balance sheet end. So I'll take long-term debt plus the revolver, so that's my debt, and then I'm gonna pull in my cash from the model as well. You can actually move the model over to make it a little bit easier. I'm pulling the cash, let me just do that just so it can show you. So if I go to the Red Bull, I can just pull across this tab and it will get me closer to the M&A model tab. You can always move it back, but it just means you're not jumping around the spreadsheet so much. Then I'm gonna pull in the financial assets. Now I can just do control page up and get the financial assets there. So the acquisition enterprise value is going to be on multiple times the LTM EBITDA. Now we could have an assumption as just a flat 78 billion, but we've got that amount of information in. Now all these numbers so far are in Euros. So what we have to do is then we need to convert them into dollars. So I'm just going to come up to my FX here and what I can probably do is range name this. So if I do control F3 and then I just do alt N for new, then I'm just gonna call this FX for simplicity and it's gonna be workbook wide. And then what I can do is I can use that multiple. So if I go to the LTM EBITDA, I'm just going to multiply this by FX and I just convert that from Euros to Dollars. The same thing for the debt, but I just need to put this into parentheses.
And then times FX, you can see how easy it is to use range names. So that's 521. Then cash times FX, and then financial assets times FX. And then the acquisition enterprise value doesn't need to change because it's using a dollarized LTM EBITDA. For the acquirer, I need to pull in these pieces of information here, and I'm gonna get this from the trading comp sheet. So your trading comp sheet can be a real engine for the model and I'm gonna go and get the share price in local currency. But before I do that, what I'm going to do is just move that number one sheet so it's just next door. It just saves time. So the share price in local currency control page up, I'm gonna go and get the last closing price, which is the 59.55. Then I've got the exchange rate, but this is dollar to dollar, so it's a little bit over engineered and that's gonna be 1 times you can do this. Sometimes you want to build models where you can flip in and out different acquirers. Okay, so I'm gonna take the share price, just divide by that and of course it's gonna be the same. And then what I want to do is get the marginal tax rate and the marginal tax rate is going to be the acquirers marginal tax rate. And we have that down here, the 22.1%, which has been put into the comps model as well. And then we've just got to get the acquirer enterprise value. So I'll start with the diluted share. Outstanding and I most will just take it directly from here in our calculation. I've got the diluted share outstanding 4,327. And then the diluted equity value. I can again take from the prior sheet, which is 257,703. And then I can get all the adjustments here, the debt and equivalence. Now you could argue I probably do the FX as well, but I'm not going to Worry too much about that. I'm just going to do this for simplicity. I'm going to go and get the debt and equivalence here and I'm gonna take it from up at the top. I get 42 and then I'm gonna go and get cash from the top as well.
So for my other adjustments, I'm gonna go to the other sheet and get the other adjustments and I could calculate the enterprise value here or I could just reference it. Let me just calculate it just to reinforce the calculation, add up the funding side of the balance sheet, subtract any financial assets, and we will get the implied enterprise value, which is about 267 billion. The next thing I need to do is I need to pull in the key numbers for revenue, EBITDA, EBIT, and net income. So for Red Bull, I'm going to get this off the forecast sheet and I'm gonna multiply it by FX to convert it to dollars. So I'm gonna get the revenue and go up to the income statement. And I've got total revenue in the last historical year times FX, which is my FX rate. And then I can copy that, right? And the FX rate should, should be fixed because it's a single cell reference. Then do the same with EBITDA. Go up and get EBITDA. The last historical year times FX, you can see using range names, single cell range names is just an absolute breeze, saves lots of time. And then I'm going to do EBIT and get EBIT times FX. Copy that, right? Or let's just keep an eyeball on the numbers. Quite easy to make mistakes here. And then finally, net income, go to the model, go down and get net income last historical year times FX and then copy that, right? Then what we want to do, once you've done the targets numbers, we want to do the chorus numbers and they're in the right currency. We could use an FX rate, but just for simplicity, what I'm going to do is I'm going to take it from the model here and nicely, this is all laid out well for us. We've got revenue, EBITDA and EBIT all in US dollars. So I can copy that down and to the right. Now, unfortunately, because this is using range names, if I select it on the other sheet, what it will do is it will pick up the range name and that means if I try and copy that down, it's gonna become a problem. Now that cell is, if we look at the calendarized numbers, is cell G14. So if I just changes this to G14, what I should be able to do is copy that down and to the right. It's A bit of a kind of basic version, but if you just do a reference, it will pull in the range name, but you can then just change the range name to a cell reference and that will still work. Now we've also got our EPS in there. We don't have an LTM EPS, but we do have a CY1 EPS 'cause we're just having forecast ESPs. And from our analysis perspective, we don't really care about the historical years because they've gone. It's only the future that we're really concerned about. So we've got our metrics in here, we've got the acquirer and we've got the target. We've got some assumptions here. We've got fees as a percentage of enterprise value. We've got equity financing being 70% cost of debt, 5% interest rate on cash, 3% the synergies as a percentage of the target LTM revenue. We're going to pull in from our synergy assumption there, and that is the 8.2%. So I'm just going to absolutely reference that. And then the run rate, we can also take from the synergy sheet as well to make it consistent analysis. And I'm gonna pull that in too. So we've got that from the synergy sheet.