Forecasting and Time-Series Analysis Questions

Direct answer
Moving beyond raw accuracy, a forecast's business value can be measured through KPIs like stockouts prevented, holding-cost reduction, and revenue uplift - each computed by comparing what actually happened (with the forecast-driven decision in place) against a credible counterfactual of what would have happened under a naive or prior approach, with attribution challenges handled by isolating the forecast's specific contribution from other simultaneous operational changes.

Structured elaboration

• Stockouts prevented: compare the actual stockout rate under the current (forecast-informed) inventory policy against a counterfactual - either the stockout rate the OLD policy would have produced on the same demand realization (if you can simulate it), or a controlled holdout where a subset of SKUs/stores continued using the old approach as a genuine comparison group.
• Holding-cost reduction: compute the excess inventory carried under the old policy versus the new forecast-informed one, valued at the business's actual holding-cost rate (capital cost, storage, spoilage/obsolescence risk) - this requires the SAME kind of counterfactual comparison as stockouts, not just "inventory went down," since inventory could fall for unrelated reasons (a demand drop, say).
• Revenue uplift: the incremental revenue attributable to better availability/staffing/allocation decisions the improved forecast enabled, computed similarly by comparison against a credible counterfactual rather than simply period-over-period revenue growth (which conflates many causes).
• Computing these from historical forecasts and actuals: requires not just the forecast and the actual outcome, but a record of what DECISION the forecast drove (the resulting inventory order, staffing level, allocation) so you can quantify the downstream operational consequence, not just the raw forecast error - a forecast can be numerically accurate yet drive a poor decision if the decision rule built on top of it (e.g. the safety-stock formula) is miscalibrated.
• Attribution challenges when multiple changes happen simultaneously: if inventory policy, pricing, AND the forecasting model all changed in the same quarter, isolating the forecast's specific contribution to any observed improvement requires either staggering the rollouts (so each change's effect window is at least partially separable), a genuine holdout/control group that kept the old forecast while everything else changed identically, or, at minimum, an honest acknowledgment in the reporting that the estimate is a joint effect of several changes rather than the forecast's alone.

Worked example
A holding-cost-reduction claim of "$500K saved this quarter" is only credible if it's benchmarked against a stated counterfactual (e.g. "versus the safety-stock levels the prior policy would have required for the same realized demand") - reported without that counterfactual, the number is really just "inventory happened to be lower," which could equally reflect a demand slowdown having nothing to do with forecast quality.

Trade-offs & pitfalls
The recurring failure mode across all three KPIs is presenting a raw before/after comparison as if it were the forecast's isolated causal contribution, when in a real operating business, demand, pricing, promotions, and policy all move simultaneously - treat any business-value number without an explicit counterfactual or control group as a directional estimate, not a precise measurement, and say so plainly when reporting it.

Direct answer
Additive seasonality assumes the seasonal swing is a roughly constant absolute amount regardless of the series' level; multiplicative seasonality assumes it scales proportionally with the level. Choosing the wrong one distorts both baselines (an additive model under-predicts the seasonal swing at a high level, or over-predicts at a low one) and anomaly thresholds (a fixed-width band around an additive baseline will be too tight during high-level periods and too loose during low-level ones).

Structured elaboration

