The Sensitivity Report is where you go after the Answer Report. It tells you how stable the optimal solution is: how much a cost, a price, or a capacity limit can change before the current plan stops being optimal. The three numbers to focus on are reduced costs (for variables at zero), shadow prices (for binding constraints), and the allowable increase/decrease ranges (for both).
Difficulty: Intermediate to Advanced. You need to understand the LP formulation and the Answer Report first. Read the companion study notes on the make-or-buy problem setup before this document.
Solving an LP gives you one answer for one set of numbers. In practice, costs change, suppliers revise their quotes, and capacity can expand or shrink. Sensitivity analysis answers the manager's follow-up: "How much can things change before I need to re-solve?" It is the bridge between a textbook optimum and a real decision, because real decisions are made under uncertainty. The Sensitivity Report in Excel Solver provides this information automatically after every solve.
Reduced cost
The amount by which a zero-valued variable's objective coefficient would need to improve (decrease, in a minimisation problem) before that variable would become positive in the optimal solution. For a variable already in the solution (final value > 0), the reduced cost is zero.
In simple terms: it is the "entry fee" a currently unused option must pay before the solver would start using it.
Objective coefficient
The per-unit cost (or profit) of a decision variable in the objective function. For "Units to Make Model A," the objective coefficient is $103.94.
Shadow price (dual price)
The rate of change in the optimal objective value per unit increase in a constraint's right-hand side. For a binding ≤ constraint in a minimisation problem, the shadow price is typically negative (more capacity lowers cost). For a binding ≥ constraint, it is typically positive (a higher requirement raises cost).
In simple terms: the shadow price tells you what one more unit of a scarce resource (or one fewer unit of a requirement) is worth to the firm.
Right-hand side (RHS)
The number on the right side of a constraint inequality. For the demand constraints, it is the number of units needed. For capacity constraints, it is the hours available.
Allowable increase
The maximum amount the objective coefficient (or RHS) can rise before the current basis changes. Within this range, the current set of basic and non-basic variables stays the same.
Allowable decrease
The maximum amount the objective coefficient (or RHS) can fall before the current basis changes.
Basis (basic feasible solution)
The set of variables that are positive (non-zero) in the current optimal solution. Sensitivity ranges tell you how far parameters can move without changing which variables are in the basis.
1E+30 (infinity)
Excel's way of displaying a very large number, effectively infinity. An allowable increase of 1E+30 means there is no upper limit on how far that parameter can change in that direction without altering the basis.
The Variable Cells table in the Sensitivity Report has five columns. Here is how to read each one, using Scenario 1 (harnessing = 3,500 hr, wiring = 6,000 hr) as the reference.
The optimal quantity for each decision variable. For variables in the basis (positive), reduced cost = 0. For variables out of the basis (zero), reduced cost > 0.
Examples from Scenario 1:
Units to Make Model A: final value = 0 (out of basis)
Units to Make Model B: final value = 1,350 (in basis)
Units to Buy Model C: final value = 613.7 (in basis)
Only meaningful for variables with a final value of zero. It answers: "How much cheaper would this option need to become before the solver would use it?"
Units to Make Model A: reduced cost = $4.20. The making cost of $103.94 would need to fall to $99.74 (a drop of $4.20) before any units of A would be made.
Units to Buy Model B: reduced cost = $14.46. The buy price of $128.00 would need to drop to $113.54 before any units of B would be bought.
Units to Buy Model D: reduced cost = $8.53. The buy price of $62.00 would need to fall to $53.47 before buying D would enter the solution.
For variables already in the solution (reduced cost = 0), the reduced cost tells you nothing new.
The current per-unit cost in the objective function. This is simply the cost data from the problem.
These define the range over which you can change a variable's objective coefficient without changing the basis.
For Units to Make Model B (objective coefficient = $101.88):
Allowable increase = $14.46, so the making cost can rise to $101.88 + $14.46 = $116.34 before it becomes optimal to stop making B and start buying it.
Allowable decrease = $113.54, so the making cost can fall to $101.88 - $113.54 = -$11.66 (effectively no practical lower bound) without changing the basis.
For Units to Make Model C (objective coefficient = $66.39):
Allowable increase = $5.43, so the cost can rise to $71.82 before the solver would shift more of Model C to buying.
Allowable decrease = $15.11, so the cost can fall to $51.28 before the solution structure changes.
The 1E+30 entries mean there is no finite limit in that direction. For instance, the allowable increase for Units to Buy Model A is 1E+30: the buy price can rise indefinitely without changing the fact that all of A is bought (because the solver has already committed to buying A for other reasons related to the harnessing bottleneck).
Wait, that needs a closer look. Actually, the allowable increase = $4.20 and allowable decrease = $116.70 for Units to Buy Model A. The buy price of $116.70 can rise by $4.20 (to $120.90) before the solver would reconsider. It can fall by the full $116.70 (to zero) before the basis changes. This makes sense: if buying becomes even cheaper, you still buy; only if it becomes too expensive do you switch to making.
The Constraints table has five columns. Again using Scenario 1 as reference.
Final Value is the left-hand side evaluated at the optimum. Constraint R.H. Side is the original limit. If Final Value = R.H. Side, the constraint is binding.
The shadow price tells you the rate at which the objective changes per unit increase in the RHS.
Demand constraints (≥ type):
Model A: shadow price = $116.70. If the firm needed 501 units of A instead of 500, total cost would rise by about $116.70. Notice this equals the buy price of A, because at the margin, extra demand for A is met by buying.
Model B: shadow price = $113.54. Extra demand for B is met partly through a chain of adjustments in the solution, and costs about $113.54 per additional unit.
Model C: shadow price = $88.30. This equals the buy price of C. Since Model C is already partially bought, an extra unit of demand is simply bought at $88.30.
Model D: shadow price = $53.47. Additional Model D demand costs about $53.47 per unit.
Capacity constraints (≤ type):
Wiring: shadow price = 0. Wiring is not binding (603.9 hours of slack), so an extra hour of wiring capacity has no value.
Harnessing: shadow price = -$35.34. One more hour of harnessing capacity would reduce total cost by $35.34. The negative sign is correct for a ≤ constraint in a minimisation problem: relaxing the constraint (raising the RHS) improves the objective (lowers cost).
These tell you how far the RHS can move before the shadow price changes.
Model A demand: allowable increase = 1E+30 (can require arbitrarily more of A; extra units will simply be bought), allowable decrease = 500 (can reduce A's requirement all the way to zero).
Model C demand: allowable increase = 1E+30, allowable decrease = 613.7 (can reduce C's requirement by up to 613.7 units, the exact amount currently being bought).
Harnessing: allowable increase = 380.5 hours, allowable decrease = 1,479.5 hours. The shadow price of -$35.34 holds for harnessing changes between 3,500 - 1,479.5 = 2,020.5 hours and 3,500 + 380.5 = 3,880.5 hours.
For the wiring constraint, the allowable decrease = 603.9 hours (matching the slack). If wiring drops by more than 603.9 hours it would become binding and develop a non-zero shadow price.
Change = -350 hours. Check the allowable decrease for harnessing: 1,479.5. Since 350 < 1,479.5, the shadow price of -$35.34 is valid.
Estimated cost change = 350 × $35.34 = $12,369 increase (less capacity means higher cost because you lose the benefit, so the objective worsens).
Actual new cost from the solver: $605,820. Actual change: $605,820 - $593,450 = $12,370. The $1 difference is rounding. The shadow price prediction is accurate.
Harnessing goes from 3,500 to 5,000 (an increase of 1,500). But the allowable increase for harnessing is only 380.5. Since 1,500 > 380.5, the shadow price of -$35.34 is NOT valid for the full change. You cannot simply say cost falls by 1,500 × $35.34.
What happens instead: beyond 3,880.5 hours of harnessing, the constraint becomes non-binding and the shadow price drops to zero. The actual cost is $573,623. The actual change is $593,450 - $573,623 = $19,827.
If you incorrectly used the shadow price for the full 1,500 hours, you would estimate a decrease of 1,500 × $35.34 = $53,010, which is badly wrong. This is exactly the kind of error exams test for.
Current objective coefficient for Units to Make Model C = $66.39. Allowable increase = $5.43. A $5 increase (to $71.39) is within the allowable range. The current basis stays the same: the solver still makes 2,386.3 and buys 613.7 of Model C. Total cost increases by $5 × 2,386.3 = $11,931.50.
If the increase were $6 instead (to $72.39), that exceeds the allowable increase of $5.43, so the basis would change. The solver might shift more of Model C to buying. You would need to re-solve to find the new optimum.
Current objective coefficient for Units to Buy Model D = $62.00. The reduced cost is $8.53. A $9 drop (to $53.00) exceeds the reduced cost, so Model D would enter the buy solution: some units of D would now be bought rather than all made. The basis changes and you need to re-solve.
Students often apply the shadow price beyond its valid range and get wildly inaccurate answers. Always check the allowable increase/decrease first.
The shadow price is sometimes confused with the reduced cost. Shadow prices belong to constraints; reduced costs belong to variables. They answer different questions.
A shadow price of zero does not mean the constraint is unimportant. It means the constraint is not binding at the current optimum. If conditions change, it may become binding and develop a non-zero shadow price.
Students sometimes think the allowable ranges let you change multiple parameters at once. They do not. Each range assumes all other parameters are held constant. Simultaneous changes require the 100% Rule (which may or may not be in your syllabus) or a full re-solve.
⚠️ "Calculate the new total cost if harnessing capacity changes by X" is a near-certain exam question. The steps: check whether X is within the allowable range, then multiply the shadow price by X and add to (or subtract from) the current total cost.
⚠️ "Interpret the reduced cost of variable Y" is standard. State the amount, state what it means (how much the cost coefficient must improve), and state the threshold value.
⚠️ You may be asked to identify which parameter change would alter the basis and which would not. Compare the proposed change to the allowable range. Within range = same basis. Outside range = new basis, must re-solve.
⚠️ Know why the shadow price of the Model A demand constraint ($116.70) equals the buy price of A. At the margin, one more unit of A demand is met by buying, so the cost of that extra unit is the buy price.
⚠️ When all capacity constraints are non-binding (Scenario 3), every shadow price on capacity is zero and every demand shadow price equals the making cost (because everything is made). This is the "unconstrained" benchmark.
True or false: The reduced cost of a variable that is positive in the optimal solution is always zero. (True)
Fill in the blank: The shadow price is valid only within the ______ range. (allowable increase/decrease)
True or false: If the shadow price of harnessing is negative 35.34 and you lose 2,000 hours of harnessing, you can estimate the cost increase as 2,000 times 35.34. (False, because 2,000 exceeds the allowable decrease of 1,479.5)
True or false: 1E+30 in the allowable increase column means the parameter can increase without limit and the basis will not change. (True)
Fill in the blank: The reduced cost of Units to Buy Model D (8.53) means the buy price would need to fall from 62.00 to ______ before buying D becomes optimal. (53.47)
Q: The shadow price of the Model C demand constraint is 88.30. What does this tell you?
A: If the firm needed 3,001 units of Model C instead of 3,000, total cost would increase by approximately 88.30. This equals the buy price of C because the marginal unit would be bought from the outside supplier.
Q: The allowable increase for the harnessing constraint RHS is 380.5 hours. What happens if harnessing capacity rises to 3,900 hours?
A: The proposed increase is 400, which exceeds the allowable increase of 380.5. The shadow price of negative 35.34 is valid only up to 3,880.5 hours. Beyond that, the basis changes and you must re-solve to find the new cost. You cannot simply multiply 400 by 35.34.
Q: In Scenario 3 (abundant capacity), the shadow price on every demand constraint equals the making cost (103.94 for A, 101.88 for B, 66.39 for C, 41.10 for D). Why?
A: With no binding capacity constraints, every additional unit of demand is met by making it in-house. The marginal cost of one more unit is simply the variable cost to make that model.
Q: A supplier offers to sell Model B at 115.00 instead of 128.00. Would the firm start buying B?
A: The reduced cost for Units to Buy Model B is 14.46. The buy price would need to fall from 128.00 to 113.54 before buying B enters the solution. A price of 115.00 is still above 113.54, so no, the firm would not buy Model B at this price.
Q: Suppose an equipment failure reduces wiring capacity from 6,000 to 5,300 hours. Does this affect the optimal cost?
A: Wiring currently has 603.9 hours of slack. A reduction of 700 hours would consume all the slack and make wiring binding (700 > 603.9). Within the first 603.9 hours of reduction, total cost is unchanged (shadow price = 0). Beyond that, wiring becomes a bottleneck and total cost rises. You would need to re-solve with 5,300 hours to find the exact new cost.
sensitivity analysis, sensitivity report, Excel Solver, shadow price, dual price, dual value, reduced cost, allowable increase, allowable decrease, right-hand side ranging, objective coefficient ranging, RHS ranging, basis, basic feasible solution, binding constraint, non-binding constraint, slack, what-if analysis, parametric analysis, 100% rule, APEC 3002, managerial economics, linear programming sensitivity, LP post-optimality analysis, Electro-Poly, make or buy, cost minimisation, opportunity cost of capacity, marginal value of a resource