M&A Case Study- Sensitivity Tables
- 03:31
How to use data tables in Excel to perform sensitivity analysis on the accretion dilution of a merger and acquisition transaction. Compare the acquisition price and multiple from different perspectives.
Glossary
accretion Dilution M&A sensitivity analysisTranscript
When we're structuring a transaction like this, we're never absolutely sure what equity financing structure or even what price we're necessarily gonna end up paying. So here what we've got is the sensitivity analysis. So we're going to look at the third fiscal year. And the reason the third fiscal year is important is that that's when the synergies are fully baked in. So probably investors are gonna focus much more on the third year than they are the first or second year. So I'm gonna put in the answer to the accretion dilution for year three, which is currently a negative number. And we've got a varying amount of equity financing now based on the relative PE multiples. We know that the debt financing should be less dilutive than the equity financing. So as we increase equity financing, we're probably seeing more dilution. Obviously the cheaper or the lower price we pay, we should also get less dilution as well. So I'm going to select the data table and my row input cell is the equity assumption, which is that 70% currently.
And then the column input cell is the price, just the multiple. And the reason we're using multiple is that this is a private company. Now we need to activate the data table. So I'm going to just recalculate, and you can see that actually we start to see accretion when we start to use more debt rather than equity financing predicted by the PE ratios, but also at a lower price. If we're paying a lower price, we need to issue less shares, we have less interest expense and therefore we're starting to get accretion. Now, in the next data table on the right here, we want to sensitize, not necessarily the multiple, but the acquisition EV. And this is looking at this from either a multiple point of view or an acquisition enterprise value point of view. Because in a negotiation, sometimes the seller will just focus on the actual dollar number rather than the multiple. But when we're thinking about accretion, dilution and valuation, we think about a multiple. So it's quite good to crosscheck the acquisition price you're paying versus the multiple you're paying. So the first thing we're going to do is pull in the metric, the LTM EBITDA for our target company, which is up here in column C.
And this is the acquisition enterprise value, which is just that earnings number times in multiple. And then what we'll do is we will do the same thing, the EPS accretion dilution.
So now we're going to activate this data table and I'll do alt DT. And the row input cell is percentage of equity financing that 70%. But actually the column in put cell is not going to be the multiple is going to be the acquisition enterprise value. So I'm going to reference this cell. It doesn't matter that it's not an assumption, cell will just destroy the formula and then replace it. So then what we should be able to do is look at the accretion dilution and it will give us the same result. But it's just quite good to have two data tables, one on the basis of price paid enterprise value, and the other on the basis of the multiple paid because. Then you can cross check between them. It's similar actually in concept to something called analysis at various prices.