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

- We need to calculate the total cost of the initial dataset.
- Select cell
**C10**. - Write down the following formula using
**the SUM function**.

`=SUM(C5:C9)`

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

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

- Open the
**Scenario Manager**dialog box. - Select
**Add**to include new scenarios.

- The
**Add Scenario**dialog box will appear. - Set
**House 2**as the**Scenario name**. - Set the range of cells
**C5**to**C9**as the**Changing cells**. - Click on
**OK**.

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

- Click on
**OK**. - It will take us back to the
**Scenario Manager**dialog box. - Click on
**Add**to include another scenario.

- The
**Add Scenario**dialog box will appear. - Set
**House 3**as the**Scenario name**. - Set the range of cells
**C5**to**C9**as the**Changing cells**. - Click on
**OK**.

- It will take us to the
**Scenario Values**dialog box. - Put the input values of rent, electricity bill, gas bill, garage bill, and others.
- Click on
**OK**.

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

- The
**Scenario Summary**dialog box will appear. - Select
**Scenario Summary**as**Report Type**. - Set cell
**C10**as the**Result cells**. - Click
**OK**.

- Get the following results where you get the outcome without creating a new worksheet.

### Method 2 – Performing What-If Analysis for Movie Theatre Profit

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

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

We need to calculate the revenue amounts. The cost of the movie theatre changes with its size. Utilize the scenario manager in that case. To calculate the movie theater profit, follow the following steps.

- Select cell
**F6**to calculate the**Ticket Sales**. - Write down the following formula.

`=C5*F5`

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

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

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

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

`=SUM(F6:F8)`

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

- Calculate the profit earned by the movie theatre.
- Select cell
**F11**. - Write down the following formula.

`=F9-C12`

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

**Step 2: Create Scenarios **

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.

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

- Open the
**Scenario Manager**dialog box. - Select
**Add**to include new scenarios.

- The
**Edit Scenario**dialog box will appear. - Set
**Medium Venue**as**Scenario name**. - Select the range of cells
**C5**to**C11**and cell**F5**. That means all the cost changes along with the size of the theater. Ticket prices will also increase. - Click
**OK**.

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

- Scroll down and set another cell value properly. See the screenshot.

- Click on
**OK**. - It will take us to the
**Scenario Manager**dialog box again. - Select Add to include another scenario.

- 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. Ticket prices will also increase. - Click
**OK**.

- It will open up the
**Scenario Values**dialog box. - Set values for a large venue. Change the seats, ticketing, lighting, security, insurance, rent, and ticket price.

- Scroll down and set another cell value properly. See the screenshot.
- Click
**OK**.

**Step 3: Generate Scenario Summary**

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

- The
**Scenario Summary**dialog box will appear. - Select
**Scenario Summary**as**Report Type**. - Set cell
**F11**as the**Result cells**. - Click on
**OK**.

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

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

### Method 1 – Using Goal Seek for Average Age

**Steps **

- Calculate the average using the available dataset.
- Select cell
**C12**. - Write down the following formula using
**the AVERAGE function**.

`=AVERAGE(C5:C10)`

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

- Go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Select
**Goal Seek**from the**What-If Analysis**drop-down option.

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

- Get the
**Goal Seek Status**dialog box where it denotes that they get a solution. - Find the change in the
**Average**and input value of a certain cell.

### Method 2 – What-If Analysis for Exam Marks

**Steps**

- Calculate the final grade of each student using exam marks and the weight of each exam.
- Select cell
**G5**. - 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.

- Drag the
**Fill Handle**icon down the column.

- Go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option. - Select
**Goal Seek**from the**What-If Analysis**drop-down option.

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

- Get the
**Goal Seek Status**dialog box where it denotes that they get a solution. - 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

### Method 1 – Calculating EMI with One Dimensional Approach

**Steps**

- Calculate the initial EMI using the given dataset.
- Select cell
**C7**. - Write down the following formula using
**the PMT function**.

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

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

- Set two new columns and put all the interest rates.
- Put the calculated EMI value in the next column.

- Select the range of cells
**E4**to**F10**.

- Go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option from the**ForecastÂ**group. - Select
**Data Table**from**What-If Analysis**drop-down option.

- The
**Data Table**dialog box will appear. - Set cell
**C5**as the**Column input cell**.

- You will see the EMI values are calculated for different interest rates. See the screenshot.

### Method 2 – Calculating EMI with Two Dimensional Approach

**Steps**

- Calculate the initial EMI using the given dataset.
- Select cell
**C7**. - Write the following formula.

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

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

- Create several columns that include different interest rates and loan amounts.

- Select the range of cells
**E4**to**K10**.

- Go to the
**Data**tab on the ribbon. - Select
**What-If Analysis**drop-down option from the**ForecastÂ**group. - Select
**Data Table**from**What-If Analysis**drop-down option.

- The Data Table dialog box will appear.
- Set cell
**C5**means the**Interest Rate**as**Row input cell**. - Set cell
**C4**means the**Loan Amount**as**Column input cell**. - Click
**OK**.

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

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