Question

Heavy Metal Transport (HMT), a contract motor carrier, has an excellent reputation for service and reliability. It has submitted an incremental rate, $4.00/cwt. for shipments weighing less than 150 cwt., $3.80 for shipments between 150 and 200 cwt., $3.60 for shipments between 200 and 250 cwt., and $3.40 for shipments over 250 cwt. up to a maximum of 400 cwt. Midland Continental Railway has submitted a piggyback rate of $3.25 per cwt. with a minimum load of 200 cwt. The piggyback rate includes pickup by truck at the steel mill, line haul by trailer on flat car, and delivery by truck to Metal Specialities' warehouse. They are considered to be a reliable carrier as well. The finance department estimates that Metal Specialities' annual inventory carrying cost is 20%, the cost of inventory in transit is 10%, and the cost of capital is 8%. The cost of placing an order for stainless steel is estimated to be $40 per order. Stainless steel presently costs $300 per cwt. Assume: Annual stainless-steel demand and hence shipping volume is deterministic and is equal to 20.000 cwt per year Capital cost of the company's truck is $150,000 111) Transporting an order via train requires minimum 2 days shipping time, whereas HMT or the company truck would require one day 1. JJ has to make a decision soon. Given the information provided, what would you advise her to do? Provide Excel output.

Answer 1

Input Data 200 300 Miles per CWT Distance Unit cost Inventory Holding cost % Inventory Holding cost $ Ordering cost per order =0.2+0.1+0.08 =B4B3 Own Truck Repair 20000 Annual operating costs 30000 Line haul cost@$2.20 per mile -2.2-B2 Total =B9+B10 B11 per delivery 249 Price Minimum Quantity Maximum Quantity Transportation cost (Fixed) Inventory Holding cost Purchase cost Ordering cost Total OWN TRUCKS 3.8 3.6 3.4 200 250 CWT 199 400 CWT =$B$12 =$B$12 =$B$12 =$B$12 =(B17/2)*$B$5 =(C17/2)*$B$5 = (D17/2)*$B$5 =(E17/2)*$B$5 =$B$3*B17 =$B$3*017 =$B$36D17 =$B$3*E17 =$B$6 =$B$6 =$B$6 B21+B20+B19+B18 C21+C20+C19-C18 D21-D20+D19+D18 E21+E20+E19+E18 =$B$6 3.8 249 Price Minimum Quantity Maximum Quantity Transportation cost Inventory Holding cost Purchase cost Ordering cost Total Heavy Metal Transport 3.6 3.4 150 200 250 CWT 149 199 400 CWT =$B27*B25 =$C27*C25 -SD27*D25 =SE27 E25 =(B27/2)*$B$5 =(C27/2)*$B$5 =(D27/2)*$B$5 =(E27/2)*$B$5 =$B$3*B27 =$B$3*C27 =$B$3*D27 =$B$3*E27 =$B$6 =$B$6 =$B$6 =$B$6 B31+B30+B29+B28 C31+C30+C29+C28 D31+D30+D29+D28 E31+E30+E29+E28

Midland 3.25 200 =C37C36 3.25 200 3.25 249 3.25 400 =B376B36 =D37D36 =E37E36 Price per CWT Minimum load Transportation cost Inventory Holding cost Purchase cost Ordering cost Total 0 =B27*$B$3 =C27*$B$3 =D27$B43 =E27*$B$3 =B41+B40+B39+B38 =C41+C40+C39+C38 =D412D40-D39+D381=E41+E40+E39 E38 INPUT DATA Distance Unit cost Inventory Holding cost % Inventory Holding cost $ Ordering cost per order 200 Miles 300 per Cwt 38% 114 Own Truck Repair Annual operating costs Line haul cost@$2.20 per mile $20,000 $30,000 $440 $50,440 per Delivery Total OWN TRUCKS 3.4 Price Minimum Quantity Maximum Quantity Transportation cost (Fixed) Inventory Holding cost Purchase cost Ordering cost Total 149 $50,440 $8,493 $44,700 $40 $103,673 3.8 150 199 $50,440 $11,343 $59,700 $40 $121,523 3.6 200 249 $50,440 $14,193 $74,700 250 CWT 400 CWT $50,440 $22,800 $120,000 $40 $193,280 $40 $139,373

Heavy Metal Transport 200 149 Price Minimum Quantity Maximum Quantity Transportation cost Inventory Holding cost Purchase cost Ordering cost Total $596 $8,493 $44,700 $40 $53,829 3.8 150 199 $756 $11,343 $59,700 $40 $71,839 249 $896 $14,193 $74,700 3.4 250 CWT 400 CWT $1,360 $22,800 $120,000 $40 $144,200 $40 $89,829 Midland 3.25 200 $650 3.25 400 $1,300 Price per CWT Minimum load Transportation cost Inventory Holding cost Purchase cost Ordering cost Total 3.25 200 $650 $0.00 $59,700.00 $40.00 $60,390.00 3.25 249 $809 $0 $74,700 $40 $75,549.25 $44,700 $40 $45,390.00 $120,000 $40 $121,340.00

The transportation using offer given by MIDLAND is lowest among all for all price breaks. Thus it should be selected for transportation.