Scenarios and sensitivity analysis
Compare related sets of assumptions and test individual inputs while keeping capacity and costs connected.
Compare coherent base, downside, and upside scenarios and test one important assumption without hiding dependencies.
Overview
The owner of Oak Print expects a new tote-bag campaign to add $440 profit next month. Before approving it, she wants to know what happens if demand disappoints or stronger sales require rented equipment.
Both businesses and all figures in this lesson are fictional teaching assumptions.
Recall Lesson 025: Why did Maple Cleaning's calculated requirement of 72 jobs fail to provide a workable plan?
Two ways to test a model
A scenario is a set of assumptions describing one possible situation. A base scenario is the reference plan. A downside scenario describes less favorable conditions; an upside describes more favorable ones. These labels do not establish probabilities or the worst and best possible outcomes.
Sensitivity analysis examines how a result responds to a change in a particular input. Today's one-input test changes sales quantity while holding other independent assumptions fixed. Related calculations must still update.
ICAEW distinguishes tests of individual variables from scenarios that change several inputs together. Both help assess how a decision depends on assumptions. ICAEW: Sensitivities and scenarios
Use sensitivity to identify assumptions that could change your recommendation. Neither method proves what will happen.
Make the assumptions fit together
Oak's campaign uses spare capacity, displaces no existing work, and lasts one month. The owner requires at least $400 additional profit in the base scenario. Assume all bags produced sell, adequate funding, no other additional costs or income taxes, and unchanged existing business results.
Existing equipment can produce 100 bags. Producing 101–140 requires a $300 monthly equipment rental. Above 140, this model is invalid. The rental cost must follow quantity in every scenario.
| Input | Base | Downside | Upside |
|---|---|---|---|
| Bags sold next month | 80 | 60 | 120 |
| Price per bag | $30 | $28 | $30 |
| Variable cost per bag | $12 | $13 | $11 |
| Campaign expense before rental | $1,000 | $1,000 | $1,000 |
The base uses the current plan. The downside assumes weaker demand, discounted prices, and a small-order cost premium. The upside assumes stronger demand at full price and a supplier quantity discount, while requiring rented capacity. These combinations need verification.
Worked example
Contribution per bag is price minus variable cost. It covers campaign expenses, then profit. Additional profit equals bags sold × contribution per bag − campaign expense − equipment rental.
Base profit is 80 × ($30 − $12) − $1,000 − $0 = $440. It exceeds the $400 target by $40.
Downside profit is 60 × ($28 − $13) − $1,000 − $0 = −$100, a loss. Upside profit is 120 × ($30 − $11) − $1,000 − $300 = $980.
Omitting the rental would overstate upside profit by $300.
For a spreadsheet, put Base, Downside, and Upside in B1:D1. Put quantity in row 2, price in row 3, variable cost per bag in row 4, and campaign expense in row 5. Enter each scenario's inputs in its column.
In B6, use =IF(B2>100,300,0) for rental expense; in B7, use =B2*(B3-B4)-B5-B6 for profit. Copy both formulas across. Label the 140-bag maximum beside the model and reject quantities above it. For reuse, reference labeled input cells for capacity limits and the rental fee.
Use the same formulas across scenarios. Keep an explanation and evidence source beside each input.
Test one input and keep dependencies
Return to Oak's base price, costs, and campaign expense. Change only quantity:
| Bags sold | Rental expense | Additional profit |
|---|---|---|
| 70 | $0 | $260 |
| 80 | $0 | $440 |
| 90 | $0 | $620 |
For 70 bags, profit is 70 × $18 − $1,000 = $260. Each extra bag adds $18 within this capacity range. The $400 target requires ($1,000 + $400) ÷ $18 ≈ 77.78, so at least 78 whole bags.
Calculated costs must still update. At 100 bags, profit is $800; at 101, rental starts and profit becomes 101 × $18 − $1,000 − $300 = $518. Sales rose, but the required capacity expense reduced profit.
Likewise, lowering price while freezing quantity is a useful isolated test, but it does not predict customers' response. A complete pricing scenario should state the assumed demand response. Document the connection instead of quietly combining favorable numbers.
Exercise
Allow 8–10 minutes. You manage a promotional-kit campaign at established wholesaler Bay Supply. These scenarios cover next month:
| Input | Base | Downside | Upside |
|---|---|---|---|
| Kits sold | 100 | 80 | 140 |
| Price per kit | $40 | $38 | $40 |
| Variable cost per kit | $25 | $26 | $24 |
| Campaign expense before rental | $1,000 | $1,000 | $1,000 |
Existing capacity is 120 kits. Producing 121–150 requires a $300 equipment rental. Above 150 is infeasible. Use Oak's remaining operating assumptions. The downside reflects discounting and smaller supplier orders; the upside assumes a volume discount plus rented capacity.
The director requires at least $500 base profit and a modeled downside loss no greater than $200. A supplier warns that base cost may rise to $26; this is unconfirmed.
- Calculate rental expense and profit in all three scenarios. Do they meet the director's stated financial tests?
- Change only base variable cost to $26. Calculate revised profit and the shortfall against target.
- Explain why using 140 kits with the original $1,000 expense alone hides a dependency. Does the downside case establish a maximum possible loss?
- Write a two-sentence recommendation naming the uncertainty to resolve and who should check it.
Try the exercise before reading on.
Answer
Base rental is zero, giving 100 × ($40 − $25) − $1,000 = $500 profit. Downside rental is also zero, giving 80 × ($38 − $26) − $1,000 = −$40. Upside requires $300 rental, giving 140 × ($40 − $24) − $1,000 − $300 = $940.
The original base meets the $500 target exactly. The modeled $40 downside loss is within the $200 limit. Both financial tests pass, conditional on the supplied assumptions.
At the revised base cost, profit is 100 × ($40 − $26) − $1,000 = $400. The extra $1 on each of 100 kits reduces profit by $100 and misses the target by that amount. Rental stays zero because quantity remains 100.
At 140 kits, rental is necessary to exceed existing capacity. Omitting it produces $1,240 instead of $940. Quantity and required capacity expense are connected; both must be reflected in the calculations.
The downside is one modeled situation, not a loss guarantee. Demand or costs could be worse. Do not assign equal probabilities merely because there are three columns, or average their profits and call that an expected result without justified probabilities.
A recommendation could read: “Hold approval until purchasing confirms the base unit cost: $25 meets the target exactly, while $26 misses it by $100. Purchasing will obtain the supplier terms, and operations will confirm rental availability before the director reviews the updated scenarios.”
Recall answer: Maple needed 72 jobs but could complete only 65. A calculated financial target was insufficient without an operationally feasible way to achieve it.
Applying this lesson
Keep three scenario columns and one sensitivity test. Label changes and dependencies. Tomorrow adds error checks, including checks of AI-suggested formulas.
Sources
Comments
Loading comments…