Scenario and Sensitivity Analysis Questions

Approach: I’d build three scenario worksheets (conservative, expected, aggressive) in Excel/BigQuery + a dashboard (Tableau/Power BI) so stakeholders can toggle assumptions and see revenues and KPIs.

Key assumptions by scenario (examples per month/quarter for first 12 months):

• Adoption rate (user funnel):
  • Conservative: low trial-to-paid conversion, slow growth (e.g., 0.5% weekly organic growth; 5% of trials convert)
  • Expected: baseline from comparable launches (1.5% weekly; 12% convert)
  • Aggressive: optimistic virality & partnerships (3% weekly; 20% convert)
• Pricing uptake:
  • Conservative: 60% choose basic, 30% mid, 10% premium; average revenue per user (ARPU) = $X_low
  • Expected: 50/35/15 split; ARPU = $X_mid
  • Aggressive: 40/40/20; ARPU = $X_high
• Operational capacity & cost:
  • Conservative: minimal support scaling; capacity buffer 10%; unit cost higher due to inefficiency
  • Expected: planned hires/releases to match demand; capacity buffer 20%
  • Aggressive: need rapid hiring/third-party scaling; higher fixed+variable ops cost
• Marketing spend & CAC:
  • Conservative: low spend, high CAC (paid channels limited)
  • Expected: planned multi-channel, benchmark CAC
  • Aggressive: heavy spend to capture share, lower CAC via scale but higher absolute spend

Compute expected net revenue:

• Projected paying users(t) = existing_users + sum(new_signups(t) * conversion_rate)
• Gross Revenue(t) = paying_users(t) * ARPU(t)
• Marketing Cost(t) = new_signups(t) * CAC
• Ops Cost(t) = fixed_ops + variable_ops_per_user * paying_users(t)
• Net Revenue(t) = Gross Revenue(t) - Marketing Cost(t) - Ops Cost(t) - other overheads
• NPV / Payback: discount cash flows, compute CAC payback period, LTV = ARPU * gross_margin * average_lifetime

Sensitivity and trade-offs to present:

• Tornado chart: show top drivers (adoption, conversion, ARPU, CAC, churn)
• Break-even analysis: at what conversion or ARPU does the launch become profitable?
• Risk table: operational risk (capacity shortfall), financial risk (overspend), reputational risk
• Recommendations: run A/B tests to validate conversion/ARPU assumptions, stage marketing spend with KPIs, build contingency hiring agreements, and set trigger points for scaling.

Deliverables: interactive model, dashboard showing monthly P&L, sensitivity charts, and a one-page executive summary with recommended go/no-go and scaling thresholds.

Approach: Start by identifying the key drivers of revenue, quantify their baseline values and credible ranges, then measure how changes propagate to the 3-year revenue forecast. Present both numeric results and clear visualizations so stakeholders can quickly see which assumptions matter most.

Which variables to vary (pick 3–5):

• Volume / units sold (customer acquisition or repeat purchase rate)
• Price / average selling price (ASP)
• Conversion rate or churn (affects active customers)
• Market growth / TAM capture (if relevant)
• Customer lifetime value or average order frequency

Defining scenarios:

• Base: the most likely forecast using current trends, historical averages, and management guidance.
• Upside: optimistic but plausible — e.g., faster adoption, higher retention, or price premium (typically +10–30% on key drivers depending on context).
• Downside: conservative but plausible — slower growth, weaker pricing, increased churn (–10–30%).

How to run the analysis:

1. Build the model so each driver can be adjusted independently.
2. For one-way sensitivity, change one variable at a time to its upside/downside and record revenue impact.
3. For scenario analysis, set combinations for base/upside/downside and compute the revenue path.

Presentation:

• Table: show revenue by year for each scenario and the % change vs base.
• Tornado chart: rank variables by impact on NPV or 3-year cumulative revenue—easy to see which drivers matter most.
• Small multiples or scenario lines on a chart: plot three revenue trajectories over 3 years.
• Executive slide: 1-line conclusion (most sensitive variable), recommended next steps (data to reduce uncertainty).

Example table (Three variables: Units Sold, ASP, Conversion Rate):

Variable adjustments:

• Base: Units=100k, ASP=$10, Conv=5%
• Upside: Units=120k (+20%), ASP=$11 (+10%), Conv=6% (+20%)
• Downside: Units=85k (–15%), ASP=$9 (–10%), Conv=4% (–20%)

3-year cumulative revenue (illustrative):
Scenario | Yr1 | Yr2 | Yr3 | 3-yr total
Base | 5.0M| 5.5M| 6.0M| 16.5M
Upside | 6.6M| 7.3M| 8.1M| 22.0M
Downside | 3.4M| 3.6M| 3.8M| 10.8M

Key takeaway: Use tornado to show which single-variable moves (e.g., Units vs ASP) change the 3-yr total most, and present scenarios plus recommended data collection to reduce uncertainty (A/B tests, market surveys, cohort analysis).