Question

please show all your steps in excel

Consider the following model: Min. cost = 340,000X1 + 270,000X2 + 290,000X3 + 32X4 + 33X5 + 30X6 subject to X4 + X5 + X6 > 38,000 < 21,000X1 < 20,000X2 X6 = 19,000X3 X1, X2, X3 = 0 or 1 X, X5, X6 20 Use excel solver to solve the above mixed integer linear programming model.

Answer 1

The Excel Solver solution can be found as below:

The objective function is as shown in cell D5, which is entered in the solver column with "Min" as the criteria.

Also, The Decision variables X1, X2,X3,..., X6 are as shown in cells D3 to I3. This is represented by changing variable cells.

Coming to the constraint, the cells in the table show the constraint

Constraint 2, 3, and 4 has to be rewitten as follows:

X4 - 21000 X1 <= 0

X5 - 20000 X2 <= 0

X6 - 19000 X3 <= 0

Also, Constraint 5 represents the Binary Variable for variables X1, X2, and X3 being equal to 0 or 1

Now, Optimal Solution is for cost at $1760,000

Decision variables:

X1 = 0; X2 = 1 ; X3 = 1 ; X4 = 0; X5 = 20,000 ; X6 = 18,000

D5 X fx =340000*D3+270000*E3+290000*F3+32*G3+33*H3+30*13 Solver Parameters C D E F G H I J Set Objective: SD55 Decision Variables X1 X3 X2 1 X4 0 X5 20000 X6 18000 TO: O Max Min value of: 1 Objective Value 1760000 By Changing Variable Cells: SD$3:SI53 Constraints 38000 38000 0 Add Subject to the Constraints: SD$10 <= $F$10 SD$11 <= $F$11 SD$3:$F$3 = binary SD$8 >= $F$8 SD$9 <= $F$9 SG$3:$1$3 >= $F$13 Change -1000 X1,x2,X3 Binary X4,X5,X6 | >= Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear Options