Analyzing
Alternative
Data Sets
In this chapter, you will learn how to
✔ De ne an alternative data set.
✔ De ne multiple alternative data sets.
✔ Vary your data to get a desired result by using Goal Seek.
✔ Find optimal solutions by using Solver.
✔ Analyze data by using descriptive statistics.
When you store data in a Microsoft Excel 2010 workbook, you can use that data, either
by itself or as part of a calculation, to discover important information about your organi-
zation. When you track total sales on a time basis, you can nd your best and worst sales
periods and correlate them with outside events. For businesses such as Consolidated
Messenger, package volume increases dramatically during the holidays as customers
ship gifts to friends and family members.
The data in your orksheets is great for answering the question, “What happened?” The data
is less useful for answering “what-if” questions, such as, “How much money would we save
if we reduced our labor to 20 percent of our total costs?” You can always save an alternative
version of a workbook and create formulas that calculate the effects of your changes, but
you can do the same thing in your existing workbooks by de ning one or more alternative
data sets and switching between the original data and the new sets you create.
Excel also provides the tools to determine the input values that would be required for a
formula to produce a given result. For example, the chief operating of cer of Consolidated
Messenger, Lori Penor, could nd out to what level the revenues from three-day shipping
would need to rise for that category to account for 25 percent of total revenue.
In this chapter, you’ll learn how to de ne alternative data sets and determine the necessary
inputs to make a calculation produce a particular result.
189
www.it-ebooks.info
190 Chapter 8 Analyzing Alternative Data Sets
Practice Files Before you can complete the exercises in this chapter, you need to copy
the book’s practice les to your computer. The practice les you’ll use to complete the
exercises in this chapter are in the Chapter08 practice le folder. A complete list of
practice les is provided in “Using the Practice Files” at the beginning of this book.
De ning an Alternative Data Set
When you save data in an Excel worksheet, you create a record that re ects the character-
istics 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, nd 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 re ects 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 acci-
dentally 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.
www.it-ebooks.info
De ning an Alternative Data Set 191
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 de ne 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
de ned 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).
www.it-ebooks.info
192 Chapter 8 Analyzing Alternative Data Sets
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 You need the 2DayScenario_start workbook located in your Chapter08
practice le folder to complete this exercise. Start Excel, open the 2DayScenario_start
workbook, and save it as 2DayScenario. Then follow the steps.
1. 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.
2. Click Add.
The Add Scenario dialog box opens.
3. In the Scenario name eld, type 2DayIncrease.
4. At the right edge of the Changing cells eld, click the Collapse Dialog button so
the worksheet contents are visible.
The Add Scenario dialog box collapses.
5. 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 eld, and the dialog box title changes to Edit
Scenario.
6. Click OK.
The Scenario Values dialog box opens.
www.it-ebooks.info
De ning an Alternative Data Set 193
7. In the value eld, type 13.2, and then click OK.
The Scenario Values dialog box closes, and the Scenario Manager is displayed again.
8. If necessary, drag the Scenario Manager dialog box to another location on the
screen so that you can view the entire table.
9. 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. For information about changing the appearance of
the ribbon to match our screen images, see “Modifying the Display of the Ribbon” at the
beginning of this book.
10. In the Scenario Manager dialog box, click Close.
11. 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.
www.it-ebooks.info
194 Chapter 8 Analyzing Alternative Data Sets
De ning Multiple Alternative Data Sets
One great feature of Excel scenarios is that you’re not limited to creating one alternative
data set—you can create as many scenarios as you like and apply them by using the
Scenario Manager. To apply more than one scenario by using the Scenario Manager,
click the name of the rst scenario you want to display, click the Show button, and
then do the same for any subsequent scenarios. The values you de ned as part of
those scenarios will appear in your worksheet, and Excel will update any calculations
involving the changed cells.
Tip If you apply a scenario to a worksheet and then apply another scenario to the same
worksheet, both sets of changes appear. If multiple scenarios change the same cell, the cell
will contain the value in the most recently applied scenario.
Applying multiple scenarios alters the values in your worksheets. You can see how those
changes affect your formulas, but Excel also gives you a way to view the results of all
your scenarios in a single, separate worksheet. To create a worksheet in your current
workbook that summarizes the changes caused by your scenarios, open the Scenario
Manager, and then click the Summary button. When you do, the Scenario Summary
dialog box opens.
From within the dialog box, you can choose the type of summary worksheet you want to
create and the cells you want to display in the summary worksheet. To choose the cells
to display in the summary, click the Collapse Dialog button in the box, select the cells you
want to display, and then expand the dialog box. After you verify that the range in the
box represents the cells you want to have included on the summary sheet, click OK to
create the new worksheet.
www.it-ebooks.info
De ning Multiple Alternative Data Sets 195
It’s a good idea to create an “undo” scenario named Normal that holds the original values
of the cells you’re going to change before they’re changed in other scenarios. For example,
if you create a scenario named High Fuel Costs that changes the sales gures in three cells,
your Normal scenario restores those cells to their original values. That way, even if you
accidentally modify your worksheet, you can apply the Normal scenario and not have to
reconstruct the worksheet from scratch.
Tip Each scenario can change a maximum of 32 cells, so you might need to create more than
one scenario to ensure that you can restore a worksheet.
In this exercise, you’ll create scenarios to represent projected revenue increases from
two rate changes, view the scenarios, and then summarize the scenario results in a
new worksheet.
SET UP You need the MultipleScenarios_start workbook located in your Chapter08
practice le folder to complete this exercise. Open the MultipleScenarios_start
workbook, and save it as MultipleScenarios. Then follow the steps.
1. 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.
2. Click Add.
The Add Scenario dialog box opens.
3. In the Scenario name eld, type 3DayIncrease.
4. At the right edge of the Changing cells eld, click the Collapse Dialog button.
The Add Scenario dialog box collapses.
5. In the worksheet, click cell C4 and then, in the dialog box, click the Expand Dialog
button.
$C$4 appears in the Changing Cells eld, and the dialog box title changes to Edit
Scenario.
6. Click OK.
The Scenario Values dialog box opens.
7. In the value eld, type 11.50.
www.it-ebooks.info
196 Chapter 8 Analyzing Alternative Data Sets
8. Click OK.
The Scenario Values dialog box closes, and the Scenario Manager is displayed
again.
9. Click Add.
The Add Scenario dialog box opens.
10. In the Scenario name eld, type Ground and Overnight Increase.
11. At the right edge of the Changing cells eld, click the Collapse Dialog button.
The Add Scenario dialog box collapses.
12. Click cell C3, hold down the Ctrl key, and click cell C6. Then click the Expand
Dialog button.
$C$3,$C$6 appears in the Changing Cells eld, and the dialog box title changes to
Edit Scenario.
13. Click OK.
The Scenario Values dialog box opens.
14. In the $C$3 eld, type 10.15.
15. In the $C$6 eld, type 18.5.
16. Click OK.
The Scenario Values dialog box closes, and the Scenario Manager dialog box is
displayed again.
www.it-ebooks.info
De ning Multiple Alternative Data Sets 197
17. Click Summary.
The Scenario Summary dialog box opens.
18. Verify that the Scenario summary option is selected and that cell E8 appears in
the Result cells eld.
19. Click OK.
Excel creates a Scenario Summary worksheet.
CLEAN UP Save the MultipleScenarios workbook, and then close it.
www.it-ebooks.info
198 Chapter 8 Analyzing Alternative Data Sets
Varying Your Data to Get a Desired Result
by Using Goal Seek
When you run an organization, you must track how every element performs, both in
absolute terms and in relation to other parts of the organization. Just as you might want
to reward your employees for maintaining a perfect safety record and keeping down
your insurance rates, you might also want to stop carrying products you cannot sell.
When you plan how you want to grow your business, you should have speci c goals in
mind for each department or product category. For example, Lori Penor of Consolidated
Messenger might have the goal of reducing the rm’s labor costs by 20 percent as
compared to the previous year. Finding the labor amount that represents a 20-percent
decrease is simple, but expressing goals in other ways can make nding the solution
more challenging. Instead of decreasing labor costs 20 percent over the previous year,
Lori might want to decrease labor costs so they represent no more than 20 percent of
the company’s total outlay.
As an example, consider a worksheet that holds cost gures for Consolidated Messenger’s
operations and uses those gures to calculate both total costs and the share each category
has of that total.
Important In the worksheet, the values in the Share row are displayed as percentages, but the
underlying values are decimals. For example, Excel represents 0.3064 as 30.64%.
Although it would certainly be possible to gure the target number that would make labor
costs represent 20 percent of the total, there is an easier way to do it in Excel: Goal Seek.
To use Goal Seek, you display the Data tab and then, in the Data Tools group, click What-If
Analysis. On the menu that is displayed, click Goal Seek to open the Goal Seek dialog box.
www.it-ebooks.info
Varying Your Data to Get a Desired Result by Using Goal Seek 199
Important If you save a workbook with the results of a Goal Seek calculation in place, you will
overwrite the values in your workbook.
In the dialog box, you identify the cell with the target value; in this example, it is cell C4,
which has the percentage of costs accounted for by the Labor category. The To Value
eld has the target value (.2, which is equivalent to 20 percent), and the By Changing
Cell eld identi es the cell with the value Excel should change to generate the target
value of 20 percent in cell C4. In this example, the cell to be changed is C3.
Clicking OK tells Excel to nd a solution for the goal you set. When Excel nishes its
work, the new values appear in the designated cells, and the Goal Seek Status dialog
box opens.
Tip Goal Seek nds the closest solution it can without exceeding the target value. In this case,
the closest percentage it could nd was 19.97 percent.
In this exercise, you’ll use Goal Seek to determine how much you need to decrease
transportation costs so those costs make up no more than 40 percent of Consolidated
Messenger’s operating costs.
SET UP You need the TargetValues_start workbook located in your Chapter08
practice le folder to complete this exercise. Open the TargetValues_start workbook,
and save it as TargetValues. Then follow the steps.
1. On the Data tab, in the Data Tools group, click What-If Analysis and then, in the
list, click Goal Seek.
www.it-ebooks.info
200 Chapter 8 Analyzing Alternative Data Sets
The Goal Seek dialog box opens.
2. In the Set cell eld, type D4.
3. In the To value eld, type .4.
4. In the By changing cell eld, type D3.
5. Click OK.
Excel displays the solution in both the worksheet and the Goal Seek Status
dialog box.
6. Click Cancel.
Excel closes the Goal Seek Status dialog box without saving the new worksheet
values.
CLEAN UP Save the TargetValues workbook, and then close it.
www.it-ebooks.info
Finding Optimal Solutions by Using Solver 201
Finding Optimal Solutions by Using Solver
Goal Seek is a great tool for nding out how much you need to change a single input
value to generate a desired result from a formula, but it’s of no help if you want to nd
the best mix of several input values. For example, marketing vice president Craig Dewar
might want to advertise in four national magazines to drive customers to Consolidated
Messenger’s Web site, but he might not know the best mix of ads to reach the greatest
number of readers. He asked the publishers for ad pricing and readership numbers, which
he recorded in a spreadsheet, along with the minimum number of ads per publication
(three) and the minimum number of times he wants the ad to be seen (10,000,000).
Because one of the magazines has a high percentage of corporate executive readers,
Craig does want to take out at least four ads in that publication, despite its relatively
low readership. The goal of the ad campaign is for the ads to be seen as many times
as possible without costing the company more than the $3,000,000 budget.
Tip It helps to spell out every aspect of your problem so that you can identify the cells you
want Solver to use in its calculations.
If you performed a complete installation when you installed Excel on your computer,
you see the Solver button on the Data tab in the Analysis group. If not, you need to
install the Solver Add-In. To do so, click the File tab, and then click Options. In the Excel
Options dialog box, click Add-Ins to display the Add-Ins page. At the bottom of the
dialog box, in the Manage list, click Excel Add-Ins, and then click Go to display the
Add-Ins dialog box. Select the Solver Add-in check box and click OK to install Solver.
www.it-ebooks.info
202 Chapter 8 Analyzing Alternative Data Sets
Tip You might be prompted for the Microsoft Of ce system installation CD. If so, put the CD
in your CD drive, and click OK.
After the installation is complete, Solver appears on the Data tab, in the Analysis group.
Clicking Solver displays the Solver Parameters dialog box.
The rst step of setting up your Solver problem is to identify the cell that contains the
summary formula you want to establish as your objective. To identify that cell, click in
the Set Objective box, click the target cell in the worksheet, and then select the option
representing whether you want to minimize the cell’s value, maximize the cell’s value, or
make the cell take on a speci c value. Next, you click in the By Changing Variable Cells box
and select the cells Solver should vary to change the value in the objective cell. Finally,
you can create constraints that will set the limits for the values Solver can use. To do so,
click Add to open the Add Constraint dialog box.
www.it-ebooks.info
Finding Optimal Solutions by Using Solver 203
You add constraints to the Solver problem by selecting the cells to which you want to
apply the constraint, selecting the comparison operation (such as less than or equal
to, greater than or equal to, or must be an integer), and clicking in the Constraint box
to select the cell with the value of the constraint. You could also type a value in the
Constraint box, but referring to a cell makes it possible for you to change the con-
straint later without opening Solver.
Tip After you run Solver, you can use the commands in the Solver Results dialog box to save
the results as changes to your worksheet or create a scenario based on the changed data.
In this exercise, you’ll use Solver to determine the best mix of ads given the following
constraints:
● You want to maximize the number of people who see the ads.
● You must buy at least 8 ads in 3 magazines and at least 10 in the fourth.
● You can’t buy part of an ad (that is, all numbers must be integers).
● You can buy no more than 20 ads in any one magazine.
● You must reach at least 10,000,000 people.
● Your ad budget is $3,000,000.
SET UP You need the AdBuy_start workbook located in your Chapter08 practice
le folder to complete this exercise. Open the AdBuy_start workbook, and save it as
AdBuy. Then follow the steps.
1. If the Solver button doesn’t appear in the Analysis group on the Data tab, follow
the instructions from earlier in this section to install it.
2. In the Analysis group on the Data tab, click Solver.
The Solver Parameters dialog box opens.
3. Click in the Set Objective box, and then click cell G9.
$G$9 appears in the Set Objective eld.
www.it-ebooks.info
204 Chapter 8 Analyzing Alternative Data Sets
4. Click Max.
5. Click in the By Changing Variable Cells eld, and select cells E5:E8.
$E$5:$E$8 appears in the By Changing Variable Cells eld.
6. Click Add.
The Add Constraint dialog box opens.
7. Select cells E5:E8.
$E$5:$E$8 appears in the Cell Reference eld.
www.it-ebooks.info
Finding Optimal Solutions by Using Solver 205
8. In the operator list, click int. Then click Add.
Excel adds the constraint to the Solver problem, and the Add Constraint dialog box
clears to accept the next constraint.
9. Click cell F9.
=$F$9 appears in the Cell Reference eld.
10. Click in the Constraint eld, and then click cell G11.
=$G$11 appears in the Constraint eld.
11. Click Add.
Excel adds the constraint to the Solver problem, and the Add Constraint dialog box
clears to accept the next constraint.
12. Click cell G9.
$G$9 appears in the Cell Reference eld.
13. In the operator list, click >=.
14. Click in the Constraint eld, and then click cell G12.
=$G$12 appears in the Constraint eld.
15. Click Add.
Excel adds the constraint to the Solver problem, and the Add Constraint dialog box
clears to accept the next constraint.
16. Select cells E5:E7.
$E$5:$E$7 appears in the Cell Reference eld.
17. In the operator list, click >=.
18. Click in the Constraint eld, and then click cell G13.
www.it-ebooks.info
206 Chapter 8 Analyzing Alternative Data Sets
=$G$13 appears in the Constraint eld.
19. Click Add.
Excel adds the constraint to the Solver problem, and the Add Constraint dialog box
clears to accept the next constraint.
20. Click cell E8.
$E$8 appears in the Cell Reference eld.
21. In the operator list, click >=.
22. Click in the Constraint eld, and then click cell G14.
=$G$14 appears in the Constraint eld.
23. Click Add.
Excel adds the constraint to the Solver problem, and the Add Constraint dialog box
clears to accept the next constraint.
24. Select cells E5:E8.
$E$5:$E$8 appears in the Cell Reference eld.
25. Verify that the <= operator appears in the operator eld, click in the Constraint
eld, and then click cell G15.
=$G$15 appears in the Constraint eld.
26. Click OK.
Excel adds the constraint to the Solver problem and closes the Add Constraint
dialog box, and the Solver Parameters dialog box opens again.
27. Click Solve.
The Solver Results dialog box opens, indicating that Solver found a solution. The
result is displayed in the body of the worksheet.
www.it-ebooks.info
Analyzing Data by Using Descriptive Statistics 207
28. Click Cancel.
The Solver Results dialog box closes.
CLEAN UP Save the AdBuy workbook, and then close it.
Analyzing Data by Using Descriptive Statistics
Experienced business people can tell a lot about numbers just by looking at them to see
if they “look right.” That is, the sales gures are about where they’re supposed to be for a
particular hour, day, or month; the average seems about right; and sales have increased
from year to year. When you need more than an informal assessment, however, you can
use the tools in the Analysis ToolPak.
If you don’t see the Data Analysis item in the Analysis group on the Data tab, you can
install it. To do so, click the File tab, and then click Options. In the Excel Options dialog
box, click Add-Ins to display the Add-Ins page. At the bottom of the dialog box, in the
Manage list, click Excel Add-Ins, and then click Go to display the Add-Ins dialog box.
Select the Analysis ToolPak check box and click OK.
Tip You might be prompted for the Microsoft Of ce system installation CD. If so, put the CD
in your CD drive, and click OK.
www.it-ebooks.info
208 Chapter 8 Analyzing Alternative Data Sets
After the installation is complete, the Data Analysis item appears in the Analysis group
on the Data tab.
You then click the item representing the type of data analysis you want to perform, click
OK, and use the commands in the resulting dialog box to analyze your data.
In this exercise, you’ll use the Analysis ToolPak to generate descriptive statistics of driver
sorting time data.
SET UP You need the DriverSortTimes_start workbook located in your Chapter08
practice le folder to complete this exercise. Open the DriverSortTimes_start
workbook, and save it as DriverSortTimes. Then follow the steps.
1. On the Data tab, in the Analysis group, click Data Analysis.
The Data Analysis dialog box opens.
2. Click Descriptive Statistics, and then click OK.
The Descriptive Statistics dialog box opens.
3. Click in the Input Range eld, and then select cells C3:C17.
$C$3:$C$17 appears in the Input Range eld.
www.it-ebooks.info
Key Points 209
4. Select the Summary statistics check box.
5. Click OK.
A new worksheet that contains summary statistics about the selected data appears.
CLEAN UP Save the DriverSortTimes workbook, and then close it. If you’re not
continuing directly to the next chapter, exit Excel.
Key Points
● Scenarios enable you to describe many potential business cases within a single
workbook.
● It’s usually a good idea to create a “normal” scenario that enables you to reset
your worksheet.
● Remember that you can change up to 32 cells in a scenario, but no more.
● You can summarize your scenarios on a new worksheet to compare how each
scenario approaches the data.
● Use Goal Seek to determine what value you need in a single cell to generate the
desired result from a formula.
● If you want to vary the values in more than one cell to nd the optimal mix of inputs
for a calculation, use the Solver Add-In.
● Advanced statistical tools are available in the Analysis ToolPak—use them to examine
your data thoroughly.
www.it-ebooks.info
