Zehad Rian Jim
This tutorial will demonstrate how to do sensitivity analysis in excel. When you are doing any business, it is very important to analyze different parameters. Within this analysis, you can understand the situation of your business and take the necessary steps toward minimizing the loss and maximizing the profit. So, it is very important to learn to analyze the sensitivity in Excel.
What Is Sensitivity Analysis?
Sensitivity analysis in excel is very essential for any business model. It uses the What If command tab to show the desired result of any financial model. It not only can predict the present situation of the business but only helps in taking crucial decisions for the business’s growth.
If you follow the steps correctly, you should learn how to do sensitivity analysis in excel on your own. If the analysis depends on only one requirement then One Variable Analyze should be used, but if the analysis needs two requirements then Two Variable Analyze should be more efficient, and last if any sudden change is needed when desired output is already known then the Goal Seek command can be useful. To learn the methods proper we have to follow the below steps:
1. Analyzing with One Variable Data Table Do Sensitivity Analysis in Excel
In this case, our goal is to do sensitivity analysis for one variable in Excel. That means in this case one important variable will have a changing effect. The steps of this method are.
Steps:
- First, create a dataset like the below image and mark the Gross profit and Net profit cells with the desired color.
- Next, insert the Net profit value of 1st table in cell F4.
- After that, insert the Gross profit value of 1st table in cell G4.
- Afterward, after pressing the Enter button you will get the result and then select the whole data table you want to analyze.
- Furthermore, go to Data > Forecast > What-If Analysis options.
- Next to that, the Data Table dialog box will open on the screen, and in the box select the desired cell (in this case cell $C$6) in the Column input cell option.
- Finally, you will get the desired result.
Read More: One and Two Ways (Variables) Sensitivity Analysis in Excel!
2. Examining with Two Variables Data Table to Perform Sensitivity Analysis
Next, we will try to do sensitivity analysis for two variables in excel. In this case, we will consider two variables, one is the Units Sold and the other is the Selling Price. The steps of this method are described below.
Steps:
- First, arrange the proper dataset and, mark the Net profit value as we need to analyze this.
- Second, create another data table like the below image. The two variables the Units Sold and the Selling Price must be marked properly for ease of analysis.
- Third, insert the Net profit value from the first table ( from cell C17) into the F5 cell of this table.
- Fourth, select the data table portion you want to use for analysis.
- Fifth, go to Data > Forecast > What-If Analysis options.
- Sixth, insert the desired cells in the Row input cell and the Column input cell like in the below image and press OK.
- Last, you will get the desired result.
Read More: How to Build a Sensitivity Analysis Table in Excel (With 2 Criteria)
3. Using Goal Seek Command
Now, our aim is to do sensitivity analysis in Excel using the Goal Seek command. It helps to determine the proper changes when output is already known. When a product has already been launched and you need to track it with proper growth, then it is very essential. The steps of this method are.
Steps:
- To begin with, arrange the proper dataset like the below image.
- In addition, go to go to Data > Forecast > What-If Analysis > Goal Seek options.
- Furthermore, in the Goal Seek dialog box, select cell C17 in the Set cell option( the cell you want to change). Then choose 8000(the desired value you want to achieve) in the To Value option and select cell C6 (the cell you want to use as the variable) and press OK.
- Finally, you will get the desired result.
How to Fix “Sensitivity Analysis Not Working in Excel”
If in any case, you have followed the steps to analyze the sensitivity but it is not showing the proper result or any result, then you can fix the problem by following the below steps.
Steps:
- At first, identify if you have a problem like the below image.
- Second, then go to the File option.
- Third, go to the More…>Options.
- Forth, the Excel Options dialog box will open on the screen.
- Fifth, select the Formulas option and choose the Automatic option in the Calculations options and press OK.
- Last, you will get the desired result.
Things to Remember
- It is very important to mark and connect the cell with the proper color. Otherwise, it will be difficult to understand which values mean what.
- Generally, in the case of launching a business with a small no of products, one should use the first method, in the case of a business with a large no of products, one should use the second method and when any crucial change needs suddenly the third method should be more useful to use.
- It is recommended to download the excel file, analyze it and then use it accordingly for better understanding.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
Henceforth, follow the above-described methods. Thus, you will be able to do sensitivity analysis in Excel. 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.
