How to use the Excel Data Tables Sensitivity tool for both a 1 variable and 2 variable table especially for 'what if' questions in Excel.
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
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,
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
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
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
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.
Advanced Excel Course – June 2013 in South Africa Our next advanced excel course is running during June 2013 in Johannesburg. During the week of the 10th June we offer the following options. You can either attend the full 5 … Continue reading
A very popular chart is the waterfall chart which shows the impact of various items on a total e.g. the various components that explain the move from budgeted profit to actual profit. A common way to build this is using … Continue reading
The remarkable thing about Adrian's presentation is his ability to further change and adapt the focus of the course as issues arose. He was also able to keep the more skilled delegates challenged...– Alexander Proudfoot Consultants