• Additive: $y_t = \text{trend}_t + \text{seasonal}_t + \text{residual}_t$ - the seasonal swing stays roughly the same size in absolute units no matter what the trend level is doing.
• Multiplicative: $y_t = \text{trend}_t \times \text{seasonal}_t \times \text{residual}_t$ - the seasonal swing grows or shrinks proportionally with the trend level; a multiplicative model can be fit as an additive one on the LOG of the series, since $\log(y_t) = \log(\text{trend}_t) + \log(\text{seasonal}_t) + \log(\text{residual}_t)$.
• Which metrics favor which: revenue-style metrics that grow substantially over time, where the seasonal spike naturally grows alongside the business (e.g. a December bump that's proportionally consistent but has grown from $10K to $50K as the business scaled), are usually multiplicative. Metrics with a roughly stable base level and a genuinely fixed-size seasonal effect (e.g. a fixed number of extra support tickets every Monday, regardless of overall ticket volume trend) are more often additive.
• Testing which model fits better: the simplest visual test is whether the peak-to-trough seasonal amplitude visibly GROWS alongside the trend on a raw plot - if it does, additive is the wrong assumption. More formally, fit both, compare residual variance (an additive fit on a genuinely multiplicative series will show residual variance that itself grows with the level, a clear diagnostic on a residual-vs-fitted plot), or simply compare backtested forecast error between the two.
• Why the choice matters for baselines/anomaly detection: an anomaly-detection threshold built on additive-model residuals (e.g. "flag anything more than 3 residual-standard-deviations away") implicitly assumes constant residual variance across the whole series; if the true process is multiplicative, that fixed threshold will be too sensitive (false positives) during low-level periods and too insensitive (missed real anomalies) during high-level periods.

Worked example
Two metrics side by side: daily active users growing from 10,000 to 100,000 over two years, with a weekend dip that's always been roughly 15% below the weekday average - that's multiplicative (the absolute size of the dip has grown 10x alongside the user base, but the RATIO has stayed constant). A metric like "number of scheduled maintenance windows per week," with a fixed operational cadence unrelated to overall traffic growth, is more likely additive.

Trade-offs & pitfalls
Log-transforming to convert a multiplicative problem into an additive one is convenient (lets you reuse additive-model tooling) but changes how you have to interpret and back-transform prediction intervals - a symmetric interval in log-space becomes an ASYMMETRIC interval once exponentiated back to the original scale, which is correct behavior (uncertainty genuinely should be asymmetric on a multiplicative series) but is easy to get wrong if you forget to back-transform properly.

Direct answer
Computing seasonally-adjusted growth in SQL means dividing each period's revenue by a seasonal index before comparing period over period; the SQL itself is straightforward window-function work, but the seasonal index needs enough HISTORY (multiple years) to be estimated reliably, which is a real limitation this exercise surfaces directly.

Structured elaboration and worked example (executed)
Against a synthetic daily_revenue table (6 months of daily rows for one product, rolled up to monthly), a query computing month-over-month growth and a naive seasonally-adjusted figure:

WITH monthly AS (
  SELECT product_id, substr(revenue_date,1,7) AS ym, SUM(revenue) AS total_revenue
  FROM daily_revenue GROUP BY product_id, ym
),
with_lag AS (
  SELECT *, LAG(total_revenue) OVER (PARTITION BY product_id ORDER BY ym) AS prev_total
  FROM monthly
),
seasonal_index AS (
  SELECT CAST(substr(ym,6,2) AS INTEGER) AS month_num,
         AVG(total_revenue) AS month_avg,
         (SELECT AVG(total_revenue) FROM monthly) AS grand_avg
  FROM monthly GROUP BY month_num
)
SELECT w.product_id, w.ym, w.total_revenue, w.prev_total,
       ROUND(100.0 * (w.total_revenue - w.prev_total) / w.prev_total, 2) AS mom_growth_pct,
       ROUND(w.total_revenue / (si.month_avg / si.grand_avg), 2) AS seasonally_adjusted_revenue
FROM with_lag w JOIN seasonal_index si ON si.month_num = CAST(substr(w.ym,6,2) AS INTEGER)
ORDER BY w.ym;

Executed against 6 months of synthetic daily data for product 1 (seed=1, Jan-Jun 2024, revenue = 1000 + 50sin(2pi*day/30) + N(0,30) per day, rolled up to monthly), the raw output showed month-over-month growth swinging from +7.23% to -5.97%, but the seasonally_adjusted_revenue column came out to the SAME value (30,402.55) for every single month. This is a real, informative artifact rather than a bug in the intent: with only ONE observation per calendar month in the history (6 months = 6 distinct calendar months, no repeats), the "seasonal index" for each month is trivially just that month's own value, so dividing by it exactly cancels out - you cannot separate trend/level from seasonality with only one cycle of data. A seasonal index needs MULTIPLE years so each calendar month has more than one historical observation to average.

• Handling sparse history or partial months: for a product with less than a full month of data (a mid-month launch), either exclude that partial month from month-over-month comparisons entirely (comparing a partial month to a full prior month is misleading) or explicitly annotate the day-count and consider a run-rate (extrapolated) comparison instead; for products with less than 2 full years of history, either fall back to an unadjusted comparison with a caveat, or borrow a seasonal index from a comparable product/category rather than attempting to estimate one from too little of the product's own data.

Trade-offs & pitfalls
The seasonal-index approach shown here is the simplest possible naive method and genuinely requires several years of history to be trustworthy - shipping a "seasonally adjusted" number computed from under a year of data (as this executed example deliberately demonstrates) can look legitimate while being mathematically degenerate. A production version would either use a proper STL/classical decomposition fit on multi-year history, or explicitly disable the seasonal adjustment and label the number as raw growth until enough history exists.

Direct answer
A strong answer to "tell me about a forecast that was significantly off" walks through a genuine situation with a specific root-cause diagnosis, concrete corrective steps (not vague "I learned from it"), and a lasting process or model change - the STAR structure (Situation, Task, Action, Result) is the scaffold, but the substance an interviewer is actually listening for is the diagnostic reasoning and the durability of the fix.

Structured elaboration

• Situation: set up the specific forecast, its stakes, and how far off it turned out to be, with enough concrete detail (what metric, what horizon, roughly how large the miss was) that the story is grounded, not abstract.
• Task: what you were responsible for and what decision the forecast was feeding - this frames why the miss actually mattered, not just that a number was wrong.
• Action - diagnosis: describe the actual investigative process: what hypotheses you considered (a genuine regime change? a data issue? a missed exogenous driver? a modeling assumption that broke?), and how you distinguished between them with evidence rather than guessing - this diagnostic rigor is usually the part that most differentiates a strong answer.
• Action - corrective steps: what you actually changed, concretely - a specific feature you added, a validation gap you closed, a monitoring alert you built - rather than a generic "I retrained the model."
• Action - communicating outcomes to stakeholders: how you told the people who relied on the forecast what happened, proactively rather than waiting to be asked - a concise, honest explanation of the root cause, what you fixed, and what would be different going forward. Stakeholders who feel informed about a miss, rather than blindsided by it, are far more likely to keep trusting the forecast afterward; a technically excellent diagnosis that's never actually communicated to the people who relied on the number doesn't rebuild trust by itself.
• Result: what happened afterward, ideally with some evidence the fix actually worked (a subsequent forecast that held up, a monitoring signal that would have caught the original issue earlier next time) - and, honestly, what you'd still do differently, since acknowledging remaining limitations reads as more credible than claiming a fully solved problem.

Worked example
A credible shape: "our forecast for a key metric ran roughly 15% low for two consecutive weeks; I first ruled out a data pipeline issue (checked the raw counts directly against a source system), then compared against a control series in an unaffected region and found it was ALSO drifting similarly, which pointed away from anything specific to our product and toward a broader, unmodeled seasonal or macro shift; digging further, a competitor had just changed pricing in a way that shifted category-wide demand patterns our model had no way to see. The fix wasn't a bigger model - it was adding a lightweight external signal (category-level demand) as a regressor, plus a monitoring rule that flags when our forecast error and a comparable control series both drift in the same direction, since that combination is a stronger signal of a shared external cause than either alone. I also sent the stakeholders who relied on that forecast a short, proactive write-up: what went wrong, why, what we changed, and what to expect going forward, rather than letting them notice the forecast had quietly improved and wonder what happened." This kind of answer demonstrates genuine diagnostic process (control-series comparison, ruling out alternatives) rather than a post-hoc guess.

Trade-offs & pitfalls
The most common weak answer in this format skips straight from "it was wrong" to "I fixed the model" without showing the actual diagnostic reasoning in between - an interviewer specifically wants to see how you distinguish between competing explanations (a bug, a genuine regime change, a coincidental fluke) with evidence, since that diagnostic discipline is exactly what predicts whether you'll handle the NEXT unexpected miss well, which is the real thing being assessed.

Direct answer
For a quick, defensible baseline with clear weekly seasonality, compute a seasonal-naive forecast (repeat the value from the same weekday last week/period) and a simple moving average, present both, and be explicit that the seasonal naive is the harder bar to beat, not the moving average.

Structured elaboration

• Seasonal naive: forecast next Monday's value as last Monday's value (or the same period one full seasonal cycle ago). Advantage: costs nothing to compute, respects the seasonal pattern by construction, and is the standard baseline every more sophisticated model should be measured against (this is literally the denominator MASE uses). Limitation: it ignores trend entirely, so it will systematically lag a genuinely growing or shrinking series, and it's a single historical point so it's sensitive to one noisy week.
• Moving average: forecast next period as the average of the last N periods. Advantage: smooths out noise better than a single seasonal-naive point. Limitation: a plain moving average IGNORES seasonality unless N is chosen to span a full seasonal cycle, so on a series with clear weekly seasonality a short moving average will systematically miss the weekly pattern (e.g. under-forecasting weekend spikes).
• Presenting uncertainty and relative performance to non-technical stakeholders: show both baselines' historical forecast errors (e.g. "seasonal-naive has typically been off by about $12K/week over the last 3 months") next to the point forecast, rather than presenting a bare number; a simple error band drawn from the historical error distribution communicates the same idea as a formal prediction interval without requiring the audience to know what one is. Frame it explicitly as "this is our floor: any model we build should beat this."

Worked example
For weekly sales with a clear weekly seasonal cycle (e.g. this metric IS itself weekly-periodic, so "weekly seasonality" here really means a recurring pattern across weeks, like a monthly or quarterly cycle within the weekly series), the seasonal-naive baseline uses last cycle's same-position value directly. A 4-week moving average, in contrast, would blend across positions in the cycle and blunt exactly the recurring pattern you're trying to respect - for a series with strong periodicity, the moving average is the WEAKER of the two baselines, which is worth stating plainly to a stakeholder who might otherwise assume "more averaging = safer."

Trade-offs & pitfalls
Presenting only a point number without ANY baseline comparison is the single most common failure mode here - a stakeholder has no way to judge whether "next quarter: $1.2M" is a good forecast without a reference point. The second most common failure is silently using a moving-average baseline when the series has real seasonality, which understates how good a properly seasonal-aware model actually needs to be to add value: if your fancier model only modestly beats a moving average but doesn't beat seasonal-naive, it isn't adding real value yet.