Scenario and Sensitivity Analysis Questions

Model summary (probabilistic expected impact)

• Base plan: 8 weeks, budget $200k.
• Risks:
  • Integration delay: P=0.20, +3 wks
  • Performance issues: P=0.10, +2 wks + $30k infra
  • Regulatory review: P=0.05, +6 wks

Expected timeline addition = 0.203 + 0.102 + 0.056 = 0.6 + 0.2 + 0.3 = 1.1 weeks
Expected budget addition = 0.10$30k = $3k

So expected delivery ≈ 9.1 weeks; expected budget ≈ $203k.
Also present 90th-percentile (conservative) scenario by enumerating combinations:

• Worst plausible single-event stack (integration + performance + regulatory) = +11 wks → 19 wks total, budget +$30k
• 90th-percentile timeline ~ base + ~3.5–4 weeks (compute via Monte Carlo or binomial approx).

Recommended contingencies & triggers

• Integration (trigger: 2 missed API milestones or failing >20% end-to-end test cases): escalate to vendor engineering, add 1 dedicated integration engineer (contingency: +2 wks buffer; cost: ~$15k).
• Performance (trigger: non-functional tests exceed 75th percentile latency or fail throughput): provision autoscaling and pre-warm infra; contingency budget $30k reserved.
• Regulatory (trigger: initial compliance query >2 items or external auditor requested): allocate legal/compliance lead and pause non-essential work; contingency: re-planning window of 6 weeks.

How I’d present it

• To executives: one-slide summary with base vs expected timelines, expected cost, worst-case timeline, and recommended contingency budget; clear ask: approval for a $50k contingency and +4 week schedule buffer, and RACI for escalation.
• To engineers: detailed Monte Carlo outputs, failure-mode breakdown, acceptance criteria/triggers, playbooks for each contingency (runbook snippets, contact points, estimated effort).

Why this works

• Quantifies expectation and tail risk, gives actionable triggers, balances executive need for a single-number decision with engineering need for operational detail.

Situation: Procurement needs a defensible cost estimate for a multi-year technical solution (hardware, software licenses, integration, support, cloud ops) with clear uncertainty communicated so they can budget and negotiate.

Approach / Model structure:

• Build a bottom-up, line-item cost model in a spreadsheet or modeling tool (e.g., Excel/Google Sheets or a notebook). Layers:
  1. Inputs: unit costs, quantities, escalation rates, one-time vs recurring, dependencies.
  2. Calculations: aggregated line-item costs by year, cash-flow timeline, discounting if needed.
  3. Risk layer: probability distributions and contingency buffers per line item.
  4. Outputs: expected cost, P(<=budget), percentile outcomes, sensitivity metrics.

Probability ranges for major line items:

• Hardware: Triangular (min, mode, max) = (−10%, base, +25%) to reflect supply variability.
• Software licenses: Discrete/step increases: (base, +10%, +30%) with probabilities from negotiations.
• Integration services (labor): Normal with mean = estimate, σ = 15% (productivity risk).
• Cloud/Ops: Lognormal to reflect usage spikes: median = forecast, +50% right tail.
• Exchange rates / escalation: scenario-specific bounded ranges.

Sensitivity analysis:

• Use one-at-a-time and tornado chart: vary each major driver across its 5th–95th percentile and compute impact on total NPV.
• Compute Sobol or variance-based indices if feasible for non-linear interactions.
• Highlight top 3 drivers (e.g., labor, license steps, cloud usage) for mitigation focus.

Scenario planning:

• Best: all favorable (hardware −10%, licenses base, labor −10%, cloud median) → 10th percentile.
• Expected: base inputs (mode/mean) → median/50th percentile.
• Worst: combined adverse (hardware +25%, licenses +30%, labor +25%, cloud +50%) → 90th/95th percentile.
• Provide probability of meeting procurement target: P(total ≤ budget).

Visualization (executive-ready):

• One-slide summary: left: single stacked bar showing Expected (50th) with error whiskers to 10th–90th percentiles and color-coded stack (HW, SW, Integration, Ops).
• Right: Tornado chart of top 6 cost drivers with dollar impact.
• Bottom: Table with scenarios (Best/Expected/Worst), total cost, percentiles, and recommended contingency % and mitigation actions.
• Include a small probability curve (cumulative distribution function) annotated with company budget thresholds and P(success).

Why this is rigorous:

• Combines bottom-up realism, probabilistic uncertainty, formal sensitivity, and clear executive visuals so procurement can set contingencies, prioritize negotiations, and accept risk knowingly.