Excel 2007 For Dummies
Book image
Explore Book Buy On Amazon

Excel 2007’s Scenario Manager lets you create and save sets of different input values that produce different calculated results as named scenarios (such as Best Case, Worst Case, and Most Likely Case). The key to creating the various scenarios for a table is to identify the various cells in the data whose values can vary in each scenario. You then select these cells (known as changing cells) in the worksheet before you open the Scenario Manager dialog box.

The steps below follow a specific example for using Scenario Manager to help you understand how to use this feature. The figure shows a Sales Forecast table after selecting the three changing cells in the worksheet — G3, named Sales_Growth; G4, named COGS (Cost of Goods Sold); and G6, named Expenses — and then opening the Scenario Manager dialog box.

The example uses three scenarios based on the following sets of values for the three changing cells:

  • Most Likely Case, where the Sales_Growth percentage is 5%, COGS is 20%, and Expenses is 25%

  • Best Case, where the Sales_Growth percentage is 8%, COGS is 18%, and Expenses is 20%

  • Worst Case, where the Sales_Growth percentage is 2%, COGS is 25%, and Expenses is 35%

    Use the Scenario Manager to add and switch to different scenarios in your worksheet.
    Use the Scenario Manager to add and switch to different scenarios in your worksheet.

Follow these steps to use the Scenario Manager:

  1. On the Data tab of the Ribbon, choose What-If Analysis→Scenario Manager in the Data Tools group.

    The Scenario Manager dialog box appears.

  2. To create a scenario, click the Add button.

    The Add Scenario dialog box appears.

  3. Type the name of the scenario (Most Likely Case, in this example) in the Scenario Name text box, specify the Changing Cells (if they weren’t previously selected), and click OK.

    Create a scenario in the Edit Scenario dialog box.
    Create a scenario in the Edit Scenario dialog box.

    Excel displays the Scenario Values dialog box.

  4. Enter the values for each of the changing cells in the text boxes.

    In this example, you would enter the following values for the Most Likely Case scenario:

    • 0.05 in the Sales_Growth text box

    • 0.20 in COGS text box

    • 0.25 in the Expenses text box

  5. Click the Add button.

    Excel redisplays the Add Scenario dialog box.

  6. Repeat Steps 3 through 5 to enter the other scenarios. When you finish entering values for the final scenario, click OK instead of Add.

    The Scenario Manager dialog box makes another appearance, this time displaying the names of all scenarios in its Scenarios list box.

  7. To have Excel plug the changing values from any scenario into the table, click the scenario name in the Scenarios list box and then click Show.

  8. Click the Close button when you’re finished with the Scenario Manager.

After adding the various scenarios for a table in your worksheet, don’t forget to save the workbook. That way, you’ll have access to the various scenarios each time you open the workbook in Excel by opening the Scenario Manager, selecting the scenario name, and clicking the Show button.

About This Article

This article is from the book:

About the book author:

Greg Harvey has authored tons of computer books, the most recent being Excel Workbook For Dummies and Roxio Easy Media Creator 8 For Dummies, and the most popular being Excel 2003 For Dummies and Excel 2003 All-In-One Desk Reference For Dummies. He started out training business users on how to use IBM personal computers and their attendant computer software in the rough and tumble days of DOS, WordStar, and Lotus 1-2-3 in the mid-80s of the last century. After working for a number of independent training firms, Greg went on to teach semester-long courses in spreadsheet and database management software at Golden Gate University in San Francisco.
His love of teaching has translated into an equal love of writing. For Dummies books are, of course, his all-time favorites to write because they enable him to write to his favorite audience: the beginner. They also enable him to use humor (a key element to success in the training room) and, most delightful of all, to express an opinion or two about the subject matter at hand.
Greg received his doctorate degree in Humanities in Philosophy and Religion with a concentration in Asian Studies and Comparative Religion last May. Everyone is glad that Greg was finally able to get out of school before he retired.

This article can be found in the category: