Question

1 Approved Answer

Posted on Sep 26, 2024

12 2.4 The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On

12 2.4 The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the item sold is not Accessories, otherwise return No. Use the fill handle to copy the formula down through G30. 13 2.4 To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the EmployeeAnalysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the EmployeeAnalysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27. In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:K9 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct values. Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their customerid, CustomerName, SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the Customer Analysis worksheet, in cell B20, enter an INDEX function that will retrieve the Customers of the customer with the highest TotalSales from the array A4:G16. Use the fill handle to copy the formula down through B23. Shipping costs vary depending on method of shipping and the zone number of the destination. Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet, create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table_array so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved. 14 3 15 3 A B D E F G H K L M N O 1 Employees Daily Sales Goals $ Accessories Clothing Clubs EMP-00076 300.00 $ 200.00 $ 500.00 $ $ $ EMP-00038 EMP-00024 EMP-00015 EMP-00026 400.00 $ 200.00 $ 200.00 $ 100.00 200.00 $ 300.00 $ 300.00 $ 300.00 500.00 $ 700.00 $ 700.00 $ 300.00 150.00 $ 250.00 $ 250.00 $ 100.00 1,250.00 $ 1,450.00 $ 1,450.00 $ 800.00 Shoes $ 150.00 $ Total $ 1,150.00 $ Commission Rates $ $ Revenue Rate 2,500 $ 7% 3,500 $ 9% 4,500 11% 5% 2 3 Golf Trainer Certification 4 EMP-ID Name Hours Status 5 EMP-00076 Ramal Hample 65 Certified 6 EMP-00038 Pat Tyner 35 Not Certified 7 EMP-00024 Tyrone Thurmar 58 Certified 8 EMP-00015 Jerrold Wilcox 67 Certified 9 EMP-00026 Christy Yammel 25 Not Certified 10 11 12 POS Training 13 EMP-ID Name Hours Level 14 EMP-00076 Ramal Hample 45 Expert 15 EMP-00038 Pat Tyner 35 Proficient 16 EMP-00024 Tyrone Thurmar 30 Proficient 17 EMP-00015 Jerrold Wilcox 20 Beginner 18 EMP-00026 Christy Yammel 25 Proficient 19 20 21 Employee Daily Commission 22 EMP-ID Job Title TotalRevenue Commission 23 EMP-00076 Asst Manager $ 3,532.63 176.63156 24 EMP-00038 Asst Manager $ 4,609.28 230.4639 25 EMP-00024 Associate $ 2,618.79 183.31495 26 EMP-00015 Associate $ 4,511.17 496.228766 27 EMP-00026 Associate $ 824.11 41.205575 28 29 30 31 32 Employee Analysis Customer Analysis Transactions Clubs Sales Goal Row Num Col Num Category Employee Sales Goals EMP-ID Name EMP-00076 Ramal Hample EMP-00038 Pat Tyner EMP-00024 Tyrone Thurman EMP-00015 Jerrold Wilcox EMP-00026 Christy Yammel Shipping Costs Products + J10 X =HLOOKUP(E10,$A$4:$D$6,2,0) B D E F G H I K L M N O P Q R Reward Program Discounts Rewards1 Rewards2 0.1 0.1 0.1 0.1 0.1 1 Transaction Analysis 2 3 Coupons 4 CouponCode T453x C384R T382P 5 Discount 15% 10% 5% Local Online Travel Travel Agent Magazine 6 Coupon Notes Promotion Promotion Promotion 7 8 9 TransID ItemID PaymentType Quantity CouponCode Club Member? Promoltem? GrossRevenue MemberDisc Coupon Cash Disc 10 P000100 C884P23 Cash 3 T453X No No $ 171.00 15% 11 PO00101 T822248 Cash 2 No Yes $ 65.90 12 PO00102 F232P37 Apple Pay 1 Yes No $ 129.00 7% 13 P000103 C583K29 Apple Pay 3 No Yes $ 177.00 14 P000104 F232P37 Cash 3 T453X No No $ 387.00 15% 15 PO00105 F375P67 Cash 3 T453X Yes No $ 46.35 7% 15% 16 P000106 X740029 Apple Pay 1 Yes Yes $ 453.00 7% 17 P000107 T981Q11 Cash 1 No No $ 12.00 18 PO00108 C884P23 Cash 2 No No $ 114.00 19 P000109 X487248 Apple Pay 3 C384R No Yes $ 1,257.00 10% 20 PO00110 R483P24 Apple Pay 1 C384R No No $ 2.00 10% 21 P000111 R483P24 Card 1 T453x Yes No $ 2.00 7% 15% 22 P000112 X349R39 Apple Pay 3T382P No Yes $ 149.85 5% 23 P000113 F833K19 Apple Pay 3T382P No No $ 17.85 5% 24 PO00114 R239157 Card 2 1453X No No $ 194.00 15% 25 P000115 C583K29 Card 1 No Yes $ 59.00 26 7% 27 7% 28 7% 29 7% 30 7% 31 32 33 34 EmployeeAnalysis CustomerAnalysis Transactions Shipping Costs Products + NetRevenue TotalDiscounts 5% $ 136.80 $ 34.20 $ 65.90$ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 S 77.40 $ 36.15 $ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30 $ 125.70 $ 1.80 $ 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90 $ 29.10 $ 59.00 $ 0.1 0.1 0.1 M N25 X fx B D E F G H J K L M N o P R 1 Shipping Costs 2 3 4 Weight (lbs) 0.5 5 Priority 5.5 Zone 2 6 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6.35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 Shipping Method Weight (lbs) Zone Shipping Costs 4 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 6 UT 8 10 10 11 12 13 14 Weight (lbs) 15 0.5 16 17 924689 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 4 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23.41 18 6 19 20 21 22 23 24 25 | 26 27 28 29 30 31 32 33 EmployeeAnalysis CustomerAnalysis Transactions Shipping Costs Products # A L27 : X fx E F G H I J K M N. o Coupons 10% Off 15% Off 20% Off B 1 Customer Analysis 2 3 CustID CustomerName 4. CUST-6218 Samuel Tapp 5 CUST-508 Arthur Crocker 6 CUST-8820 Christine Cardinal 7 CUST-1340 John Ketcham 8 CUST-704 Krista Sun 9 CUST-7076 Karen Corcoran 10 CUST-1285 Doug Vandenburg 11 CUST-2446 Dale Foxhoven 12 CUST-8440 Sherry Callahan 13 CUST-3816 Lula Claar 14 CUST-2588 Peg Moore 15 CUST-3267 Stephen Ittner 16 CUST-5600 Raymond Weber 17 18 19 Best Customer (most amount spent) 20 CustID 21 CustomerName 22 Volume 23 TotalSales 24 25 Member? No No Yes No Yes No No No No Yes Volume TotalSales Value 11 $ 714.85 Low 13 $ 661.61 High 16 $ 569.25 High 19 $ 1,338.75 High 9 $ 212.29 Low 8 $ 168.85 Low 5 $ 275.70 Low 11 $ 1,558.51 High 6 $ 163.87 Low 12 $ 2,090.44 High 3 $ 61.37 Low 7 $ 2,156.82 High 12 $ 543.75 Low Coupon 10% Off 15% Off 20% Off 15% Off 10% Off 10% Off 10% Off 15% Off 10% Off 20% Off 10% Off 15% Off 10% Off No No Yes 26 27 28 29 30 31 32 33 Employee Analysis Customer Analysis Transactions Shipping Costs Products 12 2.4 The Pro Shop sells a variety of items from numerous categories. Aleeta would like to promote all items that are NOT accessories. On the Transactions worksheet, in cell G10, replace the static value by entering a formula that will return Yes if the category of the item sold is not Accessories, otherwise return No. Use the fill handle to copy the formula down through G30. 13 2.4 To provide incentives for employees to sell more goods, Aleeta has set sales goals for each employee for items belonging to various categories. On the EmployeeAnalysis worksheet, you will use an INDEX function to retrieve the correct sales goal based on the Employeeld and the desired Category. Because INDEX functions need to be told what row and column number to go to, you will use MATCH functions to do the counting an determine the correct row and column numbers for the INDEX to use. On the EmployeeAnalysis worksheet, in cell J23, enter a MATCH function that will return the row number of whichever category is selected in cell H20. Use the fill handle to copy the formula down through J27. In cell K23, enter a MATCH function that will return the column number of the Employeeld in cell F23. Use the fill handle to copy the formula down through K27. In cell H23, enter an INDEX function, using the range G5:K9 as the array, and referencing the values in J23 and K23 for the row and column numbers. Use the fill handle to copy the formula down through H27. Change the category in cell H20 to Accessories to make sure the function retrieves the correct values. Aleeta would like to reward the customer who spends the most money at the Pro Shop and needs to be able to easily retrieve their customerid, CustomerName, SalesVolume, and TotalSales for the customer with the largest TotalSales value. On the Customer Analysis worksheet, in cell B20, enter an INDEX function that will retrieve the Customers of the customer with the highest TotalSales from the array A4:G16. Use the fill handle to copy the formula down through B23. Shipping costs vary depending on method of shipping and the zone number of the destination. Creating a dynamic retrieval function leveraging the INDIRECT function can make it easily to calculate shipping costs for customers. On the Shipping Costs worksheet, create a Priority named range for cells A5:F10 and then create an Express named range for cells A15:F20. In cell 17, enter a VLOOKUP function to retrieve the correct shipping costs based on the weight and zone. Use the INDIRECT function for the table_array so that the customer can choose Express or Priority shipping. Change the shipping method from Priority to Express to ensure the correct shipping cost is retrieved. 14 3 15 3 A B D E F G H K L M N O 1 Employees Daily Sales Goals $ Accessories Clothing Clubs EMP-00076 300.00 $ 200.00 $ 500.00 $ $ $ EMP-00038 EMP-00024 EMP-00015 EMP-00026 400.00 $ 200.00 $ 200.00 $ 100.00 200.00 $ 300.00 $ 300.00 $ 300.00 500.00 $ 700.00 $ 700.00 $ 300.00 150.00 $ 250.00 $ 250.00 $ 100.00 1,250.00 $ 1,450.00 $ 1,450.00 $ 800.00 Shoes $ 150.00 $ Total $ 1,150.00 $ Commission Rates $ $ Revenue Rate 2,500 $ 7% 3,500 $ 9% 4,500 11% 5% 2 3 Golf Trainer Certification 4 EMP-ID Name Hours Status 5 EMP-00076 Ramal Hample 65 Certified 6 EMP-00038 Pat Tyner 35 Not Certified 7 EMP-00024 Tyrone Thurmar 58 Certified 8 EMP-00015 Jerrold Wilcox 67 Certified 9 EMP-00026 Christy Yammel 25 Not Certified 10 11 12 POS Training 13 EMP-ID Name Hours Level 14 EMP-00076 Ramal Hample 45 Expert 15 EMP-00038 Pat Tyner 35 Proficient 16 EMP-00024 Tyrone Thurmar 30 Proficient 17 EMP-00015 Jerrold Wilcox 20 Beginner 18 EMP-00026 Christy Yammel 25 Proficient 19 20 21 Employee Daily Commission 22 EMP-ID Job Title TotalRevenue Commission 23 EMP-00076 Asst Manager $ 3,532.63 176.63156 24 EMP-00038 Asst Manager $ 4,609.28 230.4639 25 EMP-00024 Associate $ 2,618.79 183.31495 26 EMP-00015 Associate $ 4,511.17 496.228766 27 EMP-00026 Associate $ 824.11 41.205575 28 29 30 31 32 Employee Analysis Customer Analysis Transactions Clubs Sales Goal Row Num Col Num Category Employee Sales Goals EMP-ID Name EMP-00076 Ramal Hample EMP-00038 Pat Tyner EMP-00024 Tyrone Thurman EMP-00015 Jerrold Wilcox EMP-00026 Christy Yammel Shipping Costs Products + J10 X =HLOOKUP(E10,$A$4:$D$6,2,0) B D E F G H I K L M N O P Q R Reward Program Discounts Rewards1 Rewards2 0.1 0.1 0.1 0.1 0.1 1 Transaction Analysis 2 3 Coupons 4 CouponCode T453x C384R T382P 5 Discount 15% 10% 5% Local Online Travel Travel Agent Magazine 6 Coupon Notes Promotion Promotion Promotion 7 8 9 TransID ItemID PaymentType Quantity CouponCode Club Member? Promoltem? GrossRevenue MemberDisc Coupon Cash Disc 10 P000100 C884P23 Cash 3 T453X No No $ 171.00 15% 11 PO00101 T822248 Cash 2 No Yes $ 65.90 12 PO00102 F232P37 Apple Pay 1 Yes No $ 129.00 7% 13 P000103 C583K29 Apple Pay 3 No Yes $ 177.00 14 P000104 F232P37 Cash 3 T453X No No $ 387.00 15% 15 PO00105 F375P67 Cash 3 T453X Yes No $ 46.35 7% 15% 16 P000106 X740029 Apple Pay 1 Yes Yes $ 453.00 7% 17 P000107 T981Q11 Cash 1 No No $ 12.00 18 PO00108 C884P23 Cash 2 No No $ 114.00 19 P000109 X487248 Apple Pay 3 C384R No Yes $ 1,257.00 10% 20 PO00110 R483P24 Apple Pay 1 C384R No No $ 2.00 10% 21 P000111 R483P24 Card 1 T453x Yes No $ 2.00 7% 15% 22 P000112 X349R39 Apple Pay 3T382P No Yes $ 149.85 5% 23 P000113 F833K19 Apple Pay 3T382P No No $ 17.85 5% 24 PO00114 R239157 Card 2 1453X No No $ 194.00 15% 25 P000115 C583K29 Card 1 No Yes $ 59.00 26 7% 27 7% 28 7% 29 7% 30 7% 31 32 33 34 EmployeeAnalysis CustomerAnalysis Transactions Shipping Costs Products + NetRevenue TotalDiscounts 5% $ 136.80 $ 34.20 $ 65.90$ $ 119.97 $ 9.03 $ 177.00 $ 5% $ 309.60 S 77.40 $ 36.15 $ 10.20 $ 421.29 $ 31.71 $ 12.00 $ 5% $ 108.30 $ 5.70 $ 1,131.30 $ 125.70 $ 1.80 $ 0.20 $ 1.56 $ 0.44 $ 142.36 $ 7.49 $ 16.96 $ 0.89 $ 164.90 $ 29.10 $ 59.00 $ 0.1 0.1 0.1 M N25 X fx B D E F G H J K L M N o P R 1 Shipping Costs 2 3 4 Weight (lbs) 0.5 5 Priority 5.5 Zone 2 6 2 Priority Mail Rates Zone 2 Zone 3 $6.35 $6.79 $6.35 $6.79 $6.89 $7.03 $7.29 $8.53 $8.24 $9.63 $8.95 $10.18 Zone 1 $6.35 $6.35 $6.89 $7.29 $8.24 $8.95 Shipping Method Weight (lbs) Zone Shipping Costs 4 Zone 4 $6.89 $6.89 $7.18 $9.42 $10.99 $11.12 Zone 5 $6.98 $6.98 $7.91 $13.77 $16.84 $19.51 6 UT 8 10 10 11 12 13 14 Weight (lbs) 15 0.5 16 17 924689 Express Mail Rates Zone 2 Zone 3 $7.62 $8.15 $7.62 $8.15 $8.27 $8.44 $8.75 $10.24 $9.89 $11.56 $10.74 $12.22 Zone 1 $7.62 $7.62 $8.27 $8.75 $9.89 $10.74 4 Zone 4 $8.27 $8.27 $8.62 $11.30 $13.19 $13.34 Zone 5 $8.38 $8.38 $9.49 $16.52 $20.21 $23.41 18 6 19 20 21 22 23 24 25 | 26 27 28 29 30 31 32 33 EmployeeAnalysis CustomerAnalysis Transactions Shipping Costs Products # A L27 : X fx E F G H I J K M N. o Coupons 10% Off 15% Off 20% Off B 1 Customer Analysis 2 3 CustID CustomerName 4. CUST-6218 Samuel Tapp 5 CUST-508 Arthur Crocker 6 CUST-8820 Christine Cardinal 7 CUST-1340 John Ketcham 8 CUST-704 Krista Sun 9 CUST-7076 Karen Corcoran 10 CUST-1285 Doug Vandenburg 11 CUST-2446 Dale Foxhoven 12 CUST-8440 Sherry Callahan 13 CUST-3816 Lula Claar 14 CUST-2588 Peg Moore 15 CUST-3267 Stephen Ittner 16 CUST-5600 Raymond Weber 17 18 19 Best Customer (most amount spent) 20 CustID 21 CustomerName 22 Volume 23 TotalSales 24 25 Member? No No Yes No Yes No No No No Yes Volume TotalSales Value 11 $ 714.85 Low 13 $ 661.61 High 16 $ 569.25 High 19 $ 1,338.75 High 9 $ 212.29 Low 8 $ 168.85 Low 5 $ 275.70 Low 11 $ 1,558.51 High 6 $ 163.87 Low 12 $ 2,090.44 High 3 $ 61.37 Low 7 $ 2,156.82 High 12 $ 543.75 Low Coupon 10% Off 15% Off 20% Off 15% Off 10% Off 10% Off 10% Off 15% Off 10% Off 20% Off 10% Off 15% Off 10% Off No No Yes 26 27 28 29 30 31 32 33 Employee Analysis Customer Analysis Transactions Shipping Costs Products