Scenario and Sensitivity Analysis Questions

1. Clarify objective & scope

• Define the KPI to simulate (e.g., NPV, monthly revenue, margin) and decision threshold(s)
• Time horizon, granularity, and stakeholders

2. Identify uncertain inputs

• List drivers (price, volume, conversion rate, churn, cost per unit)
• For each, gather historical data and expert estimates

3. Select distributions

• Use empirical (historical) distributions when data is sufficient.
• Fit parametric distributions otherwise:
  • Normal for symmetric errors around mean
  • Log-normal for positive skew (prices, sales volumes)
  • Beta for bounded rates/proportions (conversion, churn) — scale to [0,1]
  • Triangular/Pert for expert-based min-most-likely-max when data scarce
• Validate fits (QQ-plots, KS test) and document assumptions

4. Model correlations

• Identify logically correlated variables (price ↔ demand, acquisition cost ↔ conversion)
• Estimate correlation matrix from historical data or elicitation
• Implement dependence via copulas or Cholesky decomposition on multivariate normals after appropriate transforms (e.g., rank-normalize for non-normal marginals)

5. Run simulation

• Use 10k–100k iterations depending on variance stability
• For time-series KPIs, simulate paths (Monte Carlo per period) preserving autocorrelation if needed

6. Analyze output for executives

• Present key summary metrics: mean, median, standard deviation, percentiles (P10/P50/P90)
• Show probability of hitting critical thresholds (e.g., P(NPV > target) = 72%)
• Visuals: histogram, cumulative distribution, tornado chart for sensitivity, scenario bands over time
• Provide actionable insights: which inputs drive variance (SVR or regression on simulation outputs), recommended mitigations, and best/worst-case scenarios with business implications

7. Validation & communication

• Backtest simulation against known outcomes, run sensitivity checks
• Document assumptions, limitations, and recommended next steps
• Deliver a concise executive brief: single-slide summary with probability statements and 2–3 recommended actions supported by the simulation results.

Approach: Link DIO to inventory balance, then to change in net working capital (NWC) and operating cash flow (OCF). Use a 360-day convention so daily COGS = Annual COGS / 360.

Key formulas:

• Inventory balance (ending) = (COGS / 360) * DIO
• Change in inventory (ΔInventory) = (COGS / 360) * ΔDIO
• Change in NWC (ΔNWC) ≈ ΔInventory + ΔAR − ΔAP (if only inventory changes, ΔNWC = ΔInventory)
• Operating cash flow impact (month) = −ΔNWC (increase in NWC is cash outflow, decrease is cash inflow)

Calculation (given): Annual COGS = $180,000,000, ΔDIO = +5 days, 360-day year

• Daily COGS = 180,000,000 / 360 = $500,000 per day
• ΔInventory = 500,000 * 5 = $2,500,000

Cash-flow impact:

• ΔNWC = +$2,500,000 (use of cash)
• Operating cash flow change = −$2,500,000 for the month when the inventory increase occurs

Notes/nuances:

• If the 5-day increase is gradual, the cash outflow will be spread over multiple months; if instantaneous, full $2.5M appears in that month.
• If AR or AP also move, net cash impact = −(ΔInventory + ΔAR − ΔAP).
• For forecast models, represent DIO as a driver and compute inventory balance each month from Inventory = (COGS_month / 360) * DIO_month so ΔInventory flows automatically into the cash flow bridge as ΔNWC.

Approach: build a single parameterized report that loads scenario inputs (base/upside/downside) from a managed source, schedules monthly refreshes, logs which assumptions were used, and distributes results automatically.

Steps — data ingestion & refresh

• Store master assumptions and monthly source data in a central DB (Snowflake/SQL Server/BigQuery). Keep assumptions versioned (assumptions table with id, name, effective_date, values, created_by).
• In Power BI: use Power Query to pull tables; publish to Power BI Service and configure an On-prem/Cloud Gateway with scheduled refresh monthly (or incremental refresh for large fact tables). In Tableau: publish extracts to Tableau Server/Online and schedule extract refreshes or use Tableau Prep flow to refresh upstream and write to the DB.
• Validate refresh with refresh failure alerts (Power BI email/Teams via Power Automate; Tableau alert subscriptions or server alerts).

Parameter management

• Expose a parameter that selects scenario_id (references versioned assumptions). In Power BI use query parameters or what-if parameters + disconnected parameter table; in Tableau use Parameter + a parameter-driven join to the assumptions table.
• Allow manual override in a controlled way: foreground UI parameter for ad-hoc runs; only admins can write new assumption versions to the assumptions table.

Audit trail / provenance

• On each scheduled run, persist metadata to an audit table: run_id, timestamp, user (if manual), scenario_id, assumptions_version, data_snapshot_id, refresh_status. Implement this via:
  • Power BI: trigger a Power Automate flow on subscription/run that writes parameters into the DB (flow can call a REST endpoint or insert into SQL).
  • Tableau: use Tableau Prep or a server-side webhook to capture run parameters and write to the audit table.
