Microsoft Office Tutorials and References

In Depth Information

8
Analyzing

Alternative

Data Sets

In this chapter, you will learn how to

✔
Define an alternative data set.

✔
Define 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

organization. When you track total sales on a time basis, you can find 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 defining 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 officer of Consolidated

Messenger, Lori Penor, could find 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 define alternative data sets and determine the necessary

inputs to make a calculation produce a particular result.

189