Question

Solve the following optimization model using Excel Solver. Upload the final excel file. min z=2x2+5xZ+8zy? s.t 8x+16 y +2172400 3x+5y+872 150 4z2+5yx<600 x,y,220 Attach File Browse My Computer Browse Content Collection

Answer 1

Answer:

As it is specifically mentioned in the question, we will solve the given Non-LP model by using MS-Excel Solver data tool as mentioned in the below steps:

Step 1: Prepare the following table with the highlighted formulas in the excel

(Note: Make Sure to prepare this table in the exact Rows and Columns as mentioned in the below screenshot)

А B D E с Decision Variables 1 Particulars 2. х Y 3 Decision Variable Min Z 4 5 c1 > 400 = 2*(B3^2)+5*83*D3+8*D3*(C3^2) =8*B3+16*C3+21*D3 =3*B3+5*C3+8*D3 =4*(D3^2)+5* B3* C3 6 C2 > 150 7 C3 < 600

Hence, we get the following table:

А B D E с Decision Variables 1 Particulars 2 х у Z 3 Decision Variable 4 Min Z 0.00 0 > 400 5 6 7 C1 C2 0 > 150 C3 0 < 600

Step 2: Now, open Solver (Data > Analyze > Solver), and fill the exact data in Solver Pop-Up Window as shown in the below screenshot:

Solver Parameters X Set Objective: $B$41 1 To: Max O Min Value Of: 6925 By Changing Variable Cells: $B$3:$D$3 1 Subject to the Constraints: $B$5:$B$6 >= $D$5:$D$6 $B$7 <= $D$7 > 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

Step 3: Once, you press the solve button as mentioned in the above step, you will get the following table. Here, the highlighted values are the optimal values for the decision variables:

A B D E С Decision Variables 1 Particulars 2 х z у 10.4041 3 Decision Variable 0.00 12.2474 < = Optimal Solution 4 Min Z 10605.79 <Min Value c1 424 > 400 5 6 C2 150 150 7 C3 600 VI 600

(Kindly raise an upvote for this answer, if you found it useful)