Question

Complete model Objective : Maximize z = y Constraint: y + x12 + x13 + x14 + x15 – (1 + 0.769*21 + .625*31 + 105*4 + 0.342*sı) = 5 X21 + x23 + x24 + x25 – (0.769x12 + 12x32 + 135442 + 2x52) = 0 x31 + x32 + x34 + x35 – (0.625x13 + 0.813x23 + 169843 + 0.543453) = 0 X41 + x2 + x^3 + x45 - (105x14 + 137x24 + 169x34 + 0.0037*54) = 0 X51 + X52 + X53 + x45 – (0.342x15 + 0.445x25 + 0.543x35 + 0.0032x45) = 0 1 X1j < 5, j = 2,3,4,5 X2; <3, j = 1,3,4,5 X3; 3 3.5, j = 1,2,4,5 X4j = 100,j = 1,2,3,5 X5; < 2.8, j = 1,2,3,4 Xij > 0, for all i and j

can you give solution y , x12 , x15 , x25 , x31 , x32 , x41 , x42 , x43 , x53 , x54 with Excel solver?

Answer 1

ANS.

The problem is formulated in excel as shown: Maximise z = y Constraint: y + x12 + x13 + x14 + x15 - (1 + 1/0.769x21 + 1/0.625x31 + 1/105x41+ 1/0.342x51) = 5 So y = =5-(J16+J17+J18+J19-(1 + 1/0.769*L16+ 1/0.625*N16+1/105*P16+1/0.342*R16)) x21 + x23 + x24 + x25 - (0.769x12 + 1/0.813x32 + 1/137x42 + 1/0.445x52) = 0 x31 + x32 + x34 + x35 - (0.625x13 +0.813x23 + 1/169x43 + 1/0.543x53) = 0 x41 + x42 + x43 + x45 - (105x14 + 137x24 + 169x34 + 1/0.0032x54) = 0 x51 + x52 + x53 + x45 -(0.342x15 +0.445x25 +0.543x35 +0.0032x45) = 0 x1j<= 5; j = 2,3,4,5 x2 <= 3; j = 1,3,4,5 x3j <= 3.5; j = 1,2,4,5 x4j <= 100; j = 1,2,3,5 x5j <= 2.8; j = 1,2,3,4 xij >= 0 for all i and Excel snapshot with formulas is given below:

_ B C D F G H J K L M N. 1 2 3 Maximise z = y z = y = 5 - [x12 + x13 + x14 + x15 - (1 + 1/0.769x21 + 1/0.625x31 + 1/105x41+ 1/0.342x51)] Constraint: =5-(J16+J17+J18+J19-(1 + 1/0.769*L16+ 1/0.625*N16+1/105*P16+1/0.342*R16)) y + x12 + x13 + x14 + x15 - (1 + 1/0.769x21 + 1/0.625x31 + 1/105x41+ 1/0.342x51) = 5 7.83 5 6 -0.25 x21 + x23 + x24 + x25 - (0.769x12 + 1/0.813x32 + 1/137x42 + 1/0.445x52) = 0 =SUM(117:120)-(0.769*G17+1/0.813*K18+1/137*M18+1/0.445*018) 7 9 0.71 *31 + x32 + x34 + x35 - (0.625x13 +0.813x23 + 1/169x43 + 1/0.543x53) = 0 =SUM(K17:K20)-(0.625*G18+0.813*|18+1/169*M19+1/0.543*019) 10 11. 12 -719.5 x41 + x42 + x43 + x45 - ( 105x14 + 137x24 + 169x34 + 1/0.0032x54) = 0 =SUM(M19:M22)-(105*G21+137*121+169*K21+1/0.0032*022) 13 14 15 x51 + x52 + x53 + x45 -(0.342x15 +0.445x25 +0.543x35 +0.0032x45) = 0 2.67 = 0 16 17 X12 X21 X31 X41 X51 18 19 X13 x32 x42 1.00 1.00 1.00 1.00 x1j <= 5; j = 2,3,4,5 x2j <= 3; j = 1,3,4,5 x3j <= 3.5; j = 1,2,4,5 x4j <= 100; j = 1,2,3,5 x5j <= 2.8; j = 1,2,3,4 xij >= 0 for all i andj 1.00 1.00 1.00 1.00 1.00 1.00 1.00 1.00 X23 x24 X25 1.00 1.00 1.00 1.00 20 X14 1.00 1.00 1.00 1.00 X43 x52 X53 x54 x34 X35 x15 x45 21 22 23

The values of all variables is kept as 1 to start of with the different constraints and objective function formulas are give as well The solver input is as given below:

Solver Parameters Set Objective: $L$3 To: O Max O Min O Value of: By Changing Variable Cells: $F$18:$F$21, $H$18:$H$21, $J$18:$J$21,$L$18:$L$21,$N$18:$N$21 Add Change Subject to the Constraints: $F$18:$F$21 <= 5 $F$18:$F$21 >= 0 $H$18:$H$21 <= 3 $H$18:$H$21 >= 0 $J$12 = $L$12 $J$15 = $L$15 $J$18:$J$21 <= 3.5 $J$18:$J$21 >= 0 $J$6 = $L$6 $J$9 = $L$9 $L$18:$L$21 <= 100 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.

The output is given below in excel:

B I CD F. G H 1 3 Maximise z = y z = y = 5 - [x12 + x13 + x14 + x15 - (1 + 1/0.769x21 + 1/0.625x31 + 1/105x41+ 1/0.342x51)] Constraint: 55-(J16+J17+J18+J19-(1 + 1/0.769*L16+ 1/0.625*N16+1/105*P16+1/0.342*R16)) y + x12 + x13 + x14 + x15 - (1 + 1/0.769x21 + 1/0.625x31 + 1/105x41+ 1/0.342x51) = 5 6.08 4 5 6 0.00 x21 + x23 + x24 + x25 - (0.769x12 + 1/0.813x32 + 1/137x42 + 1/0.445x52) = 0 =SUM(117:120)-(0.769*G17+1/0.813*K18+1/137*M18+1/0.445*018) 7 9 0.00 = 0 X31 + x32 + x34 + x35 - (0.625x13 +0.813x23 + 1/169x43 + 1/0.543x53) = 0 =SUM(K17:K20)-(0.625*G18+0.813*|18+1/169*M19+1/0.543*019) 10 11 12 -0.0 x41 + x42 + x43 + x45 - ( 105x14 + 137x24 + 169x34 + 1/0.0032x54) = 0 =SUM(M19:M22)-(105*G21+137*121+169*K21+1/0.0032*022) 13 14 15 x51 + x52 + x53 + x45 -(0.342x15 +0.445x25 +0.543x35 +0.0032x45) = 0 -0.00 16 17 18 1.24 X31 X41 x12 X13 x21 x23 3.50 2.16 x51 x52 19 0.80 X42 20 75.51 25.25 20.33 1.01 x14 x24 x32 x34 x35 x1j <= 5; j = 2,3,4,5 x2j <= 3; j = 1,3,4,5 x3j <= 3.5; j = 1,2,4,5 X4j <= 100; j = 1,2,3,5 x5j <= 2.8; j = 1,2,3,4 xij >= 0 for all i andj x43 x53 2.68 0.39 x15 5.00 x25 3.00 0.05 X45 x54 21 22 23

The values obtained are as follows: x12 = 1.24 x15 = 5 x23 = 0.80 x25 = 3 *31 = 3.5 *32 = 2.16 *35 = 0.05 x41 = 75.51 x42 = 25.25 x43 = 20.33 x45 = 1.01 *53 = 2.68 x54 = 0.39

please rate it.....