Sensitivity Tables In Excel Transcript

Get Instant Access To More Excel Video Tutorials
& PDF Cheatsheets

Sign Up

Sensitivity Tables

In this lesson, you’re going to learn how to create sensitivity tables with a merger model like this to analyze the impact of different factors on the accretion/dilution. Now the reason we need to look at sensitivity analysis for this type of model is because at the bottom, right here, you see the accretion/dilution of 5.9% and 9.1%.

That’s fine, but the reality is when you have a model like this, when you have two companies merging or one company acquiring another company, you don’t really know what’s going to happen after the fact. So with assumptions such as revenue synergies and cost synergies, these are all subject to a lot of variability. You really don’t know what you’re going to be able to achieve here, so you want to look a range of different values and see how the model is affected, how the output is affected at different values.


Also even for some of the basic assumptions here, such as the purchase price, the percent cash, stock and debt, and other variables like that, you don’t know exactly what the final transaction is going to look like here. So we have $75 per share for Apple acquiring RIM, but the reality is it could be $100 a share, or it could be $70 or it could be $80 a share. So we want to look at a range of values to make sure that we’re looking at all the possibilities and to see just how much variability there is with the impact on Apple’s EPS after the fact, here.

So similar to what we did for our DCF analysis with sensitivity tables here, the concept and the mechanics here are going to be very similar. So what we want to do in the first one here is look at the per share purchase price for RIM, and then we want to compare it to the expense synergies as a percent of their operating expenses. So the first step here is to figure out what kind of range we want to look at for the purchase price and then for the expense synergies.


Now for the purchase price, remember that in this model on the date that we’re using, RIM’s share price was $62.91 per share. So we can’t really go any lower than that. A merger would never take place if we had a share price lower than that. So what we’re going to do here is set the bottom of the range at $65 per share, and that’s really the lowest point where a merger like this could actually take place. Then we’re going to increment this by $5 in each case. So I’m going to add five. I can copy this up with Alt + E + S + F and highlight the whole area. We’re going from $65 a share to $110 per share.

Now for the expense synergies, remember what our assumption at the top was. We’re assuming that 10% of the seller’s operating expenses here can be reduced. Normally what we like to do for scenarios like this with synergies is for a baseline assumption we’re going to say 0%, because whenever two companies merge there’s always this possibility that maybe you actually cannot realize any synergies. So we want to make sure that this is somewhere in our range.


We’re going to increment this one by 2% each year. I’m going to copy this across. We see that our assumption of 10% is roughly in the middle of the range right here. So we’re going from 0% all the way up to 16%. With the range set, now what we want to do is link to the accretion/dilution percentage, right here, for year one, so the 5.9%. I’ve changed the style on the cell here. If you go to Ctrl + 1, and you go to Font, you see that I’ve changed the color of the font to match the background so that this is invisible.

So, now what we want to do is actually create this table. So I’m going to press Shift + Ctrl + Down arrow key, and then right arrow key to select the whole area. I’m going to press Alt + D + T for Data Tables. Then for the row input cell…


Remember that for the row input cell we want this to correspond with whatever is going across the top. So I’m going to select F14. Then for the column input cell, I’m going to select the per share purchase price right here.

Just hit Okay. I’m going to hit Save here to calculate this table, and so here we have our table. So we can see that for the expense synergies, as we get more expense synergies, then our accretion goes up and the transaction becomes more attractive. Likewise as the per-share purchase price goes up, the transaction becomes more dilutive and, therefore, less attractive, which makes intuitive sense.

If you pay more for a company, it’s going to be harder to get good financial results and for your EPS to go up. Whereas, if you achieve more expense synergies, then your EPS will go up more easily. Whenever you have a table like this, it’s always a good idea to do a quick spot check and make sure that that math here makes intuitive sense.


In this case we see the minimum is right here for the maximum purchase price and 0% expense synergies, and the maximum is right over here with the lowest purchase price possible and the maximum amount of expense synergies. So with that in place for the operating expense synergies, now we’re going to turn our attention to this other table and now we’re going to look at revenue synergies.

For this one, the approach is going to be very similar. I’m actually going to link directly to the purchase price range that we established up here. For the percentage of the seller revenue, remember that for revenue synergies we’re also looking at an assumption of 10%. So we’re going with the 10% assumption. As a result, I’m going to use the same range that we had for the expense synergies. I’ll just link up to the top for that. So we have that. I’m going to copy this across. Do the same for the per share purchase price.


So we have that in place. I’m also going to link to the year one accretion/dilution. With all that in place, now the last step is to just select everything with Shift + Ctrl + Down arrow key, right arrow key and then Alt + D + T for Data Table. For the row input cell we want to get the revenue synergy assumption, so let’s go up and get that. For the column input cell we want to get the per share purchase price. So we have that. Now just hit Ctrl + S, save here, to calculate everything again. So here we have the impact of our revenue synergies.

Now if we go and compare this table to the expense synergy table, it’s fairly similar. Except it looks like the revenue synergies have more of an impact on this transaction, because the minimum here for revenue synergies is lower than the minimum for expense synergies.


Then the maximum is also higher. The 12.8%, is actually higher than the 10.6% maximum that we saw for expense synergies. The reason this happens is with revenue synergies we’re working off of revenue for RIM, which, if we go up to their income statement, is around $17 billion going to $20 billion, $21 billion. With operating expense synergies, by contrast, we’re working off a much smaller number, here, so only around $2.5 or $3 billion. So that’s why the revenue synergies tend to have more of an impact.

So that’s how we actually go through and set up sensitivity tables like this for our merger model. Now there are many other variables that you could look at here, but these are some of the most common ones. Other variables that you could look at would be the percent cash, stock and debt – that’s probably one of the most common ones – purchase price, as we have here, revenue and expense synergies. You could also look at the operating performance of a company.


So maybe you make different assumptions on their revenue growth or their margins, and then you create a sensitivity table based on that. So these are all possible. You could also even, in addition to year one, you could look at year two and beyond. If you have projections beyond that you could go beyond year one and year two here.

So there are many possibilities for sensitivity tables, and you’ll see them done a lot of different ways in investment banking. But this is one of the most common set-ups, to look at purchase prices versus the synergies and the transactions and to create tables based on that.

Coming up in the next video, we’re going to be getting into interview questions on the topic of a merger model. We’ll start with one of the most basic ones which is to walk the interviewer through the mechanics of a merger model, how you set it up and how it works. After that, then we’ll get into more specific questions on the set-up, the assumptions. The items that get created in the balance sheet, the synergies and other possible interview questions you can get on those topics.

Get Instant Access To More Excel Video Tutorials
& PDF Cheatsheets

Sign Up