Excel sensitivity report: read Solver costs and limits

An Excel sensitivity report shows how a solved linear business plan responds to changes in contribution rates and resource limits. Read the original objective and constraints first, then interpret shadow prices, reduced costs and allowable ranges in those same units.

The report describes the supplied plan's mathematics. It does not establish that demand, staffing or production assumptions will hold in practice.

Start with the workbook behind the report

Consider a synthetic US manufacturer's allocation of divisible production-lot equivalents. Fractional lots are permitted for planning; this is a continuous linear problem, with no integer requirements or demand caps. Contribution is measured after the variable costs already included in each supplied rate, before any new capacity purchase.

Place the following inputs and solved quantities in A:E. The two resource columns are hours per lot equivalent.

Available assembly time is 100 hours; available finishing time is 80 hours. Rebuild the objective and usage cells before relying on any sensitivity output:

H2, total contribution: =SUMPRODUCT(B2:B4,C2:C4)
H3, assembly used: =SUMPRODUCT(B2:B4,D2:D4)
H4, finishing used: =SUMPRODUCT(B2:B4,E2:E4)

The plan contributes 40 × 50 + 20 × 40 + 0 × 25 = $2,800. Assembly usage is 40 × 2 + 20 × 1 = 100 hours. Finishing usage is 40 × 1 + 20 × 2 = 80 hours. Both constraints bind: usage equals availability.

For a new report, use H2 as the maximizing objective, B2:B4 as changing cells, nonnegative quantities and the two usage limits. Choose Simplex LP. After solving, select the Sensitivity report in the results dialog; Microsoft's Solver guide explains method selection and report creation.

The example below is a reconstructed report for these inputs, not an exported workbook. Covirage is not affiliated with Microsoft or Frontline Systems.

Read the resource constraints first

The resource section has the following values. Each allowable range assumes that only that right-hand-side limit changes.

The shadow price is the change in optimal contribution per additional hour within the applicable range. Frontline Systems explains shadow prices and allowable changes in its sensitivity-report documentation. The classical report requires no integer or binary constraints.

For assembly, the report's valid one-input range is 100 − 60 to 100 + 60, or 40 to 160 hours. For finishing, it is 80 − 30 to 80 + 120, or 50 to 200 hours. At an endpoint, another allocation can become optimal; beyond it, the same marginal value cannot be assumed.

To evaluate ten additional assembly hours, keep finishing at 80. The predicted contribution increase is 10 × $20 = $200, taking contribution to $3,000. That is gross of any extra cost to obtain those hours.

The changed allocation proves the figure:

With C still zero, solve the binding equations directly. Put the new assembly limit in K2 and finishing limit in K3:

Product A lots: =(2*K2-K3)/3
Product B lots: =(2*K3-K2)/3

Use unrounded results in the calculations. The table displays lots to four decimals and contribution to whole USD. The resource totals use the unrounded quantities.

Read reduced costs and contribution ranges

The variable section describes the contribution coefficients and the quantities at the optimum:

Product C uses one assembly hour and one finishing hour. At the shadow values, that capacity has an opportunity cost of $20 + $10 = $30 per lot. C contributes $25, leaving a reduced cost of 25 − 30 = −$5.

Forcing a small amount of C into this maximizing plan worsens contribution by $5 per lot while that basis remains applicable. Its contribution coefficient must rise by $5 to reach the break-even opportunity cost of $30. At that threshold C can enter an alternative optimum; entering is not guaranteed to improve the objective.

A's coefficient can vary from 50 − 15 = $35 to 50 + 30 = $80 without requiring a different optimal allocation, with other inputs fixed. B's corresponding range is $25 to $100. The quantities remain optimal within these ranges, but the objective changes when the per-lot contributions change.

Prove the optimum, then compare actual use

The capacity-value identity is an independent check:

100 assembly hours × 20 USD/hour + 80 finishing hours × 10 USD/hour = 2,800 USD

That equals the product contribution total. Every product's contribution is no greater than the shadow-valued capacity it uses: A equals $50, B equals $40 and C's $25 is below $30. This supplies an upper-bound proof for this continuous linear example, together with the feasible allocation that reaches it.

Now compare the plan with a supplied actual-production report:

Actual contribution at planned rates is 38 × 50 + 20 × 40 = $2,700. The gap also reconciles as −4 × 20 + −2 × 10 = −$100. Actual labor-clock hours may differ from these standard-usage figures; keep the two measures separate.

Use scenario analysis in Excel for broader business cases, and sensitivity analysis for the general distinction between changing assumptions and predicting outcomes. A data comparison workbook helps keep the supplied plan and actual reporting windows aligned.

Where it goes wrong

• Integer quantities. Whole-unit restrictions change the problem; continuous shadow prices are not dependable instructions for that allocation.
• Several changes together. Separate allowable ranges do not automatically authorize their combined use.
• Wrong units. A price per hour cannot be applied directly to a limit expressed in minutes or dollars.
• Gross benefit treated as net benefit. Deduct the actual extra capacity cost before approving spending.
• Unverified feasibility. Check all constraints, including any omitted demand limits, before calling a changed scenario achievable.

Compare supplied capacity scenarios in Covirage

Bring the completed allocation workbook, its supplied sensitivity results and the actual resource-usage report. Covirage maps the columns, its tools compute and check scenario comparisons, and the external AI model explains the results with every figure traced to its rows. Create a free Covirage account, set up your business and bring a sample of your own data.

See capacity planning for the supporting business context.