HLOOKUPS & VLOOKUPS are preferred over a bunch of IF statements.

You are about to begin a summer internship in Logistics working for a company Express Mover that provides packaging and shipping services to customers moving large amounts of materials from 2,000 pounds (Lbs.) and up. Your employees go onsite to a customer's location - provide the necessary packaging materials (boxes, tape), package the items and then ship them to their new location. Customer charges are based on both the volume and weight of their shipments as well as some special handling factors. You have created an Excel workbook to estimate the price to a specific customer's shipment. You hope to eventually use this as a template for future shipments - just substituting the new bill of lading. This bill of lading - found on worksheet packlist, contains the list of all boxes to be included in the shipment together with information on their individual box sizes and weight The workbook you have prepared contains four worksheets as follows: packlist -contains a list of boxes that have been assembled and packed for moving, including the box size (identified by letter code), the box weight (lbs.), whether it is fragile (True/False) and the estimated value if an item is considered to be High Value and requires insurance. This field will be blank for items not considered high value. boxes-contains information about each standard box size including its dimensions in inches, its cost to pack&load, if it's less than 100 pounds or 100 pounds or more, and a factor needed for shipping to calculate truck volume needed. ship - contains information on volume and weight limits by truck type and the number of miles this shipment will be sent. Also included on this worksheet are some conversion factors that may be needed in your calculations handling-contains information on cost percentages expected to package fragile items based on the item weight and additional cost percentages for insurance on high value items. To calculate this surcharge for fragile items of less than 20 lbs. include a surcharge of 25%, for items that are at least 20 lbs. but less than 50 lbs, include an additional surcharge of 30% etc. So if an item costs $10 for packaging and loading and is fragile - if it is weighted 45 lbs. - the surcharge would be 30% x $10 or $3. To calculate the value of insurance for high value items multiply the value of the item (in column High value) by the insurance rate. So a $1000 with a 7.5% insurance rate would require a $75 surcharge for insurance. You now need to complete the necessary formulas to perform the calculations for your estimate. 6. In cell boxes!B14 calculate the volume in cubic feet (CF) that will be required on the truck for all size A boxes. Remember that box sizes are given above in inches. This formula should work when copied across the row for each corresponding box size, and if the number of boxes for each size box varies. For example - count the number of size A boxes and then multiply that number for the box dimensions in the above column. Then, divide that value by the conversion to cubic ft - located on the ship worksheet. B D G H 1 E F Packaging List High Packing & Value Loading Costs Handling Surcharge Total Packing & Loading Costs $ 2 3 4 5 6 | | | Weight 131 11 90 20 99 3,500 1,500 Item# Box Size 1 H 2 D 3 4 R 5 A 6 A 7 F 8 1 9 H 10 G 11 1 12 E 13 H 14 D 15 16 E 17 R 18 F 19 D 20 21 E 22 F 23 G 24 E 25 K 26 D 27 H 28 29 30 A 31 32 I 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 12 39 55 18 78 118 41 69 105 64 35 78 Fragile FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE TRLIE boxes - I-WL-WUXIU 12 78 67 71 10 3 48 122 105 12,650 91 33 119 147 67 135 52 22 6.450 packlist ship handling + U K FE Alignment Clipboard Font H19 fx D E F G J L 1 2 K 3 Boxing Information & Pricing D E F 18 20 24 24 18 20 18 24 18 20 18 24 1.15 1.15 1.2 1.12 $ 3.06 $ 3.54 $ 3.57 $ 5.11 $ H 48 12 1 36 36 R 48 48 4 5 48 36 36 60 1.1 9.23 $ 12 1.25 3.62 $ 6 1.1 6 7 1.25 6.92 $ 6.54 00 5.17 5.17$ 6.21 $ 8.2 $ 8. 15.5 $ 5. 8.28 $ 7.36 $ 7.36 $ $ 8.42 $ 10.52 $ 12.62 $ 26.30 $ 26.30 $ 26.30 Box Summary: F D E G H K R total 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 packlist boxes ship handling + Ready A B 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 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 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 packlist boxes ship handling Ready A B D E F G 00 vou own 1 Fragile Additional % 2 Weight Cost 3 0 25% 4 20 30% 50 40% 75 60% 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 packlist boxes ship handling + Ready You are about to begin a summer internship in Logistics working for a company Express Mover that provides packaging and shipping services to customers moving large amounts of materials from 2,000 pounds (Lbs.) and up. Your employees go onsite to a customer's location - provide the necessary packaging materials (boxes, tape), package the items and then ship them to their new location. Customer charges are based on both the volume and weight of their shipments as well as some special handling factors. You have created an Excel workbook to estimate the price to a specific customer's shipment. You hope to eventually use this as a template for future shipments - just substituting the new bill of lading. This bill of lading - found on worksheet packlist, contains the list of all boxes to be included in the shipment together with information on their individual box sizes and weight The workbook you have prepared contains four worksheets as follows: packlist -contains a list of boxes that have been assembled and packed for moving, including the box size (identified by letter code), the box weight (lbs.), whether it is fragile (True/False) and the estimated value if an item is considered to be High Value and requires insurance. This field will be blank for items not considered high value. boxes-contains information about each standard box size including its dimensions in inches, its cost to pack&load, if it's less than 100 pounds or 100 pounds or more, and a factor needed for shipping to calculate truck volume needed. ship - contains information on volume and weight limits by truck type and the number of miles this shipment will be sent. Also included on this worksheet are some conversion factors that may be needed in your calculations handling-contains information on cost percentages expected to package fragile items based on the item weight and additional cost percentages for insurance on high value items. To calculate this surcharge for fragile items of less than 20 lbs. include a surcharge of 25%, for items that are at least 20 lbs. but less than 50 lbs, include an additional surcharge of 30% etc. So if an item costs $10 for packaging and loading and is fragile - if it is weighted 45 lbs. - the surcharge would be 30% x $10 or $3. To calculate the value of insurance for high value items multiply the value of the item (in column High value) by the insurance rate. So a $1000 with a 7.5% insurance rate would require a $75 surcharge for insurance. You now need to complete the necessary formulas to perform the calculations for your estimate. 6. In cell boxes!B14 calculate the volume in cubic feet (CF) that will be required on the truck for all size A boxes. Remember that box sizes are given above in inches. This formula should work when copied across the row for each corresponding box size, and if the number of boxes for each size box varies. For example - count the number of size A boxes and then multiply that number for the box dimensions in the above column. Then, divide that value by the conversion to cubic ft - located on the ship worksheet. B D G H 1 E F Packaging List High Packing & Value Loading Costs Handling Surcharge Total Packing & Loading Costs $ 2 3 4 5 6 | | | Weight 131 11 90 20 99 3,500 1,500 Item# Box Size 1 H 2 D 3 4 R 5 A 6 A 7 F 8 1 9 H 10 G 11 1 12 E 13 H 14 D 15 16 E 17 R 18 F 19 D 20 21 E 22 F 23 G 24 E 25 K 26 D 27 H 28 29 30 A 31 32 I 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 12 39 55 18 78 118 41 69 105 64 35 78 Fragile FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE FALSE FALSE TRUE FALSE TRLIE boxes - I-WL-WUXIU 12 78 67 71 10 3 48 122 105 12,650 91 33 119 147 67 135 52 22 6.450 packlist ship handling + U K FE Alignment Clipboard Font H19 fx D E F G J L 1 2 K 3 Boxing Information & Pricing D E F 18 20 24 24 18 20 18 24 18 20 18 24 1.15 1.15 1.2 1.12 $ 3.06 $ 3.54 $ 3.57 $ 5.11 $ H 48 12 1 36 36 R 48 48 4 5 48 36 36 60 1.1 9.23 $ 12 1.25 3.62 $ 6 1.1 6 7 1.25 6.92 $ 6.54 00 5.17 5.17$ 6.21 $ 8.2 $ 8. 15.5 $ 5. 8.28 $ 7.36 $ 7.36 $ $ 8.42 $ 10.52 $ 12.62 $ 26.30 $ 26.30 $ 26.30 Box Summary: F D E G H K R total 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 packlist boxes ship handling + Ready A B 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 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 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 packlist boxes ship handling Ready A B D E F G 00 vou own 1 Fragile Additional % 2 Weight Cost 3 0 25% 4 20 30% 50 40% 75 60% 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 packlist boxes ship handling + Ready