C8 X V fx 4505 A B C D E F G 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 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 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 packlist boxes ship handlingD11 X V fx A B C D E F 1 Fragile Additional % 2 Weight Cost 3 0 25% 4 20 30% 5 50 40% 6 75 60% 7 100 100% 8 125 125% 9 150 150% 10 High Value 11 Insurance 7.5% 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 packlist boxes ship handlinggiven 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!L14 9. 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. 10. In cell boxes!L18 determine the total Customer Costs, which includes the Total Packing & Loading Costs and Costs to Ship.B D E G H Packaging List High Packing & Handling Total Packing & Item# |Box Size Value Loading Costs Surcharge Loading Costs H 131 FALSE $ 16.24 16.24 D 11 FALSE 8.23 $ 8.23 90 FALSE 6.46 $ 6.46 20 FALSE 3,500 14.82 $ 262.50 277.32 5 A 99 FALSE 4.43 S 4.43 A 12 FALSE 4.43 un 4.43 F 39 TRUE 9.78 $ 2.93 $ 12.71 10 55 FALSE 22.44 22.44 11 H 18 FALSE 11.90 $ S 11.90 10 G 78 FALSE 13.39 un 13.39 13 11 118 TRUE 1,500 33.22 $ 145.72 $ 178.94 14 12 E 41 FALSE 8.71 $ 8.71 15 13 H 69 FALSE $ 11.90 16 14 D FALSE 10.42 $ $ 10.42 17 15 64 FALSE 22.44 $ S 22.44 18 16 E 35 FALSE 8.71 3.71 19 17 R 78 FALSE 14.82 $ 14.82 20 18 12 TRUE 9.78 2.45 $ 12.23 21 19 D 78 8.23 4.94 $ 13.17 22 20 57 FALSE 22.44 23 21 E 71 FALSE 8.71 S 8.71 24 22 F 10 FALSE 9.78 9.78 25 23 3 FALSE 13.39 $ 13.39 26 24 m 48 12,650 8.71 $ 951.36 960.07 25 K 122 FALSE 35.53 $ 35.53 28 26 D 105 FALSE 10.42 $ 10.42 29 27 H 91 FALSE 11.90 11.90 30 28 33 FALSE 6.46 $ 6.46 31 29 8.63 S 8.63 32 30 O D 147 FALSE 7.64 $ 7.64 33 31 6.46 $ 2.58 9.04 34 32 135 FALSE 33.22 35 33 C 52 TRUE 6,450 6.46 486.33 492.79 36 34 14 TRUE 33.22 $ 41.53 74.75 37 35 47 FALSE 22.44 $ 22.44 38 36 148 FALSE 33.22 $ 33.22 39 37 K 116 FALSE 35.53 $ S 35.53 packlist ship handling