Scenario Analysis in Excel: A Guide with 2 Sample Cases + Template

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.


Download Practice Workbook

Download this practice workbook.


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

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:

Perform Scenario Analysis in Excel

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.

Perform Scenario Analysis in Excel

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

Perform Scenario Analysis in Excel

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

Perform Scenario Analysis in Excel

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

Perform Scenario Analysis in Excel

  • If you choose House 3, it will give you this total cost:

Perform Scenario Analysis in Excel

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.

Perform Scenario Analysis in Excel

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

Perform Scenario Analysis in Excel

Here, you can see the side-by-side scenario summary in a different worksheet. Now, you can easily decide which House you should choose.


Similar Readings:


2 Practical Examples of Scenario Analysis in Excel

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:

Initial Balance* (1 + Annual interest rate / Compounding periods per year) ^ (Years * Compounding periods per year)

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”:

Scenario Analysis of Compound Interests in Excel

We are using this formula to calculate 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.

Scenario Analysis of Compound Interests in Excel

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

Scenario Analysis of Compound Interests in Excel

  • To add a scenario for Bank β€œZ”, click on Add.

Scenario Analysis of Compound Interests in Excel

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

Scenario Analysis of Compound Interests in Excel

  • After that, click on OK.

Scenario Analysis of Compound Interests in Excel

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:

Office Tour Using Scenario Manager

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

Office Tour Using Scenario Manager

  • After that, click on OK.

Office Tour Using Scenario Manager

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

Office Tour Using Scenario Manager

  • Now, click on OK.

  • After that, click on Summary to analyze the scenarios side-by-side. Then, select cell C10 for showing the result.

Office Tour Using Scenario Manager

  • Finally, click on OK.

Office Tour Using Scenario Manager

As you can see, we have successfully performed the scenario analysis of an office tour 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.


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.

Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.

Keep learning new methods and keep growing!


Further Readings

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. 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.

Leave a reply

ExcelDemy
Logo