Question

A steel rolling mill can produce I-beams at the rate of 20 tons per week. Customer demand for the beams is 5 tons per week. I-beams cost the mill $2,000 per ton and the mill has a holding cost of 25 percent. To produce I-beams, the mill must go through a setup that requires changing to the appropriate rolling patterns. The mill would like to produce I-beams in batches of 40 tons (resulting in a production batch every eight weeks). For what changeover costs would this batch size be optimal? (Show in excel if you can?)

Answer 1

To determine the optimal changeover costs for a batch size of 40 tons, you need to consider the holding costs and setup costs. The holding cost is 25% of the I-beams cost, and the setup cost needs to be minimized. Excel's Solver function can help find the optimal value for the setup cost.

Step-by-step explanation:

In order to determine the changeover costs that would make a batch size of 40 tons optimal, we need to consider the holding costs and setup costs. The holding cost is 25% of the I-beams cost, which is $2,000 per ton. Therefore, the holding cost is $500 per ton. Since the mill produces 20 tons per week and the demand is 5 tons per week, the mill needs to hold 15 tons for each of the 8 weeks between production batches. This results in a holding cost of $7,500 per batch. Now, we need to consider the setup costs. With a batch size of 40 tons, the mill would need to go through 2 setup processes every 8 weeks. Let's denote the setup cost as 'C'. Therefore, the total cost per batch, including the holding cost and setup cost, would be $7,500 + 2C. To determine the optimal changeover costs, we need to find the value of 'C' that minimizes the total cost per batch. You can use Excel's Solver function to find the optimal value for 'C'.