14. Sensitivity Analysis and Recommendations


In this lesson, we perform a sensitivity analysis to help us decide which party is getting the better deal from the proposed merger. The sensitivity analysis will incorporate price, funding mix and synergy realization.

To explore more Kubicle data literacy subjects, please refer to our full library.


  1. Lesson Goal (00:04)

    The goal of this lesson is to create several sensitivity analyses to study the proposed deal.

  2. Analyzing the Deal (00:12)

    In our proposed deal, the projections show the combined company would significantly reduce its debt balance in the coming years, maintain a strong cash balance, and see significant growth in earnings per share. In reality, this proposed deal is unrealistic. The transaction significantly undervalues the target company. As a result, we can expect them to reject the acquirer’s offer, forcing the acquirer to make a higher bid. We want to use several sensitivity analyses to study the impact of increasing the bid price on the deal.

  3. Sensitivity for Price and Debt Ratio (00:56)

    We create a sensitivity analysis showing the impact on earnings per share of adjusting the target share price and the percentage of debt in the deal. This shows that the earnings per share are higher when the target share price is lower. When the share price is higher, the acquirer is diluted more because the target company shareholders are receiving a larger amount. As a result, the earnings per share are reduced.

    The sensitivity analysis also shows us that earnings per share are higher when the percentage of debt in the deal is higher. This is because a higher share of debt means that less new equity is issued, reducing the impact of dilution on the deal.

  4. Sensitivity for Price and Synergies Realized (02:24)

    Our second sensitivity analysis studies the impact on earnings per share of adjusting the target share price and the percentage of synergies realized. Before creating this sensitivity analysis, we create a transaction assumption for the percentage of synergies realized. In our model, we multiple all the synergy items in the combined Income Statement by this percentage.

    After creating our sensitivity analysis, we see that a higher percentage of synergies increases Earnings per Share, but the Earnings per Share are still reasonable even if only 60% of synergies are realized. This suggests that synergies are not critically important to the success of this particular transaction.


In the previous lesson, we calculated the debt balance and the cash balance for the business over the next five years. And as you can see, the debt balance is incredibly low, given its initial starting point and our cash balance still stays at 200 million. All of this is achieved while the earnings per share increases dramatically, 65.7% over the five years.

As a consequence, it looks like this is an excellent deal for shirt shop. But conversely, pretty bad deal for Trouser Town's shareholders, who should be able to extract a lot more cash from earning potential buyer. In most acquisitions, it's not uncommon for a number of bids to be made before a company is actually purchased. And it's highly likely in this case that Trouser Town would hold out for a better deal. And so in this lesson, I'm going to create some sensitivity analysis that shows a higher price than the $67.90 currently bid to see what level of accretion and dilution we would get in 2021 for these different sensitivities. In addition I'm going to plot these sensitivities for different depth percentages of total funding and also different percentages of total synergies realized.

To start with, I'll select all of my cells, and hold Alt A W T to create a data table. In the row input cell will be the percentage of debt funding which I can find at the top.

In the column input cell will simply be the share price.

When I scroll down I now have my table complete.

Unsurprisingly, the earnings per share will go down if I pay more for the company and they will also go down if I use less debt. And the reason is very simple. The more I pay for the company the more I get diluted and the more debt I will use to fund the deal.

As a consequence, my earnings will go down and the number of diluted shares will go up.

At the same time, if I use a lower percentage of debt, I'll also get proportionally diluted by more. And dilution has a bigger impact than debt on my earnings per share in this example.

Let's now try another sensitivity, but this time with the percentage of total synergies realized. The great thing about data tables is that you can push in almost any variability you want as long as that variable can be represented in a single cell and it exists on the same sheet as your data table.

For the percentage of total synergies realized if we scroll up to the very top I've created an assumption which is simply the percentage of synergies realized. And this is at 100%. To implement this in our model I simply take our current synergies and multiply by the cell.

And then I'll anchor and copy across.

I'll repeat.

My cog synergies.

At cell E21.

And copy across.

And lastly, for my opex synergies.

E21 and anchored.

With these changes now made to our model we can run our sensitivity.

So I scroll down, select my table, and Alt A W T to create the data table.

My row input cell is E21 and my column input cell will be the price which I can find at the very top.

It's again $67.90.

Unsurprisingly if we only achieve 60% of synergies then we won't get as high an earnings per share boost as we would if we achieved 100% of synergies. Interestingly, we'll still get quite a boost from 60% total synergies realized. So in actual fact, this deal is less reliant on achieving all our synergies than other deals. And this is simply because the current bid is very low and undervaluing Trouser Town. However, as the price goes up, the accretion or dilution per share decreases considerably from 2.15 to 1.27.

As with other models, it's possible to create multiple tables for these types of sensitivities. And in this example I've simply looked at the 2021 accretion dilution amount in dollars per share. We could also include the earnings per share or the accretion or dilution as a percentage. I can leave that up to you. In addition we can also look at leverage ratios or the cash position of a company under these sensitivity tables. As an exercise, I'd encourage you to create some additional tables that look at the leverage ratio under these sensitivities. It'll provide even more insight to your future clients.