Question

An automobile manufacturer has five outdated plants: one each in Michigan, Ohio, and California and two in New York. Management is considering modernizing these plants to manufacture engine blocks and transmissions for a new model car. The cost to modernize each plant and the manufacturing capacity after modernization are as follows: Plant Michigan New York New York Ohio California Cost ($ millions) 25 35 35 40 20 Engine Block (1000s) 500 800 400 900 200 Transmissions(1000s) 300 400 800 600 300 The projected needs are for total capacities of 900,000 engine block and 900,000 transmissions. Management wants to determine which plants to modernize to meet projected manufacturing needs and, at the same, minimize the total cost of modernization. a) Develop a table that lists every possible option available to management. As part of your table, indicate the total engine block capacity and transmission capacity for each possible option, whether the option is feasible based on the projected needs, and the total modernization cost for each option. b) Based on your analysis in part (a), what recommendation would you provide for management? c) Formulate a 0-1 integer programming model that could be used to determine the optimal solution to the modernization question facing management. d) Solve the model formulated in part (c) to provide a recommendation for management.

Answer 1

Given inputs:

А B с D E F 1 N 3 3 4 un Plant Cost ($ mn) Engine Block (1000s) Transmissions (1000s) Michigan 25 500 300 New York 35 800 400 New York 35 400 800 Ohio 40 900 600 California 20 200 300 Total 155 2800 2400 6 7 00

a. Possible cases

Following are the options for each possible of combination in two manufacturing plant modernization.

Total combinations: 5P2 = 10

Feasible combinations = 6

Case 4 Plant Michigan New York Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 25 500 300 35 400 800 60 900 1100 Plant New York Ohio Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 35 800 400 40 900 600 75 1700 1000 Case 5 9 10 Case 1 11 12 13 14 15 16 Case 2 17 18 19 20 21 22 Case 3 23 24 25 26 500 Plant Michigan Ohio Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 25 300 40 900 600 65 1400 Plant New York Ohio Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 35 400 800 40 900 600 75 1300 1400 900 Case 6 Plant New York New York Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 35 800 400 35 400 800 70 1200 1200 Plant Ohio California Total Cost ($ mn) Engine Block (1000s) Transmissions (1000s) 40 900 600 20 200 300 60 1100 900 -

Please note that, any combination having more than 2 plants can fulfill the requirement. Hence not stated here separately.

b. Based on above analysis, case 1 & 6 have lowest modernization cost i.e. $60 mn. Recommendation can be given for implementing modernization in any one of the above plants.

c. Integer programming is as follows,

Let,

xi : binary decision variable for selecting plant

yi : cost of renovation for plant

mi : total requirement of Engine Block (1000s)

ni : total requirement of Transmission (1000s)

Constraints:

∑ mi > = 900

∑ ni >= 900

Objective function:

z = min ∑(xi * yi)

Excel formulation:

А B D E F G H 1 Plant Selection Engine Block (1000s) Transmissions (1000s) Engine Block (1000s) Transmissions (1000s) 2 3 500 300 4 400 Michigan New York New York Ohio California 800 400 5 800 =$C3*D3 =$C4*D4 =$C5*D5 =$C6* D6 =$C7* D7 =SUM(G3:67) =$C3*E3 =$C4*E4 =$C5*E5 =$C60E6 =$C7*E7 =SUM(H3:H7) 6 900 600 300 7 200 8 9 >= 900 900 Cost ($ mn) 10 11 12 13 14 15 16 17 18 19 Plant Michigan 25 New York 35 New York 35 Ohio 40 California 20 Total cost 20

Solver Parameters:

A B с D E F G H K L N M . o Р Q R S 1 Plant Selection Engine Block Transmissions (1000s) (1000s) 500 300 800 400 800 $C$19 Michigan New York New York Ohio California 400 Engine Block Transmissions Solver Parameters (1000s) (1000s) 0 0 Set Objective: 0 0 0 0 To: oMax 0 0 By Changing Variable Cells: 0 0 $C$3:$C$7 0 0 O Min O value of: 600 900 200 300 EN >= >= 900 900 Subject to the Constraints: $C$3:$C$7 = binary $G$8:$H$8 >= $G$10:$H$10 Add Cost ($ mn) 25 35 35 Change Plant Michigan New York New York Ohio California Delete 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 40 20 Reset All Load/Save Make Unconstrained variables Non-Negative Total cost 0i Select a Solving Method: Simplex LP Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Help Solve Close

Solution:

A B с D E F G H J K 1 Plant Selection 2 3 Engine Block Transmissions (1000s) (1000s) 500 300 Engine Block Transmissions (1000) (1000s) 500 300 0 0 1 4 0 400 Michigan New York New York Ohio California 800 400 5 1 800 600 800 0 6 0 400 0 0 900 7 0 200 300 0 8 900 1100 9 > 900 900 Solver Results Х Plant Michigan New York New York Ohio California Cost ($ mn) 25 35 35 40 Solver found a solution. All Constraints and optimality conditions are satisfied. Reports Answer 20 O Keep Solver Solution O Restore Original Values Total cost 60 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 Return to Solver Parameters Dialog Outline Reports OK Cancel Save Scenario... Solver found a solution. All constraints and optimality conditions are satisfied. When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this means Solver has found a global optimal solution.

d. Based on the integer programming model, case 1 can be recommended to management i.e. Michigan and Newyork with total modernization cost of $ 60 mn.

If you like the answer please give an up-vote, this will be quite encouraging for me, thank you.

If you have further suggestions in answer, please write below on the comment section, I will certainly look into it.