Question

1. Given below is a fictitious Excel output (numbers might be all made up) Answer the questions that follow. You are being tested for your ability to read the output and your skill in sensitivity analysis as taught in class.)
   

Variable Cells
			Final	Reduced	Objective	Allowable	Allowable
	Cell	Name	Value	Cost	Coefficient	Increase	Decrease
	$B$6	X1	45	0	12	10	8
	$C$6	X2	0	-6	8	15	12
	$D$6	X3	10	0	5	20	15
	$E$6	X4	35	0	6	8	10
Constraints
			Final	Shadow	Constraint	Allowable	Allowable
	Cell	Name	Value	Price	R.H. Side	Increase	Decrease
		Const1	74	12	74	50	20
		Const2	54	8.5	54	42	40
		Const3	35	-5	35	25	20
		Conts4	80	0	120	30	25

(a)        What is the objective function? You will write the algebraic version like
                          Z= 2X1+ 3X2+…etc (10)




(b)         What is the optimal solution, and the optimal value of the objective function? (15)

(b)         Can we make the following change to the RHS of the constraints as follows: (20)

                             const. 1   +20
                             const. 2    -10
                             const. 3     -5

                            
If so, what is the value of the objective function after the change. (you will show FULL work)

Answer 1

(a) The objective function will be given by the objective coefficients in the first table:

(b) The optimal solution is given by the final value column in the first table:

(c) The change should be compared against the allowable increase if positive or the allowable decrease if negative as given in the second table. As multiple constraints are changing, we have to add up the fraction of change to the allowed change. If the sum is less than 1, the change is allowed.

Constraint 1: Increase: Fraction = Increase / Allowed increase = 20 / 50 = 0.4

Constraint 2: Decrease: Fraction = Decrease / Allowed decrease = 10 / 40 = 0.25

Constraint 3: Decrease: Fraction = Decrease / Allowed decrease = 5 / 20 = 0.25

Total fraction = 0.4 + 0.25 + 0.25 = 0.9 < 1

So, the change is allowed.

So, the objective function change is calculated by multiplying each increase / decrease by the corresponding shadow price from the second table.

So, the new value of the objective function will be 980.