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.

Start with the problem: when input drivers are correlated (e.g., scope growth increases both cost and revenue), one-variable-at-a-time sensitivity is misleading because it breaks real-world relationships. Three rigorous approaches:

1. Regression-based sensitivity

• Fit a predictive model (linear / generalized linear / tree-based) of the outcome on all inputs using historical or expert-simulated data.
• Use standardized regression coefficients, partial dependence plots or Shapley values to quantify each variable’s marginal effect while holding others in their observed distribution.
• Pros: explicit conditioning on correlations; interpretable coefficients. Cons: needs representative data and correct model form.

2. Multivariate scenario design

• Design scenario bundles that move correlated variables together (best-case, base, worst-case) or use dimensionality reduction (PCA) to capture dominant joint variation.
• Use Latin Hypercube Sampling on the joint empirical distribution or sample along principal components to generate plausible scenarios.
• Pros: transparent scenarios; easy to present to stakeholders. Cons: fewer samples, may miss tail dependence.

3. Copulas in Monte Carlo simulation

• Model each variable’s marginal distribution, choose a copula (Gaussian, t, Clayton, Gumbel) to encode dependence (including tail dependence), then simulate joint draws and propagate through the model.
• Pros: flexible, preserves marginals and complex dependence; good for risk metrics (VaR, P90/P10). Cons: requires fitting and validation of copula; more complex.

Concrete example (Solutions Architect, cloud migration business case)
Situation: estimating NPV of a cloud migration where increased scope (more app modernization) simultaneously raises migration cost and increases recurring revenue/efficiency benefits.
Approach:

• Collect past project data or expert priors for three inputs: scope (% apps modernized), migration cost per app, recurring benefit per app.
• Fit marginals (e.g., Beta for scope, log-normal for costs/benefits). Fit empirical correlation; choose a t-copula if tail dependence suspected.
• Simulate 100k joint scenarios via the copula, compute NPV per scenario, and present distribution (median, P10, P90), Tornado-like bar but derived from conditional expectations (regression/Shapley) to show drivers.
  Outcome: stakeholders see that although cost rises with scope, the joint distribution shows a high probability of positive NPV due to stronger benefit elasticity; also identify scenarios where tail-costs and low benefits make ROI negative, guiding contract clauses and risk-sharing.

Key recommendation: pick the technique balancing data availability and stakeholder communication—use regression/Shapley for explainability, copulas for rigorous risk quantification, and multivariate scenarios for executive alignment. Validate models with backtesting and sensitivity to copula choice.

Three-point estimating captures uncertainty by using three inputs: optimistic (O), most-likely (M), and pessimistic (P). PERT (Program Evaluation and Review Technique) weights the most-likely more heavily to produce a single expected cost and an approximate spread.

PERT expected value (EV):
EV = (O + 4×M + P) / 6

A common standard-deviation approximation:
σ ≈ (P − O) / 6
(or you can use σ ≈ (P − O) / 6 and variance σ² = ((P − O)/6)²)

Example: O = $80k, M = $100k, P = $140k.
EV = (80 + 4×100 + 140)/6 = (80+400+140)/6 = 620/6 ≈ $103.3k
σ ≈ (140−80)/6 = 60/6 = $10k — roughly 68% chance cost within $103.3k ± $10k.

When to prefer Monte Carlo:

• Multiple uncertain inputs (labor, license, integration complexity) interact non-linearly
• You need a full probability distribution (percentiles like P(> budget), P50/P90)
• Risk correlations between inputs matter
• Decision-makers require scenario analysis or visual distributions

Monte Carlo simulates thousands of samples drawn from specified distributions (triangular, beta-PERT, normal, etc.), aggregates outcomes, and yields richer metrics (P10/P50/P90, mean, tail risk). Use PERT for quick, transparent sizing; use Monte Carlo for complex portfolios, high-stakes bids, or when dependencies and non-linearities could change outcomes. Best practice: document assumptions, choose appropriate input distributions (beta-PERT or triangular), and validate against historical data.

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.

Approach: Build a forward Monte Carlo (MC) spreadsheet or Python model that samples uncertain inputs, computes NPV/ROI per trial, and summarizes the distribution.

Required inputs:

• Deterministic: discount rate, base timeline, fixed one-time costs.
• Probabilistic variables (with base/most-likely): initial CAPEX, annual OPEX, time-to-deliver (months), revenue uplift (% by year), cost overrun (%).
• Correlation assumptions (e.g., larger projects → higher overruns).
• Number of iterations, seed, output metric(s) (NPV, payback, IRR).

Selecting distributions:

• Cost overrun: use lognormal or shifted lognormal (non-negative, right-skew). Parameterize from historical overrun mean and variance.
• Time-to-deliver: triangular (min, mode, max) if limited data; or lognormal if skewed.
• Revenue uplift: beta (bounded 0–1, flexible shape) for % increases; transform to monetary.
• For many small independent errors, normal is acceptable; ensure truncation to realistic bounds.
• If historical data exists, fit distributions (MLE/KDE) and validate with goodness-of-fit.

Model design and sampling:

• Use Latin Hypercube Sampling to improve convergence, or plain pseudo-random draws with reproducible seed.
• Model correlations via Gaussian copula: build correlation matrix among log-values (e.g., cost and time correlated), sample multivariate normals, map to marginals.
• For each iteration:
  • Sample inputs
  • Simulate cash flows across timeline (account for delayed start if time-to-deliver shifts)
  • Discount cash flows → compute NPV, IRR, payback
• Iterations: 20k–100k for smooth tails; 5k often sufficient for stable percentiles in business cases. Use convergence testing (track percentile variance vs iterations).

Python example (simplified):

import numpy as np
from scipy.stats import lognorm, beta, triang

N=20000
# cost overrun ~ lognormal
mu, sigma = 0.0, 0.3
cost_over = lognorm(s=sigma, scale=np.exp(mu)).rvs(N)
# time-to-deliver triangular
c=(0.5-0.3)/(0.8-0.3)
time = triang(c, loc=3, scale=5).rvs(N) # months
# revenue uplift beta scaled
a,b=2,5
rev_uplift = beta(a,b).rvs(N)*0.3
# compute NPV per trial (placeholder)
base_revenue=1e6
cashflows = (base_revenue*(1+rev_uplift)) - (100000*cost_over)
npv = np.sum(cashflows/(1+0.08)**np.arange(1,6), axis=0)

Summarize & present:

• Report percentiles: P10 (optimistic), P50 (median), P90 (pessimistic) for NPV, IRR, and time-to-value. Show mean and standard deviation.
• Visuals: histogram with marked percentiles, cumulative distribution (CDF), tornado chart for sensitivity (Spearman rank correlations), and scenario examples (P10/P50/P90 cashflow profiles).
• Decision framing: probability of NPV>0, expected value, downside risk (e.g., loss at P90), and breakeven probability.
• For stakeholders: one-slide summary with key percentiles and probability statements (e.g., "70% chance NPV>0; median NPV=$X; downside (P90) = -$Y"), plus appendix with methodology, assumptions, correlation rationale, and reproducible model (Excel with data table or Python notebook).

Validation & best practices:

• Back-test against historical projects, perform sensitivity and scenario analysis, document assumptions, and run a convergence check on percentiles.