Find which assumption drives your marketing forecast
Use a fixed-budget sensitivity table to find whether CPC, conversion, qualification or close-rate uncertainty deserves the next measurement effort.
Choose which uncertainty to investigate
A sensitivity check helps decide what to measure before committing a budget. Keep one baseline forecast, vary a single input across a plausible range, and observe the result. The useful question is which unresolved assumption could most change the decision, given its uncertainty and the cost of learning more.
Microsoft’s What-If Analysis documentation describes changing formula inputs to examine possible outcomes. The model and ranges below are original planning examples; the spreadsheet feature does not validate their accuracy. [Microsoft: Introduction to What-If Analysis]
Write the baseline as a chain with units
The fictional plan uses USD 6,000 of media spend, a USD 3 cost per click, a 4% click-to-inquiry rate, a 50% inquiry qualification rate and a 25% qualified-opportunity close rate. All figures are illustrative. They are not channel benchmarks or claimed campaign results.
Clicks = 6,000 / 3 = 2,000
Inquiries = 2,000 × 0.04 = 80
Qualified opportunities = 80 × 0.50 = 40
Expected customers = 40 × 0.25 = 10The model predicts an expected count, which may be fractional in other scenarios. Do not round at each funnel stage. Carry full precision until the final display, then label the result as expected rather than a promise of a specific number of customers.
Every percentage has its own denominator. Qualification is qualified opportunities divided by inquiries, while close rate is customers divided by qualified opportunities. Substituting a lead-to-customer rate into the last step would count qualification twice.
Change one input while holding the rest fixed
| Input varied | Lower-input case | Baseline | Higher-input case | Expected customer range |
|---|---|---|---|---|
| CPC USD | 2.50 | 3.00 | 4.00 | 12 to 7.5 |
| Inquiry rate | 3% | 4% | 5% | 7.5 to 12.5 |
| Qualification rate | 40% | 50% | 60% | 8 to 12 |
| Close rate | 15% | 25% | 30% | 6 to 12 |
The close-rate range produces the widest result in this chosen table: six to twelve expected customers. That makes close-rate uncertainty worth examining before the others if the decision is sensitive to that range. It does not mean sales always matters more than traffic quality. The ranking follows the particular ranges used.
Equal percentage changes would be less revealing in this simple multiplicative model. A 20% relative increase in inquiry, qualification or close rate produces a 20% increase in expected customers when everything else is fixed. What makes an input a priority is its plausible uncertainty, measurement quality and decision consequence, not a decorative percentage ranking.
Separate a wide range from a useful investigation
Ask why each range is plausible. A close-rate range based on six mature opportunities is weak evidence. A CPC range based on a recent comparable campaign may be narrower but easier to verify. Record the source period, sample coverage, cohort maturity and any difference from the planned offer.
Then consider the decision boundary. If the plan needs at least eight expected customers to fit its contribution assumptions, the close-rate downside crosses that line. The next useful action could be reviewing the sales cohort, resolving qualification definitions or running a small controlled intake. A broad range is a reason to improve evidence, not a reason to select the optimistic endpoint.
- Keep measured inputs, transferred estimates and management assumptions visibly distinct.
- Choose the business outcome and acceptable range before reviewing scenarios.
- Record the evidence that would narrow each important input range.
- Compare the cost and time of obtaining that evidence with the size of the budget decision.
Use joint scenarios after the sensitivity check
One-at-a-time analysis holds other inputs fixed for explanation. In practice, they can move together. Cheaper clicks may qualify less often, and a larger lead volume may strain follow-up capacity. Combining every favorable endpoint would hide those relationships.
A separate downside scenario could use USD 4 CPC, a 3% inquiry rate, 40% qualification and 15% close rate. The result is 6,000 ÷ 4 × 0.03 × 0.40 × 0.15 = 2.7 expected customers. That number has no assigned probability. It shows a possible combination of the stated assumptions, not a confidence bound.
I would keep the sensitivity table beside the base, downside and upside plans. Use it to name the next evidence gap and the owner of the check. Once an input is replaced with better evidence, rerun the model instead of treating the original spreadsheet as a permanent forecast.
Sources
AI-assisted writing and an AI-generated editorial cover. Source links and checked dates are listed below; illustrative examples are labeled in the text.
- Microsoft: Introduction to What-If Analysis — checked 2026-10-18