| Schweizer Distribution specializes in distributing fresh produce to local restaurants in the Chicago area. The company currently sells 12 different products, through the efforts of three sales representatives, to 10 restaurants. The company, like all small businesses, is always interested in finding ways to increase revenues and decrease expenses. |
| |
| The company's founder, Bob Schweizer, has recently hired you as a new business analyst. You have just graduated from college with a degree in marketing and a specialization in customer relationship management. Bob is eager to hear your thoughts and ideas on how to improve the business and help the company build strong lasting relationships with its customers. |
| |
| Bob has provided you with last year's sales information in the data file AYK14_Data.xlsx (You can refer to Tech Plugin 3 for information on how to use Excel.). Help Bob analyze his distribution company by using a PivotTable to determine the following: |
| | 1. | Who is Bob's best customer by total sales? |
| | 2. | Who is Bob's worst customer by total sales? |
| | 3. | Who is Bob's best customer by total profit? |
| | 4. | Who is Bob's worst customer by total profit? |
| | 5. | What is Bob's best-selling product by total sales? |
| | 6. | What is Bob's worst-selling product by total sales? |
| | 7. | What is Bob's best-selling product by total profit? |
| | 8. | What is Bob's worst-selling product by total profit? |
| | 9. | Who is Bob's best sales representative by total profit? |
| | 10. | Who is Bob's worst sales representative by total profit? |
| | 11. | What is the best sales representative's best-selling product (by total profit)? |
| | 12. | Who is the best sales representative's best customer (by total profit)? |
| | 13. | What is the best sales representative's worst-selling product (by total profit)? |
| | 14. | Who is the best sales representative's worst customer (by total profit)? |
File Home Insert Page Layout Formulas Data Review View Help Share Comment PROTECTED VIEW Be carefulfiles from the Internet can contain viruses. Unless you need to edit, it's safer to stay in Protected View. Enable Editing A1 OrderDate M N O Q R S T W X Y 67.00 630.00 14.00 B c 1 OrderDate ProductID ProductName 2 04-Jan-11 22 Mozzarella cheese 3 04-Jan-11 16 Romaine lettuce 4 05-Jan-11 30 Red onions 5 06-Jan-11 16 Romaine lettuce 6 07-Jan-11 2 Black olives 7 07-Jan-11 16 Romaine lettuce 8 07-Jan-11 16 Romaine lettuce 9 08-Jan-11 30 Red onions 10 09-Jan-11 16 Romaine lettuce 11 10-Jan-11 16 Romaine lettuce 12 10-Jan-11 39 Pineapple 13 11-Jan-11 39 Pineapple 14 14-Jan-11 16 Romaine lettuce 15 14-Jan-11 16 Romaine lettuce 16 15-Jan-11 85 Pepperoni 17 15-Jan-11 100 Parmesan cheese 18 16-Jan-11 100 Parmesan cheese 19 18-Jan-11 100 Parmesan cheese 20 18-Jan-11 16 Romaine lettuce 21 20-Jan-11 4 Tomatoes 22 21-Jan-11 31 Peppers 23 21-Jan-11 22 Mozzarella cheese 24 23-Jan-11 2 Black olives 25 24-Jan-11 22 Mozzarella cheese 26 26-Jan-11 16 Romaine lettuce 27 27-Jan-11 100 Parmesan cheese 28 28-Jan-11 31 Peppers 29 28-Jan-11 22 Mozzarella cheese 30 29-Jan-11 4 Tomatoes 31 29-Jan-11 31 Peppers 32 29-Jan-11 16 Romaine lettuce 33 29-Jan-11 30 Red onions 34 31-Jan-11 100 Parmesan cheese 35 31-Jan-11 16 Romaine lettuce 36 02-Feb-11 30 Red onions 37 02-Feb-11 16 Romaine lettuce 38 04-Feb-11 39 Pineapple 39 05-Feb-11 22 Mozzarella cheese 40 05-Feb-11 16 Romaine lettuce 41 05-Feb-11 4 Tomatoes 42 05-Feb-11 4. Tomatoes 43 06-Feb-11 31 Peppers 44 07-Feb-11 6 Sun dried tomatoes 45 09-Feb-11 16 Romaine lettuce 46 09-Feb-11 100 Parmesan cheese 47 10-Feb-11 30 Red onions 48 10-Feb-11 16 Romaine lettuce 49 11-Feb-11 4 Tomatoes D F G H Quantity Unit Price Total Sales Unit Cost Total Cost Profit 41 24.00 $ 984.00 $ 18.00 738.00 246.00 90 15.00 $ 1,350.00 $ 14.00 $ 1.260.00 S 90.00 27 12.00 $ 324.00 8.00 $ 216.00 $ 108.00 67 15.00 ,005.00 $ 14.00 938.00 $ 79 12.00 $ 948.00 $ 6.00 474.00 $ 474.00 46 15.00$ 690.00 $ 14.00 644.00 S 46.00 52 15.00 $ 780.00 $ 14.00 728.00 $ 52.00 39 12.00 $ 468.00 $ 8.00 312.00 $ 156.00 66 15.00 $ 990.00 $ 14.00 924.00 66.00 58 15.00 S 870.00 $ 14.00 812.00 58.00 40 33.00 $ 1,320.00 $ 28.00 $ 1,120.00 200.00 71 33.00 $ 2,343.00 $ 28.00 $ 1,988.00 $ 355.00 18 15.00 $ 270.00 14.00 S 252.00 $ 18.00 28 $ 15.00$ 420.00 14.00 $ 392.00 $ 28.00 33 $ 53.00 $ 1,749.00 35.00 $ 1,155.00 $ 594.00 14 $ 8.00 $ 112.00 4.00 56.00 $ 56.00 72 $ 8.00 $ 576.00 4.00 288.00 288.00 10 8.00 $ 80.00 4.00 40.00 40.00 42 15.00 S 588.00 $ 42.00 48 9.00 $ 432.00 $ 7.00 336.00 $ 96.00 29 21.00 $ 609.00 $ 12.00 348.00 261.00 10 24.00 $ 240.00 $ 18.00 180.00 60.00 98 12.00 $ 1,176.00 6.00 588.00 588.00 45 24.00 $ 1,080.00 18.00 $ 810.00 $ 270.00 58 15.00 $ 870.00 14.00 $ 812.00 $ 58.00 66 $ 8.00 $ 528.00 4.00 $ 264.00 85 21.00 $ 1,785.00 12.00 1,020.00 $ 765.00 12 $ 24.00 $ 288.00 $ 18.00 216.00 72.00 40 9.00 $ 360.00 $ 7.00 280.00 80.00 5 21.00 $ 105.00 12.00 60.00 45.00 49 $ 15.00 735.00 $ 14.00 686.00 49.00 13 $ 12.00 $ 156.00 8.00 104.00 $ 52.00 38 8.00 $ 304.00 $ 4.00 152.00 $ 152.00 51 15.00$ 765.00 $ 14.00 714.00 S 51.00 85 12.00 $ 1,020.00 $ 8.00 680.00 $ 340.00 82 15.00 $ 1,230.00 $ 14.00 1,148.00 82.00 64 33.00 $ 2, 112.00 28.00 1,792.00 320.00 22 24.00 $ 528.00 $ 18.00 396.00 132.00 74 15.00 $ 1,110.00 $ 14.00 $ 1,036.00 74.00 13 9.00 $ 117.00 $ 7.00 $ 91.00 26.00 64 9.00 S 576.00 $ 7.00 $ 448.00 128.00 59 21.00 1,239.00 12.00 $ 708.00 531.00 12 $ 55.00 $ 660.00 25.00 $ 300.00 $ 360.00 7 $ 15.00$ 105.00 $ 14.00 98.00 $ 7.00 14 8.00 $ 112.00 $ 4.00 56.00 $ 56.00 7 12.00 $ 84.00 $ 8.00 $ 56.00 $ 28.00 87 $ 15.00 $ 1,305.00 $ 14.00 1,218.00 $ 87.00 70 $ 9.00 $ 630.00 $ 7.00 $ 490.00 $ 140.00 J K CustomerlD Customer SalesRep 315 The Station PJ Helgoth 315 The Station Roberta Cross 233 Bert's Bistro Loraine Schultz 14 Smoke House Roberta Cross 572 Flagstaff House Loraine Schultz 23 Two Bitts Loraine Schultz 6 Pierce Arrow Roberta Cross 95 Mamm'a Pasta Palace Loraine Schultz 846 The Dandelion Loraine Schultz 557 Carmens Loraine Schultz 315 The Station Loraine Schultz 846 The Dandelion Loraine Schultz 557 Carmens Loraine Schultz 572 Flagstaff House PJ Helgoth 14 Smoke House Roberta Cross 14 Smoke House Loraine Schultz 95 Mamm'a Pasta Palace Roberta Cross 95 Mamm'a Pasta Palace Loraine Schultz 14 Smoke House Roberta Cross 23 Two Bitts Loraine Schultz 846 The Dandelion Roberta Cross 95 Mamma Pasta Palace PJ Helgoth 23 Two Bitts Roberta Cross 557 Carmens Loraine Schultz 23 Two Bitts Loraine Schultz 572 Flagstaff House Loraine Schultz 6 Pierce Arrow Loraine Schultz 846 The Dandelion PJ Helgoth 6 Pierce Arrow Roberta Cross 6 Pierce Arrow Roberta Cross 572 Flagstaff House Loraine Schultz 14 Smoke House Loraine Schultz 95 Mamma Pasta Palace PJ Helgoth 23 Two Bitts Loraine Schultz 572 Flagstaff House Loraine Schultz 14 Smoke House Loraine Schultz 846 The Dandelion Loraine Schultz 846 The Dandelion PJ Helgoth 6 Pierce Arrow Loraine Schultz 686 Laudisio Roberta Cross 6 Pierce Arrow Loraine Schultz 23 Two Bitts Roberta Cross 6 Pierce Arrow Loraine Schultz 95 Mamm'a Pasta Palace PJ Helgoth 95 Mamm'a Pasta Palace Loraine Schultz 23 Two Bitts Loraine Schultz 23 Two Bitts Roberta Cross 557 Carmens Roberta Cross 264.00 Name Total Schweitzer Distribution Analysis Question Who is Bob's best customer by total sales? Who is Bob's worst customer by total sales Who is Bob's best customer by profit? Who is Bob's worst customer by profit? What is Bob's best selling product by total sales? What is Bob's worst selling product by total sales? What is Bob's best selling product by profit? What is Bob's worst selling product by profit? Who is Bob's best sales representative by profit? Who is Bob's worst sales representative by profit? What is the best sales representative's best selling product (by total profit)? Who is the best sales representative's best customer (by total profit)? What is the best sales representative's worst selling product (by total profit)? Who is the best sales representative's worst customer (by total profit)? ** Select the name using the drop down list
