What-If Analysis in Excel (Definition and Types)

Get FREE Advanced Excel Exercises with Solutions!

What-If analysis in Excel means exploring different scenarios by adjusting input values to observe their impact on formulas, results, and data trends.

In this free Excel tutorial, we will learn how to do what-if analysis in some practical cases using Excel’s features.

In the following overview image, we have done a two-variable what-if analysis on a direct mail profit model. An analysis data table is created with two variables: one is the response rate, which varies through the columns, and another is the mail quantity, which varies through the rows. The corresponding values of the net profits are visible in the table.

Overview to What If Analysis Excel

In this blog post, we will learn how to do what-if analysis while analyzing some practical scenarios related to tax rates, house rent, profit goals, passing scores in the exam, or mail profit model. We will perform this what-if analysis in the case of one, two, or three dynamic variables and observe how they can affect the outcome. Finally, we will also solve a problem that could occur during the what-if analysis using the Goal Seek feature.


What Is a What-If Analysis in Excel?
How to Use Scenario Manager for What-If Analysis in Excel
   ⏵Example 1: Tax Rate
   ⏵Example 2: House Rent
How to Use Goal Seek for What-If Analysis in Excel
   ⏵Example 1: Profit Goal
   ⏵Example 2: Passing Score
How to Use Data Table for What-If Analysis in Excel
   ⏵Example 1: One Variable Sensitivity Analysis
   ⏵Example 2: Two Variable Sensitivity Analysis
   ⏵Example 3: Three Variable Sensitivity Analysis
How to Fix If Goal Seek Is Not Working in Excel


What Is a What-If Analysis in Excel?

A What-If Analysis in Excel refers to the process of exploring various scenarios by changing input values in a spreadsheet to see how they affect the outcomes. It helps users analyze different possibilities and make informed decisions by observing the impact of changes on data trends within Excel models. There are 3 different tools in What-If Analysis.

  1. Scenario Manager: Scenario Manager helps to create a report upon changing one or more values in the dataset. This report shows how the result will change upon changing an input value.
  2. Goal Seek: The Goal Seek feature helps in a situation where someone knows the final value they want and needs to know how to change the input value to achieve the desired result.
  3. Data Table: The Data Table feature table helps to create a table with different scenarios depending on the variable change.

How to Use Scenario Manager for What-If Analysis in Excel

In this section, we will do what-if analysis using Scenario Manager in Excel to create summary reports based on two practical examples.

Example 1: Tax Rate

The first example is about deducing the tax amount from varying tax rates in the case of a fixed total income. The following dataset holds the values for total income and minimum income for tax eligibility, meaning you do not have to pay tax if your income is less or equal to the minimum income amount.

First, we will calculate the tax rate, tax payable, and remaining income one by one.

Tax Rate Dataset for What If Analysis Excel

To calculate these parameters, follow the steps below:

Steps:

  • First, write the following formula inside cell C8:
=IF(C6>C7,0.1,0)
  • Press ENTER. The tax rate is calculated based on the total income.

Tax Rate Formula for What If Analysis Excel

  • Now, write the following formula inside cell C9:
=C6*C8
  • Press ENTER. The tax payable amount is calculated.

Tax Payable Formula for What If Analysis Excel

  • Finally, put the following formula inside cell C10:
=C6-C9
  • Press ENTER. The remaining income after tax deductions is calculated.

Remaining Income Formula for What If Analysis Excel

Now to perform the what-if analysis by varying the tax rate and observing changes in the tax payable, follow the steps below:

  • First, go to the Data tab => What-If Analysis inside the Forecast group => Scenario Manager…

Selecting Scenario Manager for What If Analysis Excel

  • Click on Add… inside the Scenario Manager dialogue box.

Adding Scenarios for What If Analysis Excel

  • Inside the new dialogue box named Edit Scenario, write the Scenario name as 12% Tax Rate, and insert $C$8 as the location of the Changing cells.
  • Click on OK.

Entering Scenario Names and Changing Cells for What If Analysis Excel

  • Inside the Scenario Values dialogue box, under Enter values for each of the changing cells, type 0.12, as we named the scenario as 12% Tax Rate.
  • Click on OK.

Entering Value for Changing Cells

  • Now, we can add as many scenarios as we want, following the same procedure. Just click on add before adding a new scenario.

Adding More Scenarios

Here, we have added three scenarios in total.

  • Click on Summary…

Clicking on Summary

  • Inside the Scenario Summary dialogue box, select Scenario summary, write C9 under Result cells: and click OK.

Fixing Result Cell for Scenario Summary

The result of the what-if analysis will now appear on a new sheet in the form of a table titled “Scenario Summary”. There, you can see the changed values of tax payable with varying tax rates.

Scenario Summary Using Scenario Manager


Example 2: House Rent

The second example is about calculating the total rents of different houses. The total rent depends on four variables: rent, utility bill, garage rent, and common fund. Every house has a combination of these rent amounts, which ultimately results in different total rents. We will run a what-if analysis to calculate and compare the total rents of three houses. The following dataset holds the values for House 1.

House Rent Dataset

Follow the steps below for the what-if analysis:

Steps:

  • First, to calculate the total rent of house 1, write the following formula inside cell C10:
=SUM(C6:C9)
  • Press ENTER.

Total House Rent

  • Now, go to the Scenario Manager and click on Add as before.
  • Inside the Edit Scenario dialogue box, write House 2 under the Scenario name.
  • Select a range of cells, such as $C$6:$C$9 instead of a single cell under Changing cells; multiple cell values will now change the value of the total rent.
  • Click OK.

Inputting Array as Changing Cells for House Rent

  • Input the scenario values inside the respected fields for House 2 inside the Scenario Values dialogue box.
  • Click OK.

Inputting Array Information for House Rent

  • Follow the process for adding new scenarios and getting the summary from Example 1. Just specify the Result cells: as C10 in this case.
  • Click OK.

Fixing Result Cell for House Rent Scenario Summary

The result of the what-if analysis will now appear in a new sheet in the form of a table, titled “Scenario Summary” as before. There you can see the values of the total rents of 3 houses with varying rent parameters.

Scenario Summary for House Rent


How to Use Goal Seek for What-If Analysis in Excel

In this section, we will use the Goal Seek feature of the what-if analysis tool to find the conditions to achieve our goals based on two practical examples.

Example 1: Profit Goal

The first example is about calculating the required units to be sold to reach a net profit goal for a specific product. The following dataset holds the values for fixed cost, variable cost per unit, units sold, and unit price of a product. First, we are going to calculate the total cost, total revenue, and net profit of the product one by one.

Profit Goal DatasetTo calculate these parameters, follow the steps below:

Steps:

  • First, write the following formula inside cell C9:
=C6+C7*C8
  • Press ENTER. The total cost of the product is calculated based on the given data.

Formula for Calculating Total Cost

  • To calculate the total revenue, write the following formula inside cell C11:
=C8*C10
  • Press ENTER.

Formula for Calculating Total Revenue

  • To calculate the net profit, write the following formula inside cell C12:
=C11-C9
  • Press ENTER.

Formula for Calculating Net Profit

Suppose our goal is to increase our current net profit from $48000 to $70000. Now we are going to find out how many units of product we need to sell if we want to make $70000 in net profit, keeping all other parameters constant.

  • Go to Data tab => What-If Analysis from Forecast group => Goal Seek…

Selecting Goal Seek Tool

A dialogue box named Goal Seek will appear.

  • Beside the Set cell:, write the cell address of the net profit value (in this case, C12). Beside To value: write the target value (in this case, 70000). Lastly, besides changing cell: write the cell address of the number of products sold (in this case, $C$8).
  • Click on OK.

