7. In cell boxes!L13 calculate the total weight for all boxes. This value should match the total weight you calculated on worksheet packlist. If not - you will need to find your error. Note: The values in Cell Boxes!L13 should match the value in Packlist!C226. 8. In cell boxesIL14 calculate the total volume needed in the truck for all boxes of all sizes Since boxes do not necessarily fit perfectly without wasted space, add the appropriate additional percentage given in the volume usage row (worksheet boxes - row 6). So size A boxes will require an additional 10% volume, size Cand additional 12% etc. Hint: use the SumProduct function that can automatically multiply corresponding components in the given arrays, and return the sum of those products. 9. Here you are trying to determine the size of the truck needed based on the total weight and volume listed in cells L13 and L14. Truck types are listed on the Ship worksheet. In cell boxes!L15 write a formula to automatically determine the number of the truck type to recommend - assuming you will need to use the larger of the two predicted based on weight and on volume. (For full credit - do it w/o using an if function). Hint: Your function syntax will be as follows: =MAX(VLOOKUP... ..),VLOOKUP(........... Determine a recommended truck type based on the following method: Automatically determine the truck type (1 through 6) needed based on the total weight calculated in cell boxes |L13. Use the schedule setup on the ship worksheet - so shipments of Less than 20,000 pounds assume only a small truck is needed (truck type 1), shipments of at least 20,000 pounds but less than 25,000 pounds assume a medium size truck will be needed etc. Automatically determine the truck type (1 through 6) based on total volume needed as calculated in boxes/114 10. In cell boxes/L16 determine the total cost for shipping. If truck type 6 was selected (more than 1 truck), return the text, "calculate manually". Otherwise this formula should calculate this value based on the truck type. The cost of shipping includes a base price that varies by truck type and the cost per mile per ton ($/mi per ton), the number of miles (cell ship!B2) and shipment weight you previously calculated. The conversion factor for pounds to tons is also provided on sheet ship. M R 1 2 Box Size 3 Length (IN) Width (N) Height (IN) Volume Usage Box Price Packing & Loading 8 100 lbs 10 B D E F G H Boxing Information & Pricing D E K 18 24 18 20 24 24 48 36 36 12 18 20 18 24 12 36 36 12 12 18 20 18 24 12 48 60 1.1 1.12 1.15 1.15 1.2 1.12 1.25 1.25 1.1 1.33 $ 2.32 $ 3.06 $ 3.54 $ 3.57 $ 5.11 $ 3,62 $ 6.92 $ 9.23 $ 4567 48 48 6 11 6.54 $ $ 3.10 $ 4.14 $ 5.17 $ 5.17 $ 6.21 $ 8.28 $ 3.28 $ 15.52 S 5.17 $ 8.28 $ 6.31 S 6.31 $ 7.36 S 736 s 8.42 $ 10.52 $ 12.62 $26.30 $ 26.30 S 26.30 D Box Summary: E F 2258 1245 c 2061 total G 2229 H 1827 K 1292 R 1372 1653 2247 12 13 Total Weight (lbs) 1112 14 Total Volume HVALUEI 15 Recommended Truck Type: 16 Costs to ship: 17 18 Total customer costs: 19 B25 Jx F G H DE 1 Shipping Costs - For Overland Truck 2 Miles: 435 Truck Volume Weight $/mi per Truck 3 Type Truck Description CF Lbs. Base ton Type 4 1 Small 350 1.85 1 5 2 Medium 1,700 20,000 650 1.25 2 6 3 Large 2,380 25,000 850 0.95 3 7 4 Extra Large 4,080 30,000 1,000 0.9 4 8 5 Double 4,505 48,000 1,200 0.8 5 9 6 need more trucks 4,950 55,000 6 10 11 Additional Info: 12 Cubic Inches per Cubic Foot 1,728 13 Lbs. per Ton 2,000 3 in 7. In cell boxes!L13 calculate the total weight for all boxes. This value should match the total weight you calculated on worksheet packlist. If not - you will need to find your error. Note: The values in Cell Boxes!L13 should match the value in Packlist!C226. 8. In cell boxesIL14 calculate the total volume needed in the truck for all boxes of all sizes Since boxes do not necessarily fit perfectly without wasted space, add the appropriate additional percentage given in the volume usage row (worksheet boxes - row 6). So size A boxes will require an additional 10% volume, size Cand additional 12% etc. Hint: use the SumProduct function that can automatically multiply corresponding components in the given arrays, and return the sum of those products. 9. Here you are trying to determine the size of the truck needed based on the total weight and volume listed in cells L13 and L14. Truck types are listed on the Ship worksheet. In cell boxes!L15 write a formula to automatically determine the number of the truck type to recommend - assuming you will need to use the larger of the two predicted based on weight and on volume. (For full credit - do it w/o using an if function). Hint: Your function syntax will be as follows: =MAX(VLOOKUP... ..),VLOOKUP(........... Determine a recommended truck type based on the following method: Automatically determine the truck type (1 through 6) needed based on the total weight calculated in cell boxes |L13. Use the schedule setup on the ship worksheet - so shipments of Less than 20,000 pounds assume only a small truck is needed (truck type 1), shipments of at least 20,000 pounds but less than 25,000 pounds assume a medium size truck will be needed etc. Automatically determine the truck type (1 through 6) based on total volume needed as calculated in boxes/114 10. In cell boxes/L16 determine the total cost for shipping. If truck type 6 was selected (more than 1 truck), return the text, "calculate manually". Otherwise this formula should calculate this value based on the truck type. The cost of shipping includes a base price that varies by truck type and the cost per mile per ton ($/mi per ton), the number of miles (cell ship!B2) and shipment weight you previously calculated. The conversion factor for pounds to tons is also provided on sheet ship. M R 1 2 Box Size 3 Length (IN) Width (N) Height (IN) Volume Usage Box Price Packing & Loading 8 100 lbs 10 B D E F G H Boxing Information & Pricing D E K 18 24 18 20 24 24 48 36 36 12 18 20 18 24 12 36 36 12 12 18 20 18 24 12 48 60 1.1 1.12 1.15 1.15 1.2 1.12 1.25 1.25 1.1 1.33 $ 2.32 $ 3.06 $ 3.54 $ 3.57 $ 5.11 $ 3,62 $ 6.92 $ 9.23 $ 4567 48 48 6 11 6.54 $ $ 3.10 $ 4.14 $ 5.17 $ 5.17 $ 6.21 $ 8.28 $ 3.28 $ 15.52 S 5.17 $ 8.28 $ 6.31 S 6.31 $ 7.36 S 736 s 8.42 $ 10.52 $ 12.62 $26.30 $ 26.30 S 26.30 D Box Summary: E F 2258 1245 c 2061 total G 2229 H 1827 K 1292 R 1372 1653 2247 12 13 Total Weight (lbs) 1112 14 Total Volume HVALUEI 15 Recommended Truck Type: 16 Costs to ship: 17 18 Total customer costs: 19 B25 Jx F G H DE 1 Shipping Costs - For Overland Truck 2 Miles: 435 Truck Volume Weight $/mi per Truck 3 Type Truck Description CF Lbs. Base ton Type 4 1 Small 350 1.85 1 5 2 Medium 1,700 20,000 650 1.25 2 6 3 Large 2,380 25,000 850 0.95 3 7 4 Extra Large 4,080 30,000 1,000 0.9 4 8 5 Double 4,505 48,000 1,200 0.8 5 9 6 need more trucks 4,950 55,000 6 10 11 Additional Info: 12 Cubic Inches per Cubic Foot 1,728 13 Lbs. per Ton 2,000 3 in