Aung Shine
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.
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: One and Two Ways (Variables) Sensitivity 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.
Read More: How to Perform Sensitivity Analysis for Capital Budgeting in Excel
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.
