Question

Solve the following optimization model using Excel Solver. Upload the final excel file. min z=2x2+5xZ+8zy2 sit 8x+16y+21 22400 3x+5y+872 150 4z2+5yx=600 X, Y, 220

Answer 1

Formulate Excel model as shown below:

A B с D 1 X= st z = 2 3 y = 4 5 6 Objective value = =2*B2^2+5*B2*B4+8*B4*B312 7 8 Constraint 1 E8*B2+16*B3+21*B4 9 Constraint 2 =3*B2+5*B3+8*B4 10 Constraint 3 =4*B4^2+5*B3*B2 11 12 13 8 >= 400 150 600 14

x =
y =
z =
Objective value =	=2*B2^2+5*B2*B4+8*B4*B3^2
Constraint 1	=8*B2+16*B3+21*B4	>=	400
Constraint 2	=3*B2+5*B3+8*B4	>=	150
Constraint 3	=4*B4^2+5*B3*B2	<=	600

Enter Solver Parameters as follows:

Solver Parameters Х Set Objective: $B$6 ↑ To: Max Min Value of: 0 By Changing Variable Cells: $B$2:$B$4 1 Subject to the Constraints: $B$10 <= $D$10 $B$8:$B$9 >= $D$8:$D$9 > Add Change Delete Reset All Load/Save Make Unconstrained variables Non-Negative Select a Solving Method: GRG Nonlinear 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

Note that objective function and constraint 3 have quadratic powers of variables, therefore, it is a nonlinear model.

Hence, in Solver Parameters, Select a Solving Method: GRG Nonlinear

Click Solve.

Values appear automatically in variables cells , B2:B4

A B с D E F I G 1 4 B 2 X= 0 3 y = 10.40408 z = 12.24745 5 6 Objective value = 10605.79 7 8 Constraint 1 423.6617 >= 400 9 Constraint 2 150 150 10 Constraint 3 600 600 11 12 Solver Results X 13 14 Solver found a solution. All Constraints and optimality 15 conditions are satisfied. Reports Answer 16 Keep Solver Solution Sensitivity 17 Limits 18 Restore Original Values 19 20 21 | Return to Solver Parameters Dialog Outline Reports 22 23 24 OK Cancel Save Scenario... 25 26 Solver found a solution. All Constraints and optimality conditions are satisfied. 27 28 When the GRG engine is used, Solver has found at least a local optimal solution. When Simplex LP is used, this 29 means Solver has found a global optimal solution. 30 31

Click OK

.

Result:

x =	0
y =	10.40408
z =	12.24745
Objective value =	10605.79