• Surface audit info in the report footer or a dedicated "Run History" page showing run_id, timestamp, assumptions links, and a link to the assumptions snapshot (CSV or archived extract).

Distribution

• Power BI: configure email subscriptions for stakeholders; use paginated reports for pixel-perfect PDFs if needed. For richer automation, use Power Automate to attach the report, include run metadata, and deliver to email/Teams/SharePoint.
• Tableau: configure subscriptions on Tableau Server/Online to email PDFs or images; use Tableau REST API or Tabcmd to export and send customized emails. For advanced flows, call server APIs from an orchestration tool (Airflow, Azure Data Factory) to bundle report + audit details.

Testing & governance

• Implement automated tests: row counts, key KPIs, anomaly checks post-refresh. Fail runs if tests fail.
• Document ownership and a process for creating new assumption versions.
• Secure parameters and underlying tables with RBAC; log changes to assumptions table (who/when/why) using DB auditing.

Why this works

• Versioned assumptions + audit table provide reproducibility and accountability.
• Scheduled refresh + gateway/Prep ensures timely data.
• Parameterized UI gives flexibility to view scenarios without duplicating reports.
• Automation (Power Automate/REST APIs) centralizes distribution and records the exact parameters used for each delivered report.

To incorporate seasonality and trend into next-year revenue scenarios for a retail chain, I’d follow a structured, data-driven approach.

Methods:

• STL decomposition: robustly separates trend, seasonal, and residual components (works with irregular seasonality and missing data).
• Additive vs multiplicative models: use additive if seasonal amplitude is stable; multiplicative if seasonality scales with level (common in retail where high-sales months grow proportionally).
• Moving averages / LOESS: smooth short-term noise and reveal underlying trend for baseline scenario.
• Prophet or SARIMA for scenario generation: SARIMA for well-behaved seasonal series, Prophet for flexible holiday effects and changepoints.

Data requirements:

• 2–3 years minimum of consistent, cleaned weekly or daily revenue; monthly may mask intra-month seasonality.
• Calendar flags: holidays, promotions, store openings/closures, price changes, marketing campaigns.
• External drivers: weather, macro indicators, foot traffic, online vs in-store splits.

Validating seasonal assumptions:

• Visual checks: seasonal subseries plots and heatmaps by day/week/month.
• Statistical tests: autocorrelation function (ACF) at seasonal lags, seasonal decomposition consistency across rolling windows.
• Compare additive vs multiplicative fit via AIC/BIC and holdout error (MAPE/RMSE) on seasonal windows.
• Backtest scenarios: simulate last-year forecasts using only pre-period data and compare predicted seasonal peaks to actuals; validate holiday/promo uplift using difference-in-differences on control stores.
• Sensitivity analysis: vary seasonal amplitude and trend slope to produce optimistic/central/pessimistic scenarios and present business impact (revenue, inventory needs).

Deliverable approach:

• Create a dashboard showing decomposition components, model fit metrics, and scenario sliders for trend and seasonality assumptions so stakeholders can explore impacts and validate with domain knowledge.

One-way (single-factor) sensitivity analysis measures how a single input (e.g., price, volume, discount) affects an output (e.g., profit) while holding other inputs constant. Multi-way sensitivity analysis varies two or more inputs simultaneously to show combined effects and interactions (non-additive effects).

Practical steps — Excel

• One-way: create a base model on a worksheet. Use a data table (one-variable) or copy scenarios down a column changing one input and record output. Use a one-variable Data Table (Data → What-If Analysis → Data Table) or simple formulas.
• Multi-way: use a two-variable Data Table for two inputs. For more than two inputs, build scenario tables (Data → What-If Analysis → Scenario Manager) or use formulas with parameter table + PivotTable to summarize outputs. Use conditional formatting / charts to visualize sensitivity heatmaps.

Practical steps — Power BI / Tableau

• One-way: expose the input as a slicer or parameter; create a measure for the output; interactively change slicer/parameter or use What-If parameter (Power BI) to see measure change. In Tableau, use Parameter control and show a calculated field in a chart.
• Multi-way: create multiple slicers/parameters and a measure that depends on them. Use small-multiples, heatmaps (matrix of input A vs input B) or parameter-driven dashboards. For more than two dims, create scenario tables in the data model (Cartesian product of parameter values) and visualize with filters and tooltips.

Example where multi-way reveals interaction
Imagine margin = (price - cost) * volume. One-way analysis: increasing price always raises margin; increasing volume always raises margin. Multi-way analysis (price vs discount) reveals that offering discounts increases volume but reduces per-unit margin; at certain price-discount combinations the increased volume more than offsets margin loss (net gain), while at others it doesn’t. The interaction—volume response depends on price—cannot be seen by separate one-way tables but appears as a ridge in a two-way heatmap, guiding optimal price-discount strategy.