Blitz Report Excel Add-in Skills / Analytics
Forecasting
br-forecasting
Forecast a time series from a Blitz Report — future demand, sales, GL trend, AR collections, cash, or inventory — using Excel's native FORECAST.ETS (Holt-Winters) with seasonality detection, confidence intervals, and a backtest accuracy check.
When to use
Use this whenever the user wants to forecast, project, predict, or extrapolate future values of a dated metric in an Oracle EBS / Blitz Report sheet — "forecast next quarter's sales", "project AR collections", "what will the GL trend look like", "predict demand". Trigger it for any "forecast / projection / what will happen next" request over time.
Install
Claude in Excel: upload the zip under Customize > Skills > Create skill. Microsoft 365 Copilot in Excel: copy the skill folder into Documents/Copilot/Microsoft Excel/Skills in your OneDrive. Or install blitz-report-excel-addin-ai-skills in Claude Code and run /blitz-report-excel-addin-ai-skills:excel-skills-setup.
Requires Blitz Report and the Blitz Report Excel add-in, connected to your Oracle EBS instance.
Skill contents
SKILL.mdreferences/forecasting.md
SKILL.md
# br-forecasting — project a Blitz Report metric forward
> **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.references/forecasting.md
# Forecasting — Excel FORECAST.ETS reference These are **native Excel formulas** written into cells on the `br_calc_` spine, then read via `formula_results`. They are not JavaScript. Adapt cell ranges to the data. ## TOC 1. Build the regular period spine 2. Aggregate values into the spine 3. Seasonality detection 4. Forecast + confidence interval 5. Backtest MAPE --- ## 1. Build the regular period spine Monthly spine from a start date in `A2`, one period per row: ``` A3: =EDATE(A2,1) ' drag down to the last period; gives 1st-of-month dates ``` Weekly spine (7-day step): ``` A3: =A2+7 ``` The timeline must be strictly evenly spaced with **no gaps and no duplicates**. Verify the count of periods equals (last−first)/step + 1. ## 2. Aggregate the metric into each period (fill gaps with 0) With raw dates in `Data!D:D` and amounts in `Data!E:E`, monthly bucket on spine date `A3`: ``` B3: =SUMIFS(Data!$E:$E, Data!$D:$D, ">="&A3, Data!$D:$D, "<"&EDATE(A3,1)) ``` A period with no transactions returns 0 — that is correct and required; do not leave it blank. ## 3. Seasonality detection ``` =FORECAST.ETS.SEASONALITY(B3:B40, A3:A40) ``` Returns the detected cycle length (e.g. 12). If the series has fewer than 2 full cycles of history, override to 1 (no seasonality) in the forecast call below and note it. Known cycles: 12 monthly-annual, 7 daily-weekly, 4 quarterly-annual. ## 4. Forecast and confidence bounds for a future period in `A41` ``` Forecast: =FORECAST.ETS(A41, $B$3:$B$40, $A$3:$A$40, 12, 1) ConfInt : =FORECAST.ETS.CONFINT(A41, $B$3:$B$40, $A$3:$A$40, 0.95, 12, 1) Lower : =Forecast - ConfInt Upper : =Forecast + ConfInt ``` Arguments: (target_date, values, timeline, [seasonality], [data_completion=1 fills gaps], [aggregation]). Set seasonality to 1 to disable. `FORECAST.ETS.STAT(values,timeline,stat,…)` returns fit detail (stat 4 = alpha, 5 = beta, 6 = gamma, 8 = MASE) if you want to report smoothing parameters. ## 5. Backtest MAPE (trust signal) Hold out the last `h` actual periods, forecast them from earlier data only, then: ``` Per-period APE: =ABS(Actual - Forecast)/ABS(Actual) MAPE: =AVERAGE(of the APE column) ' express as % ``` Skip periods where Actual = 0 (division blows up) or use a small-denominator guard. Interpretation: ≲10% strong, 10-20% usable, 20-50% rough, >50% treat as directional only.