Great Deal! Get Instant $10 FREE in Account on First Order + 10% Cashback on Every Order Order Now

Shipping Costs Your company has several distributors in Guam that service all of your customers on the island. You have been shipping items in parcel shipments based on the requisitions from the...

1 answer below »



Shipping Costs






Your company has several distributors in Guam that service all of your customers on the island. You have been shipping items in parcel shipments based on the requisitions from the distributors, but your Boss thinks that you can improve your operations by opening a warehouse in Agana and pre-stocking items for delivery.






In order to do a complete cost-savings analysis of the options, you are going to need the current costs per item for parcel shipping so you can compare the costs to the bulk shipping that you are planning to do.






You have good forecasts of the item demand for the out-years, and you want to estimate the shipping cost savings based on item quantity and shipping cost per unit item.






Unfortunately, your data on current shipping costs for parcel shipments is maintained at the shipment level—you only have the quantities of each item shipped and the total cost of the shipments. What’s worse, the shipments generally have different quantities of different items.






The Excel file “Shipping Costs” (on Canvas) contains the data from the most recent 339 shipments for 35 of the items you ship.






Calculate the shipping cost per item for each item in your inventory to report to your Boss. Include any uncertainty you may have with your conclusions.






HINT: Some of these shipments might have been unusual—overnight or rush orders—that may cause you some problems with the data.








Answered Same Day Feb 10, 2023

Solution

Prince answered on Feb 10 2023
38 Votes
Pivot
    Row Labels    Min of Ship Cost Per item    Max of Ship Cost Per item    Average of Ship Cost Per item
    107197    25.86    200.21    84.12        Analysis
    138179    1.93    51.75    23.20
    139184    14.94    51.75    27.88        Based on our analysis, it seems that the cost per item for parcel shipping is highly variable and depends on the quantity of each item shipped in a given shipment. Specifically, the minimum cost per item of 1.93 was observed for item 138179, while the maximum cost per item of 244.76 was observed for item 239192. On average, the cost per item was estimated to be 72.74.
This variability in cost could be due to differences in shipping ca
iers, packaging materials, and handling fees. Additionally, since the shipping costs were determined at the shipment level an estimation has to be made to determine the cost of each item for those shipments containing multiple items. This inherent uncertainty in our analysis should be taken into account when making predictions about future shipping costs.
In order to accurately determine the cost saving from opening a warehouse in Agana, it will be necessary to consider other factors such as the cost of warehousing and the cost of bulk shipping. With all relevant costs taken into account, a more precise analysis can be conducted to accurately evaluate the potential cost savings.
    141184    38.05    38.05    38.05
    142184    10.59    51.75    24.10
    235200    42.52    195.54    123.88
    239192    26.92    244.76    102.41
    250183    137.45    137.45    137.45
    251350    7.92    17.70    12.81
    337131    114.65    181.00    142.02
    340141    83.43    83.43    83.43
    438200    20.09    178.44    71.83
    473199    21.73    129.00    54.07
    481790    114.65    119.28    116.96
    497138    29.00    228.00    120.23
    568164    19.11    177.86    64.99
    574145    11.93    114.65    54.12
    689162    21.72    89.54    57.76
    720200    36.20    36.20    36.20
    748169    23.86    238.55    71.80
    779191    10.65    74.68    36.88
    801113    67.06    67.06    67.06
    834185    26.73    180.00    93.02
    873139    5.38    72.00    28.92
    891630    47.00    205.00    120.08
    894187    15.44    142.90    57.64
    904203    79.00    195.54    139.94
    920171    36.20    219.36    89.21
    924199    36.20    36.20    36.20
    927139    130.40    130.40    130.40
    941190    180.00    180.00    180.00
    941920    177.00    177.00    177.00
    968190    105.42    105.42    105.42
    3072990    93.85    93.85    93.85
    6239990    56.01    175.84    102.32
    Grand Total    1.93    244.76    72.74
Data
    Order Number    Item Number    Quantity    ShipCost    Ship Cost Per...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here