How to Do What-If Analysis Using Scenario Manager in Excel

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, the what if analysis scenario manager is a useful command. Using this command, you can analyze your dataset scenario with several cases. The final summary will show you all the cases’ final output. This scenario manager is a part of the data analysis. It means comparing values and results side-by-side. This article will show you the details of the what-if analysis scenario manager in Excel with two practical examples. I hope you find this article informative and gain more knowledge about data analysis.

What Is Scenario Manager 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. This process uses to get the final results for different conditions and it also provides a complete view of each condition. The scenario manager works like the data table in Excel.

How to Use Scenario Manager in Excel

We can do a what-if analysis using scenario manager in Excel. To use this scenario manager, we need to create a dataset that gives a final result utilizing several input values. Using the scenario manager, we set several input conditions and get the final result for each condition. Finally, create a summary to compare it with the initial input condition.

Scenario

Here, we take a scenario where we calculate the EMI as the final value. To calculate the EMI, you need to have the loan amount, interest rate, and no of months of payment. By using the scenario manager, we will calculate the preferable EMI. Then, compare it with the initial one.

Then, go to the Data tab on the ribbon. From the What-If Analysis drop-down option, select the Scenario Manager option.

After that, add several scenarios in the scenario manager. Here, we need to change the loan amount, NPER, and interest rate. In the first scenario, we set the following inputs.

In scenario 2, we change the previous setup and set the following inputs.

After that, apply the Summary option in the Scenario Manager which we will give the final output for both conditions and we can easily compare this with the initial output. The output is negative because we have to give this amount as EMI.

Here, you can get the side-by-side summary of three conditions including the initial condition. Now, you can decide which scheme is better for you in the long run.

Benefits of Using Scenario Manager in Excel

There are several benefits to using the scenario manager. Most of them are important in your day to purposes. The scenario manager helps to take a better decision in the long run and reduces the risk of future assessment.

• The scenario manager allows you to alter the input values for 32 cells at a time.
• Allows you to alter input values without changing the original dataset.
• Provides a comparative study of all scenarios.
• Helps to choose the best possible scheme in the long run.
• Provides a platform to develop strategies and plans for the future.
• Determines which scheme to take for both the long run and short run.
• Helps us to understand the best and worst possible cases.
• More importantly, assists us to moderate risk and making better decisions.

2 Suitable Examples to Do What-If Analysis Using Scenario Manager in Excel

To explain what-if analysis scenario manager in Excel, we would like to show two practical examples through which you can have complete knowledge about the topic. These two examples will discuss how to do a what-if analysis scenario manager and what we can get from this analysis in detail.

1. Using Scenario Manager for Exam Marks

Our first 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. We would like to utilize the scenario manager to look at how the final grade changes with the change of exam marks. Finally, we can compare these scenarios with the initial one. Before doing anything, we need to set the weight of each exam.

Here, we want to create three scenarios for Exam 2.

• What if we got 70 in Exam 2?
• What if we got an 80 in Exam 2?
• What if we got a 95 in Exam 2?

Follow the steps to use the what-if analysis scenario manager in Excel.

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.

• First, select cell C5.
• 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 Exam Marks 70 as the Scenario name.
• Then, set C5 as the Changing cells.
• Finally, click on OK.

• After that, the Scenario Values dialog will appear.
• Here, enter 70 in the Enter values for each of the changing cells section.
• Then, click on OK.

• It will take us to the Scenario Manager dialog box again.

• Set Exam Marks 80 as the Scenario name.
• Then, set C5 as the Changing cells.
• Finally, click on OK.

• After that, the Scenario Values dialog will appear.
• Here, enter 80 in the Enter values for each of the changing cells section.
• Then, click on OK.

• It will take us to the Scenario Manager dialog box again.

• Set Exam Marks 95 as the Scenario name.
• Then, set C5 as the Changing cells.
• Finally, click on OK.

• After that, the Scenario Values dialog will appear.
• Here, enter 95 in the Enter values for each of the changing cells section.
• Then, click on OK.

• It will take us to the Scenario Manager dialog box again.
• Then, select Summary to get the overall summary of all scenarios.

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

• As a result, we get the scenario summary for different exam marks.
• If you look at it closely, you will see there is a change in final grade along with scenarios.
• This summary tells us how a student can change the final grade by altering certain exam marks.

2. Using Scenario Manager 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 three scenarios under consideration.

• Medium Venue
• Large Venue
• Very 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 theatre. 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 theatre. 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.
• As a result, it will take us to the Scenario Manager dialog box again.
• Then, select Add to include a very large venue scenario.

• After that, set Very Large Venue as the 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 very 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.

• As a result, we get the three scenarios in the Scenario Manager dialog box.

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

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

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.

Conclusion

To explain the what-if analysis scenario manager, we have shown its use of it and also shown two practice examples of how to use the what-if analysis scenario manager in Excel effectively. In this article, we also denote the profound benefits of using scenario managers. If you have any questions, feel free to ask in the comment box. Don’t forget to visit our Exceldemy page.

Related Articles

Durjoy Paul

Hi there! I'm Durjoy. I have completed my graduation from the Bangladesh University of Engineering and Technology. I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF