How to Create a Sensitivity Table in Excel (2 Effective Ways)

Sensitivity analysis or sensitivity tables in Microsoft Excel are quite easy to create or perform. By giving proper input of data and following some commands, one can easily perform a sensitivity analysis. In this article, I will show you how to create a sensitivity table in Excel.

Introduction to Sensitivity Analysis in Excel

According to a specific set of assumptions, sensitivity analysis evaluates how various values of an independent variable impact a specific dependent variable. One can apply this method within defined parameters that are dependent on one or more input variables.

How to Create a Sensitivity Table in Excel: 2 Effective Ways

In this article, you will see two effective ways to create a sensitivity table in Excel. In the first method, I will create the table with only one variable. But, in the second approach, I will use two variables to build the table. In both ways, I will require the Data Table feature of Excel to accomplish my task.

To illustrate my article further, I will use the following data set.

1. Create a Sensitivity Table with One Variable

In the first method, I will create a sensitivity table using one variable in Excel. After creating the table, you will be able to analyze the final result based on different inputs. The steps to perform this procedure are as follows.

Steps:

First of all, I need to do some pre-calculations before starting the main procedure.
Here, make three extra fields in the primary data set to calculate the total sales, total cost, and profit.

Next, to calculate the total cost insert the following formula in cell F6 and press Enter to get the value.

=F4*F5

To find out the total cost, I will need to add the utility cost and the transport cost and for that, in cell F9 type the following formula and hit Enter.

=F7+F8

Lastly, I will determine the profit for this whole procedure by entering the following formula in cell F10. Press Enter to get the desired result.

=F6-F9

Afterward, I will start the main procedure from this step. To start the procedure, in cell C13 of the sensitivity table, insert the same input of cell F10 in the following image.

After that, in the product quantity row, input the amount of product as per your choice.
Then, select range C12:H13 and go to the Data tab of the ribbon.
Lastly, from the What-If Analysis dropdown, choose Data Table.

You will see the Data Table dialogue box after the previous step.
In the Row input cell box, select cell F4, as I am creating this data table based on product quantity.
Lastly, press OK.

Finally, you will be able to create the sensitivity table after performing the previous steps.
From the image below, you can see the estimated profits for this business earned by selling different quantities of the same product.

2. Create a Sensitivity Table with Two Variables

In the second method of this article, I will create the sensitivity table with two variables instead of one. The other procedures for this method will be the same. For a better understanding, see the below-given steps.

Steps:

Firstly, create an additional table, under the primary data set like the following pictures.
Additionally, I did the pre-calculations for this method just like the first procedure of this article.

Then, in cell C13, insert the same cell value as in cell F10 just like in the following image.

Again choose Data Table from What-If Analysis after selecting the range C13:H18 like the picture given below.
Before selecting the range, input values of both variables as per your choice.

After selecting the previous command, you have to give some input in the Data Table dialogue box.
In the Row input cell box select cell F5, as the different values of price per item are in the same row.
In the Column input cell box select cell F4 as various values of the product quantity are in the same column.
Lastly, press OK.

Finally, you will see the sensitivity table filled with various values of profit just like the following image.
This indicates how much profit you will earn in terms of that respective product quantity and price per unit.
For example, if other costs are fixed and you sell 80 units of products at $65 per product then it will make a profit of $1750.

Things to Remember

Remember that the data table cell must contain the same formula just like the data set, while you are giving input from the main data set in the data table.
Otherwise, if you input the value only instead of the formula, it won’t show any actual result.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.

Sensitivity Table.xlsx

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to create a sensitivity table in Excel. Please share any further queries or recommendations with us in the comments section below.

Our team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.

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

Get FREE Advanced Excel Exercises with Solutions!