Forecasting Copy link

Performance: Read br-analytics-standards at the start of every run (read_skill("br-analytics-standards")). All pipeline steps must follow those rules. Target runtime: under 7 minutes for single-module, under 20 minutes for orchestrations.

Turn a dated metric in a Blitz Report into a forward forecast with confidence bounds, and tell the user how much to trust it. Use Excel's native FORECAST.ETS family (exponential smoothing / Holt-Winters) — it lives in the workbook, handles seasonality and prediction intervals, and stays live if the source refreshes. No external compute needed.

When to use / not use

• Use for: time-series forecasting of a single metric over regular periods (monthly sales, weekly demand, GL balance trend, collections).
• Don't use for: predicting a per-row outcome from features (that's regression/classification), or when there isn't a real time axis. Be honest about short history: reliable seasonality needs ≥ 2 full cycles (≥ 24 months for monthly seasonality); with less, forecast without seasonality and say the bands are wide.

Shared conventions (the br_ analytics house style)

1. Source = an existing Blitz Report sheet; row 1 is headers. Never modify source columns.
2. Scratch work on br_calc_; delete when done.
3. Outputs (all four): a results sheet br_forecast_results (Period | Actual | Forecast | Lower | Upper); a chart; a dashboard (reuse br-data-dashboard for KPI layout); a plain-English chat summary with the accuracy read-out and caveats.
4. Determinism: ETS is deterministic given the same series and settings; state the period, horizon, seasonality, and confidence level you used.
5. Privacy: operate only on the provided sheet.

Workflow — follow in order

Step 1 — Identify the series and settings

Read headers + sample rows. Identify the date column and the value column. Decide:

• Period grain: monthly by default; weekly if the data is daily operational volume. State it.
• Horizon: how many future periods (default 12).
• As-of cutoff: forecast from the last complete period (drop a partial final period so it doesn't read as a fake drop). Confirm only if ambiguous.

Steps 2-4 — Spine + seasonality + forecast formulas (single execute_office_js call)

In one call: read the date + value columns, build the regular period spine (Step 2), and write the seasonality (Step 3) and FORECAST.ETS / CONFINT / STAT formula block (Step 4) — headers, seed formula row, and autoFill down the spine — all in the same JS block. Trust the formula_results this call returns; do not insert a verification read between building the spine and reading the forecast. The per-step detail follows.

Step 2 — Build a regular period spine (critical)

FORECAST.ETS requires an evenly spaced timeline with no gaps. On br_calc_, build a continuous list of periods from first to last, and aggregate the value into each period (SUMIFS). Fill missing periods explicitly — a month with no rows is a real 0, not absent. Confirm there are no duplicate or skipped periods before forecasting. Formulas in references/forecasting.md.

Step 3 — Detect seasonality

Use FORECAST.ETS.SEASONALITY(values, timeline) to detect the cycle length, or set it from the known business cycle (12 = monthly annual, 7 = daily weekly). If detected seasonality would need more history than you have (< 2 cycles), set seasonality to 1 (none) and note it.

Step 4 — Forecast with bounds and fit stats

• FORECAST.ETS(targetDate, values, timeline, [seasonality]) for each future period.
• FORECAST.ETS.CONFINT(targetDate, values, timeline, 0.95, [seasonality]) for the ± interval → Lower/Upper bounds.
• FORECAST.ETS.STAT(...) for fit statistics if you want to report smoothing/error detail.

Step 5 — Backtest accuracy (the trust signal)

Hold out the last h periods, forecast them from the earlier data only, and compute MAPE (mean absolute % error) against the actuals. Report it: ≲ 10% strong, 10-20% usable, 20-50% rough,

50% don't rely on point values. This number is what tells the user whether to believe the forecast.

Step 6 — Results sheet + chart (results in one execute_office_js call; chart is the permitted second call)

br_forecast_results: Period | Actual | Forecast | Lower | Upper (Actual blank for future periods; Forecast/Lower/Upper blank for history except the fitted line if you include it) — write the full sheet in a single call. Then, in the permitted separate charts call, chart a line of Actual + Forecast with a shaded confidence band (Upper/Lower as a band series). Mark where history ends and forecast begins.

Step 7 — Dashboard

Dashboard from br_forecast_results with KPI cards: next-period forecast, trend % (forecast vs last actual), seasonality found, backtest MAPE.

Step 8 — Chat summary

Report: the forecast for the horizon (or the headline next 1-3 periods), the trend direction, whether seasonality was found and its cycle, the MAPE accuracy, and caveats — wide bands far out, don't forecast through a known structural break (price change, system migration, COVID-type shock), and that outliers in history distort ETS (consider running br-anomaly-detection first to clean).

Gotchas

• Gaps break ETS. Build the regular spine and fill empty periods with 0 (or interpolate only if a 0 is clearly wrong, and say so). Step 2 is mandatory.
• A partial last period looks like a cliff — drop it or annotate it.
• Seasonality needs ≥ 2 full cycles; otherwise turn it off rather than inventing a pattern.
• Confidence bands widen with horizon — long forecasts are illustrative, not precise.
• Outliers and one-off spikes pull the smoothing; clean or flag them first.
• These are native Excel functions — write them as cell formulas on the spine and read results via formula_results; they are not JS.