Video summary

Linear Programming Sensitivity Analysis - Interpreting Excel's Solver Report

Main summary

Key takeaways

Educational

Main ideas and lessons from the video

The video explains how to interpret Excel Solver’s Sensitivity Report for a linear programming (LP) model, focusing on:

  • Optimality ranges for objective coefficients (unit profits)
  • Reduced costs to determine which variables/products should be included or excluded
  • Feasibility (RHS) ranges for constraints and how shadow prices impact the objective value
  • Slack/Surplus to distinguish binding vs non-binding constraints

It also covers the “100% Rule” for checking whether the current optimal solution remains valid when multiple objective coefficients change simultaneously.

Step-by-step methodology

1) Identify the optimal solution and compute the optimal objective value

From the Sensitivity Report, the optimal variable values are:

  • (A = 0)
  • (B = 70)
  • (C = 30)

Compute the optimal objective function value by substituting into the objective function. The tutorial reports:

  • Optimal objective value = 5850

Assumption clarified in the tutorial:

  • The objective coefficients are treated as unit profits for products A, B, C.

2) Use “Allowable Increase & Decrease” (optimality ranges) for objective coefficients

The optimality ranges specify how far each objective coefficient can change without changing the optimal solution’s structure.

Product A

  • Allowable Increase: 7.5
    • If A increases from 50 to at most 57.5, the optimal solution remains unchanged.
  • Allowable Decrease: 1E+30 (treated as effectively infinity)
    • Lower limit: negative infinity

Product B

  • Upper limit: infinity
  • Lower limit: computed as 60 − 5 = 55
  • Interpretation:
    • If B’s unit profit decreases by more than the allowable decrease, the optimal solution may change.

Product C

  • Upper limit: 60
  • Lower limit: 40
  • Interpretation:
    • Changes within [40, 60] keep the optimal solution unchanged.

Examples

  • If unit profit on B decreases by 20
    • Allowable Decrease on B is 5
    • Since (20 > 5), the optimal solution will no longer be optimal.
  • If unit profit on C becomes 45 (from 55)
    • 45 is within [40, 60]
    • The optimal solution remains optimal
    • Total profit becomes 5550

3) Handle multiple objective coefficient changes using the “100% Rule”

Excel sensitivity analysis often assumes one change at a time. When multiple coefficients change simultaneously, use the 100% Rule:

100% Rule (video’s application)

For each changed coefficient:

  1. Compute [ \text{ratio}=\frac{\text{proposed change}}{\text{allowable change}} ] using the appropriate increase or decrease bound.

  2. Sum the ratios across all changed coefficients.

  3. If sum of ratios ≤ 100%, the current optimal solution remains valid.
  4. If sum > 100%, the optimal solution may no longer be valid and the LP should be re-solved.

Example from the video

Change A and C together so both become consistent with a new profit value of 53:

  • Proposed increase in A

    • (53 - 50 = 3)
    • Allowable increase = 7.5
    • Ratio = (3/7.5 = 0.4)

  • Proposed decrease in C

    • (55 - 53 = 2)
    • Allowable decrease = 15
    • Ratio = (2/15 \approx 0.133)

  • Sum of ratios = (0.4 + 0.133 = 0.533) → 53.3%

Since 53.3% < 100%:

  • the optimal solution remains optimal
  • total profit becomes 5790

4) Interpret reduced costs

Reduced cost interpretation (example for variable A):

  • Reduced cost shown: -7.5

Meaning given:

  • The magnitude indicates how much the objective would worsen/improve if you try to include the variable in the basis (i.e., make it positive).
  • Product A is not profitable enough to enter the optimal mix.

Tutorial implication:

  • To make A positive, profit contribution must improve by at least 7.5.

5) Use RHS feasibility ranges (shadow prices)

The constraints section provides RHS ranges where the shadow price remains valid.

Definition

  • Shadow price = change in the optimal objective value per unit increase in the RHS of a constraint.

Examples

Constraint 1

  • Shadow price = 60
  • If RHS increases by 5:
    • Allowable Increase for constraint 1 = 10
    • Since (5 \le 10), shadow price applies
    • Profit change = (5 \times 60 = 300)
    • Profit goes from 5850 to 6150

Constraint 2

  • RHS decreases from 300 to 250
    • Proposed decrease = 50
  • Allowable Decrease for constraint 2 = 30
  • Since (50 > 30), shadow price is not valid
  • Conclusion: re-solve the LP

Constraint 4

  • Current RHS = 60, new RHS = 44
    • Decrease = 16
  • Allowable Decrease is sufficient
  • Shadow price = -2.5
  • Objective change = (-16 \times -2.5 = 40)
  • Profit increases by 40 from 5850 to 5890

6) Interpret slack and surplus; identify binding constraints

Slack/surplus is computed conceptually as:

[ \text{Slack/Surplus} = (\text{Final LHS value}) - (\text{RHS}) ]

Mapping based on constraint type:

  • Constraints 1 & 2 are ≤ constraints → associated with slack variables
  • Constraints 3 & 4 are ≥ constraints → associated with surplus variables

Values stated

  • Slack:
    • Constraint 1 slack = 0
    • Constraint 2 slack = 30
  • Surplus:
    • Constraint 3 surplus = 20
    • Constraint 4 surplus = 0

Binding constraints

  • Defined as those where Final value equals RHS → slack/surplus = 0
  • Binding: constraints 1 and 4
  • Non-binding: constraints 2 and 3

Extra note:

  • Binding constraints typically have non-zero shadow prices.

Speakers / sources featured

  • Unnamed instructor / narrator (the video author) speaking throughout the tutorial.

Original video