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
- 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.
If Analysis to Create a Sensitivity Table with One Variable" width="693" height="546" />
- 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.
If Analysis to Create a Sensitivity Table with Two Variables" width="690" height="716" />
- 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.
Download Practice Workbook
Sensitivity Table.xlsx
Related Articles
- One and Two Variables Sensitivity Analysis in Excel
- Data Table Not Working in Excel
- [Fixed] Excel Data Table Input Cell Reference Is Not Valid
Save Saved Removed 0
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
Leave a reply Cancel reply
Recent Posts
- 5 Excel Formulas Every Data Analyst Should Know
- 5 Essential Excel Functions for Data Analysis
- Excel Power Pivot Measures – 5 Examples
- Power Pivot Formulas (2 Suitable Examples)
- Excel Data for Analysis (Free Download 11 Suitable Datasets)
- Excel Sample Data (Free Download 13 Sample Datasets)
Posts from: Data Table Excel
- How to Create a 4-Variable Data Table in Excel (with Easy Steps)
- How to Create a Data Table with 3 Variables – 2 Examples
- [Fixed] Excel Data Table Input Cell Reference Is Not Valid
- How to Edit a Data Table in Excel (With Easy Steps)
- How to Delete a Data Table in Excel (4 Methods)
- Troubleshooting Data Table Issues in Excel (7 Solutions)
- Example of Excel Data Table (6 Criteria)
- How to Create a Data Table in Excel (7 Ways)
- One and Two Variables Sensitivity Analysis in Excel (2 Examples)
- How to Create a Two Variable Data Table in Excel – 3 Examples
About ExcelDemy.com
ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.
