Scenario and Sensitivity Analysis Questions

Variance analysis is the process of comparing actual monthly results to budget or forecast, quantifying the differences, and explaining why they occurred so business owners can act. A variance = Actual − Budget (or Forecast). Favorable (F) means the variance improves the KPI vs plan (e.g., revenue actual > budget, or costs actual < budget). Unfavorable (U) means it worsens performance (revenue < budget, or costs > budget).

Simple root‑cause decomposition framework (volume, price, mix, cost):

1. Volume (quantity): Change due to units sold/served. Example: +1,000 units vs budget → revenue up.
2. Price (rate): Change due to per‑unit price or yield. Example: promotional discount lowered ASP → revenue down (U).
3. Mix (product/customer mix): Shift across products or channels with different margins. Example: higher share of low‑margin SKUs reduces gross margin.
4. Cost (unit cost/overhead): Variance in direct or indirect costs (material, labor, overhead). Example: raw material spike increases COGS (U).

How to present:

• Start with headline variance (amount, %), label F/U.
• Show decomposition: Revenue variance → Price + Volume + Mix; Gross margin → cost/unit + mix; Operating variance → volume-driven fixed cost absorption + variable cost changes.
• Use waterfall charts and driver tables in dashboards; include confidence/assumptions and suggested actions (price changes, cost control, forecasting updates).

Edge checks: timing effects, one‑offs, FX, data quality, and allocation differences.

Requirements (clarify): compact grid showing for each metric (Revenue, Gross Margin, EBITDA) and each quarter (Q1–Q4 next year) values for Base, Upside, Downside plus Absolute delta (to Base) and Percent delta.

Schema (fact + dimensions)

• dim_date (date_id, year, quarter, quarter_label)
• dim_scenario (scenario_id, scenario_name ['Base','Upside','Downside'])
• dim_metric (metric_id, metric_name ['Revenue','Gross Margin','EBITDA'])
• fact_actuals (date_id, metric_id, scenario_id, value numeric, last_updated)

Design notes:

• date_id = first day of quarter
• store measures at quarterly granularity for this report (pre-aggregated ETL)
• use columnar DB (BigQuery/Redshift Spectrum/Snowflake) or a star schema in warehouse

Indexes / performance:

• partition fact_actuals by year
• cluster/sort by (metric_id, date_id, scenario_id)
• create a materialized view or aggregate table pivoted per metric-date for interactive dashboards

SQL to produce compact comparison (pivot + deltas):

WITH vals AS (
  SELECT d.quarter_label,
         m.metric_name,
         s.scenario_name,
         f.value
  FROM fact_actuals f
  JOIN dim_date d ON f.date_id = d.date_id
  JOIN dim_metric m ON f.metric_id = m.metric_id
  JOIN dim_scenario s ON f.scenario_id = s.scenario_id
  WHERE d.year = 2026 AND d.quarter_label IN ('Q1','Q2','Q3','Q4')
)
SELECT quarter_label, metric_name,
       MAX(CASE WHEN scenario_name='Base' THEN value END) AS base,
       MAX(CASE WHEN scenario_name='Upside' THEN value END) AS upside,
       MAX(CASE WHEN scenario_name='Downside' THEN value END) AS downside,
       (MAX(CASE WHEN scenario_name='Upside' THEN value END) - MAX(CASE WHEN scenario_name='Base' THEN value END)) AS abs_delta_upside,
       SAFE_DIVIDE((MAX(CASE WHEN scenario_name='Upside' THEN value END) - MAX(CASE WHEN scenario_name='Base' THEN value END)), NULLIF(MAX(CASE WHEN scenario_name='Base' THEN value END),0)) AS pct_delta_upside,
       (MAX(CASE WHEN scenario_name='Downside' THEN value END) - MAX(CASE WHEN scenario_name='Base' THEN value END)) AS abs_delta_downside,
       SAFE_DIVIDE((MAX(CASE WHEN scenario_name='Downside' THEN value END) - MAX(CASE WHEN scenario_name='Base' THEN value END)), NULLIF(MAX(CASE WHEN scenario_name='Base' THEN value END),0)) AS pct_delta_downside
FROM vals
GROUP BY quarter_label, metric_name
ORDER BY quarter_label, metric_name;

Implementation tips:

• Precompute pivoted materialized view refreshed after ETL window so dashboards read a single row per metric-quarter.
• If interactive filters (metric picker / scenario compare), push filtering to DB and use caching in BI tool.
• Use number formatting and small-multiples visualization: rows = metrics, columns = Q1–Q4 with stacked cells showing Base / Upside / Downside and inline deltas.
• Monitor query patterns and add specific aggregate tables if latency > target (e.g., per-month/per-quarter cubes).

Requirements & constraints:

• Support thousands of model versions, many concurrent contributors, approval workflow with audit trail, BI tool integrations (Tableau/Power BI/Looker), low-latency scenario calculation, secure multi-tenant access and compliance.

High-level architecture:

• Web UI (React) + API Gateway → Orchestration Service → Microservices:
  • Model Service (CRUD, versioning)
  • Assumptions Service (time-series / key-value store)
  • Calculation Engine (containerized, scalable)
  • Workflow & Approval Service (state machine)
  • Audit Service (immutable event store)
  • AuthZ/AuthN Service (OIDC + RBAC/ABAC)
  • BI Connector Service (publish datasets / semantic layer)

Storage:

• Models & metadata: relational DB (Postgres) with JSONB for model definitions; use row-level versioning and soft deletes.
• Assumptions & time-series: columnar store (ClickHouse) for analytics, or TimescaleDB for time-series.
• Large artifacts (files): object storage (S3) with content-addressed IDs.
• Audit/event log: append-only log (Kafka + immutable S3 archive) and write-once ledger for compliance.

Calculation engine:

• Stateless workers in Kubernetes; accept model + assumptions snapshot ID, run in isolated pods; use caching for common sub-calculations; support deterministic outputs and reproducible runs by binding to specific code container image + snapshot.

Versioning & concurrency:

• Immutable snapshot model: every save creates a new snapshot (modelID + versionID). Use optimistic concurrency at edit time: client obtains working branch; merges use three-way merge with conflict detection; if conflicts, present diffs in UI.
• Locking for critical sections: advisory locks for approvals.
• Background jobs reconcile merges; calculation engine always references immutable snapshot to ensure reproducibility.

Workflow & audit:

• Workflow service implements approval states (draft → review → approved → published). Approvals require digital signatures (JWT + audit entry).
• Audit service records all actions with user, timestamp, prev/next version, hashes of payloads; store logs in append-only store and exportable WORM snapshots for compliance.

BI integration:

• BI Connector exposes semantic layer via:
  • Materialized dataset exports to data warehouse (Redshift/Snowflake) per approved scenario
  • Live query endpoints (pre-aggregated tables / cache) consumable by Tableau/Power BI via ODBC/JDBC or direct query
  • Catalog/metadata API so BI developers can surface scenario versions in dashboards
• Push notifications on publish to trigger ETL jobs that refresh dashboards.

Security:

• AuthN: enterprise SSO (OIDC/SAML). AuthZ: RBAC + row/field-level ABAC for sensitive assumptions.
• Data encryption at rest (KMS) and in transit (TLS). Secrets managed by Vault.
• Tenant isolation: logical separation via schemas + VPCs for sensitive customers.
• Activity monitoring: SIEM integration, anomalous behavior alerts, MFA for privileged roles, least-privilege service accounts.

Scalability & reliability:

• Autoscale calculation workers; cache results in Redis; use CDNs for static UI.
• Use CQRS: read-optimized stores for UI, write events produce audit log and async projection updates.
• Backups, point-in-time restore, and immutable snapshots for compliance.

Trade-offs:

• Immutable snapshots increase storage but ensure reproducibility and simple audit.
• Optimistic concurrency favors collaboration; may require UX for conflict resolution.
• Real-time BI live queries add complexity vs. scheduled materializations — choose based on SLA.

How this serves a BI Analyst:

• Easy access to approved scenario datasets in the warehouse for dashboards.
• Traceability from dashboard value back to model version and assumptions via audit links.
• Ability to rerun scenarios deterministically for “what-if” analysis and to reproduce reported figures.

Approach summary: generate the 5^5 parameter grid once as a set, join it to pre-aggregated SKU-level metrics (not raw joins) and do set-based math to produce outputs. Use temporary/staging tables, appropriate indexes, and a stored procedure to orchestrate—avoid repeating heavy joins per combination.

Step 1 — pre-aggregate SKU base metrics

• Build a table sku_base_metrics(sku_id, base_price, base_volume, base_var_cost, base_churn, other_dims...)
• Populate from heavy joins once (daily or on-demand) and cluster/index on sku_id and any filter dims (category, region).

Step 2 — generate parameter grid set-based

• Use cross join of five small level tables (or VALUES) to produce 3125 rows.
• Persist as temp table #grid(level_id, price_mult, volume_mult, varcost_mult, marketing_mult, churn_mult) and index on a surrogate id.

Example grid generation:

WITH price AS (SELECT 1 p, 0.9 mult UNION ALL SELECT 2, 0.95 UNION ALL SELECT 3,1.0 UNION ALL SELECT 4,1.05 UNION ALL SELECT 5,1.1),
volume AS (...),
varcost AS (...),
marketing AS (...),
churn AS (...)
SELECT ROW_NUMBER() OVER () AS grid_id, p.mult price_m, v.mult volume_m, c.mult varcost_m, m.mult marketing_m, ch.mult churn_m
INTO #grid
FROM price p CROSS JOIN volume v CROSS JOIN varcost c CROSS JOIN marketing m CROSS JOIN churn ch;

