Extended Learning Module D Decision Analysis With Spreadsheet Software

1. Extended Learning Module D Decision Analysis With Spreadsheet Software

2. Overview of Module D • Lists • Basic AutoFilter • Custom AutoFilter • Conditional Formatting • Pivot Tables • Back to Decision Support

3. 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.

4. Lists • List - a collection of information arranged in columns and rows in which each column displays one particular type of information.

5. 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.

6. Lists • List definition table - a description of a list by column. • The columns of information in Figure D.2 include: • 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.

7. Lists Column headings Each column has only one type of information.

8. 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: • 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.

9. Basic AutoFilter Shows only customers in the North REGION.

10. Basic AutoFilter • You can filter on multiple columns. Customers in the North REGION who own a home with only 1 household member.

11. 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.

12. Custom AutoFilter • Select (Custom…) from list arrow box for NUM HOUSEHOLD.

13. Custom AutoFilter • You will then see a Custom AutoFilter box.

14. Custom AutoFilter • In top-left entry box, click on the pull-down arrow and click on is greater than.

15. Custom AutoFilter • In top-right entry box, click on the pull down arrow and click on 3.

16. Custom AutoFilter Only customers with more than four people in their households.

17. 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?

18. Custom AutoFilter

19. Custom AutoFilter Only customers who have spent less than $20 or more than $100

20. 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.

21. Conditional Formatting • Select entire TOTAL PURCHASES column.

22. Conditional Formatting • Click on Format and then Conditional Formatting.

23. 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.

24. Conditional Formatting • Click on Patterns tab. • Choose the color red. • Click on Ok (twice).

25. Conditional Formatting Only customers whose number of employees exceeds 100 are highlighted in red.

26. Pivot Tables • Pivot table - enables you to group and summarize information.

27. Pivot Tables • To create a pivot table, perform the following steps: • 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.

28. Pivot Tables • First, from the menu bar, click on Data and then PivotTable and PivotChart Report.

29. Pivot Tables • Second, click on Next in the Step 1 of 3 box. • Assumes pivot table will come from a list.

30. Pivot Tables • Third, click on Next in the Step 2 of 3 box. • This defaults to the entire list.

31. Pivot Tables • Fourth, click on Finish in the Step 3 of 3 box. • This places the pivot table in a new worksheet.

32. Pivot Tables • You will then see a skeletal structure of a pivot table.

33. 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.

34. 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.

35. Pivot Tables Drag and drop RENT VS. OWN here. Drag and drop REGION here. Drag and drop CUST ID here.

36. 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.

37. Pivot Tables • Click on the Field Settings button in the Pivot Table toolbar.

38. Pivot Tables • Click on Count in Summarize by: • Click on OK.

39. Pivot Tables Now, the pivot table counts the number of customers.

40. 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)

41. Pivot Tables Drag and drop TOTAL PURCHASES here to obtain a summary of another dimension of information.

42. 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.

43. Summary Student Learning Outcomes • 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.