Excel Chapter 5 Prepare Case - Pro Shop Sales Analysis
abparsley
Parsley_Excel_Ch05_Prepare_ProShopSales.xlsxEmployeeAnalysis
| Employees | ||||||||||
| Golf Trainer Certification | Daily Sales Goals | |||||||||
| EMP-ID | Name | Hours | Status | EMP-00076 | EMP-00038 | EMP-00024 | EMP-00015 | EMP-00026 | ||
| EMP-00076 | Ramal Hample | 65 | Accessories | $ 300.00 | $ 400.00 | $ 200.00 | $ 200.00 | $ 100.00 | ||
| EMP-00038 | Pat Tyner | 35 | Clothing | $ 200.00 | $ 200.00 | $ 300.00 | $ 300.00 | $ 300.00 | ||
| EMP-00024 | Tyrone Thurman | 58 | Clubs | $ 500.00 | $ 500.00 | $ 700.00 | $ 700.00 | $ 300.00 | ||
| EMP-00015 | Jerrold Wilcox | 67 | Shoes | $ 150.00 | $ 150.00 | $ 250.00 | $ 250.00 | $ 100.00 | ||
| EMP-00026 | Christy Yammel | 25 | Total | $ 1,150.00 | $ 1,250.00 | $ 1,450.00 | $ 1,450.00 | $ 800.00 | ||
| POS Training | CommissionRates | |||||||||
| EMP-ID | Name | Hours | Level | Revenue | $ - 0 | $ 2,500 | $ 3,500 | $ 4,500 | ||
| EMP-00076 | Ramal Hample | 45 | Rate | 5% | 7% | 9% | 11% | |||
| EMP-00038 | Pat Tyner | 35 | ||||||||
| EMP-00024 | Tyrone Thurman | 30 | ||||||||
| EMP-00015 | Jerrold Wilcox | 20 | ||||||||
| EMP-00026 | Christy Yammel | 25 | ||||||||
| Category | Clubs | |||||||||
| Employee Daily Commission | Employee Sales Goals | |||||||||
| EMP-ID | Job Title | TotalRevenue | Commission | EMP-ID | Name | Sales Goal | Row Num | Col Num | ||
| EMP-00076 | Asst Manager | $ 3,532.63 | EMP-00076 | Ramal Hample | ||||||
| EMP-00038 | Asst Manager | $ 4,609.28 | EMP-00038 | Pat Tyner | ||||||
| EMP-00024 | Associate | $ 2,618.79 | EMP-00024 | Tyrone Thurman | ||||||
| EMP-00015 | Associate | $ 4,511.17 | EMP-00015 | Jerrold Wilcox | ||||||
| EMP-00026 | Associate | $ 824.11 | EMP-00026 | Christy Yammel |
CustomerAnalysis
| Customer Analysis | ||||||||
| CustID | CustomerName | Member? | Volume | TotalSales | Value | Coupon | Coupons | |
| CUST-6218 | Samuel Tapp | No | 11 | $ 714.85 | 10% Off | |||
| CUST-508 | Arthur Crocker | No | 13 | $ 661.61 | 15% Off | |||
| CUST-8820 | Christine Cardinal | Yes | 16 | $ 569.25 | 20% Off | |||
| CUST-1340 | John Ketcham | No | 19 | $ 1,338.75 | ||||
| CUST-704 | Krista Sun | Yes | 9 | $ 212.29 | ||||
| CUST-7076 | Karen Corcoran | No | 8 | $ 168.85 | ||||
| CUST-1285 | Doug Vandenburg | No | 5 | $ 275.70 | ||||
| CUST-2446 | Dale Foxhoven | No | 11 | $ 1,558.51 | ||||
| CUST-8440 | Sherry Callahan | No | 6 | $ 163.87 | ||||
| CUST-3816 | Lula Claar | Yes | 12 | $ 2,090.44 | ||||
| CUST-2588 | Peg Moore | No | 3 | $ 61.37 | ||||
| CUST-3267 | Stephen Ittner | No | 7 | $ 2,156.82 | ||||
| CUST-5600 | Raymond Weber | Yes | 12 | $ 543.75 | ||||
| Best Customer (most amount spent) | ||||||||
| CustID | ||||||||
| CustomerName | ||||||||
| Volume | ||||||||
| TotalSales |
Transactions
| Transaction Analysis | |||||||||||||||
| Coupons | |||||||||||||||
| CouponCode | T453X | C384R | T382P | ||||||||||||
| Discount | 15% | 10% | 5% | ||||||||||||
| CouponNotes | Online Travel Promotion | Travel Agent Promotion | Local Magazine Promotion | ||||||||||||
| Reward Program Discounts | |||||||||||||||
| TransID | ItemID | PaymentType | Quantity | CouponCode | ClubMember? | PromoItem? | GrossRevenue | MemberDisc | Coupon | CashDisc | NetRevenue | TotalDiscounts | Rewards1 | Rewards2 | |
| P000100 | C884P23 | Cash | 3 | T453X | No | No | $ 171.00 | 15% | 5% | $ 136.80 | $ 34.20 | ||||
| P000101 | T822Z48 | Cash | 2 | No | Yes | $ 65.90 | $ 65.90 | $ - 0 | |||||||
| P000102 | F232P37 | Apple Pay | 1 | Yes | No | $ 129.00 | 7% | $ 119.97 | $ 9.03 | ||||||
| P000103 | C583K29 | Apple Pay | 3 | No | Yes | $ 177.00 | $ 177.00 | $ - 0 | |||||||
| P000104 | F232P37 | Cash | 3 | T453X | No | No | $ 387.00 | 15% | 5% | $ 309.60 | $ 77.40 | ||||
| P000105 | F375P67 | Cash | 3 | T453X | Yes | No | $ 46.35 | 7% | 15% | $ 36.15 | $ 10.20 | ||||
| P000106 | X740Q29 | Apple Pay | 1 | Yes | Yes | $ 453.00 | 7% | $ 421.29 | $ 31.71 | ||||||
| P000107 | T981Q11 | Cash | 1 | No | No | $ 12.00 | $ 12.00 | $ - 0 | |||||||
| P000108 | C884P23 | Cash | 2 | No | No | $ 114.00 | 5% | $ 108.30 | $ 5.70 | ||||||
| P000109 | X487P48 | Apple Pay | 3 | C384R | No | Yes | $ 1,257.00 | 10% | $ 1,131.30 | $ 125.70 | |||||
| P000110 | R483P24 | Apple Pay | 1 | C384R | No | No | $ 2.00 | 10% | $ 1.80 | $ 0.20 | |||||
| P000111 | R483P24 | Card | 1 | T453X | Yes | No | $ 2.00 | 7% | 15% | $ 1.56 | $ 0.44 | ||||
| P000112 | X349R39 | Apple Pay | 3 | T382P | No | Yes | $ 149.85 | 5% | $ 142.36 | $ 7.49 | |||||
| P000113 | F833K19 | Apple Pay | 3 | T382P | No | No | $ 17.85 | 5% | $ 16.96 | $ 0.89 | |||||
| P000114 | R239T57 | Card | 2 | T453X | No | No | $ 194.00 | 15% | $ 164.90 | $ 29.10 | |||||
| P000115 | C583K29 | Card | 1 | No | Yes | $ 59.00 | $ 59.00 | $ - 0 | |||||||
ShippingCosts
| Shipping Costs | ||||||||
| Priority Mail Rates | ||||||||
| Weight (lbs) | Zone 1 | Zone 2 | Zone 3 | Zone 4 | Zone 5 | Shipping Method | Priority | |
| 0.5 | $6.35 | $6.35 | $6.79 | $6.89 | $6.98 | Weight (lbs) | 5.5 | |
| 2 | $6.35 | $6.35 | $6.79 | $6.89 | $6.98 | Zone | Zone 2 | |
| 4 | $6.89 | $6.89 | $7.03 | $7.18 | $7.91 | Shipping Costs | ||
| 6 | $7.29 | $7.29 | $8.53 | $9.42 | $13.77 | |||
| 8 | $8.24 | $8.24 | $9.63 | $10.99 | $16.84 | |||
| 10 | $8.95 | $8.95 | $10.18 | $11.12 | $19.51 | |||
| Express Mail Rates | ||||||||
| Weight (lbs) | Zone 1 | Zone 2 | Zone 3 | Zone 4 | Zone 5 | |||
| 0.5 | $7.62 | $7.62 | $8.15 | $8.27 | $8.38 | |||
| 2 | $7.62 | $7.62 | $8.15 | $8.27 | $8.38 | |||
| 4 | $8.27 | $8.27 | $8.44 | $8.62 | $9.49 | |||
| 6 | $8.75 | $8.75 | $10.24 | $11.30 | $16.52 | |||
| 8 | $9.89 | $9.89 | $11.56 | $13.19 | $20.21 | |||
| 10 | $10.74 | $10.74 | $12.22 | $13.34 | $23.41 |
Products
| Product List | |||
| ItemID | Description | Category | Retail Price |
| X349R39 | Windbreaker (Resort) | Clothing | $ 49.95 |
| X487P48 | Windren Pro (right-handed) | Clubs | $ 419.00 |
| T981Q11 | Resort golf balls | Accessories | $ 12.00 |
| C584T23 | Trimble Comforts | Shoes | $ 49.00 |
| C583K29 | Trimbel Striders | Shoes | $ 59.00 |
| R483P24 | Resort Tees | Accessories | $ 2.00 |
| F833K19 | Resort divot key chain | Accessories | $ 5.95 |
| F375P67 | Grabber golf gloves | Accessories | $ 15.45 |
| C483T89 | Resort cap | Clothing | $ 14.95 |
| C492R32 | Resort polo shirt | Clothing | $ 39.95 |
| C581T49 | golf shoe insert | Accessories | $ 11.39 |
| C557P29 | golf button, shirt | Clothing | $ 32.95 |
| T822Z48 | golf button, shirt | Clothing | $ 32.95 |
| X733R14 | golf button, shirt | Clothing | $ 32.95 |
| X740Q29 | Windren pro (left-handed) | Clubs | $ 453.00 |
| F232P37 | Resort golf bag (elite) | Accessories | $ 129.00 |
| C884P23 | Resort golf bag (carryall) | Accessories | $ 57.00 |
| R239T57 | Resort golf easy-go pull cart | Accessories | $ 97.00 |
