How to Create a Scenario Summary Report in Excel (2 Easy Ways)

In Excel, we often need to create a scenario summary report to summarize the possible scenarios and take crucial business decisions based on the scenario summary report. Using Microsoft Excel, we can create a scenario summary report quite easily. In this article, we will learn 2 simple methods to create a scenario summary report in Excel.


Download Practice Workbook


What Is a Scenario Summary Report?

A scenario summary report is a kind of report, where we can compare two or more scenarios and represent the summary of both scenarios in a simple, concise, and informative approach. To create a scenario summary report we need to use at least 2 scenarios. In Excel, we can create a scenario summary report in 2 ways. They are

  • Using the Scenario Summary option,
  • Utilizing the Scenario PivotTable report option.

2 Ways to Create a Scenario Summary Report in Excel

In this section of the article, we will discuss 2 simple methods to create a scenario summary report in Excel. In the following dataset, we have Profit Analysis data for Product A and Product B. Our aim is to create a scenario summary report using these data.

how to create a scenario summary report in excel

Not to mention that we have used Microsoft Excel 365 version for this article, you can use any other version according to your convenience.


1. Creating a Default Scenario Summary Report in Excel

First, we will create a default scenario summary report in Excel. It is also known as a static scenario summary report. Let’s follow the steps mentioned below to do this.

Steps:

Creating a Default Scenario Summary Report in Excel

Consequently, the Scenario Manager dialogue box will open on your screen as shown in the following picture.

  • Now, click on the Add option from the Scenario Manager dialogue box.

As a result, the Add Scenario dialogue box will be visible on your worksheet.

  • After that, from the Add Scenario dialogue box, type the scenario name you want in the Scenario Name box. In this case, we typed in Best Case.
  • Then, click on the marked region of the following image.

adding the scenario name to how to create a scenario summary report in excel

  • Following that, choose the range of cells where the inputs will change. Here, we have selected the range $C$5:$D$9.
  • Now, click on the marked area of the image below.

  • Afterward, type in the values for the Best Case scenario in the marked boxes shown in the following picture.

entering values for Best Case scenario to how to create a scenario summary report in excel

  • After typing in the values, click on Add in the Scenario Values dialogue box.

  • Now, type in the name of the second scenario. In this case, we used the name Worst Case.
  • Following that, click on OK.

editing the Worst Case scenario to how to create a scenario summary report in excel

  • Then, type in the values for the Worst Case scenario as shown in the following image.

  • After inserting the values for the Worst Case scenario, click on OK.

  • As a result, you will be redirected to the Scenario Manager dialogue box and click on Summary from the dialogue box.

Consequently, the Scenario Summary dialogue box will open on your worksheet.

  • Now, from the Scenario Summary dialogue box, choose the Report Type as Scenario Summary.
  • Following that, press and hold the CTRL key and select cells C10 and D10.
  • Finally, click on OK.

There you go! You have successfully created a scenario summary report in Excel, which should look like the following image.

final output of 1st method to how to create a scenario summary report in excel

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


2. Constructing a Scenario PivotTable Summary Report in Excel

In this section of the article, we will learn how we can create a scenario summary report in Excel in the form of a PivotTable. This is also known as the dynamic scenario summary report. Let’s follow the procedures discussed below to do this.

Steps:

  • Firstly, follow the steps mentioned earlier in the 1st method to obtain the following output.

Constructing a Scenario PivotTable Summary Report in Excel

  • Following that, choose the Scenario PivotTable report option from the Scenario Summary dialogue box.
  • Then, click on the marked region of the image given below.

  • Now, choose the range of cells $C$10:$D$10 as the Result cells.
  • After that, click on the marked portion of the following picture.

choosing result cells to how to create a scenario summary report in excel

  • Subsequently, click on OK.

Consequently, you will have your scenario summary report in a PivotTable format.

Read More: How to Do Scenario Analysis in Excel (with Scenario Summary Report)


Practice Section

In the Excel Workbook, we have provided a Practice Section on the right side of the worksheet. Please practice it by yourself.

practice section to how to create a scenario summary report in excel


Conclusion

That’s all about today’s session. I strongly believe that this article was able to guide you to create a scenario summary report in Excel. Please feel free to leave a comment if you have any queries or recommendations for improving the article’s quality. To learn more about Excel, you can visit our website, ExcelDemy. Happy learning!


Related Articles

Zahid

Zahid

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo