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

 

 

Method 1 – Sensitivity Analysis with One-Variable Data in Excel

Steps:

  • Create a dataset like in the image below and mark the Gross profit and Net profit cells with the desired color (manually). The cells in Formulas need to be referencing the cells from Inputs.

Methods to Do Sensitivity Analysis

  • Reference the Net profit value from the table in cell F4.

Methods to Do Sensitivity Analysis

  • Reference the Gross profit value in cell G4.

Methods to Do Sensitivity Analysis

  • Select the whole data table you want to analyze.

Methods to Do Sensitivity Analysis

  • Go to Data > Forecast > What-If Analysis option.

Methods to Do Sensitivity Analysis

  • The Data Table dialog box will open on the screen. Select the variable cell (in this case cell $C$6) in the Column input cell option.

Methods to Do Sensitivity Analysis

  • The table will autofill.

Methods to Do Sensitivity Analysis

Read More: How to Use What If Analysis in Excel


Method 2 – Perform Sensitivity Analysis with a Two-Variable Data Table

Let’s consider two variables: Units Sold and Selling Price. The two-variable data table will show different values for a single result (such as Net Profit) as the variables change.

Steps:

  • Create a proper dataset and mark the Net profit value as the one being analyzed.

Methods to Do Sensitivity Analysis

  • Create another data table like the below image. The two variables (Units Sold and Selling Price) should be marked.

Methods to Do Sensitivity Analysis

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

Methods to Do Sensitivity Analysis

  • Select the data table portion you want to use for analysis.

Methods to Do Sensitivity Analysis

  • Go to Data > Forecast > What-If Analysis options.

Methods to Do Sensitivity Analysis

  • Insert the desired cells in the Row input cell and the Column input cell with absolute references (see image below) and press OK.

Methods to Do Sensitivity Analysis

  • The table will populate.

Methods to Do Sensitivity Analysis

Read More: How to Build a Sensitivity Analysis Table in Excel


Method 3 – Using Goal Seek Command for Sensitivity Analysis to Set a Variable

Steps:

  • Construct a dataset below (use the download sample).

Methods to Do Sensitivity Analysis

  • Go to Data > Forecast > What-If Analysis > Goal Seek.

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

  • The Goal Seek formula will adjust cell C6 based on the expected value in C17.


How to Fix “Sensitivity Analysis Not Working in Excel”

Steps:

  • Identify if you have a problem like the below image.

  • Select the File tab.

Methods to Do Sensitivity Analysis

  • Go to More…>Options. The Excel Options dialog box will open on the screen.

  • Select the Formulas tab, then choose the Automatic option in the Calculations options and press OK.

  • This should fix the table.

Read More: What If Analysis Data Table Not Working


Download Practice Workbook

You can download the practice workbook from here.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo