A B C D E F G H I J K L M N O 1 2 When shipping products two major costs are incurred: the 3 cost of insuring the item and the actual freight cost. 4 Insurance is dependent on the value of the shipment 5 Quickstove has an agreement with shippers for a fixed 6 rate that is determined by destination state and service 7 level (regular, express or overnite). 8 Freight Cost AZWAL NV | U | CAMI ORICO | HIJ Regular $ 5.00 $ 6.00 $ 5.00 I $ 3.00 $ 5.00 I $ 6.00 $ 6.00 $ 5.00 $ 8.00 Express $ 7.60 $ 8.40 1 $ 7.60 $ 4.70 $ 7.60 $ 8.40 S 8.40 $ 7.60 $ 13.50 Overnite $ 11.60 $ 13.70 $ 11.60 $ 7.80 $ 11.60 $ 13.70 $ 13.70 $ 11.60 $ 17.10 10 OrderId Shipping Insurance Value 1 Class Cost Ship To ProductID Freight Cost Insurance Cost Shipping Total Product Name 111003L AZ 12 1004 WA | 13 | 1007: NV 14 10111 UT 15 1027| CA 16 1041 MT 17 10421 CA 18 1050L OR 19 1058 UT $ 68.80 Express $ 86.00 Express ###### Regular ###### Express S 17.55 lOvernite $ 4.00 lOvernite $ 69.98 Overnite Shipment Insurance $ - $ 0.50 $ 10 $ 1.00 $ 30 $ 2.00 $ 50 $ 2.50 $ 100 $ 3.00 $ 200 $ 4.00 $ 400 $ 5.00 ##### Overnite ISI1518181IZII 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 102 Replacement Fuel Cells 104 Quick Stove Can 1041 Quick Stove Can 1041 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can 102 Replacement Fuel Cells 1041 Quick Stove Can 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 106 Stove In A Can 106 Stove In A Can 1061 Stove In A Can 1061 Stove In A Can 106 Stove In A Can 1061 Stove In A Can ######Express $ 15.68 Regular $ 69.98 Overnite $ 60.80 Regular $34.99 Overnite $ 19.60 Overnite $ 4.00 Overnite $ 8.00 Express $ 8.00 Express $ 38.00 Express $ 34.99 Overnite ###### Regular $ 34.99 Express ###### Overnite $ 34.99 Regular ###### Regular WA MT 64 3 Use the VLOOKUP function in cell G11 to determine the insurance cost. Use the "Value" in cell E11 to lookup the appropriate cost on the "Insurance Cost" reference table (range K11:L18). Make sure to use relative and absolute references appropriately. 23 Copy your formula in cell G11 and paste it down to complete the "Insurance Cost" column of the table. Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references. Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row. 3 Copy your formula in cell H11 and paste it down to complete the "Freight Cost" column 2 Calculate the Invoice "Shipping Total" in cell 111 1 Copy your formula in cell 111 and paste it down to complete the "Shipping Total" column A B C D E F G H I J K L M N O 1 2 When shipping products two major costs are incurred: the 3 cost of insuring the item and the actual freight cost. 4 Insurance is dependent on the value of the shipment 5 Quickstove has an agreement with shippers for a fixed 6 rate that is determined by destination state and service 7 level (regular, express or overnite). 8 Freight Cost AZWAL NV | U | CAMI ORICO | HIJ Regular $ 5.00 $ 6.00 $ 5.00 I $ 3.00 $ 5.00 I $ 6.00 $ 6.00 $ 5.00 $ 8.00 Express $ 7.60 $ 8.40 1 $ 7.60 $ 4.70 $ 7.60 $ 8.40 S 8.40 $ 7.60 $ 13.50 Overnite $ 11.60 $ 13.70 $ 11.60 $ 7.80 $ 11.60 $ 13.70 $ 13.70 $ 11.60 $ 17.10 10 OrderId Shipping Insurance Value 1 Class Cost Ship To ProductID Freight Cost Insurance Cost Shipping Total Product Name 111003L AZ 12 1004 WA | 13 | 1007: NV 14 10111 UT 15 1027| CA 16 1041 MT 17 10421 CA 18 1050L OR 19 1058 UT $ 68.80 Express $ 86.00 Express ###### Regular ###### Express S 17.55 lOvernite $ 4.00 lOvernite $ 69.98 Overnite Shipment Insurance $ - $ 0.50 $ 10 $ 1.00 $ 30 $ 2.00 $ 50 $ 2.50 $ 100 $ 3.00 $ 200 $ 4.00 $ 400 $ 5.00 ##### Overnite ISI1518181IZII 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 102 Replacement Fuel Cells 104 Quick Stove Can 1041 Quick Stove Can 1041 Quick Stove Can 102 Replacement Fuel Cells 104 Quick Stove Can 102 Replacement Fuel Cells 1041 Quick Stove Can 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 102 Replacement Fuel Cells 106 Stove In A Can 106 Stove In A Can 1061 Stove In A Can 1061 Stove In A Can 106 Stove In A Can 1061 Stove In A Can ######Express $ 15.68 Regular $ 69.98 Overnite $ 60.80 Regular $34.99 Overnite $ 19.60 Overnite $ 4.00 Overnite $ 8.00 Express $ 8.00 Express $ 38.00 Express $ 34.99 Overnite ###### Regular $ 34.99 Express ###### Overnite $ 34.99 Regular ###### Regular WA MT 64 3 Use the VLOOKUP function in cell G11 to determine the insurance cost. Use the "Value" in cell E11 to lookup the appropriate cost on the "Insurance Cost" reference table (range K11:L18). Make sure to use relative and absolute references appropriately. 23 Copy your formula in cell G11 and paste it down to complete the "Insurance Cost" column of the table. Use the HLOOKUP function in cell H11 to calculate freight cost based on the shipping class and state to which the shipment is going. Freight Cost can be referenced on the "Freight Cost" lookup table (range E5:N8). Be sure to use appropriate relative and absolute cell references. Hint: You will need to use a function within the HLOOKUP function to determine the appropriate row. 3 Copy your formula in cell H11 and paste it down to complete the "Freight Cost" column 2 Calculate the Invoice "Shipping Total" in cell 111 1 Copy your formula in cell 111 and paste it down to complete the "Shipping Total" column