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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
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.
2 Effective Ways to Create a Sensitivity Table in Excel
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.
- 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.
- Secondly, to calculate the total cost insert the following formula in cell F6 and press Enter to get the value.
- Thirdly, for finding 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.
- And lastly, I will determine the profit for this whole procedure by entering the following formula in cell F10, and by pressing Enter, I will get the desired result.
- 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 like the following image.
- After that, in the product quantity row, input the amount of product as per your choice.
- Then, select the cell range from C12:H13 and go to the Data tab of the ribbon.
- Lastly, from the What-If Analysis dropdown, choose Data Table.
- Consequently, you will see the Data Table dialogue box after the previous step.
- Then, in the Row input cell type 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.
- How to Create Table from Data Model in Excel (With Easy Steps)
- Make a Table in Excel with Lines (with Easy Steps)
- How to Create Excel Table with Row and Column Headers
- Excel VBA to Create Table from Range (6 Examples)
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.
- Firstly, create an additional table, under the primary data set like the following pictures.
- Additionally, I did the pre-calculations for this method just in like the first procedure of this article.
- Secondly, in cell C13, insert the same cell value as in cell F10 just like in the following image.
- Thirdly, again choose Data Table from What-If Analysis after selecting the cell range C13:H18 like the picture given below.
- Additionally, before selecting the cell range, input values of the both variables as per your choice.
- Fourthly, after selecting the previous command, you have to give some input in the Data Table dialogue box.
- First of all, in the Row input cell box select cell F5, as the different values of price per item are in the same row.
- Then, 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.
- Consequently, 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 exact 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.
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.
The ExcelDemy 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.