# How to Build a Sensitivity Analysis Table in Excel (One-Variable and Two-Variable)

This guide will show you how to build sensitivity analysis tables in Excel,Â looking at both single factors and two factors at once. To illustrate, weâ€™ll use the following dataset as an example. For instance, we have a Mortgage Amount, Interest Rate, and Months. In the One-Variable case, weâ€™ll consider 5 different mortgage amounts. And in the Two-Variable case, weâ€™ll take 3 Â different values for months along with the 5 mortgage amounts. In cell C9, we input the formula:

`=PMT(C5/12,C6,C4)`

The PMT function determines the loan amount for a fixed interest rate, time period, and present value of the mortgage.

### 1. Build One Variable Sensitivity Analysis Data Table in Excel

STEPS:

• Select the cell range B9:C14.
• Go to Data âž¤ Forecast âž¤ What-If Analysis âž¤ Data Table.

• In theÂ Data Table window, select the cell that has the variable you want (this is cell C4 in our example) as the Column input cell.
• Hit OK.

• Excel will generate different monthly payment amounts based on the different loan amounts you entered.

• Click any cell in the generated result range (C10:C14).
• Youâ€™ll see the same formula applied to all the output values.

Read More: How to Use What If Analysis in Excel

### 2. Create Excel Two-Variable Sensitivity Analysis Table

In this example, weâ€™ll arrange our dataset to consider the two variables. Weâ€™ll take 3 different time periods (60 months, 120 months, and 180 months) to pay back the mortgage amount. Here, 5 different values for the mortgage will be considered as well. Weâ€™ll move the formula in cell C9 for calculating the loan to cell B9.

STEPS:

• Choose the range B9:E14.
• Select Data âž¤ Forecast âž¤ What-If Analysis âž¤ Data Table.

• In theÂ Data Table window, choose C6 in the Row input cell.
• Select cell C4 as the Column input cell.
• Hit OK.

• It will generate the sensitivity analysis data table as shown below.

• Select any cell in the output range.
• You’ll see that the same formula is behind all the outputs.

## Things to Remember

• Any changes in the input data will result in changes in the final calculation of the output data. For example, if you change the interest rate, the monthly payment amounts will get modified too.
• You can’t edit or delete parts of the results table that Excel automatically fills in. Youâ€™ll get a warning message if you try to do so.
• When setting up a two-variable table, double-check that you’ve chosen the right cells for the row and column variables. Otherwise, itâ€™ll result in a big error.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine

Aung Shine completed his bachelorâ€™s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

1. Please help me create a sensitivity analasis to project annual revenue for an absa bank in south Africa

• Hello Poppy Lukhele,

Could you be more specific? If possible share any sample data.

Regards
ExcelDemy

Advanced Excel Exercises with Solutions PDF