See all How-To Articles

Do a Sensitivity / What-If Analysis in Excel

This tutorial will demonstrate how to do a sensitivity analysis in Excel.

WhatIf intro

Single-Input Data Tables

A single input data table enables us to change one variable in a table in order to see various results known as a What-If Analysis. In the graphic above, the repayment period in months has been expanded to show what the repayment would be if the term were 6, 9, 18, or 24 months, as well as the original 12 months that the formula in C8 is using.

Highlight the data table area in your worksheet.

WhatIF select table

In the Ribbon, select Data > What-If Analysis > Data Table.

WhatIf ribbon

As we are doing a single-input data table, and the terms we are changing are going down a column, we populate the column input cell with the cell address C6.

WhatIf Column input cell

This will amend the formula in cell c8 for each row in the data table, picking up the values in Column B (i.e., 6, 9, 18, and 24) in place of the value 12 in C6. Click OK.

WhatIf single input result

The table will be filled in with the new scenarios.

Double Input Data tables

A double input data table enables us to change two variables at the same time: both the row and the column.

Consider the following worksheet.

WhatIf double input blank

In this scenario, we wish to see what our repayments would be if both the term, and the interest rate were to change.

First, (1) highlight the data table, and then, (2) in the Ribbon, select Data > What-If Analysis > Data Table.

WhatIf double input ribbon

 

In the row input box, select C5 (the interest rate), and in the column input cell, select C6 (the term).

WhatIf double input cell addresses

 

Click OK.

WhatIf double input complete