Using the Scenario Manager to Evaluate What-If Questions

The Goal Seek and Solver commands are extremely useful, though if you run several forecasts you can quickly forget what your original values were. More important, you have no real way to compare the results of the Goal Seek and Solver commands. Each time you change the data, the previous solution is lost. To address this limitation, the Scenario Manager helps you to keep track of multiple what-if models. Using the Scenarios command on the Tools menu, you can create new forecasting scenarios, view existing scenarios, run scenario management commands, and display consolidated scenario reports. We'll show you each technique in this section.

Creating a Scenario

A scenario is a named what-if model that includes variable cells linked together by one or more formulas. Before you create a scenario, you must design your worksheet so that it contains at least one formula that's dependent on cells that can be fed different values. For example, you might want to compare a best-case and a worst-case scenario for sales in a coffee shop based on the number of cups of coffee sold in a week. Figure 23-6 shows a worksheet that contains three variable cells and several formulas that can serve as the basis for several scenarios. (This coffee sales worksheet is the same one we used in the Solver example, without the constraints table.) In the following example, we'll use this worksheet to show how to create a Best Case and a Worst Case sales scenario.

ON THE WEB
The Scenario.xls example is in the Running Office 2000 Reader's Corner page.

To create a scenario, follow these steps:

  1. From the Tools menu, choose Scenarios. The Scenario Manager dialog box appears:
  2. click to view at full size.

    Figure 23-6. Before you create a scenario, you'll need to build a worksheet that has one or more formulas dependent on variable cells.

  3. Click the Add button to create your first scenario. You'll see the Add Scenario dialog box.
  4. Type Best Case (or another suitable name) in the Scenario Name text box, and press the Tab key.
  5. In the Changing Cells text box, specify the variable cells that you want to modify in your scenario. You can type cell names, highlight a cell range, or hold down the Ctrl key and click individual cells to add them to the text box. (If you hold down the Ctrl key, Excel automatically places commas between the cells that you click.) To follow our example, hold down the Ctrl key and click cells D5, D9, and D13. Your screen should look like this:
  6. TIP
    You might want to use cell names when you define your variable cells. That way, you'll have an easier time identifying your variables when you create your scenarios and when you type in arguments later.

  • Click OK to add your scenario to the Scenario Manager. You'll see the Scenario Values dialog box, asking you for your model's variables. The default values are the numbers that were already in the cells.
  • Type 150, press Tab, type 225, press Tab, and type 125. These are the values that will produce the revenue in your best-case scenario based on the constraints described in the section "Setting Up the Problem" Your screen will look like the one shown below.
  • Click Add to create a second scenario. Type Worst Case, and click OK to display the Scenario Values dialog box.
  • Type 50, 40, and 30 in the variable cells, and then click OK. (These values represent our guess at the worst case.) The Scenario Manager dialog box appears and lists the Best Case and Worst Case scenarios. Now you're ready to view the results of your forecasting models.
  • Click Close to close the Scenario Manager dialog box.
  • TIP
    You can save Solver problems as scenarios for future trials by clicking the Save Scenario button in the Solver Results dialog box when the Solver computes a new forecast. The Solver will prompt you for a name, which you can use later to view the scenario in the Scenario Manager.

    Viewing a Scenario

    Excel keeps track of each of your worksheet scenarios. You can view them by choosing the Scenarios command on the Tools menu whenever your worksheet is open. Before you view a scenario, however, it's a good idea to save your workbook so that you can restore the original values in your worksheet if you want to.

    To view a scenario, follow these steps:

    1. Choose Scenarios from the Tools menu. You'll see a dialog box similar to the one shown below.
    2. In the Scenarios list box, select the scenario that you want to view.
    3. Click the Show button. Excel substitutes the values in the scenario for the variables in your worksheet and displays the results in your worksheet, as shown in Figure 23-7. (You might need to move the Scenario Manager dialog box to view the results.)
    4. Select additional scenarios, and click the Show button to compare and contrast the what-if models in your worksheet. When you've finished, click the Close button. The last active scenario remains in your worksheet.

    click to view at full size.

    Figure 23-7. The Show button lets you compare the results of different what-if scenarios in your worksheet.

    Creating Scenario Reports

    Although you can easily compare different scenarios by switching between them using the Show button in the Scenario Manager dialog box, you might occasionally want to view a report that contains consolidated information about the scenarios in your worksheet. You can accomplish this quickly by clicking the Summary button in the Scenario Manager dialog box. Excel will automatically format the summary report and copy it to a new worksheet in your workbook.

    To create a scenario report, follow these steps:

    1. From the Tools menu, choose Scenarios. The Scenario Manager dialog box opens.
    2. Click the Summary button.
    3. The Scenario Summary dialog box opens, prompting you for a result cell to total in the report and also for a report type. A scenario summary report is a formatted table displayed in its own worksheet. A pivot table is a special summary table whose rows and columns can be rearranged, or pivoted:

      SEE ALSO
      For more information about viewing pivot table reports, see "Rearranging Fields in a Pivot Table"

  • Select the result cell that you want to total (cell G4 in this example), click the report option button that you want to use (accept the Scenario Summary default if you're not sure), and then click OK.
  • After a few moments, a new Scenario Summary tab will appear in your workbook, as shown in Figure 23-8. The outlining buttons in the left and top margins of your report will help you to shrink or expand the rows and columns in your scenario summary if you find it necessary to hide or expand values.

    TIP
    Each time you click the Summary button in the Scenario Manager dialog box, Excel creates a new summary worksheet in your workbook. To delete unwanted summary reports, click the unwanted scenario's summary tab in the workbook, and then choose Delete Sheet from the Edit menu.

    click to view at full size.

    Figure 23-8. The Summary button creates a scenario summary report in a new worksheet in your workbook.

    Managing Your Scenarios

    Once you have defined a scenario using the Add button, luckily you're not stuck with that scenario forever. You can edit and delete scenarios by clicking the Edit and Delete buttons in the Scenario Manager dialog box. The Edit button lets you change the name of the scenario, remove existing variable cells, add new variable cells, or even choose a completely new group of variables. To remove a particular scenario permanently, simply highlight it in the Scenario Manager dialog box, and click the Delete button. Finally, you can copy scenarios from other open workbooks into your current worksheet by clicking the Merge button in the Scenario Manager dialog box and specifying a source workbook and worksheet in the Merge Scenarios dialog



    Running Microsoft Office 2000 Small Business
    Running Microsoft Office 2000
    ISBN: 1572319585
    EAN: 2147483647
    Year: 2005
    Pages: 228
    Authors: Michael Halvorson, Michael J. Young
    BUY ON AMAZON

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net