Transcript Slide 1

Extended Learning Module D
Decision Analysis With
Spreadsheet Software
D-1
Overview of Module D






D-2
Lists
Basic AutoFilter
Custom AutoFilter
Conditional Formatting
Pivot Tables
Back to Decision Support
Introduction

Connect to the Web site that supports
this text (www.mhhe.com/haag and
select XLM/D).

Download the file called
XLMD_Customer.xls.
D-3
Lists

D-4
List - a collection of information arranged
in columns and rows in which each
column displays one particular type of
information.
Lists

Each column has only one type of information.

The first row in the list contains the labels or
column headings.

The list does not contain any blank rows.

The list is bordered on all four sides by blank
rows and blank columns.
D-5
Lists

List definition table - a description of a list by
column.

The columns of information in Figure D.2 include:







D-6
CUST ID – a unique ID for each customer
REGION – the region in which the customer lives
RENT VS. OWN – whether the customer rents or owns a
home
NUM HOUSEHOLD – number of family members
ANNUAL INCOME – total combined annual income
TOTAL PURCHASES – dollar total of all purchases made by
the customer within the last six months
NUM PURCHASES – count of all purchase made by the
customer within the last six months.
Lists
Column headings
Each column has only
one type of
information.
D-7
Basic AutoFilter

AutoFilter function - filters a list and allows you
to hide all the rows in a list except those that
match criteria you specify.

To use AutoFilter:



D-8
Click in any cell in the list.
Click on Data, point at Filter, and click on AutoFilter.
Click on a list arrow box and choose filtering.
Basic AutoFilter
Shows only customers
in the North REGION.
D-9
Basic AutoFilter

You can filter on multiple columns.
Customers in the North REGION
who own a home with only 1
household member.
D-10
Custom AutoFilter

Custom AutoFilter function - allows you to hide all
the rows in a list except those that match criteria,
besides “is equal to,” you specify.

To perform Custom AutoFilter, click on (Custom…) in
list arrow box.

To see all customers who have at least 4 people in their
household, perform the steps on the following slides.
D-11
Custom AutoFilter

Select (Custom…)
from list arrow box for
NUM HOUSEHOLD.
D-12
Custom AutoFilter

You will then see a
Custom AutoFilter
box.
D-13
Custom AutoFilter

In top-left entry box,
click on the pull-down
arrow and click on is
greater than.
D-14
Custom AutoFilter

In top-right entry box,
click on the pull down
arrow and click on 3.
D-15
Custom AutoFilter
Only customers with
more than four people
in their households.
D-16
Custom AutoFilter

You can also use “and” and “or” with
Custom AutoFilter.

What customers have spent less than $20
or more than $100 in the past six months?
D-17
Custom AutoFilter
D-18
Custom AutoFilter
Only customers who have
spent less than $20 or
more than $100
D-19
Conditional Formatting

Conditional formatting - highlights
the information in a cell that meets
some criteria you specify.

To conditionally format in red all TOTAL
PURCHASES greater than $100,
perform the steps on the following slides.
D-20
Conditional Formatting

Select entire TOTAL
PURCHASES
column.
D-21
Conditional Formatting

Click on Format and
then Conditional
Formatting.
D-22
Conditional Formatting

Pull down arrow for the field second from the left and click
on the greater than.

In the field on the right, enter 100.

Click on Format.
D-23
Conditional Formatting

Click on Patterns tab.

Choose the color red.

Click on Ok (twice).
D-24
Conditional Formatting
Only customers whose
number of employees
exceeds 100 are
highlighted in red.
D-25
Pivot Tables

Pivot table enables you to
group and
summarize
information.
D-26
Pivot Tables

To create a pivot table, perform the
following steps:




D-27
Click on Data and then PivotTable and
PivotChart Report.
In Step 1 of 3, click on Next.
In Step 2 of 3, click on Next.
In Step 3 of 3, click on Finish.
Pivot Tables

First, from the menu
bar, click on Data and
then PivotTable and
PivotChart Report.
D-28
Pivot Tables

Second, click on Next
in the Step 1 of 3 box.

Assumes pivot table
will come from a list.
D-29
Pivot Tables

Third, click on Next in the Step 2 of 3 box.

This defaults to the entire list.
D-30
Pivot Tables

Fourth, click on Finish in the Step 3 of 3 box.

This places the pivot table in a new worksheet.
D-31
Pivot Tables

You will then see
a skeletal
structure of a
pivot table.
D-32
Pivot Tables

We want to build a pivot table just the like the
one in Figure D.1 on page 225.

It shows number of customers by REGION and
RENT VS OWN.
D-33
Pivot Tables

From Pivot Table Field List box:




Drag REGION and drop it into “Drop Row
Fields Here.”
Drag RENT VS OWN and drop it into “Drop
Column Fields Here.”
Drag CUST ID and drop it into “Drop Data
Items Here.”
Result is shown on next slide.
D-34
Pivot Tables
Drag and drop RENT
VS. OWN here.
Drag and drop REGION
here.
Drag and drop CUST ID
here.
D-35
Pivot Tables

The default aggregation is by summing,
not what we want.

To change to counting of CUST ID,
perform the steps on the following slides.
D-36
Pivot Tables

Click on the Field
Settings button in the
Pivot Table toolbar.
D-37
Pivot Tables

Click on Count in
Summarize by:

Click on OK.
D-38
Pivot Tables
Now, the pivot table
counts the number of
customers.
D-39
Pivot Tables

You can add as many pieces of
information to a pivot table as you want.

We added TOTAL PURCHASES in Figure
D.13. (p. 238)
D-40
Pivot Tables
Drag and drop TOTAL
PURCHASES here to
obtain a summary of
another dimension of
information.
D-41
BACK TO DECISION SUPPORT

AutoFilter - helps you quickly create a view of a
partial list of information.

Conditional Formatting - maintains the view of
the entire list of information but highlights key
pieces of information that you may be looking
for.

Pivot table - helps you quickly aggregate or
summarize information by dimension.
D-42
Summary
Student Learning Outcomes
1.
2.
3.
4.
D-43
Define a list and list definition table within the
context of spreadsheet software and describe
the importance of each.
Compare and contrast the AutoFilter function
and Custom AutoFilter function in spreadsheet
software.
Describe the purpose of using conditional
formatting.
Define a pivot table and describe how you can
use it to view summarized information by
dimension.