Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–13
• This slide sets up an example of having multiple pieces of information
in the body of a 2D pivot table.
• This slide presents the screen capture in Figure D.13 on page 400.
• It shows the result of adding another piece of information to the body
• This slide sets up an example of creating a 3D pivot table.
• Subsequent slides present screen captures for completing the steps in
• These final two slides present the steps for creating a 3D pivot table.
• This slide shows how to view information by the third (page)
dimension.
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–14
• This slide introduces the notion of optimization and goal seeking
within the context of breakeven analysis.
• Have your students download the appropriate workbook and follow
along.
• This slide covers the step to set up the goal seek associated with
breakeven.
• Slides 55 and 56 are from Figure D.17 on page 404.
• Slides 55 and 56 are from Figure D.17 on page 404.
Back to Jump List
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–15
SHORT-ANSWER QUESTIONS
Back to Jump List
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–16
ASSIGNMENTS & EXERCISES (p. 405)
1. WHAT PRODUCTION PROBLEMS DO YOU HAVE? Throughout this module, you’ve been
practicing some spreadsheet features using XLMD_Production.xls. It seems you have some
real problems. There are an unacceptable number of defective products being produced.
Your task is to use some combination of Filter, conditional formatting, and pivot tables to
illustrate where the problems seem to be concentrated, perhaps by product, by employee,
by machine, or even by batch size. Based on your analysis, recommend how to correct the
problems.
DISCUSSION
• This assignment has no real “right” or “wrong” answers.
2. EVALUATING TOTAL PURCHASES AND ANNUAL INCOME Using XLMD_Customer.xls,
create a pivot table that illustrates the relationship between TOTAL PURCHASES and
ANNUAL INCOME. What trends do you see in the information? Suppose your task is to
concentrate marketing efforts and resources. On which annual income level would you
concentrate? Why? If you were a marketing manager, what additional information would
be helpful as you make your decision? Where would you be able to obtain such
information?
DISCUSSION
• Below you can see the pivot table illustrating the relationship.
Sum of TOTAL PURCHASES
ANNUAL INCOME
100K+
Grand Total
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–17
3. FINDING OUR INFORMATION ABOUT YOUR EMPLOYEES Suppose you own a small
business and have a workbook of information on your employees including ID, First Name,
Last Name, Department, Title, Salary, Hire Date, Birth Date, Gender, and Clearance (for
security purposes). This file is XLMD_Employee.xls and is on the Web site at
www.mhhe.com/haag. You are to use the features in this module to build various decision
support information.
DISCUSSION
• Below are the screen captures for the 6 tasks your students are required to complete.
Pivot table – average salary by gender within department
Average of
Salary
Department
Gender
Engineering
Human
Resources
Management
Marketing
Grand
Total
F
M
$
$
$
$
$
Grand Total
$
$
$
$
$
$
$
$
$
$
Pivot table – number of employees by clearance
Count of
ID
Clearance
Total
C
2
N
6
S
6
Grand
Total
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–18
Conditional formatting – employees in the Engineering department
Conditional formatting – employees who have no clearance
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–19
Basic Filter – employees who have top secret clearance
Custom Filter – employees who earn more than $50,000
Mod D Book–20
4. EXPLORING INFORMATION AT B&B TRAVEL Benjamin Travis and Brady Austin are co-
owners of B&B Travel Consultants, a medium-size business in Seattle with several branch
offices. B&B specializes in selling cruise packages. Ben and Brady maintain a workbook that
contains information for each cruise package sale including Location #, Travel Agent #,
Cruise Line, Total Package Price, and Commission. You can find this workbook
(XLMD_Travel.xls) on the Web site at www.mhhe.com/haag. Using the decision support
features in this module, you are to make some recommendations to B&B regarding (1)
which location should be closed, (2) which travel consultants should be downsized, and (3)
on which cruise lines B&B should focus its efforts.
DISCUSSION
• Like the first assignment, the focus of this project is to get your students to use the
5. CREATE A LIST FOR A BOOKSTORE Suppose that you’re the manager for your school’s
bookstore. Your task is to create a list in a workbook that contains information about the
textbooks it sells. In addition to tracking price, first author name, and publisher, identify
five other pieces of information for each textbook. Then build a fictitious list and illustrate
the use of the decision support features presented in this module.
DISCUSSION
• This assignment definitely has no “right” and “wrong” answers.
Mod D Book–21
6. GOAL SEEKING FOR NET PROFIT Using XLMD_BreakEven.xls, perform the task of
computing the break-even when the following changes are made:
A. Shipping costs increase to $3
B. Fixed costs increase to $1,800
C. Shipping costs are passed on to your customers
D. Variable costs increase by 10% and sales price per poster increases by 10%
Assume each task is independent, so reset the break-even analysis to its original values after
completing each task.
DISCUSSION
• Increase shipping cost to $3: Break-even is 750 units
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–22
ADDITIONAL ASSIGNMENTS AND EXERCISES
LISTS, LIST DEFINITION TABLES, AND USING BASIC FILTER
Note: Use this in the section on Basic Filter
Go to the Web site that supports this text (www.mhhe.com/haag), select XLM/D, and download
the file called XLMD_Customer2.xls. Take a moment and review the information in that
workbook.
First, create the list definition for it.
Now, perform the following Basic Filter exercises:
2. Show only those customers whose type of business is nonprofit.
4. Show only those customers in Texas whose types of business is government.
5. Show only those customers in the manufacturing business sector.
USING CUSTOM FILTERS
Note: Use this in the section on Custom Filter
CONDITIONAL FORMATTING
Note: Use this in the section on Conditional Formatting
Extended Learning Module D (Book Version, Office 2010) – Decision Analysis with Spreadsheet Software
Mod D Book–23
CREATING A TWO-DIMENSIONAL PIVOT TABLE
Note: Use this in the section on pivot tables
Back to Jump List