Step 3 — set-based compute join to SKU metrics

• Join sku_base_metrics to #grid (this is 50k * 3125 ≈ 156M rows — doable if processed once with proper memory)
• Push calculation into a single INSERT ... SELECT that computes revenue, margin, etc., using arithmetic expressions; avoid scalar UDFs.

INSERT INTO sensitivity_results(grid_id, sku_id, price, volume, revenue, margin)
SELECT g.grid_id, s.sku_id,
       s.base_price * g.price_m,
       s.base_volume * g.volume_m + g.marketing_m * s.elasticity, -- example
       (s.base_price * g.price_m) * (s.base_volume * g.volume_m),
       ((s.base_price * g.price_m) - s.base_var_cost * g.varcost_m) * (s.base_volume * g.volume_m)
FROM sku_base_metrics s
CROSS JOIN #grid g;

Performance considerations

• Batch writes (INSERT SELECT) with minimal logging or use CTAS in analytical DBs.
• Create covering indexes on sensitivity_results (grid_id, sku_id) for downstream analytics.
• If 156M rows exceed resources, shard by grid_id ranges or SKU ranges and run parallel jobs.
• Use columnar storage (e.g., Parquet, columnstore index) to reduce IO for analytical DBs.
• Avoid recomputing aggregations by storing aggregated results at SKU x grid level; roll-ups to category-level can be computed from those precomputed rows.

Orchestration

• Encapsulate generation and compute in a stored procedure or scheduled ETL job that:
  • Refreshes sku_base_metrics on cadence
  • Generates #grid
  • Streams/batches the INSERT into sensitivity_results
  • Updates materialized summaries (by category/product line) for dashboards

Edge cases & validation

• Handle nulls and zero volumes, clamp churn rates to [0,1], validate extreme multipliers.
• Add checks for result cardinality and sampling totals to ensure correctness.

Why this is efficient

• Heavy joins run once to produce sku_base_metrics.
• Grid is small; arithmetic scales via set-based operations.
• Persistence and indexing enable fast downstream queries and incremental recompute.

1. Missing / null values
   Detect: SQL count nulls per column and percent nulls; dashboard test shows sudden drops in record counts or KPI gaps.

SELECT COUNT(*) AS total, COUNT(col) AS non_null, COUNT(*)-COUNT(col) AS nulls
FROM table;

Remediation: Backfill from source or mark scenarios as unreliable; enforce NOT NULL or ETL validation that rejects/load alerts when null% > threshold.

2. Duplicate records
   Detect: SQL grouping to find duplicate keys; dashboard anomaly when metrics inflate.

SELECT key, COUNT(*) FROM table GROUP BY key HAVING COUNT(*)>1;

Remediation: Deduplicate in ETL with deterministic keys; implement idempotent loads and uniqueness constraints.

3. Incorrect / inconsistent formats (dates, numeric)
   Detect: Validate parsing success rates, min/max sensible ranges, dashboard parsing error counters.

SELECT COUNT(*) FROM table WHERE TRY_CAST(date_col AS DATE) IS NULL;

Remediation: Standardize formats in ingestion, reject malformed rows, add transformation checks and unit tests.

4. Outliers and implausible values
   Detect: Statistical checks (z-score, IQR) in SQL; dashboard outlier visualizations and alerts when values exceed domain limits.
   Remediation: Flag and review outliers; add business rules to cap/require manual validation before using in scenarios.

5. Late-arriving or incomplete time slices
   Detect: Time-series completeness checks (expected partitions per period) and freshness dashboard showing lag.

SELECT date, COUNT(*) FROM table GROUP BY date HAVING COUNT(*) < expected;

Remediation: Implement SLA monitoring, backfill process, and prevent analyses until required windows are complete.

6. Mis-joined or wrong aggregation (join multiplicity)
   Detect: Row count jumps after join, sanity checks comparing totals pre/post join, or reconciliation dashboards.
   Remediation: Use keys validated for 1:1 or 1:many semantics, add surrogate keys, and include unit tests for joins.

7. Stale or outdated reference data
   Detect: Check last_updated timestamps and mismatches vs. source; dashboard flag when reference lookup misses increase.
   Remediation: Automate refresh jobs, cache invalidation rules, and alert on stale TTL breaches.

8. Semantic drift / changed business definitions
   Detect: Sudden KPI shifts without corresponding business events; audit logs showing changes to definitions.
   Remediation: Maintain data dictionary/versioned metrics, require review/approval for definition changes and tag historical reports with definition version.

For each, add automated SQL checks in CI/ETL, surface test results on a data-quality dashboard, and block high-stakes scenario runs when critical checks fail.