CS&E 2111 PRE LAB 5

EXPRESS MOVER

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.

o  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.

o  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 .

1.    Determine the Packaging and Loading costs in cell packlist!F3.

Write an Excel formula to determine the packaging and loading costs for this item based on its box size and   weight. This cost should also include the box price but do not include any surcharges for fragile or high value items.  Copy the formula down the column to calculate this value for each corresponding item.

For example – your value will include the box price (from the Box Price row on the boxes worksheet) plus the price based on weight (from the Packing and Loading rows, also on the boxes worksheet).

2.    Determine the Handling surcharge for items which are fragile and insurance cost for items which have a high value. (Handling surcharge for fragile items + Insurance for high value items) Write an Excel formula in cell packlist!G3 to calculate this value for the corresponding item.   Handling surcharge and Insurance values are given on sheet handling. Round these charges to the nearest cent.  Copy your formula down the column to determine this value for each corresponding item.

For example - (Handling surcharge for fragile items * F3 (the Packing & Loading Cost)) + Insurance (no insurance unless it is a High Value Item)

3.    Determine the total Packing & Loading costs for item 1 in cell packlist!H3 – Copy your formula down the column to determine this value for each corresponding item.

4.    At the very bottom of the packaging list calculate the total weight of all boxes, the total packaging and loading costs excluding surcharges, total handling fees and total packaging and loading costs. Use appropriate formatting for the corresponding columns.

5.    In cell boxes!B13, summarize total weight for all size A boxes.  Write the formula so that it can be copied across the row for each corresponding box size.  These formulas should automatically update if any of the individual weights are updated on the packaging list. Again format your values in a consistent manner to the data on sheet packlist.  For formatting, select cells B13:L14 and set the formatting to Number style, no decimals.

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. Format cells to match figure below.

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.

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 boxes!L14   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 C and 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!L14

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.

11.  In cell boxes!L18 determine the total Customer Costs, which includes the Total Packing & Loading Costs and Costs to Ship.

Express Mover is considering adding more space to their existing warehouse with an associated cost of $250,000. Create a new worksheet named, Financial Options, to analyze the following scenarios. (All inputs should be explicitly listed and the worksheet should be set up similar to the one that was created during the first part of your Pre Lab.)

1.    Fill out the spreadsheet as you see in the screenshot below with the text and values listed.

2.    In Cells B4 – B6, you should create formulas based on the listed down payments in cells A4 – A6 and the Estimated Remodeling Cost in cell B2.

3.    In Cell D4, enter the function that will produce the monthly payment for the remodel, with a 25% down payment, and an APR of 3.5% compounded monthly. (The loan will be for ten years.)

4.    In Cell D5, enter the function that will produce the yearly payment for the remodel, with a 20% down payment, and an APR of 4% compounded quarterly. (The loan will be for ten years.)

5.    In Cell F6, enter the function that will produce how many years it would take to pay off the loan, with monthly           payments of $3,500 at an APR of 5% compounded monthly, and no down payment. (Round the value to the nearest year.)

6.    Express Mover has a 10 year CD with an initial value of $35,000 that will be maturing this next month. The initial value of the CD was $35,000, with an APR of 2.25% compounded semi-annually. The company would like to        determine if the value of the CD would be enough to make a loan down payment of $50,000. In Cell C7, enter a formula that will determine T/F if the maturing CD is enough for the down payment.

Screenshot Results:

Top of Packlist Worksheet

Bottom of Packlist Worksheet