Section 19.1. Using Scenarios


19.1. Using Scenarios

People often use spreadsheets to try and predict the future. For instance, in order to plan for the upcoming year, a company may need to make reasonable assumptions about expenses, sales, and profits. However, part of the planning process is considering different possibilities. A responsible companyone that doesn't want to tumble suddenly into bankruptcy, or sell more products than it can makeuses contingency planning to prepare for the best- and worst-case scenarios.

You can certainly use Excel to create a workbook that includes best- and worst-case information on your own. However, to do so, you need to create duplicate copies of your data. The problem with this approach is that duplicated data is difficult to maintain. For example, if you change the profit calculation formula in a worksheet that calculates the best-case scenario, you need to make sure you track down the same calculation in every other worksheet in order to make sure your data remains consistent.

Excel provides an easier solution with its scenarios feature. Scenarios help you perform multiple calculations on the same set of dataall within the same worksheet. For example, a ski resort might hope for the best and plan for the worst with the scenarios " Projected 2005 Results for a Long Winter" and "Worst Case 2005 Results for a Short Season." You can choose and view the scenarios separately, or even generate a summary report with the click of a button.

POWER USERS' CLINIC
Scenarios-To-Go

When you first create a scenario, it's attached to a single worksheet. However, the Scenario Manager also lets you use a little-known technique to merge scenarios. This technique lets you transplant your scenarios (and their accompanying cell values) into another worksheet in the same workbook, or another worksheet in a different workbook.

Merging scenarios isn't often useful, but it does come in handy if you need to perform the same analysis with different worksheets that contain the same arrangement of data. For example, if you have several sales projections worksheets, and the exchange rate and profit margin cells are in the same place in every worksheet, you could copy the scenarios to each worksheet.

To transfer scenarios, make sure both worksheets are open (the one containing the scenario and the one where you want to copy it to). Then go to the worksheet where you want to place the copied scenarios, choose Tool Scenarios, and click Merge in the Scenario Manager dialog box. You can choose the source workbook and worksheet from the lists that Excel provides. Click OK to complete the deal.

When you merge a scenario into a new worksheet, Excel copies the scenario names and the scenario values (the values for all the changed cells). Of course, the merged scenario isn't of much use unless the new worksheet has exactly the same structure as the original. Otherwise, when you select the scenario, Excel inserts the changed values in the wrong places.


19.1.1. Creating a New Scenario

You can create a scenario for any worksheet. Start by identifying all the cells specific to your scenario. For instance, if you're creating a business plan that estimates revenue from international sales, you might create different scenarios based on different possible currency exchange rates. In this example, the cell containing the exchange rate's value is a scenario-specific cell. That means each scenario uses a different value for this cell. As you change from one scenario to the next , Excel modifies the value in this cell but doesn't change any of the other cells (although if they use formulas based on the currency exchange rate, the program recalculates their results with the new information).

To get a better understanding of how this whole process works, consider the worksheet shown in Figure 19-1.

Figure 19-1. This worksheet shows sales predictions for the coming year for five regional divisions of a company. Assuming that the projected sales are correct, there are two values that would be interesting to modify, in order to see their effect on the projected profits listed in cells C3 through C9: the exchange rate (shown in C12) and the profit margin (C13). How would fluctuations in either of these values affect the profits listed in column C? You can find the answers to those questions by using Excel's Scenarios feature.


To consider different possibilities for any formula, you can add scenarios to a worksheet. The following example uses Figure 19-1 as the basis for seeing how the scenario tool works.

  1. Select the cells that will change in your scenario.

    These cells contain the assumptions that your scenario makes. You can select any range of cells you want, and you can hold down the Ctrl key to select a non-contiguous range. In the sales projection example, these cells are C12 (the exchange rate) and, optionally , C13 (the profit margin).

  2. Choose Tools Scenarios.

    The Scenario Manager dialog box appears.

  3. Click the Add button to create a new scenario.

    The Add Scenario dialog box appears, as shown in Figure 19-2.

    Figure 19-2. In the Add Scenario dialog box, you choose a name for your scenario. If needed, you can enter a more complete description of the scenario in the Comment text box. You don't need to edit the reference in the "Changing cells" text box because Excel automatically enters whatever cells are selected on the worksheet. In addition, you can make your scenario tamper-proof with document protection by turning on the two checkboxes at the bottom of the dialog box. These settings work only if you use the document protection features as explained on Section 15.4.4 in Chapter 15.


  4. Enter a descriptive name for the scenario you're creating.

    For example, if you plan to create a scenario that explores what happens if the U.S. dollar rises against the Euro (thereby causing your profits to plummet), you might use a name like High U.S. Dollar , Worst Case , or Most Likely Case .

  5. If you need to change the cell references used for this scenario, edit the "Changing cells" text box.

    Excel automatically fills this text box with references to the cells you selected before you started creating the scenario. If you're creating multiple scenarios at once, you might need to change this information. You can edit it directly as long as you remember to separate each cell reference with a comma. For example, use a value of C12, C13 if you want to include both cells C12 and C13. You can also point and click your way to success. Just click inside the "Changing cells" text box, and then click the worksheet to select the cells you want (holding down Ctrl to select several cells at once).


    Tip: There are two ways to go about setting your scenario values. You can enter them in all the changing cells before you create the scenario (because Excel uses the current cell values when you create the scenario), or you can adjust them in the Add Values dialog box when you create the scenario. Both approaches work equallyit's just a matter of which one you prefer.
  6. Click OK.

    This action closes the Add Scenario dialog box and opens the Scenario Values dialog box, as shown in Figure 19-3.

    Figure 19-3. This particular scenario includes only a single changing value: the currency exchange rate. To examine what happens if the U.S. dollar rises, this currency exchange rate is adjusted up to 1 from its original value of 0.83.


  7. Set the values of all the changing cells according to your scenario.

    Excel automatically inserts the current value of each cell in the Scenario Values dialog box. You can adjust these values or, if they're already correct for your scenario, you can keep them without making any changes. In the sales projection example, you can adjust the currency rate to make it higher or lower.

  8. Click OK.

    This action returns you to the Scenario Manager dialog box, which now shows your newly created scenario in the list.

  9. If you want to create more than one scenario at a time, you can repeat steps 3 to 8 for each new scenario.

    There's no limit to the number of different scenarios you can create. Figure 19-4 shows several scenarios in the Scenario Manager dialog box.


Note: Different scenarios don't always need to have the same changing cells, although it simplifies life if they do. For example, in the sales worksheet, you can create some scenarios that change the exchange rate value, and others that change both the exchange rate and the profit margin percentage. However, this approach can be a little confusing if you switch rapidly from one scenario to another. For example, if you switch to a scenario that updates both cells, and then switch to a scenario that updates just one of the two cells, the other cell will still have the value from the previous scenariowhich might not be what you want!

19.1.2. Managing Scenarios

Once you've created your scenarios, you'll probably want to put them to work to compare the different possible cases. You can switch from one scenario to another using the buttons in the Scenario Manager dialog box. (If the Scenario Manager isn't visible, just choose Tools Scenarios.)

Here are the tasks you can perform in the Scenario Manager:

  • To switch from one scenario to another, select the scenario you want to view in the list and click Show. Excel immediately updates the changed cells (and any cells that reference them).

  • To change a scenario's assumptions, select it and click Edit. The Edit Scenario dialog box that appears looks exactly like the Add Scenario dialog box. It lets you change the name, modify the changing cells, and edit the description. Click OK to move ahead and adjust the actual values for the changing cells.

  • To remove a scenario completely, select it in the list and click Delete.

  • To return to your Excel worksheet, click Close at any time. The values from the scenario you applied the previous time remain in effect.

Figure 19-4. Scenarios are extremely useful and easy to use. Apply a scenario by choosing it from the list shown in the Scenario Manager dialog box and clicking Show. The worksheet updates automatically. Using the scenarios in this example, you can see quickly how the total profit falls from $71,265 to $42,250 in the worst-case scenario.


19.1.3. Creating a Summary Report

Scenarios are great for exploring different possibilities, but you're still limited to viewing one scenario at a time. If you'd rather have an at-a-glance look at all the scenarios you've defined, you can generate an automatic summary report.

To create a summary report, follow these steps:

  1. Choose Tools Scenario.

    The Scenario Manager dialog box appears.

  2. Click Summary.

    The Scenario Summary dialog box appears, as shown in Figure 19-5.

    Figure 19-5. Once you pick the report type, you need to specify (in the "Result cells" box) which cells to display in the summary report. Your choices include cells C3 to C7 and C9 (since these cells' values change depending on the scenario you're considering). You can include all of these cells, but you might just be interested in the final total in C9.


  3. Choose the type of summary you want. In most cases, the first option ("Scenario summary") is what you want.

    You can also create a summary pivot table instead of a summary report by selecting "Scenario PivotTable report" instead. For more information about pivot tables, which are an advanced feature for summarizing large amounts of data, check out Chapter 20.

  4. In the "Result cells" text box, specify the cells you want to include in the report.

    These are the cells that fluctuate based on the scenario values you've used. When Excel creates the summary, it includes all the values you've created for the changing cells, and then shows the resulting value for each of the result cells.

  5. Click OK to create the summary report.

    The summary report always opens in a new worksheet named Scenario Summary. Excel automatically formats the scenario summary to be readable, and gives it basic outlining (Section 14.2.1, Chapter 14) that lets you collapse the summary to show just the portions that interest you. Figure 19-6 shows an example.


Note: Once Excel creates it, the summary report is completely independent from your data. If you change any of the scenario values or calculations, the summary report doesn't update (although you can recreate it using the same sequence of steps).

Figure 19-6. This report compares different scenarios for the sales projection worksheet. Rows 6 and 7 show the scenario-specific cells. Row 9 gives the bottom linethe calculated result that appears in cell C9 based on the scenario-specific values.




Excel. The Missing Manual
Excel 2010: The Missing Manual
ISBN: 1449382355
EAN: 2147483647
Year: 2003
Pages: 185

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