Excel What-If Analysis: How to Use the Scenario Manager

The Scenario Manager is a great, but often overlooked What-If Analysis feature of Excel that will let you swap multiple sets of data in a worksheet and even compare them side-by-side. This technique can help you decide between multiple courses of action or what the implications are among several possibilities.

For example, let’s say we are concert promoters and want to produce a show. We need to decide what venue to use because that will determine costs, revenues, profit or loss, and what talent to contract for. 

In this exercise, we’ll use the Scenario Manager to compare four sets of numbers: small, medium, large and very large locations and their associated costs and revenues, assuming each show sells out.

Screencast

Please accept marketing cookies to load this content.

Excel What-If Analysis: How to Use the Scenario Manager

Watch the complete tutorial screencast above, or work through the step-by-step written version below, but first download the practice worksheet so you can work through the excercises.

Download the Practice Worksheet

You can re-create the sheet below or download practice-files.zip, which contains the scenarios.xlsx worksheet below and a worksheet of the completed Excel Scenario Manager exercise.

This sheet currently displays the smallest of the venues, which has 300 seats. The numbers in orange boxes are calculated, so we won’t adjust them in the scenarios. Here are the formulas the calculated numbers use:

• B13: Total costs adds the costs from the cells above.
• B19: Ticket sales multiplies the number of seats x ticket price (B4*B17).
• B20: Merchandising (t-shirts, souvenirs) assumes patrons purchase an average of $5/seat (5*B4).
• B21: Food & beverage assumes patrons purchase an average of $15/seat (15*B4).
• B22: Total revenue adds the revenues from the cells above.
• B24: Profit or loss subtracts total cost from total revenue (B22-B13).

Tip: press Ctrl + ` (accent mark) to display all the formulas on the sheet at once. Press Ctrl + ` again to return the sheet to normal. This shortcut is identical in both Windows and Mac.

1. Make Your First Scenario

Step 1: Set up the First Scenario

Now we'll dig into What-If Analysis in Excel. We'll open up the Scenario Manager and begin:

1. First, select all the cells that will change. To do that, click B4, hold the Ctrl key (Command key on the Mac) while dragging from B6 down to B12, then Ctrl + click (Command + click on the Mac) B17.
   
2. On the ribbon, select the Data tab > What-If Analysis > Scenario Manager.

This displays the Scenario Manager dialog box. Since we haven’t created any scenarios yet, it says there are none defined.

Each scenario will be a set of the cells you just selected, containing unique values. The first set will be the current values.

Step 2: Now Create the First Scenerio

1. In the dialog box, click Add.
2. Enter the name Original values.
3. The changing cells are what you selected. If you selected different cells by mistake, you can enter the correct ones here (see image below).
4. Enter a comment if you want. This is optional.
5. The checkboxes for Protection are only if you want to protect the sheet from changes. We won’t do that in this exercise, so ignore these choices.

Click OK. The Scenario Values dialog box shows you a list of all the cells in the scenario and what their current values are. Note that you can’t resize this box, so use its scrollbar to see all of them.

For now, there’s nothing to change, but note the Add button. A quick way of creating several scenarios one after another is to click this Add button after entering values. That will immediately display the Add Scenario screen.

For now, click OK. That brings back the main Scenario Manager dialog, showing the first one listed.

The Manager has buttons for adding a new scenario, deleting one, editing one, merging in a scenario from another open workbook, and creating a summary. The summary is the coolest part, and we’ll do that below.

2. Create Additional Scenarios

Step 1: Add More Scenarios

Click Add. This is the same thing as clicking the Add button in the previous step.

Create 3 more scenarios using the data from the table below. The general concept is that larger venues will have higher costs – not always in proportion – along with the ability to charge higher ticket prices resulting in greater revenues. For the sake of simplicity, assume that if a concert has more than one act, they’re combined in the Artist category.

The fastest way of entering the numbers is not to use the mouse. Just type a number, press the Tab key, type another number, press the Tab key, and so on.

After entering the last scenario, click OK to return to the main Scenario Manager screen. It should look like this:

Step 2: Switch Between 

The sheet still shows the original values, so here’s the first cool feature: Double-click one of the scenario names in the list. The sheet updates with those values.

Step 3: View All the Scenarios at Once

1. Click the Summary button.
2. That confirms you want to create a summary, not a PivotTable, so leave the default radio button selected.
3. It also confirms the main result cell is the Profit or Loss in B24.

 Click OK. That creates a new sheet in the workbook, called Scenario Summary.

Step 4: Engaging With the Scenario Summary

This shows the values that the sheet currently displays (you could have changed these manually) as well as the sets of numbers from all four scenarios.

Notice the small plus and minus symbols in the margins. These are part of Excel’s Group and Outline feature, which you can use separately from Scenario Manager. The Outline button is also on the ribbon’s Data tab, all the way on the end.

Click any of the minus signs to collapse the sheet so it shows only summary data, or click the plus signs to expand and show detail.

Step 5: Two Things to Be Aware Of

1. None of the values are dynamic. If you change the underlying data on the original sheet, the values on this sheet will not change. You will need to create a new summary.
2. Down column C, you see Excel lists the cell references, not their labels (Artist, Venue rental, etc.). If you want to see the labels, stretch out column C and type them manually.

Conclusion

The next time you want to compare several sets of data, maybe to decide among multiple courses of action, give the Excel What-If Analysis - Scenario Manager a try. It might show exactly what you need to make a decision.