Setting Goal Seek Parameters for Profit Goal

A dialogue box will appear stating the current value and the previous value of net profit. Finally, inside the data table, the quantity of units sold is changed according to the new net profit.

  • Click on OK to keep the changes in the data table.

Goal Achieved for Profit


Example 2: Passing Score

The second example is about calculating the required marks in the final exam to score the minimum percentage to pass. The following dataset holds the values of the total marks, minimum passing percentage, and the marks corresponding to attendance, class tests, and the final exam. First, we need to calculate the total mark obtained by a student and express that mark as a percentage of the total.

Passing Score DatasetTo calculate these parameters, follow the steps below:

Steps:

  • Type the following formula inside cell C12:
=SUM(C9:C11)
  • Press ENTER. The total marks obtained by the student is calculated.

Calculating Total Obtained Mark

  • Then, inside cell C13, put the following formula:
=C12/C6
  • Press ENTER. The percentage of the obtained marks is currently 52%.

Calculating Percentage Obtained

Now our goal is to achieve 60% marks to pass the course, and we will evaluate the minimum marks that are required to score in the final exam to achieve that percentage, given that the other parameters are constant.

  • To do that, go to the Goal Seek… feature as before.
  • Inside the Goal Seek dialogue box, type the following information:

Set cell: $C$13
To value: 0.6
By changing cell: $C$11

  • Click on OK.

Setting Goal Seek Parameters for Passing Marks

We can see that the minimum mark that is to be obtained in the final exam is 125 to achieve a passing percentage.

  • Click OK to keep the changes.

Passing Percentage Obtained by Changing Final Marks


How to Use Data Table for What-If Analysis in Excel

In this section, we will use the Data Table feature of the what-if analysis tool to do sensitivity analysis in Excel for one, two, and three variables.

Example 1: One Variable Sensitivity Analysis

Here, we will observe the remaining incomes for the varying tax rates by creating a one-variable data table using what-if analysis.

To do that, follow the steps below:

Steps:

  • Create a second data table on the right side of the previous data table, having two columns, one for the varying tax rates and another for the corresponding remaining incomes, as shown in the image below. Also, enlist the tax rates for which we want to know the remaining incomes.
  • Inside cell F7, type the following formula:
=C10
  • Press ENTER.

Making a Suitable Table for Data Table Analysis

  • Then, select the range E7:F10 and click on Data tab => What-If Analysis inside Forecast group => Data Table…

Selecting Data Table Analysis Tool

  • Inside the dialogue box named Data Table, select the cell $C$8 that contains the current tax rate beside the Column input cell:
  • Click on OK.

Fixing Column Input Cell for 1 variable Data Table

The remaining incomes for the mentioned tax rates are visible inside the corresponding cells.

One Variable Data Table Result


Example 2: Two-Variable Sensitivity Analysis

In this example, a company wants to make a direct-mail promotion to sell its products. This worksheet calculates the net profit from the direct-mail promotion.

This data table model uses two input cells: the number of mails sent and the expected response rate. Also, some more items appear in the parameters area.

To begin with, we need to calculate some of these parameters.

Mail Quantity Dataset

Follow the steps below to calculate the missing parameters in this data table:

Steps:

  • Inside cell C11, write the following formula:
=IF(C7<300000,0.3,0.25)
  • Press ENTER. This formula generates the printing cost per mail as $0.3, if the total number of mail to be printed is below 300000, and $0.25 if the number is greater than 300000.

Deducing Printing Cost Per Mail

  • Again, type the following formula inside cell C13:
=C7*(C11+C12)
  • Press ENTER. This will calculate the total cost of sending the mails.

Deducing Total Cost for Mailing

  • Inside cell C14, write the following formula:
=C7*C8
  • Press ENTER. This will calculate the number of responses received after sending those mails.

Deducing Number of Responses

  • To calculate the gross profit, write the following formula inside cell C16:
=C14*C15
  • Press ENTER.

