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

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.


Download Practice Workbook

Download the following workbook to practice by yourself.


2 Different Criteria to Build a Sensitivity Analysis Table in Excel

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.

Build One Variable Sensitivity Analysis Data Table in Excel


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.

Build One Variable Sensitivity Analysis Data Table in Excel

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

Build One Variable Sensitivity Analysis Data Table in Excel

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

Build One Variable Sensitivity Analysis Data Table in Excel

  • Now, click any cell in the spilled result range (C10:C14).
  • You’ll see the same formula applied to all the output values.

Build One Variable Sensitivity Analysis Data Table 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.

Create Excel Two-Variable Sensitivity Analysis Table

STEPS:

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

Create Excel Two-Variable Sensitivity Analysis 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. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo