**What-if analysis** is a process through which you can see the outcome by changing any cell in the dataset. In Microsoft Excel, you can get three different types of what-if analysis. All of these types are fairly useful for data analysis. This article will show a** what-if analysis example **in Excel. I hope you find this article interesting and gain lots of knowledge regarding the what-if analysis.

## What-If Analysis in Excel: Overview

What-if analysis is a process through which you can see the outcome by changing any cell in the dataset. By using the what-if analysis in Excel, you can use several sets of values and get the desired outcome. For example, in the what-if analysis, you can calculate the total expenses of several houses and finally select your preferred house. That means, using the what-if analysis, you can establish a proper overview of all types of things and also denotes how it reacts in the future. The main purpose of the what-if analysis in Excel is to determine the outcome in a statistical mood and do the risk assessment. The main advantage to use what-if analysis in Excel is that there is no need to create a new worksheet but we can get the final results for different inputs.

## Types of What-If Analysis in Excel

There are three types of what-if analysis in Excel: **Scenario**,** Goal Seek** and **Data Table**. The scenario and data table take the input values and provide the possible outcome using these inputs using the 3 types of what-if analysis in Excel.

The **Scenario Manager** allows you to alter your input values without changing your dataset and establish the final results which compare the new values with the existing ones. In this process, you can change input values up to 32 cells at a time. The Scenario manager provides a platform to develop strategies and plans for the future and determines which scheme to take for both the long run and short run. It helps us to understand the best and worst possible cases. More importantly, assists us to moderate risk and making better decisions.

The **Goal Seek **which is the second of three types of what-if analysis in Excel helps to measure the required amount of something beforehand if you have the desired result in your mind. That means if you have the result in your mind but donâ€™t know how to change the input value to get the desired result. In that case, goal seek can help you. For example, you know what you want to get in the final result, but donâ€™t know how much you need to score to touch that grade. Goal seek will help you to do that effectively.

When you have a formula that requires one or two variables or multiple formulas require one variable, in that case, you can utilize **Data Table** to get all the outcomes in a single place. For example, you need to calculate EMI for different interest rates or calculate EMI for the different loan amounts and interest rates. Both of the examples can be done by using the **Data TableÂ **which is the third of three types of what-if analysis in Excel.

## Examples of What-If Analysis with Scenario Manager in Excel

To understand the first type of what-if analysis example using scenario manager in Excel, we would like to show two different examples through which you can use it properly. We take two different examples considering a house rent problem and movie theater profit. In both cases, you would like to utilize the scenario manager.

### 1. What-If Analysis of House Rent in Excel

Our first example is based on the house rent. Using the scenario manager, you can find out which house is applicable for us. We would like to consider two scenarios

**House 2****House 3**

The initial condition or dataset can consider as house 1. Then, the scenario manager summary will give us the total cost of each house. Using this summary, you can select any possible house to stay in. To understand the example clearly, follow the steps.

**Steps**

- First, we need to calculate the total cost of the initial dataset.
- Select cell
**C10**. - Then, write down the following formula using
**the SUM function**.

`=SUM(C5:C9)`

- Press
**Enter**to apply the formula.

- Then, go to the
**Data**tab in the ribbon. - Select the
**What-If Analysis**drop-down option from the**ForecastÂ**group. - Then, select the
**Scenario ManagerÂ**option.

- As a result, it will open the
**Scenario Manager**dialog box. - Then, select
**Add**to include new scenarios.

- Then, the
**Add Scenario**dialog box will appear. - First, set
**House 2**as the**Scenario name**. - Then, set the range of cells
**C5**to**C9**as the**Changing cells**. - Finally, click on
**OK**.

- It will take us to the
**Scenario Values**dialog box. - Here, we need to put the input values of rent, electricity bill, gas bill, garage bill, and others.

- Then, click on
**OK**. - As a result, it will take us back to the
**Scenario Manager**dialog box. - Click on
**Add**to include another scenario.

- Then, the
**Add Scenario**dialog box will appear. - First, set
**House 3**as the**Scenario name**. - Then, set the range of cells
**C5**to**C9**as the**Changing cells**. - Finally, click on
**OK**.

- It will take us to the
**Scenario Values**dialog box. - Here, we need to put the input values of rent, electricity bill, gas bill, garage bill, and others.
- Then, click on
**OK**.

- After that, in the
**Scenario Manager**dialog box, select**Summary**.

- As a result, the
**Scenario Summary**dialog box will appear. - Then, select
**Scenario Summary**as**Report Type**. - Set cell
**C10**as the**Result cells**. - Finally, click on
**OK**.

- Finally, we get the following results where you get the outcome without creating a new worksheet.

**Read More:** How to Do Sensitivity Analysis in Excel

### 2. Performing What-If Analysis for Movie Theatre Profit

Our next example is based on the scenario of the movie theatre. In this example, we will focus on the profit of movie theatres for different scenarios. First, we take a dataset that consists of the cost and revenue of a small movie theatre. Then, we would like to use the scenario manager to get the final output for several scenarios.

Here, we would like to take two scenarios under consideration.

**Medium Venue****Large Venue**

To use a what-if analysis scenario manager for a movie theater example, follow the steps carefully.

**Step 1: Calculate Movie Theatre Profit **

First, we need to calculate the revenue amounts. Here, the cost of the movie theatre changes with its size. So, we would like to utilize the scenario manager in that case. To calculate the movie theater profit, follow the following steps.

- First, select cell
**F6**to calculate the**Ticket Sales**. - Write down the following formula.

`=C5*F5`

- Then, press
**Enter**to apply the formula.

- Select cell
**F7**to calculate the**Food & Beverages**. - We create a link with the total number of seats in the movie theater. By using the total number of seats, we assume the
**Food & BeveragesÂ**amount. - Write down the following formula.

`=15*C5`

- Then, press
**Enter**to apply the formula.

- Select cell
**F8**to calculate the**Others**. - We create a link with the total number of seats in the movie theatre. By using the total number of seats, we assume the
**OthersÂ**amount. - Write down the following formula.

**=**

`4*C5`

- Then, press
**Enter**to apply the formula.

- To calculate the
**Total Revenue**, select cell**F9**. - Then, write down the following formula using the
**SUMÂ**function.

`=SUM(F6:F8)`

- After that, press
**Enter**to apply the formula.

- Next, we need to calculate the profit earned by the movie theatre.
- Select cell
**F11**. - Then, write down the following formula.

`=F9-C12`

- Press
**Enter**to apply the formula.

**Step 2: Create Scenarios **

In this step, we will create three different scenarios in the Scenario Manager. These three scenarios include medium venue, large venue, and very large venue. To create these, follow the steps.

- First, go to the
**Data**tab in the ribbon. - Select the
**What-If Analysis**drop-down option from the**ForecastÂ**group. - Then, select the
**Scenario ManagerÂ**option.

- As a result, it will open the
**Scenario Manager**dialog box. - Then, select
**Add**to include new scenarios.

- As a result, the
**Edit Scenario**dialog box will appear. - Here, set
**Medium Venue**as**Scenario name**. - Then, select the range of cells
**C5**to**C11**and cell**F5**. That means all the cost changes along with the size of the theater. However, ticket prices will also increase. - After that, click on
**OK**.

- It will open up the
**Scenario Values**dialog box. - Here, we set values for a medium venue. In this section, we need to change the seats, ticketing, lighting, security, insurance, rent, and ticket price.

- Then, scroll down and set another cell value properly. See the screenshot.

- Then, click on
**OK**. - As a result, it will take us to the
**Scenario Manager**dialog box again. - Then, select Add to include another scenario.

- After that, set
**Large Venue**as the**Scenario name**. - Select range of cell
**C5**to**C11**and cell**F5**. That means all the cost changes along with the size of the theater. However, ticket prices will also increase. - After that, click on
**OK**.

- It will open up the
**Scenario Values**dialog box. - Here, we set values for a large venue. In this section, we need to change the seats, ticketing, lighting, security, insurance, rent, and ticket price.

- Then, scroll down and set another cell value properly. See the screenshot.
- Then, click on
**OK**.

**Step 3: Generate Scenario Summary**

In this step, we will create a summary of the scenarios including the initial one. The summary includes the input values and the estimated output of the created scenarios.

- In the
**Scenario Manager**dialog box, select the**Summary**.

- As a result, the
**Scenario Summary**dialog box will appear. - Then, select
**Scenario Summary**as**Report Type**. - Set cell
**F11**as the**Result cells**. - Finally, click on
**OK**.

- As a result, we get the summary of all the scenarios including the initial one.
- This summary implies how the profit changes with the size of the theater.
- It also helps us to think about the cost section more and how to utilize and get the best possible solution.

**Read More:** How to Get Sensitivity Report from Solver in Excel

## Examples of What-If Analysis Using Goal Seek in Excel

After showing the what-if analysis example in Excel using the scenario manager, we turn our attention to the goal seek analysis. Here, we would like to show two examples including average age and exam marks. Both of these examples use goal seek analysis. The goal seek analysis can be applied when the final result is known but you donâ€™t know how you get it by changing input values. These two examples will give you a complete overview of a what-if analysis example in Excel.

### 1. Using Goal Seek for Average Age

Our first example is based on the goal seek analysis for average age. Here, we set a final average age. Then, using the goal seek analysis, we will get the change in input values. Follow the steps carefully.

**Steps **

- First, calculate the average using the available dataset.
- Select cell
**C12**. - Then, write down the following formula using
**the AVERAGE function**.

`=AVERAGE(C5:C10)`

- Press
**Enter**to apply the formula.

- Then, go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Then, select
**Goal Seek**from the**What-If Analysis**drop-down option.

- As a result, the
**Goal Seek**dialog box will appear. - Put cell
**C12**in the**Set CellÂ**section. - Put
**30**in the**To value**section. - Set cell
**C10**in the**By changing cellÂ**section.

- After that, we get the
**Goal Seek Status**dialog box where it denotes that they get a solution. - In the dataset, you will find the change in the
**Average**and input value of a certain cell.

**Read More:** How to Perform Sensitivity Analysis for Capital Budgeting in Excel

### 2. What-If Analysis for Exam Marks

Our second example is based on the exam marks. Here, we take a dataset that calculates the final grade of a student using several students and their marks in the exam. Using the **Goal Seek** analysis, we set a final grade of a student and then change the input value according to the final grade. Before doing anything, we need to set the weight of each exam because each exam carries different values.

**Steps**

- First, we need to calculate the final grade of each student using exam marks and the weight of each exam.
- Select cell
**G5**. - Then, write down the following formula in the formula box.

`=0.25*B5+0.25*C5+0.25*D5+0.15*E5+0.1*F5`

- Press
**Enter**to apply the formula.

- After that, drag the
**Fill Handle**icon down the column.

- Then, go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Then, select
**Goal Seek**from the**What-If Analysis**drop-down option.

- As a result, the
**Goal Seek**dialog box will appear. - Put cell
**G5**in the**Set CellÂ**Section. - Put
**80**in the**To valueÂ**Section. - Set cell
**B5**in the**By changing cellÂ**Section.

- After that, we get the
**Goal Seek Status**dialog box where it denotes that they get a solution. - In the dataset, you will find the change in the
**Final Grade**as**80**and the input value of**Exam 1**becomes**84**.

## Examples of What-If Analysis Using Data Table in Excel

When you have a formula that requires one or two variables or multiple formulas require one variable, in that case, you can utilize Data Table to get all the outcomes in a single place. To understand the data table of what-if analysis example in Excel properly, we will show two examples including one or two variables. Here, we try to calculate EMI for different interest rates, and in the second example, we will try to calculate EMI for different interest rates and different loan amounts.

### 1. Calculating EMI with One Dimensional Approach

Our first example is a one-dimensional approach where data table analysis gives the final results using one single variable and others remain constant. Follow the steps carefully.

**Steps**

- First, we need to calculate the initial EMI using the given dataset.
- Select cell
**C7**. - Then, write down the following formula using
**the PMT function**.

`=PMT(C5/12,C6,-C4)`

- Press
**Enter**to apply the formula.

- Then, set two new columns and put all the interest rates.
- After that, put the calculated EMI value in the next column.

- Then, select the range of cells
**E4**to**F10**.

- Go to the
**Data**tab on the ribbon. - Then, select
**What-If Analysis**drop-down option from the**ForecastÂ**group. - After that, select
**Data Table**from**What-If Analysis**drop-down option.

- As a result, the
**Data Table**dialog box will appear. - Set cell
**C5**as the**Column input cell**.

- As a result, you will see the EMI values are calculated for different interest rates. See the screenshot.

**Read More:** Sensitivity Analysis for NPV in Excel

### 2. Calculating EMI with Two Dimensional Approach

In our what-if analysis example in excel, we will use two different variables. By using them, we will calculate the EMI. Here, we utilize different interest rates and loan amounts. To understand it properly, follow the steps.

**Steps**

- First, we need to calculate the initial EMI using the given dataset.
- Select cell
**C7**. - Then, write down the following formula.

`=PMT(C5/12,C6,-C4)`

- Press
**Enter**to apply the formula.

- Then, you need to create several columns that include different interest rates and loan amounts.

- After that, select the range of cells
**E4**to**K10**.

- Go to the
**Data**tab on the ribbon. - Then, select
**What-If Analysis**drop-down option from the**ForecastÂ**group. - After that, select
**Data Table**from**What-If Analysis**drop-down option.

- As a result, the Data Table dialog box will appear.
- Set cell
**C5**means the**Interest Rate**as**Row input cell**. - Then, Set cell
**C4**means the**Loan Amount**as**Column input cell**. - Finally, click on
**OK**.

- As a result, you will see the EMI values are calculated for different interest rates and loan amounts. See the screenshot.

## Things to Remember

- The scenario summary report canâ€™t automatically recalculate. So, if you change the dataset, there will be no change in the summary report.
- You donâ€™t require result cells to generate a scenario summary report, but you need to require them for a scenario PivotTable report.
- Check the goal-seeking parameters. The supposed output cell must contain a formula that depends on the supposed input values.
- Try to avoid circular reference in both formula and goal-seeking parameters.

**Download Practice Workbook**

Download the practice workbook below.

## Conclusion

We have shown the what-if analysis example in Excel. In this article, we have shown various examples of each type to get a complete view of the what-if analysis example in Excel. All three types are useful in different segments. In article gives you a complete overview of types of what-if analysis in Excel. If you have any questions, feel free to ask in the comment box.

**Related Articles**

- How to Delete What If Analysis in Excel
- What If Analysis Data Table Not Working
- How to Build a Sensitivity Analysis Table in Excel
- How to Do IRR Sensitivity Analysis in Excel

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