In Microsoft Excel, analyzing scenarios is one of the crucial tasks. We consider it as a part of data analysis. Scenario analysis means comparing values and results side-by-side. You will create a dataset first. After that, you have to create a scenario for every possible value. In this tutorial, you will learn to do scenario analysis in Excel.
This tutorial will be on point with suitable examples and proper illustrations. So, read this article to enrich your Excel knowledge.
What is Scenario Manager in Excel?
Scenario manager in Excel is an element of three what-if-analysis tools in Excel, which are built-in in, excel. In uncomplicated words, you can notice the effect of switching input values without altering the existing data. It basically works like the data table in Excel. You must input data that should change to acquire a particular outcome.
Scenario Manager in Excel lets you change or replace input values for numerous cells. After that, you can see the output of different inputs or different scenarios at the same time.
How to Perform Scenario Analysis in Excel
We can perform a scenario analysis by the Scenario Manager in Excel. We discussed that earlier. Now, in this section, you will learn to create your first scenario in Excel. So, stay tuned.
Scenario:
You want to rent a house. There are some options for houses. We can consider these options as scenarios. Now, you have to decide which house to decide to save more money.
To demonstrate this, we are going to use the following dataset:
This is for House 1. Now, we are going to create a scenario for House 2 and House 3.
📌 Steps
- First, go to the Data From the Forecast group, select What-If Analysis > Scenario Manager.
- Then, the Scenario Manager dialog box will appear. After that, click on Add.
- Then, in the Edit Scenario dialog box, give a Scenario name. We are giving it House 2. After that, select Changing cells.
- Next, select the range of cells C5:C9. We will change these inputs.
- After that click on OK.
- Now, in the Scenario values dialog box, we are giving the expenses of House 2. Then, click on Ok.
- Now, we have added a scenario for House 2. Do the same for House 3.
- Here, we are giving these values for House 3
- We added both scenarios. Select House 2 and click on Show to see the changes.
- Now, you will see these changes for House 2.
- If you choose House 3, it will give you this total cost:
As you can see, we have successfully performed scenario analysis in Excel
Create Scenario Summary:
You can also show these effects side-by-side using the Scenario Summary.
📌 Steps
- First, open the Scenario Manager.
- Then, click on Summary.
- Now, select your Result cells. Here, our result cell is C10 because we were showing our Total values on that cell. Next, click on OK.
Here, you can see the side-by-side scenario summary in a different worksheet. Now, you can easily decide which House you should choose.
Read More: How to Use Scenario Manager in Excel
Scenario Analysis in Excel: 2 Practical Examples
In the following sections, we will provide you with two practical examples of scenario analysis in Excel. We recommend you read and try all of these. We hope it will increase your interest in scenario analysis. Hopefully, it will improve your Excel knowledge.
1. Scenario Analysis of Compound Interests in Excel
In this section, we will show you an example of the Compound interests of banks. We will create two scenarios of this example to demonstrate.
Compound interest means earning or paying interest on interest. Basically, it is one of those popular financial terms. When we think about compound interest, we consider it as gaining money. It increases our savings after a limited period.
The formula of Compound Interest:
This example will contain the same dataset. But we will calculate differently compound interests.
Suppose, you want to invest $10000 for ten years somewhere. You have got three options:
Bank "X" is providing 5% interest compounded yearly.Bank "Y" is offering 5% interest compounded monthly.Bank "Z" is giving 5% interest compounded daily.
Now, you are in puzzlement where to apply. So, let’s use our scenario manager to find which one will provide you with more profit.
This is the dataset for Bank “X”:
We are using this formula to calculate the Estimated Balance:
=C4 * (1 + C5 /C6) ^ (C7 * C6)Let’s create a scenario analysis.
📌 Steps
- First, go to the Data tab. Then, from the Forecast group, select What-If Analysis > Scenario manager.
- Then, the Scenario Manager dialog box will appear. After that, click on Add.
- Then, in the Edit Scenario dialog box, give a Scenario name. We are giving it Bank “Y”. After that, select cell C6 in Changing cells. Because only the number of compounding periods per year will vary here. Everything will be the same. Then, click on OK.
- Then, in the Scenario Values dialog box, enter 12. Because Bank “Y” gives 5% compound interest monthly. So, there will be 12 compounding periods per year. Next, click on OK.
- Now, we have created a scenario for Bank “Y”.
- To add a scenario for Bank “Z”, click on Add.
- Then, give this scenario the name Bank “Z”. Then, select cell C6 as the changing cell.
- Now, give the scenario values 365. Because Bank “Z” is offering 5% interest compounding daily. So, no. of compounding periods will be 365 days.
- Then, click on OK.
- Now, to create a scenario summary report, click on Summary. Then select cell C9 as the result cell.
- After that, click on OK.
As you can see, we have successfully created a scenario analysis in Excel. You can see the estimated balance for each compound interest of the banks.
2. Preparing Budget for an Office Tour Using Scenario Manager
In this section, we are going to show you almost a similar example as we showed earlier.
Suppose, your office has decided to go on an office tour. Now, your boss has given you the responsibility to make the budget. You have three options for choosing a place.
For this, you have made this budget:
Now, the budget you have made is for place 1. You have to make a budget for Place 2 and Place 3. After that, you have to decide which option will be better.
📌 Steps
- First, go to Data Then, from the Forecast group, select What-If Analysis > Scenario manager.
- Then, the Scenario Manager dialog box will appear. After that, click on Add.
- Then, in the Edit Scenario dialog box, give a Scenario name. We are giving it Place 2. After that, select the range of cells C5:C9 in Changing cells. Then, click on OK.
- Now, give the expenses for Place 2
- After that, click on OK.
- Now, we have added the Place 2 scenario. After that, click on Add to add scenario for Place 3.
- Create a Scenario for Place 3 in the same process. Now, give your expenses for Place 3.
- Now, click on OK.
- After that, click on Summary to analyze the scenarios side-by-side. Then, select cell C10 for showing the result.
- Finally, click on OK.
As you can see, we have successfully performed the scenario analysis of an office tour in Excel.
Read More: How to Create a Scenario Summary Report in Excel
💬 Things to Remember
✎ By default, the summary report uses cell references to recognize the Changing cells and the Result cells. If you make named ranges for the cells before you run the summary report, the report will have the names instead of cell references.
✎ Scenario reports do not automatically recalculate. If you modify the values of a scenario, those modifications will not show up in a current summary report but will show up if you build a new 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.
Download Practice Workbook
Download this practice workbook.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to create a scenario analysis in Excel. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Keep learning new methods and keep growing!
Thank you for sharing Shanto! I found a problem that also appears in your article. In the Scenario Summary for section 1 example, the “Current Values” column shows data for House 3 as the result of the last operation. How to retrieve the original data for House 1?
Hello HOWARD,
Thanks for asking this important question. Basically scenario summary will show the latest dataset in the current values column. As we changed the scenario by clicking OK.
Now, this is not a wonderful solution. But it may help you.
1. Copy the original dataset to a new sheet.
2. Then go to Scenario Manager
3. Now click Summary.
You will see the original data in Current Values.
Thank You.