Cost benefit analysis: sensitivity analysis and thresholds

Cost benefit analysis becomes more useful when sensitivity analysis shows which assumptions can reverse the decision. Recalculate discounted benefits and costs under each change, then identify the point where the expected surplus disappears. A positive base case is only the starting point.

Keep the appraisal basis fixed

Use a case table with year, cost, benefit, assumption owner and supporting source. Define benefits against the same alternative in every case. Separate cash saved from staff capacity released without a budget reduction; they are different benefits and require different evidence.

The GAO Cost Estimating and Assessment Guide includes assumptions, sensitivity and risk analysis among the elements of a reliable cost estimate. For an investment appraisal, apply that discipline to both sides of the decision: the spending estimate and the benefits expected from it.

The sensitivity analysis glossary explains the general idea. Here the specific question is whether an investment's discounted benefits still exceed its discounted costs. For worksheet controls and tables, use the separate guide to scenario analysis in Excel.

Build and reconcile the base case

A US distributor is evaluating an operational investment. It pays $300,000 now, then $40,000 of operating costs at each of the next three year-ends. It expects $180,000 of verified cash benefits at each of those year-ends. There is no terminal value; the illustrative discount rate is 8%.

PV cost or benefit = Amount / (1 + discount rate)^year

Net present value (NPV) = Total PV benefits - Total PV costs

Benefit-cost ratio (BCR) = Total PV benefits / Total PV costs

Keep full precision in calculations; display money to cents and ratios to three decimals. The base NPV is $60,793.58 and BCR is 1.151. The NPV check is 463,877.46 minus 403,083.88 equals 60,793.58 using displayed totals.

For a sheet with year in A2:A5, cost in B2:B5, benefit in C2:C5 and the discount rate in J1, enter these formulas in D2 and E2, respectively, and fill down:

=B2/(1+$J$1)^A2
=C2/(1+$J$1)^A2

In separate summary cells, calculate PV costs, PV benefits, NPV, BCR and the independent NPV check, in that order:

=SUM(D2:D5)
=SUM(E2:E5)
=SUM(E2:E5)-SUM(D2:D5)
=SUM(E2:E5)/SUM(D2:D5)
=NPV($J$1,C3:C5-B3:B5)+C2-B2

The last expression uses array arithmetic supported by current spreadsheet versions. Alternatively, subtract cost from benefit in a helper column and pass its future-year cells to NPV. Microsoft's NPV documentation places equally spaced flows at period-end; the year-zero payment remains outside NPV. Covirage is not affiliated with Microsoft.

Change one input and rebuild both totals

Each row below changes only the assumption stated. All other amounts and dates remain those in the base table. These are illustrative stress tests, not predicted outcomes or assigned probabilities.

NPV uses unrounded present values. Subtracting the displayed rounded totals can differ by one cent.

The delay case keeps every benefit payment: it shifts the benefit stream one year later. Year 1 then has no benefit, and year 4 has $180,000 of benefit and zero cost. Divide the base PV benefits by 1.08 to reproduce that case. Costs remain in years zero through three.

If the actual decision window ends at year three, a delayed year-four benefit needs separate treatment. Likewise, if delay requires another year of operating costs, add that spending explicitly. Calling both cases a one-year delay hides a material difference.

Calculate the switching values

The base case can tolerate only a limited loss of benefits when costs stay fixed. In separate cells, calculate the maximum proportional benefit reduction, maximum additional year-zero cost and maximum proportional increase in year-zero cost, respectively:

=1-SUM(D2:D5)/SUM(E2:E5)
=SUM(E2:E5)-SUM(D2:D5)
=(SUM(E2:E5)-SUM(D2:D5))/B2

The benefit switching value is 13.11%, rounded to two decimal places. The initial-spending headroom is $60,793.58, or 20.26% of the initial $300,000. Use unrounded cell values at the decision boundary: the rounded percentages are labels, not exact approval limits.

These thresholds describe different changes. The cost threshold applies to the year-zero payment alone, while the benefit threshold reduces all three benefit payments proportionally. A rate change affects both future cost and benefit streams. Do not apply one threshold to a different pattern of cash flows.

Use the result to request evidence where it matters. The 15% benefit shortfall reverses this decision; the rate and timing cases shown do not. An education program appraisal or construction project appraisal needs its own benefit evidence and timing assumptions.

Where it goes wrong

• Mixing nominal and real amounts. A rate that includes inflation needs a compatible cash-flow basis.
• Changing only the reported NPV. Rebuild the streams so the new cost and benefit totals can be checked.
• Moving benefits without naming the horizon. A delayed receipt and a lost receipt are different cases.
• Counting the same saving twice. Lower labor costs and released hours may represent the same operating change.
• Treating sensitivities as probabilities. A tested reduction says nothing about its likelihood without supporting evidence.

Check your case tables in Covirage

Upload a workbook containing the dated cost and benefit assumptions; Covirage maps the columns, and its tools compute and check the figures for your supplied cases. The external AI model explains the comparisons with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data after setting up your business.

See forecast analysis for the planning context.