Page 255 - Excel Workbook for Dummies

26_798452 ch18.qxp 3/13/06 7:45 PM Page 238
238 Part V: Doing Data Analysis
Exploring Various Scenarios

Excel enables you to create and save sets of input values that produce different
results as scenarios using its Tools➪Scenario command. A scenario consists of a
group of input values in a worksheet to which you assign a name such as Best Case,
Worst Case, Most Likely Case, and so on.

Then, to reuse the input data and view the results they produce in the worksheet, you
simply select the name of the scenario you want to use and Excel applies the input
values stored in that scenario to the appropriate cells in the worksheet. After creating
your different scenarios for a worksheet, you can also use the Scenario Manager to
create a summary report showing you both the input values stored in each scenario
as well as key results produced by each.

Try It

Exercise 18-3: Constructing Various Scenarios for the Spreadsheet

Open the Exercise18-3.xls workbook file in your Chapter 18 folder in the My Practice
Spreadsheets folder on your hard disk or in the Excel Workbook folder on the workbook
CD-ROM. The Sales Forecast worksheet in this workbook contains a Sales Forecast for
2007 table that you will use to practice creating and using different growth scenarios
using different rate of sales growth, COGS (cost of goods sold), and expenses:

1. Select the following changing cells for your scenarios as one cell selection (by
holding down the Ctrl key as you click them):
• Sales_Growth cell, H4
• COGS cell, H5

• Expenses cell, H7
2. While cells H4, H5, and H7 are selected, choose Tools➪Scenarios to open the
Scenario Manager dialog box.
3. Select the Add button in this dialog box to open the Add Scenario dialog box and
then enter Most Likely in its Scenario Name text box before you select OK.
As soon as you select OK, the Add Scenario dialog box closes and the Scenario
Values dialog box opens. Here, you indicate the values to be used by the chang-
ing cells in the particular scenario you’re building. In this case, Excel picks up
the values 0.05, 0.2, and 0.28 for the Sales_Growth, COGS, and Expenses changing
cells from the entries made in cells H4, H5, and H7, respectively.
4. Select OK to accept the values in the Scenario Values dialog box picked up from
the Sales_Growth, COGS, and Expenses cells in the Sales Forecast table.
As soon as you select OK, the Scenario Values dialog box closes and the Scenario
Manager dialog box reappears.
5. Click the Add button and then enter Worst Case as the name for a second sce-
nario in the Add Scenario dialog box and then select OK.
6. Change the values in the designated changing cells as follows:
• 0.02 in Sales_Growth
• 0.3 in COGS
• 0.3 in Expenses
7. Select OK to return to the Scenario Manager dialog box that now contains both a
Most Likely and a Worst Case scenario.