Question

Answers please

Production and Operations Management (29:623:311) Spring 2019 3. A linear programming model is given as follows: maximize Z = 50x1 + 80x2 + 64x3 + 80x4 Subject to 5xit 2.5x2 +4.5x3 +3.99x, S 600 4.1x, + 2.6xz +5.5x +1.9x, S 500 15x, 22x2 +18x3 +25x4 S 400 8x+ 12.6x2 +9.7x3 +10.55x4 S 1700 x1 + x2 2 0.60 (a) Solve the problem by using excel solver. (b) What are the sensitivity ranges for the objective function coefficients? (c) What are the sensitivity ranges for the constraint quantity values? (d) What is the effect on the optimal solution if the 2nd constraint were discarded from the formulation? (e) What is the effect on the optimal solution if the coefficient of x, is decreased from 80 to 50?

Answer 1

a)

The solver setup is shown below

X3 64 Obj func 0 X2 2 Coeff 3 Dec var 4 5 Const 1 6 Const 2 7 Const 3 8 Const 4 9 Const 5 10 50 80 80 Value 0 0 Limit 2.5 3.99 1.9 25 10.55 4.1 5.5 18 9.7 500 400 1700 2.6 15 12.6 0 12 13 15 16 18 19 Sensitivity Report 51 Sheet785 Sheet787 Sheet788 Sensitivity Report 2 Sheet791 She 囲回凹 145%

The spreadsheet formulas are shown below

A1 x4 Obj func 2 80 3 0 4 5 3.99 6 1.9 7 25 8 10.55 9 10 -SUMPRODUCT(B2:E2,SB$3:ŞE$3) Value Limit -SUMPRODUCT(B5:E5,SBS3:$E$3) -SUM PRODUCT(B6:E6,SBS3:$E$3) SUMPRODUCT(B7:E7,ŞB$3:$EŞ3) SUMPRODUCT(B8:E8,SB$3:$EŞ3) B3+C3 600 500 400 1700 -SUM(B3:E3)*0.6 12 13 15 16 17 18 Sensitivity Report 51 Sheet785 Sheet787 Sheet788 Sensitivity Report 52 Sheet791 She . READY + 145%

The solver parameters are shown below

Solver Parameters Obj func Set Objective: SG$2 2 Coeff 3 Dec var 50 80 Max Yalue Of: 0 0 Value 0 0 0 By Changing Variable Cells: SBS3 SES3 Subject to the Constraints SGS9-SIS9 2.5 2.6 5 Const 1 6 Const 2 7 Const 3 8 Const 4 9 Const 5 10 4.5 5.5 18 9.7 3.99 1.9 25 10.55 15 12.6 Delete Reset All Load/Save 12 Sclect a Solving Method: Simplex LP Solving Method 15 16 17 18 19 Select the GRG Nonlinear engine for Sorer Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Sover problems that are non-smooth. Help ciose Sens tivity Report 51 Sheet785 Sheet787 Sheet788 Sensitivity Report 52 Sheet791 She

The result is shown below. Here, x2 = 18.18 and x1, x3, x4 = 0

Obj func 1454.55 2 Coeff 3 Dec var 50 80 0 18.1818 0 0 Value 45.4545 47.2727 400 229.091 18.1818 Limit 2.5 2.6 5 Const 1 6 Const 2 7 Const 3 8 Const 4 9 Const 5 10 4.5 5.5 18 9.7 3.99 1.9 25 10.55 Solver Results 500 400 1700 10.9091 15 Solver found a solution. All Constraints and optimality conditions are satisfied 12.6 Keep Solver Solution Sensitivity O Bestore Original values Return to Solver Parameters Dialog Outline Reports 12 13 Cancel Save Scenario... Solver found a solution. All Constraints and optimality conditions are 15 16 17 18 19 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. Sensitivity Report 51Sheet785 787Sheet788Sensitivity Report 52 Sheet71She 14596

The sensitivity analysis is shown below

Microsoft Excel 15.0 Sensitivity Report A B 6 Variable Cells Final Value Reduced objective Allowable Allowable Decrease Cell Name Coefficient Increase 9SBS3 Dec var x1 10 $C$3 Dec var x2 11 $D$3 Dec var x3 12 SE$3 Dec var x4 13 14 Constraints 15 16 Cell 17 $G$5 Const 1 Value 45.4545455 18 ŞG$6 Const 2 Value 47.2727273 19 SGS7 Const 3 Value 20 $G$8 Const 4 Value 229.090909 21 $G$9 Const 5 Value 18.1818182 1E+30 0 -454545455 0 0 1.45454545 0 -10.9090909 50 4.54545455 80 64 1.45454545 80 10.9090909 18.1818182 1E+30 1E+30 Final Value Shadow Constraint Allowable Allowable R.H. Side Increase Name Price Decrease 0 0 400 3.636363636 600 500 400 2568.25397 1E+30 554.545455 1E+30 452.727273 400 1E+30 1470.90909 1E+30 1700 0 7.27272727 4 .. Sheet785 Sheet787 Sheet788 Sensitivity Report 52 Sensitivity Report 53 Sheet791 She ...G + 145%

b)

Sensitivity ranges for obj function coefficients (high to low)

X1 = 54.54 to infinity

X2 = Infinity to 78.22

X3 = 65.45 to infinity

X4 = 90.90 to infinity

c)

Sensitivity ranges for constraint quantity values is (high to low)

Const 1 = Infinity to 45.45

Const 2 = Infinity to 47.27

Const 3 = 2968.25 to 1700 (current value)

Const 4 = Infinity to 229.09

d)

There will be no effect. The second constraint is not a binding constraint

e)

There will be no effect. The allowable decease of x4 coefficient is infinity.