Blog · Territory, capacity and quota planning · Finance and FP&A teams
Decision tree sensitivity analysis tests whether a business-case choice changes when supplied probabilities or payoffs move. Use a worked outcome table, exact spreadsheet formulas, and switching thresholds to check which assumptions matter.
Decision tree sensitivity analysis shows how changes in supplied probabilities or payoffs affect a business-case choice. Start with a complete outcome table, calculate expected payoffs, and find the point where the alternatives tie. That switching point tells finance which assumption needs better evidence before committing resources.
For this business task, a decision tree is a set of alternatives followed by uncertain outcomes. It is not a classification exercise. You are evaluating supplied outcomes, rather than training a system to predict which outcome will occur.
State the decision date, alternatives, outcome definitions, evaluation horizon, currency, and payoff basis. Use mutually exclusive outcomes that cover the possibilities included in the case. Mark any excluded outcome explicitly. The short definition of sensitivity analysis explains the broader method; here the question is whether a particular decision changes.
The basic calculation is a weighted sum:
Expected payoff = Sum of (Outcome probability × Net outcome payoff)
OpenStax's explanation of expected value defines it as the sum of each possible value multiplied by its probability. It is an average over the stated distribution, not a promise that an individual project will deliver that amount.
A US distributor is comparing a large regional launch with a limited launch. Finance supplies two demand outcomes and a common strong-demand probability of 60%. Weak demand therefore has a probability of 40%.
All payoffs below are incremental USD cash outcomes at the same evaluation date, after each alternative's launch cost. Do not deduct that launch cost again. The simplified case has no intermediate cash flows, tax differences, or funding constraints in its expected-payoff calculation.
| Alternative | Demand outcome | Probability | Net payoff (USD) | Probability × payoff (USD) |
|---|---|---|---|---|
| Large launch | Strong | 60% | 500,000 | 300,000 |
| Large launch | Weak | 40% | -100,000 | -40,000 |
| Limited launch | Strong | 60% | 280,000 | 168,000 |
| Limited launch | Weak | 40% | 40,000 | 16,000 |
The large launch's expected payoff is 300,000 - 40,000 = $260,000. The limited launch's is 168,000 + 16,000 = $184,000. The difference is 260,000 - 184,000 = $76,000 in favor of the large launch under these assumptions.
With the table in A1:E5, enter the following Excel formulas:
E2=C2*D2
H2=SUM(C2:C3)
H3=SUMPRODUCT(C2:C3,D2:D3)
H4=SUM(C4:C5)
H5=SUMPRODUCT(C4:C5,D4:D5)
Fill E2 down through E5. H2 and H4 must each equal 1. H3 and H5 must match the sums of their weighted-payoff rows. Microsoft documents SUMPRODUCT as the sum of the products of corresponding ranges, which is exactly this calculation.
Do not sum all four probabilities and expect 100%. Each alternative has its own complete set of outcomes. Reconcile payoffs to the approved business-case workbook before testing them.
Let p be the probability of strong demand. The weak-demand probability is 1 - p, so changing one automatically changes the other.
| Strong-demand probability | Large launch expected payoff (USD) | Limited launch expected payoff (USD) | Higher expected payoff |
|---|---|---|---|
| 35% | 110,000 | 124,000 | Limited launch |
| 60% | 260,000 | 184,000 | Large launch |
For the first row, the large launch gives 0.35 × 500,000 + 0.65 × -100,000 = $110,000. The limited launch gives 0.35 × 280,000 + 0.65 × 40,000 = $124,000. The choice changes without changing either outcome payoff.
To find the exact switching probability, set the expected payoffs equal:
500,000p - 100,000(1-p) = 280,000p + 40,000(1-p)
360,000p = 140,000
p = 140,000 / 360,000 = 38.89%
The percentage is rounded to two decimal places. Using the original table references, the spreadsheet calculation is:
=(D5-D3)/((D2-D3)-(D4-D5))
Above the exact threshold, the large launch has the higher expected payoff. Below it, the limited launch does. If the denominator is zero, the two expected-payoff lines have the same slope, so this formula cannot produce a unique switching probability.
Keep the strong-demand probability at 60% and change only the large launch's weak-demand payoff. The limited launch remains at an expected $184,000.
| Large-launch weak payoff (USD) | Large-launch expected payoff (USD) | Limited-launch expected payoff (USD) |
|---|---|---|
| -100,000 | 260,000 | 184,000 |
| -290,000 | 184,000 | 184,000 |
| -300,000 | 180,000 | 184,000 |
The tie is found from 0.60 × 500,000 + 0.40 × weak payoff = 184,000. Therefore weak payoff = (184,000 - 300,000) / 0.40 = -$290,000. At -$300,000, the large launch gives 300,000 - 120,000 = $180,000.
Use scenario analysis in Excel for a wider set of linked assumptions. If different cash-flow dates or discount rates drive the result, prepare comparable discounted payoffs first and use DCF sensitivity analysis for that separate timing question.
Bring a sample of your own business-case outcome table, with alternative, outcome, supplied probability, and net payoff columns. Covirage maps the columns, and its tools compute and check the supplied comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring your file; account creation takes about a minute.
See Covirage for finance teams for the business context.
Covirage is not affiliated with Microsoft.
It identifies the leading alternative under the stated expected-payoff criterion. A company may still reject it because the downside exceeds available funding, violates a policy, or creates unacceptable delivery exposure. Record those constraints alongside the expected-value comparison.
They must come from an identified source, such as comparable completed projects, customer research, or a documented management judgment. Sensitivity analysis tests supplied probabilities; it does not make an unsupported assumption more reliable or automatically estimate the chance of success.
Yes. Use an explicitly labeled scenario when assumptions move together, and retain separate one-input tests to show individual effects. A lower demand probability and a worse weak-demand payoff may describe a coherent downside, but they should not be silently combined.