Advertisement
  1. Business
  2. Microsoft Excel
Business

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

by
Difficulty:IntermediateLength:MediumLanguages:

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

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.

Excel Scenerio Manager Practice Worksheet
Excel What-If Analysis Scenerio Manager - Practice Worksheet

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.

Display formulas using Excel Keyboard Shortcut
Display formulas using Excel keyboard shortcut

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.
Excel What-If Analysis Scenario Manager
Excel 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.

Excel Scenario Manager dialog box
Scenario Manager dialog box

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.
Excel Scenario Protection options
Scenario Protections options

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.

Scenario Values dialog box
Scenario Values dialog box 

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.

Scenario Manager dialog
Back to the main Scenario Manager dialog box

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.

Description

Value

Scenario name

Medium venue

B4 (# of seats)

800

B6 (artist)

7500

B7 (venue rental)

1000

B8 (amplification)

600

B9 (lighting)

350

B10 (ticketing)

250

B11 (security)

300

B12 (insurance)

250

B17 (ticket price)

35

 

 

Scenario name

Large venue

B4 (# of seats)

1500

B6 (artist)

12000

B7 (venue rental)

3500

B8 (amplification)

1000

B9 (lighting)

700

B10 (ticketing)

350

B11 (security)

1000

B12 (insurance)

500

B17 (ticket price)

50

 

 

Scenario name

Very large venue

B4 (# of seats)

5000

B6 (artist)

25000

B7 (venue rental)

10000

B8 (amplification)

2500

B9 (lighting)

2000

B10 (ticketing)

500

B11 (security)

2500

B12 (insurance)

2500

B17 (ticket price)

50

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

Scenario Manager screen
Scenario Manager screen

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.

Updated Values
Updated 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.
Profit or Loss Cell Result
Profit or Loss cell Result

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

Scenario Summary worksheet
Scenario Summary worksheet

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.

Outline feature
Outline features

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.
Scenario Summary Issues
Scenario Summary issues

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.

Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.