git:20260929.1c55fa5 to git:20260929.bf65181

---

name: spreadsheet-authoring

description: >-

Design scalable spreadsheet models with category-level raw data, formula-

driven aggregation tables, provenance, and charts. Use when creating or

restructuring Excel or Google Sheets workbooks that must grow by adding rows.

---

# Spreadsheet authoring

Build the workbook as a data pipeline, not as a decorated report:

`raw observations → selector-driven aggregation table → chart`

## Raw observation contract

- Keep one raw sheet per category.

- Keep one numeric observation per row.

- Put dates in typed `Period Start` and `Period End` columns.

- Put `Average`, `Start`, `Peak`, `End`, `Low`, `High`, or similar qualifiers

in a separate categorical `Observation Point` column.

- Preserve the source wording in `Original Period Label` for audit, but never

use that display label as the aggregation key.

- When a source publishes a bounded start/end range but omits an average, a

midpoint may be added only as a derived estimate: retain the source URL,

label `Observation Type` as `derived estimate`, and record the formula in

`Notes`. Never derive a start or end value from a peak, low, or other

non-endpoint observation.

- Keep geography, scope, segment, entity, metric, value, unit, source, source

URL, source location, derivation method, and derived-from lineage as

separate fields.

- Synthetic observations may be added to the category raw sheet only when

they are explicitly marked `Observation Type = derived estimate`. They must

include `Derivation Method`, `Derived From`, a source URL for the underlying

observations, and a formula/method note. The aggregate must support a

published-only view and a published-plus-derived view; charts must make the

selected mode visible.

- The default permitted synthetic methods are `midpoint` from matching Start

and End observations and `linear interpolation` between bracketing periods.

Never synthesize a competitor's missing observation from another entity, or

infer Start/End/Peak/Low from an unrelated observation point.

When parsing a range such as `Nov 2024–Sep 2025`, write the first and last

dates as typed values. When parsing `Q4 2023 end`, write the quarter dates and

`End` separately. Derive human-readable titles after the typed fields exist.

## Aggregation and chart contract

- The aggregation table must reference raw fields, not copied values.

- Include every dimension that distinguishes observations in its key, including

the observation point and cohort/segment.

- Let selectors choose the metric and the relevant subset of raw data.

- Reserve expandable formula ranges so adding a valid raw row updates the table.

- Charts must reference the aggregation table only, never the raw sheet.

- Declare the expected entity inventory for each category and measurement slice.

A table/chart is not complete until every expected entity has a valid,

source-backed observation for each aggregation key, or the raw data carries

an explicit `No observation published` status. Never convert an absent value

into zero or silently treat it as complete. Derived estimates do not satisfy

source-backed entity coverage.

## Preflight and verification

Before editing the live workbook, validate representative period labels,

source URLs, expected-entity coverage, and the migration result. Reject any

schema where a time column contains a qualifier token, where two distinct

observation points collapse to one key, or where an expected entity is absent

from a source-backed aggregation key. For CSV exports or fixtures, run

`scripts/validate_schema.py <file> --expected-entities A,B,C`.

If coverage fails, do not proceed to table or chart generation. Run a bounded

source-collection retry loop up to three times; each attempt must either add

new source-backed raw observations or record an explicit unresolved status.

After the third attempt, stop with `SPREADSHEET_COVERAGE: FAIL` and list the

missing entity/period keys. Never resolve the failure by copying, averaging,

zero-filling, or estimating an unreported competitor value.

After writing, reread raw headers, typed dates, observation points, derivation