# How to Create a Sensitivity Table in Excel (2 Methods)

Sensitivity analysis evaluates how various values of an independent variable impact a specific dependent variable. We will apply this to the sample data set below.

### Method 1 – Create a Sensitivity Table with One Variable

Steps:

• Make three extra fields in the primary data set to calculate the total sales, total cost, and profit.

• To calculate the total cost insert the following formula in cell F6 and press Enter to get the value.
`=F4*F5`

• To find the total cost, add the utility cost and the transport cost together.
• To do this, in cell F9 type the following formula and hit Enter.
`=F7+F8`

• Determine the profit by entering the following formula in cell F10.
• Press Enter.
`=F6-F9Â `

• In cell C13 of the sensitivity table, insert =F10 as in the following image.

• In the product quantity row, input the amount of product.
• Select range C12:H13 and go to the Data tab of the ribbon.
• From the What-If Analysis dropdown, choose Data Table.

• In the Row input cell box, select cell F4, to create a data table based on product quantity.
• Press OK.

• The sensitivity table displays estimated profits for selling different quantities of a single product.

### Method 2 – Create a Sensitivity Table with Two Variables

Steps:

• Create an additional table under the primary data set.

• In cell C13, insert the same cell value as cell F10 as in the following image.

• Choose Data Table from What-If Analysis after selecting the range C13:H18 as in the picture given below.
• Input the values of both chosen variables.

• In the Row input cell box select cell F5.
• In the Column input cell box select cell F4.
• Press OK.

• The sensitivity table has been filled with various values of profit.
• This indicates how much profit can be earned in terms of each product quantity and price per unit.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF