What-If Analysis in Excel with Examples: Complete Guide

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.

What-if analysis example in Excel using scenario manager

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

What-if analysis example in Excel Applying scenario manager

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

What-if analysis example in Excel Utilizing scenario manager

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

What-if analysis example in Excel Exercising scenario manager


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

Calculate Movie Theatre Profit to Show What-If Analysis Example

  • 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

Estimate Movie Theatre Profit to Express What-If Analysis Example

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

Create Scenarios to Establish What-If Analysis Example

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

Establish Scenarios to Show What-If Analysis Example

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

Generate Scenario Summary to Show What-If Analysis Example

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

Produce Scenario Summary to Express What-if Analysis Example


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)

Using Goal Seek for Average Age to Show What-If Analysis Example

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

Utilizing Goal Seek for Average Age to Show What-If Analysis Example

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

Applying Goal Seek for Average Age to Create What-If Analysis Example


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

Using Goal Seek for Exam Marks to Create What-If Analysis Example

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

Applying Goal Seek for Exam Marks to Express What-If Analysis Example

  • 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)

Calculation of EMI with One Dimensional Approach to do What-if Analysis Example

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

Estimation of EMI with One Dimensional Approach to Express What-if Analysis Example

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

Calculation of EMI with Two Dimensional Approach to Do What-If Analysis Example

  • See the EMI values are calculated for different interest rates and loan amounts. See the screenshot.

Estimation of EMI with Two Dimensional Approach to Show What-If Analysis Example


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


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

Get FREE Advanced Excel Exercises with Solutions!
Durjoy Paul
Durjoy Paul

Durjoy Kumar, with a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, is a dedicated contributor to the ExcelDemy project. His substantial contributions include crafting numerous articles and demonstrating expertise in Excel and VBA. Durjoy adeptly automates Excel challenges using VBA macros, offering valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel, SPSS, C, C++, C#, JavaScript, Python Web Scraping, Data Entry... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo