In the following dataset, we have a two-variable what-if analysis on a direct mail profit model. One variable 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.

## What Is a What-If Analysis in Excel?

A What-If Analysis in Excel refers to 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 create a report when one or more values in the dataset are changed. This report shows how the result will change upon changing an input value.**Goal Seek:**The**Goal Seek**feature helps when 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 a Scenario Manager for What-If Analysis in Excel

### Example 1: Tax Rate

**Steps**:

- Enter the following formula in cell
**C8**:

`=IF(C6>C7,0.1,0)`

- Press
**ENTER**. The tax rate is calculated based on the total income.

- Enter the following formula in cell
**C9**:

`=C6*C8`

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

- Enter the following formula in cell
**C10**:

`=C6-C9`

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

To perform the what-if analysis by varying the tax rate and observing changes in the tax payable:

- 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**, insert 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 changing cell, type 0.12, as we named the scenario 12% Tax Rate. - Click on
**OK**.

- Following the same procedure, we can add as many scenarios as we want. Before adding a new scenario, click on add.

Here, we have added three scenarios in total.

- Click on
**Summary…**

- Inside the Scenario Summary dialogue box, select Scenario Summary.
- Type 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

**Steps:**

- To calculate the total rent of house 1, enter the following formula in cell
**C10**:

`=SUM(C6:C9)`

- Press
**ENTER**.

- Go to the
**Scenario Manager**and click on**Add**as before. - Write House 2 under the Scenario name inside the Edit Scenario dialogue box
**.** - 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 total rent value. - 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**. 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 as 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

### Example 1: Profit Goal

**Steps:**

- Enter 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, enter the following formula in cell
**C11**:

`=C8*C10`

- Press
**ENTER**.

- To calculate the net profit, enter the following formula in cell
**C12**:

`=C11-C9`

- Press
**ENTER**.

Suppose we aim to increase our net profit from $48000 to $70000. Now we will 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:**type the cell address of the net profit value (in this case,**C12**). - Beside
**To value:**type the target value (in this case, 70000). - Besides
**changing cell:**type 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 and previous values of net profit. The quantity of units sold changes according to the new net profit in the data table.

- Click on
**OK**to keep the changes in the data table.

### Example 2: Passing Score

**Steps:**

- Enter the following formula in cell
**C12**:

`=SUM(C9:C11)`

- Press
**ENTER**. The total marks obtained by the student is calculated.

- Enter the following formula in cell
**C13**:

`=C12/C6`

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

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. - In the
**Goal Seek**dialogue box, enter 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 required to achieve a passing percentage in the final exam is 125.

- Click
**OK**to keep the changes.

## How to Use Data Tables for What-If Analysis in Excel

### Example 1: One Variable Sensitivity Analysis

**Steps:**

- Create a second data table on the right side of the previous one, with 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.
- Enter the following formula in cell
**F7**:

`=C10`

- Press
**ENTER**.

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

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

**Steps:**

- Enter the following formula in cell
**C11**:

`=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.

- Enter the following formula in cell
**C13**:

`=C7*(C11+C12)`

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

- Enter the following formula in cell
**C14:**

`=C7*C8`

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

- To calculate the gross profit, enter the following formula in cell
**C16**:

`=C14*C15`

- Press
**ENTER**.

- Enter the following formula in 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:

- Create a new data table that will accommodate the varying response rates as columns and mail quantities as rows, as shown in the image below.
- Enter the following formula in cell
**B21**:

`=C17`

- Press
**ENTER**.

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

- Enter the following information in the Data Table dialogue box:

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 net profit values explain that losses take place under those corresponding mail quantities and response rates instead of any profit.

### Example 3: Three Variable Sensitivity Analysis

**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. - Enter the following formula in cell
**C15:**

`=E7`

- Press
**ENTER**.

Our three-variable data table is now ready for what-if or sensitivity analysis.

Choose any profit per response from the drop-down list in cell E7 and observe the dynamic changes in the corresponding net profits.

## How to Fix If Goal Seek Is Not Working in Excel

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

- Go to
**File**=>**Options**=>**Formulas** - Make changes in the following parameters:

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

** Maximum Change:** Lower this number if your formula needs to be more precise. 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 a solution was found.

**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 and solved a problem related to the Goal Seek feature. 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 visit the linked article.

** ****Download the Practice Book**

## What-If Analysis in Excel: Knowledge Hub

**<< Go Back to Learn Excel**