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.

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.

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

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.

- Now, write the following formula inside cell
**C9**:

`=C6*C8`

- Press
**ENTER**. The tax payable amount is calculated.

- Finally, put the following formula inside cell
**C10**:

`=C6-C9`

- Press
**ENTER**. The remaining income after tax deductions is calculated.

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…**

- Click on
**Add…**inside the**Scenario Manager**dialogue box.

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

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

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

Here, we have added three scenarios in total.

- Click on
**Summary…**

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

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.

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

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

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

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

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

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.

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

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

- To calculate the total revenue, write the following formula inside cell
**C11**:

`=C8*C10`

- Press
**ENTER**.

- To calculate the net profit, write the following formula inside cell
**C12**:

`=C11-C9`

- Press
**ENTER**.

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…**

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

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.

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

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

- Then, inside cell
**C13**, put the following formula:

`=C12/C6`

- Press
**ENTER**. The percentage of the obtained marks is currently**52%**.

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

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.

## 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**.

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

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

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

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

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.

- Again, type the following formula inside cell
**C13**:

`=C7*(C11+C12)`

- Press
**ENTER**. This will calculate the total cost of sending the mails.

- Inside cell
**C14**, write the following formula:

`=C7*C8`

- Press
**ENTER**. This will calculate the number of responses received after sending those mails.

- To calculate the gross profit, write the following formula inside cell
**C16**:

`=C14*C15`

- Press
**ENTER**.

- Finally, type the following formula inside cell
**C17**:

`=C16-C13`

- Press
**ENTER**. The net profit for the initial conditions is calculated.

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

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

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

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.

### 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**.

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.

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

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.

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

**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**