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.

Build One Variable Sensitivity Analysis Data Table in Excel


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.

Build One Variable Sensitivity Analysis Data Table in Excel

  • 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.

Build One Variable Sensitivity Analysis Data Table in Excel

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

Build One Variable Sensitivity Analysis Data Table in Excel

  • Click any cell in the generated 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

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.

Create Excel Two-Variable Sensitivity Analysis Table

STEPS:

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

Create Excel Two-Variable Sensitivity Analysis 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.

Read More: How to Perform Sensitivity Analysis for Capital Budgeting in Excel


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.

Download Practice Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
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

2 Comments
  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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo