How to Do Sensitivity Analysis in Excel (3 Easy Methods)

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.


Download Practice Workbook

You can download the practice workbook from here.


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.


3 Easy Methods to Do Sensitivity Analysis in Excel

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

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.

Methods to Do Sensitivity Analysis

  • Next, insert the Net profit value of 1st table in cell F4.

Methods to Do Sensitivity Analysis

  • After that, insert the Gross profit value of 1st table in cell G4.

Methods to Do Sensitivity Analysis

  • Afterward, after pressing the Enter button you will get the result and then select the whole data table you want to analyze.

Methods to Do Sensitivity Analysis

  • Furthermore, go to Data > Forecast > What-If Analysis options.

Methods to Do Sensitivity Analysis

  • 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.

Methods to Do Sensitivity Analysis

  • Finally, you will get the desired result.

Methods to Do Sensitivity Analysis

Read More: One and Two Ways (Variables) Sensitivity Analysis in Excel!


2. Examining with Two Variables Data Table

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.

Methods to Do Sensitivity Analysis

  • 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.

Methods to Do Sensitivity Analysis

  • Third, insert the Net profit value from the first table ( from cell C17) into the F5 cell of this table.

Methods to Do Sensitivity Analysis

  • Fourth, select the data table portion you want to use for analysis.

Methods to Do Sensitivity Analysis

  • Fifth, go to Data > Forecast > What-If Analysis options.

Methods to Do Sensitivity Analysis

  • Sixth, insert the desired cells in the Row input cell and the Column input cell like in the below image and press OK.

Methods to Do Sensitivity Analysis

  • Last, you will get the desired result.

Methods to Do Sensitivity Analysis

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.

Methods to Do Sensitivity Analysis

  • 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 When Sensitivity Analysis in Excel Not Working

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.

Methods to Do Sensitivity Analysis

  • 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.

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.


Related Articles

Zehad Rian Jim

Zehad Rian Jim

Hi there! I am Zehad Rian Jim. I graduated with a bachelor's degree in engineering from BUET. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of the workplace, my hobbies and interests include watching movies, tv series, and meeting new people. I also enjoy sports. My favorite sports are Cricket (to watch and play) and Badminton (play).

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo