Problem 4.

A power plant in California uses coal at the rate of 100,000 pounds each day. It also uses MRO material at the rate of 1,000 pounds each day. The coal comes from Wyoming, and the MRO material comes from Chicago. Coal costs $0.01 per pound, whereas MRO material costs $10 per pound, on average. Holding costs at the power plant are 25%. Transportation choices available are as follows:

Train for Coal

Lead time = 15 days

Carload (100,000 pound) at $400 per carload

Full train (70 cars) at $15,000 per train

Truck for MRO

Lead time = 4 days

Minimum cost = $100

Up to 10,000 pounds at $0.08 per pound

Between 10,000 and 20,000 pounds at $0.07 per pound for entire load

Between 25,000 and 40,000 pounds at $0.06 per pound for entire load

Small TL (40,000 pounds) for $2,000

Large TL (60,000 pounds) for $2,600

Safety inventory of coal and MRO materials is kept at twice the consumption during the lead time of supply. What mode of transport do you recommend for each of the two products? Why?

Please send .xlsx I would aprecciate it a lot.