You are a sales rep for a sporting equipment vendor. To assist you in pricing your golf ball product you have setup a worksheet and will now complete it. Golf balls are sold by the 3 can canisters ($/each). Canister pricing varies based on the total number ordered. The shipping costs vary based on the method of shipment as listed here. Quantity (in units) Under 36 36-95 96-179 180 or more $/each $2.85 2.63 2.27 2.00 Shipping Method Rail Truck Ship Customer arranges shipping $/each $0.19 0.22 0.14 0.00 The Golf workbook containing lookup tables and an order form is in a worksheet named Golf Balls as seen here. In these steps, you need to complete this worksheet using the appropriate lookup functions to calculate the total prices, total shipping charges, and grand totals. Complete the following: Pricing Information for Golf Balls TheZone Equipment Division Unit Pricing Shipping Costs Shipping Discount LOS Pace Eppingi 1 Unik Price Total Price Unit Total Shipping Shipping Shipping Discount Grand Total Order CustID Shipping Ouantity 101 RON Truck 12H4ruck 103 XALA3 Bow 104 PESShip 105 P Customer 106 ENT37 Rai RS 1. In the Golf Balls worksheet, complete the Unit Pricing lookup table to include the units and corresponding prices that your company charges for golf balls based on the pricing info given in the table in the problem description. 2. Complete the Shipping Costs table to list the appropriate unit shipping charges. 3. In cell E14, write a formula using a LOOKUP function to calculate the unit price for this order based on the quantity ordered. Write the formula so that it can be copied down the column, and then copy the formula into cells E15:E19. 4. In cell F14, calculate the total price of this order (excluding shipping). Write the formula so that it can be copied down the column, and then copy the formula into cells F15:F19. 5. In cell G14, calculate the unit shipping charge based on the shipping method. Write the formula so that it can be copied down the column, and then copy the formula into cells G15:19. Correct data entry errors, as necessary. 6. In cell H14, calculate the total shipping cost. Write the formula so that it can be copied down the column, and then copy the formula into cells H15:H19. Ignore any inconsistent formula errors. 7. In cell 114, calculate the shipping discount, using Shipping Discount 2, based on the total price (column F). The discounted value will be the corresponding percentage times the total shipping cost previously calculated. Write the formula so that it can be copied down the column, and then copy the formula into cells 115:119. 8. In cell J14, calculate the grand total for this item. Write the formula so that it can be copied down the column, and then copy the formula into cells J15:J19. Pricing Information for Golf Balls The Zone Equipment Division Unit Pricing Golf Balls Qty Shipping Costs Sleach = Method Customer Rad Ship Truck Shipping Discount Total Price $0 Shipping Discount 1 0 % Shipping Discount 2 0 % $200 20% 15% $500 25% 10% Unit Price Total Price Unit To Shipping Shipping Shipping Discount Grand Total Order CustID 101 RG339 102 HM394 103 XA843 104 PK423 105 PK880 106 EN737 Shipping Quantity Truck 601 Truck Ship 120 Ship 2160 Customer 360 Ral 120