Deducing Gross Profit

  • Finally, type the following formula inside cell C17:
=C16-C13
  • Press ENTER. The net profit for the initial conditions is calculated.

Deducing Net Profit

Now, to create a two-variable data table in Excel for performing sensitivity analysis on this data model, follow the steps below:

  • Create a new data table that will accommodate the varying response rates as columns and the varying mail quantities as rows, as shown in the image below.
  • Inside cell B21, type the following formula:
=C17
  • Press ENTER.

Creating Table for 2 Variable Data Table Analysis

  • Now, select the range B20:I27 and click on Data tab => What-If Analysis inside Forecast group => Data Table…

Selecting Data Table for 2 Variable Analysis

  • Inside the Data Table dialogue box, write the following pieces of information:

Row input cell: $C$18
Column input cell: $C$7

  • Click on OK.

Fixing Row Input and Column Input for 2 Variable Analysis

The net profits for the mentioned mail quantities and response rates are visible inside the corresponding cells. The negative values of the net profit explain that under those corresponding mail quantities and response rates, losses take place instead of any profit.

Output for 2 Variable Data Table Analysis


Example 3: Three Variable Sensitivity Analysis

In this instance, we will use the Data Table command to create a data table with three variables in Excel. Here, we will first create a regular 2-way data table, exactly like before. Then, we will connect the data table with the third variable, which is profit per response.

To connect the third variable, follow the steps below:

Steps:

  • Create another table for the third variable, as shown in the image below (Range E6:F12).
  • Create a drop-down list inside cell E7 containing the varying profit per response value.
  • Inside cell C15, write the following formula:
=E7
  • Press ENTER.

Adding Number of Responses as 3rd Variable

Our three-variable data table is now ready for what-if or sensitivity analysis. Just choose any profit per response from the drop-down list at cell E7 and observe the dynamic changes for the corresponding net profits.

3 Variables Data Table Analysis Result


How to Fix If Goal Seek Is Not Working in Excel

If you’re using Goal Seek in Excel and can’t find a solution, it might mean there isn’t a perfect answer. In such cases, Excel will give you the closest possible value and let you know that it might not have found an exact solution.

Showing Error in Goal Seek

If you’re sure there should be a solution, consider applying these troubleshooting tips below:

1. Recheck Goal Seek Parameters

Make sure the cell you’re setting refers to a cell with a formula. Also, ensure that the cell you are changing refers to a cell with no formula but value in it. Then, check if the formula in that cell depends on the changing cell, either directly or indirectly.

2. Adjust Maximum Iterations

Follow the steps below to solve the problem:

  • First, go to File => Options => Formulas
  • Make changes in the following parameters:

Maximum Iterations: If you want Excel to explore more possible solutions, just raise this number.

Maximum Change: If your formula needs to be more precise, lower this number. For instance, if you’re working with a formula where the input should be 0, but Goal Seek stops at 0.1, adjusting the maximum change to 0.0001 can help solve the problem.

Adjusting iteration settings

You can see that this time Goal Seek Status shows that it found a solution.

Goal Seek Problem Solved

3. Avoid Circular References

To ensure Goal Seek or any Excel formula functions correctly, the formulas involved mustn’t rely on each other. Circular references occur when a formula refers to its cell directly or indirectly, creating a loop. Avoiding such interdependencies helps prevent errors and ensures the accurate execution of calculations in Excel.

Here, we have demonstrated a problem related to the Goal Seek feature and solved it. But if you face a problem with the what-if analysis data table not working in Excel while using the data table feature, you can pay a visit to the linked article.


 Download Practice Book


This article has shown how to do what-if analysis in Excel. The two methods have discussed the techniques of performing what-if analysis using different features in Excel. We showed you how to employ the what-if analysis tool and how to avoid a potential error while working with Goal Seek analysis. Leave a comment for any further queries.


What-If Analysis in Excel: Knowledge Hub


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo