Defining and Editing Alternative Data Sets

When you save data in an Excel 2007 worksheet, you create a record that reflects the characteristics of an event or object. That data could represent an hour of sales on a particular day, the price of an item you just began offering for sale, or the percentage of total sales accounted for by a category of products. After the data is in place, you can create formulas to generate totals, find averages, and sort the rows in a worksheet based on the contents of one or more columns. However, if you want to perform a what-if analysis or explore the impact that changes in your data would have on any of the calculations in your workbooks, you need to change your data.

The problem of working with data that reflects an event or item is that changing any data to affect a calculation runs the risk of destroying the original data if you accidentally save your changes. You can avoid ruining your original data by creating a duplicate workbook and making your changes to it, but you can also create alternative data sets, or scenarios, within an existing workbook.

When you create a scenario, you give Excel 2007 alternative values for a list of cells in a worksheet. You can use the Scenario Manager to add, delete, and edit scenarios.

Clicking the Add button causes the Add Scenario dialog box to appear.

From within this dialog box, you can name the scenario and identify the cells that will hold alternative values. After you click OK, a new dialog box appears with spaces for you to enter the new values.

Clicking OK returns you to the Scenario Manager dialog box. From there, clicking the Show button replaces the values in the original worksheet with the alternative values you just defined in a scenario. Any formulas using cells with changed values recalculate their results. You can then remove the scenario by clicking the Undo button on the Quick Access Toolbar.

Warning

If you save and close a workbook while a scenario is in effect, those values become the default values for the cells changed by the scenario! You should strongly consider creating a scenario that contains the original values of the cells you change or creating a scenario summary worksheet (a topic covered later in this chapter).

In this exercise, you'll create a scenario to measure the projected impact on total revenue of a rate increase on two-day shipping.

USE the 2DayScenario workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Alternatives folder.

BE SURE TO start Excel 2007 before beginning this exercise.

OPEN the 2DayScenario workbook.

1.	On the Data tab, in the Data Tools group, click What-If Analysis and then click Scenario Manager. The Scenario Manager dialog box appears.
2.	Click Add. The Add Scenario dialog box appears.
3.	In the Scenario Name field, type 2DayIncrease.
4.	At the right edge of the Changing cells field, click the Contract Dialog button. The Add Scenario dialog box contracts.
5.	Click cell C5 and then click the Expand Dialog button. C5 appears in the Changing cells field.
6.	Click OK. The Scenario Values dialog box appears.
7.	In the value field, type 13.2 and then click OK. The Scenario Values dialog box disappears.
8.	If necessary, drag the Scenario Manager dialog box so you can view the entire table.
9.	Click Show. Excel 2007 applies the scenario, changing the value in cell C5 to $13.20, which in turn increases the value in cell E8 to $747,450,000.00.
10.	Click Close. The Scenario Manager dialog box disappears.
11.	On the Quick Access Toolbar, click the Undo button. Excel 2007 removes the scenario.

CLOSE the 2DayScenario workbook.