🔍
Linear Programming Sensitivity Analysis - Interpreting Excel's Solver Report - YouTube
Channel: Joshua Emmanuel
[0]
Welcome!
[1]
In this tutorial, I’ll be answering the
following questions for this LP model.
[5]
I’ll also be interpreting the components
of LP sensitivity analysis based on this Sensitivity
[11]
Report from Excel.
[13]
First note here that these objective coefficients
here are displayed here in the output.
[19]
We assume these are unit profits for products
A, B, and C. And the constraint Right Hand
[24]
Side are displayed here.
[26]
So let’s begin by examining the top part
of the table for optimality ranges.
[31]
The optimal solution is represented by the
final values here: A = 0, B = 70, and C = 30.
[39]
So the optimal objective function value can
be found by plugging the optimal solution
[43]
into the objective function to obtain 5850.
[47]
Now, these Allowable Increase & Decrease values
specify how much the objective coefficients
[53]
can change before the optimal solution will
change.
[57]
For example, since the Allowable Increase
for A is 7.5, if we increase the objective
[63]
coefficient of A, from 50 to any value, up
to an upper limit of 57.5, the optimal solution
[70]
will not change.
[72]
For the Allowable Decrease, Excel usually
represents very large values with 1E+30.
[78]
So you can think of it as infinity.
[80]
Thus the lower limit for the coefficient of
A is negative infinity.
[86]
For B, the upper limit will be infinity, and
the lower limit will be 60 – 5 which gives
[91]
55.
[93]
For C, we have 60 for the upper limit, and
40 for the lower limit.
[98]
So what will happen to the optimal solution
if the unit profit on B (that is, the coefficient)
[103]
decreases by 20?
[105]
We can see here that the Allowable Decrease
on B is 5.
[109]
Therefore, the optimal solution will change
if we decrease it by 20.
[113]
That is, these final values will no longer
be optimal.
[116]
And what will happen if the unit profit on
C decreases to 45 from its current value of
[122]
55.
[123]
You can see here that 45 is between 40 and
60, so the optimal solution will remain optimal,
[129]
but the total profit now becomes 5550.
[133]
Next, what will happen if unit profits on
both A and C are changed to 53?
[141]
Note that this sensitivity report only accounts
for one change at a time.
[146]
So if there are simultaneous changes, as we
have here, we have to check if the sum of
[151]
the ratio of proposed changes to allowable
changes is within 100%.
[157]
If the sum of these ratios is over 100%, the
optimal solution may no longer be valid.
[163]
This is called the 100% Rule.
[165]
The proposed increase in A is 53 minus 50
which gives 3 and the allowable is 7.5, giving
[174]
a ratio of 0.4.
[178]
Whereas for C, the proposed decrease is 55
minus 53 which gives 2, with an allowable
[184]
decrease of 15, giving a ratio of 0.133.
[190]
The sum of these ratios is 53.3% which is
less than 100% so the optimal solution will
[197]
remain optimal, and the total profit becomes
5790.
[203]
Now let’s discuss the reduced cost for A.
The reduced cost of -7.5 here represents the
[209]
amount by which profit will be reduced if
we include a unit of A in the solution.
[215]
In essence, product A is not attracting enough
profit to warrant its inclusion in the product
[221]
mix.
[222]
To include product A (or to make A positive),
it’s profit contribution needs to improve
[227]
by at least 7.5.
[229]
But at its current value of 50, making A positive
in the optimal solution will bring a reduction
[234]
of 7.5 to Profit, per unit.
[239]
Now the bottom part of the table, titled Constraints,
addresses the range of feasibility.
[245]
That is, the range for the Right Hand Side
of a constraint where the shadow price remains
[250]
unchanged.
[251]
For example, as long as the RHS of constraint
1 is between 93.33 and 110, the shadow price
[259]
of 60 will apply.
[261]
Shadow Price here refers to the amount of
change in the optimal objective function value,
[266]
per unit increase in the RHS of a constraint.
[270]
So what will happen to optimal profit if the
RHS of Constraint 1 increases by 5.
[277]
Notice here that the Allowable Increase for
constraint 1 is 10.
[281]
So an increase of 5 is allowable.
[283]
Therefore, the optimal profit will change
by 5 (the amount of change) times 60 (the
[289]
shadow price) to give 300.
[292]
Since this is positive, Profit will increase
by 300 from 5850 to become 6150.
[299]
And what will happen if the RHS of constraint
2 decreases to 250.
[304]
Notice here that the Allowable Decrease for
Constraint 2 is 30.
[308]
The current value is 300, so decreasing it
to 250 is a proposed decrease of 50.
[314]
Since the proposed decrease exceeds the allowable
decrease, the shadow price is no longer valid.
[320]
As a result, we cannot tell what will happen
to optimal Profit based on this output.
[325]
We need to re-solve the model.
[327]
Next, how will the objective function change
if the RHS of constraint 4 changes to 44.
[333]
The current RHS value for constraint 4 is
60.
[339]
Changing it to 44 represents a decrease of
16 units which is less than the Allowable
[344]
Decrease if 60.
[346]
Therefore, the shadow price applies and the
total profit changes by -16*-2.5 to give 40.
[355]
Since this value is positive, optimal profit
will actually increase by 40 to become 5890.
[363]
For Slack and Surplus values, we simply take
the differences between the Final Values (that
[368]
is, the left side of the constraint) and the
RHSides.
[371]
Constraints 1 & 2 are less or equal constraints
so they will be associated with slack variables
[378]
while 3 & 4 are greater or equal constraints
and will be associated with surplus variables.
[384]
So the slack value for constraint 1 is 0,
and for constraint 2, it is 30.
[388]
The surplus for constraint 3 is 20, and for
constraint 4 it is 0.
[394]
Lastly, the binding constraints are the ones
that have Final Value equal to the RHSide-
[400]
that is, the ones that have 0 slack or surplus
values.
[405]
So constraints 1 and 4 are binding, while
2 and 3 are non-binding.
[410]
Note also that the binding constraints have
non-zero shadow prices.
[413]
And that’s it for this video.
[416]
Thanks for watching.
Most Recent Videos:
You can go back to the homepage right here: Homepage