Optimal Brake Shoe Production Scheduling: Minimizing Costs with ILP and Spreadsheet Modeling

This article explores the optimal scheduling of brake shoe production across six machines with varying costs and capacities. Radford Castings has received an order for 1,800 brake shoes and needs to determine the optimal production schedule to minimize costs. We will use both Integer Linear Programming (ILP) and spreadsheet modeling to find the solution.

a. ILP Model:

Let Xi represent the number of brake shoes produced on Machine i (i = 1, 2, 3, 4, 5, 6). The objective is to minimize the total cost while meeting the demand of 1,800 brake shoes.

Minimize:

Cost = 1000X1 + 950X2 + 875X3 + 850X4 + 800X5 + 700X6

Subject to:

21500X1 + 23600X2 + 25750X3 + 24400X4 + 20600X5 + 26800X6 >= 1800 Xi >= 0 (for all i)

b. Spreadsheet Model:

Using a spreadsheet software like Microsoft Excel or Google Sheets, create a table with the following columns:

• Machine
• Fixed Cost
• Variable Cost
• Capacity
• Quantity Produced
• Total Cost

Input the given data into the respective columns. In the 'Quantity Produced' column, input the decision variables X1, X2, X3, X4, X5, and X6. Use the SUMPRODUCT function to calculate the 'Total Cost' by multiplying the 'Quantity Produced' with 'Variable Cost', and adding the 'Fixed Cost'.

Use the SUM function to calculate the total quantity produced and compare it with the demand of 1,800 brake shoes. Apply Solver (Excel) or the built-in optimization tool (Google Sheets) to minimize the 'Total Cost' while satisfying the capacity and demand constraints.

c. Optimal Solution:

The optimal solution will depend on the specific values given in the problem and the solver tool used. The optimal solution will provide the values of X1, X2, X3, X4, X5, and X6, which represent the number of brake shoes produced on each machine to minimize the total cost.