When you save data in an Excel worksheet, you create a record that
reflects the characteristics of an event or object. That data could
represent the number of deliveries in an hour on a particular day, the
price of a new delivery option, or the percentage of total revenue
accounted for by a delivery option. 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 with manipulating data that reflects an event or item
is that when you change any data to affect a calculation you run 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 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 displays the Add Scenario dialog
box.
From within this dialog box, you can name the scenario and
identify the cells for which you want to define alternative values.
After you click OK, a new dialog box opens with spaces for you to type
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 the scenario.
Any formulas referencing cells with changed values will recalculate
their results. You can then remove the scenario by clicking the Undo
button on the Quick Access Toolbar.
Important
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 seriously consider creating a scenario that
contains the original values of the cells you change or creating a
scenario summary worksheet (a topic covered in the next
section).
In this exercise, you’ll create a scenario to measure the
projected impact on total revenue of a rate increase on two-day
shipping.
Set Up
Start Excel,
open the 2DayScenario_start workbook, and save it as
2DayScenario. Then follow the steps.
-
On the Data tab, in the
Data Tools group, click What-If Analysis and then, in the list,
click Scenario Manager.
The Scenario Manager dialog box opens.
-
Click Add.
The Add Scenario dialog box opens.
-
In the Scenario name field,
type 2DayIncrease.
-
At the right edge of the Changing
cells field, click the Collapse
Dialog button so the worksheet contents are
visible.
The Add Scenario dialog box collapses.
-
In the worksheet, click cell C5 and then, in the Add Scenario dialog box, click the
Expand Dialog button.
$C$5 appears in the Changing Cells field, and the dialog box
title changes to Edit Scenario.
-
Click OK.
The Scenario Values dialog box opens.
-
In the value field, type 13.2, and then click OK.
The Scenario Values dialog box closes, and the Scenario
Manager is displayed again.
-
If necessary, drag the Scenario
Manager dialog box to another location on the screen so
that you can view the entire table.
-
In the Scenario Manager
dialog box, click Show.
Excel 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.
Troubleshooting
The appearance of buttons and groups on the ribbon changes
depending on the width of the program window.
-
In the Scenario Manager
dialog box, click Close.
-
On the Quick Access Toolbar, click the Undo button.
Excel removes the effect of the scenario.
Clean Up
Save the 2DayScenario workbook, and then close it.
