Download the file exped.xls from the book's Web site and open it with MS Excel or LibreOffice Spreadsheet. This file is a sample of 1000 sales transactions for the Expeditioner. For each sale, there is a row recording when it was sold, where it was sold, what was sold, how it was sold, the quantity sold, and the sales revenue.
Use LibreOffice's Pivot Table (Data>Pivot Table >Create…) or Microsoft's PivotTable (Data>Summarize with PivotTable) to produce the following report:
| Sum of REVENUE | ||||
| WHERE | Catalog | Store | Web | Grand Total |
London |
50,310 | 151,015 | 13,009 | 214,334 |
| New York | 8,712 | 28,060 | 2,351 | 39,123 |
| Paris | 32,166 | 104,083 | 7,054 | 143,303 |
| Sydney | 5,471 | 21,769 | 2,749 | 29,989 |
| Tokyo | 12,103 | 42,610 | 2,003 | 56,716 |
| Grand Total | 108,762 | 347,537 | 27,166 | 483,465 |
Continue to use the Pivot Table to answer the following questions:
| Question | Answer |
|||||||||||||||||||||||||
| 1 | What was the value of catalog sales for London in the first quarter? | 7,810 |
||||||||||||||||||||||||
| 2 | What percent of the total annual sales were Tokyo Web sales in the fourth quarter? | 0.01% |
||||||||||||||||||||||||
| 3 | What percent of Sydney's annual sales was its Catalog sales? | 18.24% |
||||||||||||||||||||||||
| 4 | What was the value of catalog sales for London in January and give details of the transactions? | 411
|
||||||||||||||||||||||||
| 5 | What was the value of Camel saddle sales for Paris by quarter? |
|
||||||||||||||||||||||||
| 6 | How many Elephant polo sticks were sold in New York in each month? |
|
This page is part of the promotional and support material for Data Management (open edition) by Richard T. Watson |