We can use two variable sensitivity analysis to evaluate the validity of a matrix containing a range of values for two variables related to a goal.
If you were interested in taking out a financial loan, you could use this function to model the possible monthly loan payments based on two variables – the amount of money you want to loan and the interest rate p.a. (per annum).
You first identify the range of the two variables, interest rate and loan value. For the loan amount you are willing to consider loans between 80,000 and 200,000. For the interest rate you will consider 7.8% - 9.0%.
Excel uses this range of values for both variables to generate a data table that lists the monthly payment at each intersection of the two variables. So, if I take this loan of 150,000 at 7.9%, on the table I will go to intersection those loan and interest values, in that cell will be the monthly payment.
In Excel we use the Data Table feature to generate this analysis.
Steps to create a Data Table/Two Variable Sensitivity Analysis
The steps below will take you through the process using sample data. A quick reference guide is available at the end of this document.
1. Open sample spreadsheet - How To Do A Two Variable Sensitivity Analysis In Excel Sample Data.
2. This sheet shows the monthly payment on a loan of 100,000 at 8.4% interest is 8,400.
3. In cell D9, the PMT function calculates the monthly payment, we will include this equation in the Data Table, where Excel applies this formula to the each of the variables listed.
4. Click in F5, type =D9, press enter. This step is to place the PMT formula within the Data Table.
5. In cell G5, type 80,000, in H5 type 100,000.
6. Select cells G5 and H5, left click the box in the bottom right, drag it to the right until the figure 200,00 displays, release the mouse.
7. In cell F6, type 7.8%, in cell F7 type 7.9%.
8. Select both F6 and F7, drag the fill handle down the column to value 9.0%.
9. Select F5 through M18.
10. On the menu click Data, in the Forecast group click What if Analysis, Data Table.
11. The Data Table window opens, the cursor is in the Row input cell. Click D6 as this contains the loan amount which are the values going along the top row of the data table area.
12. In the Data Table window, click in the Column input cell field, click D7 to indicate the interest rate is in the first column of the data table. Click OK.
13. Excel populates the data table with monthly payment amounts. Notice the value at the intersection of 100,000 and 8.4% is correct at 8,400, confirming the results of the PMT equation in D9.
We can now observe the monthly payment in multiple scenarios (e.g., taking out a larger loan at a smaller interest rate) and be confident that the values are valid.
Quick reference guide
- Open a spreadsheet, list the data and formula you would like to analyse. The two variables and the equation must be in separate cells. We have created a list of abbreviations to make the steps easier to follow.
- Variable A (row) = VA
- Variable B (column) = VB
- Equation = E
- Data Table location = DTL