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

Question

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

Accepted Answer

The Answer is in the image, click to view ...

Question

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

Step by Step Solution

Step: 1

Get Instant Access to Expert-Tailored Solutions

Step: 2

Step: 3

Ace Your Homework with AI

Recommended Textbook for

Advances In Knowledge Discovery In Databases

Students also viewed these Databases questions

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question

Question