Excel Data Tables Sensitivity

Excel Data Table Sensitivity Tool for What If Analysis

In this segment you will learn how to find and activate the Excel Data Tables Sensitivity feature. You will also learn how to use the feature in its two forms, to compare the results of a calculation using different input automatically. Either one variable at a time, or two variables at a time.

DATA TABLES are particularly useful in spreadsheet models where the result is one cell which gives an important number of some sort, and that number is based on one or two key variables. So for example here we have a Mortgage repayment, we have the Amount owed, we’ve got a Repayment period in months and the Interest rate, and that results in a monthly repayment.And what we’d like to do is run a few scenarios on it, but run it automatically, looking at the changes in interest rate and changes in the repayment periods.

If we want to know what will happen to the repayments if interest rates changes only, we can generate a one variable data table. The way you do this is

• You click on a cell,
• you make it equals to the important cell, the cell that you are looking at. So we make it equal to the repayment period,
• and then below and to the left you input the various interest rates you want to compare, so we want look at 8%, 9%, 10 as the base case, 11 and 12%.
• What you do is you highlight this section
• you click on Data
• there’s an item called Table, and Excel will pop up this box. and what its asking you to do is give it a bit of information of what the table has got, and where in the model it must look for these relevant fields.
• Its asked you for the Row Input cell, and as you’ll see you haven’t given it a row, so that you can leave blank,
• the Column Input cell, you have provided a column of interest rates, and what its saying is, this column, what cell does it relate to in the model? and we know it relates to the interest rate,
• we click it and we say ok,
• what has happened now, Excel has gone and said, if the interest rate was 8%, it goes puts 8% in the model ,generates a number,
• so to check it, if you put 8% there, you’ll see it generates the same result,
• and similarly it has gone through and done that for each item

What’s nice about this feature is, let’s say you decide now that this is too conservative, and you want to look at a wider range of interest rates,

• You can just come here and push 6%
• and you’ll see it automatically recalculates the amount,
• lets say 8%, 10 is ok, 12, and 14%
• so almost immediately you’ve got an automatic calculation based on various interest rates,

What I like to do is clearly show that these are inputs that can be adjusted, by applying a colour, and I like to label it so that people know what this refers to, and this one as well.

What can you do, if you actually want to have a matrix showing the relationship on the repayment amount, between changes in interest rates and the changes in the repayments period?
You can do this, by using a two variable Data Table. The way to do that is

• you click on a cell,
• and make it equal to the cell you are making your decision on, in this case the repayment per month,
• click enter,
• directly below it, you input your one of the variables, so we’ll make it our interest rate 8%, 9%, 10, 11 and 12,
• next to it you put the other potential input values, and because its going to be repayment periods we’ll put 120 months, which is 10 years. 180 months which is 15 years, 240 and 300
• you now highlight this entire section
• and you go to Data,
• Table,
• now Excel’s saying, in the row you’ve given me, where in the model does this information relate to? and it relates to the repayment periods
• and it asks for the Column Input cell, so you’ve given a column of information, where in the model does it relate to, and it relates to the interest rate,
• and you push ok
• and what Excel has now done, it has gone to each combination
• put it in the model,
• found an answer
• and put it here

So for example if assuming as in the base case, interest rates are10%, and the repayment period is 240 months, there’s the 965 which is exactly the same.
And similarly it has gone and done the exact same thing with various interest rates, and various repayment periods.

What this means now is, you can say well lets go back to our more aggressive interest rate changes. If we put a 6 in there, what it does is it calculates if it’s 6%, what will happen to the repayments under the various scenarios
Similarly you can make changes to one of these, so lets say

• we want to go 240 here, 300 and we’ll go to 360 months,

So what you have is a very quick and easy way of identifying what happens to your decision cell, based on two different variables

Some general comments on the Data Table feature. I like to format my tables so that they add a bit of meaning. So for example I would highlight where ever my inputs are, and make them a colour. Similarly, I’d explain what they were, so this is the Interest Charge, Payment period, and Payment amount.

It’s also important to know that Data Tables as you can imagine, use up a lot of Excel’s memory.
Because when ever you make a change anywhere in the spreadsheet, what it does is, it goes and puts this input in, that input in , it gives you a number, and so it does it again and again.
You can control how that affects your memory by going to

• Tools,
• Options,
• and in the Calculations tab you’ll see you have an option to do all calculation except for the tables, which you then do manually by pushing F9
• or you can make everrything manual and make sure you just run the calculations by pushing F9

Another import thing about Data Tables is that all the information needs to be on the same sheet. So both the inputs to the model, and the final result, needs to be on the same sheet. The rest of the model can be over multiple sheets, but for this to work you need for it all to be on one sheet.