What-If Analysis in Excel with Example

Get FREE Advanced Excel Exercises with Solutions!

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.

What-if analysis example in Excel using scenario manager

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

What-if analysis example in Excel Applying scenario manager

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

What-if analysis example in Excel Utilizing scenario manager

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

What-if analysis example in Excel Exercising scenario manager

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

Calculate Movie Theatre Profit to Show What-If Analysis Example

  • 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

Estimate Movie Theatre Profit to Express What-If Analysis Example

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

Create Scenarios to Establish What-If Analysis Example

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

Establish Scenarios to Show What-If Analysis Example

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

Generate Scenario Summary to Show What-If Analysis Example

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

Produce Scenario Summary to Express What-if Analysis Example

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  • As a result, you will 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.


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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