A C D E F G J K L M N O 2 When shipping products two major costs are incurred: the cost of insuring the item and the actual freight cost. Insurance is dependent on the value of the shipment 3 Freight Cost 4 AZ WA NV UT CA MT OR CO HI 5 Quickstove has an agreement with shippers for a fixed rate that is determined by destination state and service level 5.00 $ 3.00 5.00 Regular Express 7.60 8.40 Overnite 11.60 5.00 6.00 6.00 6.00 5.00 8.00 7regular, express or overnite) 7.60 4.70 7.60 8.40 8.40 7.60 | 13.50 11.60 7.80 11.60 13.70 13.70 |11.60 13.70 17.10 8 9 Shippin Insuran Freight Shippi Product Insurance Cost OrderID Ship To ID Product Name Value q Classce Cost Cost 10 ng Shipme Insuran $68.80 Express $86.00 Express #####Reqular #####Express 17,55 Overnite 4.00 Overnite $69.98 Overnite #####Overnite ## Express $15.68 Regular 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 Quick Stove Can 1003 AZ 11 nt ce 1004 WA 0.50 12 10 1.00 30 2.00 50 2.50 1007 13 1011 UT $ 14 1027 CA $ 15 1041 MT $ 100 3.00 16 $200 4.00 1042 CA 17 1050 OR 400 5.00 18 1058 UT 19 102 Replacement Fuel Cells 1059 AZ 20 104 Quick Stove Can 1060 WA $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 Reqular #####Regular $37,85 Overnite 10.00 Express $69.98 EMpress $47.50 Regular 19.60 Express $31.54 Express 19.95 Overnite 21 .. . 102 Replacement Fuel Cells 1062 UT 22 104 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 106 Stove In A Can 106 Stove In A Can 106 Stove In A Can 106 Stove In A Can 108QuickStove Fuel Disk 107 SIAC Fuel Cel 106 Stove In A Can 107 SIAC Fuel Cell 107 SIAC Fuel Cell 108 QuickStove Fuel Disk 101 QuickStove Can 101 QuickStove Can 101 QuickStove Can 108QuickStove Fuel Disk 101 QuickStove Can 101 QuickStove Can 101 QuickStove Can 110 Firebox Bundle 110 Firebox Bundle 101 QuickStove Can 101 QuickStove Can 1073 CO 23 1074 CO 24 1088 WA 25 1094 26 1109 HI 27 1120 OR 28 1255 OR 29 1262 UT 30 WA 1265 31 1267 CA 32 MT 1268 33 1364 AZ 34 UT 1417 35 1629 CA 36 1635 CO 37 1661 AZ 38 1666 OR 39 1677 WA 40 1686 CA 41 1701 CO 19.95 Overnite 19.95 Overnite 47.31 Express $39.90 Overnite $78.20 Overnite $78.20 EHpress #####Reqular $87.75 Express $78.20 Overnite $78.20 Overnite $49.90 Express $24.95 Regular 42 1708 AZ 43 1712 AZ 44 1718 OR 45 UT 1720 46 1725 WA 47 1733 CA 48 1746 CO 49 1748 50 1776 MT 51 . . 114 Cube Stove+2 Fuel Disks WA 1797 52 114 Cube Stove 2 Fuel Disks 1814 AZ 53 T 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 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. Copy your formula in cell H11 and paste it down to complete the "Freight Cost" column Calculate the Invoice "Shipping Total" in cell I11 Copy your formula in cell I11 and paste it down to complete the "Shipping Total" column A C D E F G J K L M N O 2 When shipping products two major costs are incurred: the cost of insuring the item and the actual freight cost. Insurance is dependent on the value of the shipment 3 Freight Cost 4 AZ WA NV UT CA MT OR CO HI 5 Quickstove has an agreement with shippers for a fixed rate that is determined by destination state and service level 5.00 $ 3.00 5.00 Regular Express 7.60 8.40 Overnite 11.60 5.00 6.00 6.00 6.00 5.00 8.00 7regular, express or overnite) 7.60 4.70 7.60 8.40 8.40 7.60 | 13.50 11.60 7.80 11.60 13.70 13.70 |11.60 13.70 17.10 8 9 Shippin Insuran Freight Shippi Product Insurance Cost OrderID Ship To ID Product Name Value q Classce Cost Cost 10 ng Shipme Insuran $68.80 Express $86.00 Express #####Reqular #####Express 17,55 Overnite 4.00 Overnite $69.98 Overnite #####Overnite ## Express $15.68 Regular 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 100 QuickStove 102 Replacement Fuel Cells 104 Quick Stove Can 104 Quick Stove Can 104 Quick Stove Can 1003 AZ 11 nt ce 1004 WA 0.50 12 10 1.00 30 2.00 50 2.50 1007 13 1011 UT $ 14 1027 CA $ 15 1041 MT $ 100 3.00 16 $200 4.00 1042 CA 17 1050 OR 400 5.00 18 1058 UT 19 102 Replacement Fuel Cells 1059 AZ 20 104 Quick Stove Can 1060 WA $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 Reqular #####Regular $37,85 Overnite 10.00 Express $69.98 EMpress $47.50 Regular 19.60 Express $31.54 Express 19.95 Overnite 21 .. . 102 Replacement Fuel Cells 1062 UT 22 104 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 106 Stove In A Can 106 Stove In A Can 106 Stove In A Can 106 Stove In A Can 108QuickStove Fuel Disk 107 SIAC Fuel Cel 106 Stove In A Can 107 SIAC Fuel Cell 107 SIAC Fuel Cell 108 QuickStove Fuel Disk 101 QuickStove Can 101 QuickStove Can 101 QuickStove Can 108QuickStove Fuel Disk 101 QuickStove Can 101 QuickStove Can 101 QuickStove Can 110 Firebox Bundle 110 Firebox Bundle 101 QuickStove Can 101 QuickStove Can 1073 CO 23 1074 CO 24 1088 WA 25 1094 26 1109 HI 27 1120 OR 28 1255 OR 29 1262 UT 30 WA 1265 31 1267 CA 32 MT 1268 33 1364 AZ 34 UT 1417 35 1629 CA 36 1635 CO 37 1661 AZ 38 1666 OR 39 1677 WA 40 1686 CA 41 1701 CO 19.95 Overnite 19.95 Overnite 47.31 Express $39.90 Overnite $78.20 Overnite $78.20 EHpress #####Reqular $87.75 Express $78.20 Overnite $78.20 Overnite $49.90 Express $24.95 Regular 42 1708 AZ 43 1712 AZ 44 1718 OR 45 UT 1720 46 1725 WA 47 1733 CA 48 1746 CO 49 1748 50 1776 MT 51 . . 114 Cube Stove+2 Fuel Disks WA 1797 52 114 Cube Stove 2 Fuel Disks 1814 AZ 53 T 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 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. Copy your formula in cell H11 and paste it down to complete the "Freight Cost" column Calculate the Invoice "Shipping Total" in cell I11 Copy your formula in cell I11 and paste it down to complete the "Shipping Total" column