Question

Voldemort, an auto parts distributor, has a large warehouse in the Chicago region and is deciding on a policy for inbound shipping. Three shipping policies avaliable for inbound shipping are LTL, TL, and hybrid. LTL shipping costs $1 per unit. TL shipping costs $800 per truck plus $110 per pickup. Hybrid shipping policy costs $160 as a fixed cost per shipment along with a variable cost for $0.50 per unit shipped. A truck can carry up to 2,000 units. Voldemort incurs a fixed cost of $90 for each order placed with a supplier. Each units costs $200, and Voldemort uses a holding cost of 20 percent. Demand at Voldemort is 2400 per month.

Part A. Assume that LTL shipping policy is used.

a) What is the optimal order size?

b) What is the annual total cost of the optimal policy (Do not include annual material cost)?

Part B. Assume that TL shipping policy is used.

c) What is the optimal order size?

d) What is the annual total cost of the optimal policy (Do not include annual material cost)?

Part C. Assume that hybrid shipping policy is used.

e) What is the optimal order size?

f) What is the annual total cost of the optimal policy (Do not include annual material cost)?

Part D. Comparison.

g) Which shipping policy would you recommend and why?

Please solve on excel and show formulas.

Answer 1

EXCEL FORMULAS:

E9 =SQRT(2*$E$2*E8/E7)

E11 =E7*E9/2+E8*$E$2/E9

E17 =90+800+110

E18 =SQRT(2*$E$2*E17/E16)

E20 =E16*E18/2+E17*$E$2/E18