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\mathrm{.}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!F$3.$

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!G$3$ 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 $*$ F$3($the Packing & Loading Cost$))+$ Insurance $($no insurance unless it is a High Value Item$)$

$1$

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