Shipping CostsYour 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.
























Sheet1 Order NumberItem NumberQuantityShipCost 6557916891624248.06 6557917481694248.06 32196259201715247.73 5274742391921244.76 5274744731997244.76 5274747481694244.76 32356095681642240.17 32356097481693240.17 32356099201712240.17 300806757414520238.62 300806787313940238.62 307158957414520238.62 307158987313940238.62 313064874816910238.62 320481157414520238.62 320481187313940238.62 30114684817902238.55 30114685681644238.55 30114687481691238.55 30114689201712238.55 3898434731995237.91 3898437481694237.91 3898439201712237.91 11252821071972232.02 112528214218420232.02 11252825681644232.02 11252828341852232.02 11252828941872232.02 17527242391924231.72 17527244731994231.72 6095534817902229.29 6095531071972229.29 6095533371312229.29 6095535741452229.29 6095538941878229.29 6095539201713229.29 18246734971381228 18246735681642228 18246737481694228 31312505681643227.56 31312507481693227.56 14771157481696221.6 14771158731396221.6 26100442391921220.14 26100444731993220.14 26100447481692220.14 11251215681646219.36 11251217481691219.36 11251219201711219.36 13305412391921215.47 13305415681643215.47 13305417481692215.47 28736981071974215 287369887313940215 32211081071974213.81 32211088341858213.81 32211088941878213.81 148038977919120213 16918857481694209.87 16918859201711209.87 20820539201714209 23857232391921207 23857234731994207 23857237481691207 23857239201711207 23856799201714206 15709667481695205.02 22786085681642205 22786087481692205 22786089201712205 26910618916301205 26910612391922205 26910614731995205 26910617481691205 1876637481695202.75 1876639201711202.75 27726654731994201.92 27726657481693201.92 30463854731994201.92 30463857481693201.92 31311454731994201.92 31311457481693201.92 29955321071971200.21 29955324731994200.21 29955327481692200.21 6683577481696199.87 16210807481696199.87 26878988916301199 26878985681642199 26878987481692199 26878989201711199 196784325135025198 19678437481693198 19678438731394198 22791644731994198 22791647481693198 30081859201715195.57 11936251071971195.54 11936252352001195.54 11936255681643195.54 11936257481691195.54 11936258941872195.54 11936259042031195.54 11256984731994195.53 11256989201713195.53 3903515681645194.38 3903517481691194.38 3903519201711194.38 1874247481695191.53 1874248731398191.53 18974075681643190 18974077481693190 28389921071973189.66 28389928341853189.66 28389928941875189.66 4589785681642188.97 4589787481692188.97 4589789201712188.97 12648185681642186.08 12648187481693186.08 12648189201711186.08 29240605681643183.26 29240607481692183.26 24786387481693182 24786389201711182 26886372391922182 26886377481692182 3180151071971181.58 3180155681646181.58 14383405681642181.42 14383407481693181.42 19954083371311181 19954084971381181 19954085681642181 19954087481692181 11252129201715180.99 16931039201715180.99 30701101071972180.96 30701105741453180.96 30701108341852180.96 30701108731394180.96 30701108941872180.96 25852187481693180 25852189201711180 27191441071971180 27191442352001180 27191448341851180 27191448941872180 27191449042031180 27191449411901180 91572614218410179.08 9157266891622179.08 9157267481693179.08 9147681071973178.44 9147684382001178.44 9147688941875178.44 9301451381794177.86 9301455681641177.86 9301457481693177.86 9301459201711177.86 13537335681643177.74 13537337481692177.74 13537339201711177.74 212501725135010177 21250179419201177 212501713817910177 21250175681643177 21250177481691177 6882734731992176.71 6882737481694176.71 29958934731993175.84 29958937481691175.84 299589362399901175.84 21489477481694175 214894787313910175 23053714731993175 23053717481693175 17998165681642174 17998167481691174 17998169201712174 23603447481694174 28723561071972172 28723562352002172 28723568941873172 28723569042031172 29836901071973169.75 29836908941875169.75 3091251071973169.67 3091254382001169.67 3091258941874169.67 4363885681643169.62 4363887481692169.62 25173785681644168 25173787481691168 25291982391922168 25291984731993168 25291987481692168 10808542391922167.6 10808544731993167.6 28270771071971167.34 28270774731994167.34 28270779201711167.34 19675075681642167 19675077481692167 19675079201711167 21226865681642167 21226867481692167 21226869201711167 2396535681641166.88 2396537481691166.88 2396539201713166.88 20694729201714166 29218204731994165.92 29218207481692165.92 12528994731993165.13 12528997481693165.13 27840575681642165.06 27840577481692165.06 27840579201711165.06 32190707481694163.73 32190708731394163.73 32356441071974160.28 30206851071972160.08 30206852352001160.08 30206858941878160.08 30206859042031160.08 28495014731992158.07 28495017481692158.07 28495019201711158.07 6795751421845156.99 6795759201713156.99 16784852391921156.44 16784854731994156.44 16784857481691156.44 23863602391921154 23863605681642154 23863607481691154 23193958941878153 11137392391922150.12 11137395681642150.12 30481455681643147.02 30481457481691147.02 30481459201712147.02 18535951071971145 18535955681641145 18535958941872145 18535959201711145 11942749201714144.79 20456907481694144 23048901381795144 23048905681642144 23048908731392144 23048909201711144 26606935681643144 26606939201712144 30693214731992143.26 30693217481692143.26 32354024731992143.26 32354027481692143.26 31307611071971142.9 31307612352001142.9 31307615681642142.9 31307618941871142.9 31307619042031142.9 5401734731995141.98 5401737481691141.98 6886305681643141.55 6886307481692141.55 19934265681645141 199342687313920141 30698364731996140.91 10476615681642140.42 10476617481692140.42 27860335681641138.08 27860337481692138.08 27860339201711138.08 24087405681641138 24087407481692138 24087409201711138 3390512501831137.45 3390514731992137.45 3390517481691137.45 24796305681641133 24796307481693133 27847467481693132.9 32402377481693132.9 12511044731992132.6 12511047481692132.6 28374574731993131.53 28374579201711131.53 29851493371311130.4 29851494971381130.4 29851499201711130.4 29851499271391130.4 24717614731991129 24717619201712129 24779914731994128 24779917481692128 18702825681642127 18702827481692127 29725965681642125.95 29725967481692125.95 19941112391922125 19941114731992125 10601955681645124.88 3065057481695124.61 122522313817910124.55 12252237481692124.55 12252239201711124.55 24678465681642124 24678467481692124 30704401071972123.53 30704408941878123.53 2546777481693123.01 11159947481693123.01 12640877481693123.01 22922812391922123 22922814731992123 22931999201713123 9931421071973122.06 9931428941873122.06 26615715681642122 26615717481692122 12517745681641120.91 12517749201712120.91 32535661071971120.75 32535665681641120.75 32535667481691120.75 32535668941872120.75 1561108916301120.45 1561105681645120.45 22304504731992119 22304507481692119 17677461071971118 17677468941871118 17677469201711118 26280434731992116 26280437481692116 28725354731992116 28725357481692116 32381644731991115.93 32381647481692115.93 20943455681644115 20943459201711115 18639137481692114 18639138941871114 20825772391922114 20825777481691114 7756771071971112.29 7756775741451112.29 7756777481691112.29 7756778341851112.29 7756778941871112.29 10836411071972111.26 10836418941874111.26 18389872391921110 18389874731993110 9602075681641109.89 9602077481692109.89 9602078731394109.89 9269785681643109.15 9269787481691109.15 9269789201711109.15 16182259201713108.59 1981645681643108.24 1981647481691108.24 2828865681643108.24 2828867481691108.24 11248965681643108.24 11248967481691108.24 15813555681643108.24 15813557481691108.24 18711057481693108 20440667481693108 21473437481693108 22167777481693108 25288554731994108 24672095681642106 24672097481691106 9292945681643105.42 9292948941871105.42 9292949681901105.42 21504701071971105 21504702352001105 21504708341851105 21504708941873105 21504709042031105 3768921381792103.49 3768921391842103.49 3768921421842103.49 3768924731991103.49 3768929201711103.49 6878651071971103.41 6878658341851103.41 6878658941872103.41 6878659201711103.41 1867805681644103.34 1867809201711103.34 27587151071972102.87 27587158941874102.87 8812127481692102.82 8812129201711102.82 17996808916301101 17996805681641101 17996807481691101 22026244731993100 22026244971381100 2069987568164199 2069987748169299 3007009473199298.96 3007009748169198.96 3146848473199298.96 3146848748169198.96 3117529873139498 3117529920171298 2785190568164397.36 2785190873139297.36 2360404568164197 2360404748169297 2530464239192197 2530464473199197 2530464748169197 2291575748169195 2291575920171195 863494107197193.85 863494894187493.85 8634943072990193.85 2995887568164293.07 2995887920171193.07 1278614239192291.71 1640202920171291.71 585083568164191.6 585083748169291.6 618793568164191.6 618793748169291.6 3019904568164290.43 3019904873139690.43 2851670748169288.6 2984729748169288.6 3019094748169288.6 3204385748169288.6 1146339574145287.58 1146339873139487.58 1146339920171187.58 2453996574145287 2453996748169287 2453996873139487 1477051473199486.94 2059777689162486.86 1183121568164286.16 1183121920171186.16 1298189568164286.15 1298189920171186.15 3093058574145185.2 3093058748169185.2 3093058873139285.2 3146794574145185.2 3146794748169185.2 3146794873139285.2 1428170107197185.03 1428170235200285.03 1428170574145485.03 1428170894187185.03 1428170904203185.03 2330131138179284 2330131139184284 2330131142184384 2330131473199184 2330131920171184 504551340141183.43 504551568164283.43 504551873139283.43 646329568164283.26 646329748169183.26 790208568164283.26 790208748169183.26 859642568164283.26 859642748169183.26 1895390748169382 1968597748169382 2798126473199381.64 2798126748169181.64 654320107197180.81 654320834185180.81 654320894187280.81 3069415142184480.7 3069415473199180.7 3069415920171180.7 2231408235200179 2231408894187379 2231408904203179 2478589568164379 2901467920171278.23 2923592920171278.23 3145641138179576.09 3145641139184576.09 3145641141184276.09 3145641473199176.09 3204203568164175.85 3204203748169175.85 747997473199375.56 747997748169175.56 2771117748169175.11 27711176239990175.11 3059458748169175.11 3059458920171175.11 2279669239192175 2279669748169175 892377139184574.68 892377779191174.68 892377920171174.68 470412107197274.17 2598248568164174 2598248748169174 1910620748169273 2950097107197172.62 2950097834185172.62 2950097894187272.62 3175798107197172.62 3175798834185172.62 3175798894187272.62 2797416574145272.6 2797416920171172.6 1048148920171272.39 1464254920171272.39 1630937920171272.39 2983098748169271.98 3145355748169271.98 317305568164170.21 317305748169170.21 1069585568164170.21 1069585748169170.21 1192834568164170.21 1192834748169170.21 2746139748169270 1437532748169169.51 1437532920171169.51 1057390568164167.06 1057390801113167.06 1057390873139267.06 914042748169266.62 1628730748169266.62 2409267239192166 2409267473199266 666068107197165.23 666068574145165.23 666068894187165.23 3205975142184364.21 3205975748169164.21 2910647473199263.39 2910647748169163.39 1860091239192163 1860091473199163 2136845568164163 2136845748169163 2386066239192162 2386066473199162 2687896138179462 2687896139184462 2687896920171162 1390599568164161.17 1390599920171161.17 2837851473199159.47 2837851748169159.47 305213568164258.41 273549568164158.29 273549748169158.29 980317568164158.29 980317748169158.29 1181844568164158.29 1181844748169158.29 1329847568164158.29 1329847748169158.29 1023041473199157.93 1023041920171157.93 3069270107197157.77 3069270894187257.77 927576568164357.32 3020307748169156.01 30203076239990156.01 321648473199155.04 321648748169155.04 2147739568164254 1579429239192253.83 1770619473199153 1770619497138153 2688056568164253 982594142184552.95 982594920171152.95 2410115920171152 2467490920171152 914551779191250.59 3070643920171149.55 1826739473199248 1895855473199248 2136989891630148 2294380891630147 411739920171145.85 859364920171145.85 926043920171145.85 936643920171145.85 1004943920171145.85 1630333920171145.85 2203410748169145 2861827748169144.3 2972548748169144.3 3019329748169144.3 3047476748169144.3 3131603748169144.3 2279246748169144 2454022748169144 2320625748169143 714711438200242.67 1986146920171142 338570748169141 895191748169141 1073453748169141 1078953748169141 1286482748169141 1329842748169141 1426432748169141 1436891748169141 1498430748169141 1640092748169141 1640131748169141 1994335107197141 2294352920171141 283848438200240.18 2970606107197140.07 3205132107197140.07 2598023920171140 2360327239192139 508888107197137.09 1092661107197137.09 187105920171136.2 388595920171136.2 619744920171136.2 846964920171136.2 881235920171136.2 1404281920171136.2 1476552920171136.2 3019932720200136.2 3019932924199136.2 2148851748169136 2278334748169136 2293245748169136 2971122748169135.99 1343038748169133.31 1755141748169133.31 1181401107197131.73 1618910107197131.73 2771265568164131.55 2785450568164131.55 2675072568164131 436136568164129.2 1215699568164129.2 29242481381791529.01 2714890497138129 3047409568164126.98 3203089107197125.86 1003748473199125.3 777152568164124.98 24094661381791022 654479473199121.73 198576438200121.33 153777438200120.09
info4590shippingcosts-2bofeqx1.xlsx
Answered Same DayFeb 10, 2023

Answer To: Shipping CostsYour company has several distributors in Guam that service all of your customers on...

Prince answered on Feb 10 2023
45 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 carriers, 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
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here
Have files?