One and Two Variables Sensitivity Analysis in Excel (2 Examples)

The article will illustrate two examples to perform one and two-variable sensitivity analysis in Excel. Sensitivity analysis studies how different sources of uncertainty can affect the final output in a mathematical model. We can perform one and two-variable sensitivity analysis in Excel by using some formulas.

The sample dataset below will be used for illustration.

Example 1 – Example of One-Way Variable Sensitivity Analysis in Excel

We need to first rearrange the data to perform the one-way variable sensitivity analysis.

Steps:

We have selected the table ranges (cells B9:C16 in this case) which begin one row above the first input value (row 9) and the last row is the row containing the last input value (row 16).
The first column in the range is the column containing inputs (column B) and the last column is the column including outputs (column C).
Click the Data tab on the ribbon, choose What-If Analysis and click on Data Table.

A Data Table dialog box is prompted after clicking on Data Table. Since our table is column-oriented, enter the column input cell reference $C$4 and leave the row input cell blank.

On clicking OK, we will get the following results.
When the mortgage amount decreases from $200,000 to $140,000, the monthly payment will decrease from $886.96 to $620.87. The amount will decrease in the other variable mortgages as well as the monthly payment.

We have made a one-way data table to see the impact of changes in terms of the months.

Example 2 – Example of Two Way Variable Sensitivity Analysis in Excel

Let’s find out how monthly payment varies as total mortgage varies from $140,000 to $260,000 (in $20,000 increments) and term length varies from 5 years to 35 years (in 5-year increments).

Steps:

We have changed two inputs and therefore we created the two-way data table.
We have entered the mortgage amount values down the first column (column B) of the table range and term length values in the 7^th

Note: A two-way data table can have only one output cell and you must place the formula for the output in the upper-left corner (B9 in this case) of the table range.

As with a one-way table, we selected the table range (range B9:I16) and clicked the Data tab. In the Data Tools group, choose What-If Analysis and select Data Table. Since there are two sources of uncertainty, we need to fill both rows input cell and column input cell. In our case, cell C4 (mortgage amount) is the column input cell and cell C6 (mortgage term length) is the row input cell.

In the Data Table dialog box, we entered the cell reference $C$6 in the Row input cell section and $C$4 in the Column input cell

After clicking OK, the two-way data table will be shown as follows. For example, in cell C8, when the mortgage amount is $140,000 and the term length is 5 years or 60 months, the monthly payment is $2,540.58.

Things to Remember

If you change input values in a worksheet, the values calculated by a data table changes too. For example, if you increase the interest rate from 3.40% to 4.00%, the monthly payment will be $2578 (number in a blue square) given a 5-year $120,000 mortgage. When comparing with the above figure, you can see in the following image that all values in this two-variable data table have been changed.

You cannot delete or edit a portion of a data table. If you select a cell in the data table range and edit accidentally, the Excel file will prompt a warning message and you can’t save, change or even close the file. The only way that you can close it is by ending the task from task management.
Though the column-oriented one-way table (which is also applied in this post) is used popularly, you can also make your own row-oriented one-way table by yourself. Simply enter inputs in one row and only fill the row input cell reference in the Data Table dialog box.
When making a two-way data table, do not mix up your row input cell and column input cell.
Excel enables the automatic calculation by default and that’s the reason why any change in the inputs can cause all data in the data table to recalculate. However, sometimes, we’d like to disable this feature especially when the data tables are large and automatic recalculation is extremely slow. In this situation, how can you disable automatic calculation? Just click the File tab on the ribbon, choose Options, click the Formulas tab and select Automatic Except For Data Tables. You will be able to recalculate all your data in the data table only when you press the F9 (recalculation) key.

Download Practice Workbook

One and Two Variable Sensitivity Analysis.xlsx

<< Go Back to Data Table in Excel | What-If Analysis in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!