M&A Case Study - Income Statement Combination
- 06:50
Create a pro forma income statement for a merger and acquisition scenario, using Excel formulas and assumptions. How to calculate the combined EBITDA, net income, and synergies of the acquirer and the target, as well as the effects of debt financing, refinancing, and cash usage.
Glossary
Assumptions deb financing EBITDA M&A Net Income Pro FormaTranscript
So for the income statement combination, I'm actually going to pull in the LTM numbers for EBITDA because that would be useful for the leverage stats. But for everything else, I'm not gonna worry about the LTM years. So if I go up to the acquirer's EBITDA, I can pull it up from here. So the EBITDA is 14, then I can pull in the targets again, from that table above, remember that's been dollarized, so I can use that number directly. And then we've got some synergies. Now I'm not gonna put synergies in the LTM year, but I'm gonna copy this right because I can pull that in from those two tables above. So we've got our key numbers there. Then for the synergies, we have an assumption which is 8.2% of the targets revenue in CY1. And then I'm gonna multiply that by the run rate because the synergies aren't going to be extracted all in the first year. It's gonna take three years we think, to affect those synergies. So that means the synergies will build up over the three-year period. And sometimes you will do an analysis where you will compare on like a full run rate synergies. In other words, assuming you've got 100 percent of synergies in year one, but on a practical level, you'd normally expect the synergies to build up steadily over time. And then I'm gonna calculate the acquirer's net income and the acquire and net income. We're gonna be using a simplifying assumption here. We only have an EPS forecast. So I'll take the EPS forecast and I'm gonna multiply that by the number of shares for the acquirer. So I'm gonna go to column M and I'm gonna get the diluted shares outstanding there. So I'll F4 that. And that will give me my estimated acquirer's net income based on that forecast EPS. And we want to use the consensus estimates because we are trying to look at this from the perspective of the public markets. Now they're acquiring a private company. So there's no EPS forecast for the private company. There's just a net income forecast probably from the confidential information memorandum. Then we're pulling in the synergies. Now the synergies we've got above here, but that's a pre-tax number. So what I'm going to do is I'm gonna multiply that by 1 minus the tax rate. And it's a bit of a judgment call about which tax rate you would multiply by. But we're gonna use the targets tax rate, absolute reference that, and that may not always be the case. It really depends which entity is capturing the synergies. So once I have calculated the synergies on an after tax basis, then I need to incorporate the effect of the acquisition debt. So I'm gonna go up to row 29 and we've got the cost of debt financing. And I absolute reference this because I want to multi, I want to copy it across. And I'm gonna multiply that by the amount of debt. And I'm gonna go to the column H37. So that's the new acquisition debt and I want to absolutely reference that. So that's going to be the new acquisition debt times the interest rate. But remember that is a before tax item. And so that means we'll save some tax. And I'm gonna multiply that by 1 minus the tax rate. Now, which tax rate you use is going to depend on whether it's sits on the targets balance sheet or the acquirer's balance sheet. Because they will still stay separate legal entities post-acquisition and the debt financing is much more likely to be on the acquirer's balance sheet. So we're gonna take the acquirers marginal tax rate there and cell M10. And I'm gonna assume that the debt is not repaid during the period, but I'm gonna make it negative. So that means that interest expense will come out of the forecast earnings and it will reduce via drag on earnings. But remember we refinanced the target's debt. So actually we also won't have to pay the interest on the refinance debt. So I'm gonna go up to row 29 and I'm gonna make the simplifying assumption that the cost of debt of old debt is the same as new debt. We could have had two assumptions, but we're just having one for simplicity. But that's not always the case, particularly if interest rates have shifted. So I'm gonna take that and I'm gonna multiply that by the refinanced debt.
Okay? And then what I'm going to do, again, I'm gonna multiply this by 1 minus the marginal tax rate. And because this sits in the targets entity, I'm gonna use the targets tax rate.
So that is the cost of debt times refinance debt times 1 minus the targets debt. And so we're actually going to benefit from not having that interest. And then remember there was some cash used on the balance sheet of the target company. And if you go to the target cash, that 657 is no longer gonna be there. So that will come out of net income. So what I'm going to do is I'll take that cash number, absolutely reference it, and I'm gonna multiply it by the interest rate on cash. So we're going to lose that. But actually less income means less tax. So again, you want to multiply it by 1 minus the tax rate. And again, I'm going to use the targets marginal tax rate.
Now that comes in as a positive number, but in reality this is a loss of interest income. Because you are foregoing that interest income because you have got rid of cash on the balance sheet. So that's gonna be a deduction. So the pro-forma net income, it's going to be the acquirers known income, the targets known income, the synergies on a post-tax basis, the additional cost of acquisition debt. But because we refinanced the targets debt, we get the benefit of not paying interest on that. But because we use the cash on the balance sheet of the target, we don't get the benefit of the interest income. So there's lots of positives and negatives there. It's quite tricky sometimes to get it clear in your brain. But then we get the proforma and income.