Update to Revelata deepKPI

{
  "description": "Computes seasonal quarterly ratios from historical actuals and applies them to split annual projections into quarterly estimates. Trigger for: quarterly breakdown of annual forecasts, in-progress fiscal year projections, any request involving quarterly splits or seasonal patterns. Always retrieve underlying data via the kpi skill first. For `.xlsx`/CSV workbook exports, follow ../_common/revelata-excel-styling-rules.md (plus the xlsx skill).\n",
  "included_files": [],
  "name": "seasonality",
  "skill_md_contents": "---\nname: seasonality\ndescription: >\n  Computes seasonal quarterly ratios from historical actuals and applies them to\n  split annual projections into quarterly estimates. Trigger for: quarterly\n  breakdown of annual forecasts, in-progress fiscal year projections, any request\n  involving quarterly splits or seasonal patterns. Always retrieve underlying data\n  via the kpi skill first. For `.xlsx`/CSV workbook exports, follow\n  ../_common/revelata-excel-styling-rules.md (plus the xlsx skill).\n---\n\n# seasonality\n\nComputes each quarter's typical share of the full fiscal year, then applies those\nratios to project quarterly values from annual forecasts.\n\n**All historical inputs must come from deepKPI** (via the `kpi` skill). Call\n`list_kpis` first to discover exact KPI names for both the quarterly and annual\nseries before searching. Never use hardcoded or assumed figures.\n\n**Do not** apply to balance sheet items (cash, receivables, inventory) — these\nare point-in-time stocks; use the fiscal year-end value as Q4 directly.\n\n## Data source\n\nSee `../_common/data-source-rules.md`. Deep KPI data flows through the standard\n`query_company_id` / `list_kpis` / `search_kpis` workflow used by the `kpi` skill.\n\n## Hard stop if deepKPI access fails\n\nSee `../_common/connection-failure.md`. STOP and ask before falling back.\n\n## Core formula\n\n```\nQ_ratio(year, q)  = Q_value(year, q) / FY_value(year)\nseasonal_ratio(q) = simple average of Q_ratio across most recent 3 years\nprojected_Q_value = projected_FY × seasonal_ratio(q)\n```\n\nFour ratios must sum to ~100%. If materially off, normalize before applying.\n\n## Steps\n\n1. **Retrieve** annual + quarterly actuals from deepKPI (minimum 2 years; 3 is\n   standard). Capture the provenance URL for every value returned.\n2. **Always derive Q4** — companies don't file a Q4 10-Q so Q4 will never be\n   directly in deepKPI. Always compute `Q4 = FY − (Q1+Q2+Q3)` for every year\n   in the series. Use the `implied-metric` skill. Put Q4 **in the quarterly\n   revenue (metric) row**, Q4 column—**not** a separate \"Q4 (derived)\" row. Flag in\n   **Notes**.\n3. **Compute per-year ratios** — divide each quarter by its fiscal year total.\n4. **Average** across the 3 most recent complete years.\n5. **Flag anomalies** — if one year is distorted (product launch, acquisition,\n   COVID), note it in the Notes column and consider excluding from the average.\n6. **Apply** ratios to each projected annual figure.\n\n## In-progress fiscal year\n\nWhen Q1–Q2 are actuals and you need to project Q3–Q4 only:\n\n```\nremainder     = projected_FY − Q1_actual − Q2_actual\nQ3_reweighted = Q3_ratio / (Q3_ratio + Q4_ratio)\nQ4_reweighted = Q4_ratio / (Q3_ratio + Q4_ratio)\nprojected_Q3  = remainder × Q3_reweighted\nprojected_Q4  = remainder × Q4_reweighted\n```\n\nThe same re-weighting logic applies for any subset of remaining quarters.\n\n## Output format (in-chat tables)\n\nFollow the `kpi` and `implied-metric` skills for how tables read in chat.\n\n**From the `kpi` skill (data pull):**\n\n- **Provenance:** Every numeric figure uses a clickable deepKPI link —\n  `[value](exact-url)` — per the `kpi` skill's **Provenance — non-negotiable rules**\n  (copy URLs exactly as returned; never reconstruct or guess). For **derived**\n  values, link the **operands** or state sources as that skill requires.\n- **KPI identity:** Merge into one series when deepKPI returns the **same KPI**\n  (same metric, unit, aggregation); use **separate** rows or tables when they\n  differ.\n- **Order:** **Annual figures first**, chronological (**oldest at top**), then a\n  **clear break** (rule, blank line, or subheading), then **quarterlies** (oldest\n  first). Do not interleave annual and quarterly without separation.\n- **Tall vs wide:** For ordinary historical feeds, prefer **tall** tables — **each\n  row is one period**, first column **Period**, then **one column per metric** when\n  periods align — per the `kpi` skill's **In-chat table template (data pull)**. **Do\n  not** use a wide date axis \"many period columns\" layout for `.xlsx`-style modeling\n  in chat; that layout is for files (`../_common/revelata-excel-styling-rules.md`).\n\n**Actuals block (aligned quarters + Q4 across years)** — use the `implied-metric`\nskill's **Multi-period table** pattern (wide grid is allowed **here** for this\nderivation layout):\n\n- **Time flows left to right** — column headers are period labels (`FY22-Q1` …\n  `FY22-Q4`), earliest at left.\n- **One row per time series** — each operand row maps 1:1 to one deepKPI series; do\n  not fragment into separate rows per quarter (e.g. not \"Revenue - Q1\" / \"Revenue -\n  Q2\").\n- **Imputed Q4 in-stream** — the quarterly **metric row** carries Q1–Q3 from\n  deepKPI and **Q4 as imputed** (formula or linked value) in the Q4 columns—**no**\n  extra line item for \"Q4 (derived)\". **Annual** row stays separate when it is a\n  different series. **Bold** imputed cells only if it helps; **Notes** documents\n  imputation.\n- **Links embed in cell values** — `[$248.0M](exact-deepkpi-url)`; no separate Source\n  column.\n- **Notes** is the **bottom row** of the block; populate only in columns where\n  something non-standard occurred (derivation, anomaly, re-weighting, etc.).\n\nShow **all work**: operands (FY, Q1–Q3) visible with links; Q4 appears **in the\nquarterly row**. Ratios block still shows per-year math. Per the `implied-metric` skill.\n\n**Ratios** and **Projections** blocks can use compact tables; still hyperlink any\ncell that is a direct deepKPI-reported value.\n\nIf the reply includes these **data** tables and the user did **not** ask for CSV /\nExcel / `.xlsx` in the same turn, end with the **bold** workbook offer per the\n`kpi` skill (**Mandatory: Excel offer after in-chat data pull**).\n\nThree blocks in sequence: **Actuals → Ratios → Projections**.\n\n```\n## Seasonality — [Metric] ([Ticker])\nUnits: $M unless noted\n\nACTUALS\n                   | FY22-Q1       | FY22-Q2       | FY22-Q3       | FY22-Q4    | FY23-Q1       | FY23-Q2       | FY23-Q3       | FY23-Q4    | FY24-Q1       | FY24-Q2       | FY24-Q3       | FY24-Q4    |\n-------------------|---------------|---------------|---------------|------------|---------------|---------------|---------------|------------|---------------|---------------|---------------|------------|\nRevenue (quarterly)| [$248](url)   | [$301](url)   | [$292](url)   | **$355** (FY−Q1−Q2−Q3) | [$267](url)   | [$325](url)   | [$297](url)   | **$495** (same) | [$248](url)   | [$301](url)   | [$292](url)   | **$715** (same) |\nRevenue (annual)   |               |               |               |[$1,196](url)|              |               |               |[$1,384](url)|              |               |               |[$1,556](url)|\nNotes              |               |               |               | imputed Q4 |               |               |               | imputed Q4 |               |               |               | imputed Q4 |\n\nSEASONAL RATIOS\n     | FY2022 | FY2023 | FY2024 | 3-yr avg |\n-----|--------|--------|--------|----------|\nQ1   | 20.7%  | 19.3%  | 15.9%  | 18.6%    |\nQ2   | 25.1%  | 23.5%  | 19.3%  | 22.6%    |\nQ3   | 24.4%  | 21.5%  | 18.8%  | 21.6%    |\nQ4   | 29.7%  | 35.8%  | 45.9%  | 37.1%    |\nSum  | 100%   | 100%   | 100%   | 100%     |\nNotes| Q4 from imputed quarter / FY | (same) | (same) | |\n\nPROJECTIONS (FY20XXE = $X)\n                  | FY2XE-Q1 | FY2XE-Q2 | FY2XE-Q3 | FY2XE-Q4 |\n------------------|----------|----------|----------|----------|\nProjected ($M)    |          |          |          |          |\nNotes             |          |          |          |          |\n```\n\nThe Notes row must be populated for: derived Q4 values, excluded anomaly years,\nre-weighted in-progress quarter projections, stock items used as Q4 directly, or\nany year where the ratio was estimated rather than computed from deepKPI data.\n\n## Exporting to Excel or CSV\n\nUse `../_common/revelata-excel-styling-rules.md` for all spreadsheet layout,\nstyling, numeric date headers, freeze panes, column grouping, hyperlinks on\n**value cells** (green, no underline — **no** separate Sources sheet/rows for\nfilings), number formats, and the pre-delivery checklist. Implement with the\n**xlsx** skill.\n\n**Seasonality model addenda (on top of that spec):**\n\n- **Three ALL CAPS section blocks** in document order: **ACTUALS**, **SEASONAL\n  RATIOS**, **PROJECTIONS**. Row semantics match the markdown template earlier in\n  this skill; **period columns** follow `../_common/revelata-excel-styling-rules.md`\n  (**annual block**, blank separator, **quarterly block** — not quarterlies-left as\n  in legacy examples).\n- **Every ratio and every projected quarter** is a live `=` formula. Do not\n  hardcode seasonal ratios or projection numbers. Examples:\n  - **Q4 actual (in the quarterly metric row, Q4 column):**\n    `=FY_cell - Q1_cell - Q2_cell - Q3_cell` — **not** a separate \"Q4 derived\" row\n  - **Per-year Q ratio:** `=Q_cell / FY_cell`\n  - **Average ratio:** `=AVERAGE(ratio_year1, ratio_year2, …)`\n  - **Projected quarter:** `=projected_FY_cell * avg_ratio_cell`\n  - **In-progress re-weighting:** `=Q3_ratio_cell / (Q3_ratio_cell + Q4_ratio_cell)`\n- The model must **recalculate** when FY projection assumptions change.\n- **CSV:** Cannot store live formulas or real hyperlinks. Add `provenance_url` and\n  `formula` (raw strings) as needed; warn the user and recommend `.xlsx`.\n\nRun the pre-delivery checklist in `../_common/revelata-excel-styling-rules.md` before\nsharing the file; add seasonality-specific checks: all ratios/projections are\nformulas; Notes row populated per this skill's rules where applicable.\n\n## Caveats to flag in Notes column\n\n- **Imputed Q4**: flag in Notes every year where Q4 = FY − Q1 − Q2 − Q3 (not directly reported), even though the value sits **in the quarterly row**\n- **Anomaly year excluded**: identify the year, the distortion, and that it was excluded\n- **Shifting pattern**: if the quarterly mix is trending over time, note the direction\n- **Non-December fiscal year**: match Q1–Q4 labels to the company's fiscal calendar\n  (e.g., AAPL ends September, NVDA ends January)\n- **In-progress re-weighting**: note which quarters are actuals vs. projected\n- **Geographic/segment splits**: same technique applies; note the series used\n"
}