# How to Build a Sensitivity Analysis Table in Excel (With 2 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. There are many default Excel functions that we can use to create formulas. Many educational institutions and business companies use Excel files to store valuable data. Sometimes, we estimate possible outcomes from real-life projects by changing input variables. Accordingly, an analysis of those probable scenarios is necessary. This is called Sensitivity Analysis. In that case, we can stay prepared for whatever happens. Hence, this article will show you the 2 different criteria to build a Sensitivity Analysis Table in Excel.

## How to Build a Sensitivity Analysis Table in Excel: With 2 Criteria

In this article, weâ€™ll build the sensitivity analysis table for both one-variable and two-variable cases. 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 take3 Â 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

Follow the below steps carefully to create a sensitivity analysis table based on one variable.

STEPS:

• First, select the cell range B9:C14.
• Then, go to Data âž¤ Forecast âž¤ What-If Analysis âž¤ Data Table.

• Consequently, the Data Table will pop out.
• Select cell C4 as the Column input cell.
• After that, press OK.

• As a result, itâ€™ll spill the precise monthly payment amounts.
• See the picture below.

• Now, click any cell in the spilled 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. Moreover, weâ€™ll move the formula in cell C9 for calculating the loan to cell B9. Now, learn the following process to build a Two-Variable Sensitivity Analysis Table in Excel.

STEPS:

• Firstly, choose the range B9:E14.
• Now, select Data âž¤ Forecast âž¤ What-If Analysis âž¤ Data Table.

• Subsequently, the Data Table will pop out.
• Next, choose C6 in the Row input cell.
• Again, select cell C4 as the Column input cell.
• After that, press OK.

• Thus, youâ€™ll get 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 inputs will result in changes in the final calculation of the outputs. For example, if you change the interest rate, the monthly payment amounts will get modified too. Therefore, special care is a must so that we donâ€™t change any input data by mistake.
• Moreover, you canâ€™t edit or delete a certain portion of the spilled output range. Youâ€™ll get a warning message if you try to do so. This could also result in unnecessary complications.
• During the two-variable data analysis, make sure you donâ€™t mix up while choosing the row and column input cells. Or else, itâ€™ll result in a big error.

## Conclusion

Henceforth, you will be able to build a Sensitivity Analysis Table in Excel following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Donâ€™t forget to drop comments, suggestions, or queries if you have any in the comment section below.

## Related Articles

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

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF