# Cross Functional Spreadsheet Build

> Builds a spreadsheet somebody else will maintain: inputs, calculations and outputs on separate sheets, no constants buried inside formulas, a documented assumptions block, a checks sheet that fails loudly, and a written refresh procedure. Use when producing a model, tracker, budget, forecast, pricing sheet, reconciliation or allocation workbook that will be reopened next month by someone who did not build it. Trigger on 'build me a spreadsheet', 'make this into an Excel model', 'turn this into a workbook', 'build a forecast/budget/pricing model', 'can you produce an xlsx', 'this sheet keeps breaking'. Not for checking numbers already produced by a report or dashboard (use data-analytics-report-qa), not for agreeing what a metric means before it is modelled (use data-analytics-metric-definition), and not for writing the narrative that accompanies a variance (use finance-budget-vs-actual-commentary).

- Skill: `alihusains/cross-functional-spreadsheet-build` (Agent Skill)
- Install (CLI): `npx skillmds@latest add alihusains/cross-functional-spreadsheet-build`
- Raw SKILL.md: https://api.skillmd.com/api/skills/alihusains/cross-functional-spreadsheet-build/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: alihusains (https://skillmd.com/u/alihusains)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/alihusains/cross-functional-spreadsheet-build

---


# Building a maintainable spreadsheet

## Purpose

A workbook produced in one pass and handed over is almost always structurally
unmaintainable: a growth rate typed inside a formula, an override pasted over a
calculated cell, an assumption that exists only in the author's head. It gives the
right answer once and then quietly gives wrong answers for a year. This skill
fixes the layout, the formula rules, the assumptions block and the self-checks so
that the next person can change an input without reverse-engineering the author.

## Prerequisites

Check all three **before** building anything. If any is missing, name it and stop —
do not produce a partial workbook or a CSV substitute without saying so.

- **Inputs:** the actual data (file, table, or explicit figures), the periods and
  grain, the outputs the reader needs, and every assumption that is not derivable
  from the data. If assumptions are missing, list the ones you need and stop; a
  model built on invented rates is worse than no model.
- **Runtime:** code execution, plus a spreadsheet-writing library available in the
  environment — commonly `openpyxl` or `xlsxwriter` for `.xlsx`. Verify by import
  at step 0. If neither is importable, say which is missing and stop. Do not build
  the whole model and discover this at the write step.
- **Formula support:** if the workbook must contain **live formulas** rather than
  pasted values, confirm the library writes formula strings. A library that only
  writes values cannot produce a maintainable model — state that and ask whether a
  values-only workbook is acceptable before continuing.
- **Access:** where source data is pulled rather than supplied, the read
  permission on that source.

## Structure

Separate the three roles onto their own sheets. Mixing them is the root cause of
almost every unmaintainable workbook.

| Sheet | Contains | Never contains |
| --- | --- | --- |
| `Assumptions` | Every input a human may change: rates, prices, headcount, FX, dates, scenario switches. One per row, with unit, source and owner | Formulas referencing calculation sheets |
| `Data` | Raw imported records, unmodified, with an as-of timestamp and the source named | Manual edits, sorted-in-place corrections |
| `Calc` | The working — one calculation per column, consistent down the column | Typed constants, pasted values |
| `Output` | What the reader looks at: summary tables and charts | Any calculation not available on `Calc` |
| `Checks` | The self-tests from step 6 | Anything that has to be read to be interpreted |
| `Notes` | Version, owner, refresh procedure, change log | — |

Small workbooks may merge `Data` into `Calc`. `Assumptions` and `Checks` are never
merged away — they are the two sheets that make the workbook survivable.

## Procedure

1. **Verify the prerequisites and import the library.** Fail here, loudly, or not
   at all.
2. **Write the `Assumptions` sheet first**, before any calculation. Each row:
   name, value, unit, source (document, system, or "management estimate"), owner,
   and last-reviewed date. An assumption with no source is a guess and must be
   labelled as one.
3. **Load raw data to `Data` unmodified**, with the source name and an as-of
   timestamp in a header row. Corrections happen in a visible adjustment column on
   `Calc`, never by overtyping the source.
4. **Build `Calc` one column per step,** each column a single consistent formula
   from its first row to its last. Every rate, threshold, price or date reference
   points at a cell on `Assumptions`. **No numeric literal appears inside a
   formula** other than structural constants — 0, 1, 12 for months, 100 for a
   percentage conversion. `=B2*1.07` is the defect this rule exists to prevent: it
   is invisible, unsearchable, and it will be wrong next year.
5. **Never overtype a calculated cell.** If a value must be forced, add an
   explicit override column on `Assumptions` and have the formula consume it, so
   the override is visible and reversible. A hardcoded value pasted over a formula
   column breaks the column silently for everyone downstream.
6. **Build the `Checks` sheet so failures are loud.** Each check is a row with a
   description, the computed difference, and a status cell reading `OK` or `FAIL`
   — never a bare `TRUE`/`FALSE`, and never a blank that could mean either. Apply
   conditional formatting so a failure is visible without reading. Minimum set:

   | Check | Fails when |
   | --- | --- |
   | Totals tie | Output total ≠ the same total computed independently from `Data` |
   | Segments are additive | Parts do not sum to the whole, beyond a stated rounding tolerance |
   | Row counts | `Calc` rows ≠ `Data` rows (silent join or filter loss) |
   | No blanks in required inputs | Any `Assumptions` value is empty |
   | Balance / reconciliation | The identity the model must satisfy does not hold |
   | Data freshness | The as-of date is older than the stated refresh cadence |

   Put a single master status cell at the top of `Output`: `FAIL` if any check
   fails. A check nobody sees is not a check.
7. **Label units and periods in every header.** `Revenue` is ambiguous;
   `Revenue (USD 000s, month ending)` is not. Currency, thousands-or-units, and
   period-end-or-average are the three that cause silent misreading.
8. **Write the refresh procedure on `Notes`:** where each data source comes from,
   which cells are replaced on refresh, which are never touched, the expected
   cadence, what to check after refreshing (the `Checks` master cell), and who
   owns the workbook. Without this the workbook is single-use.
9. **Protect the structure, not the inputs.** Lock `Calc` and `Checks`; leave
   `Assumptions` open. This is the mechanical enforcement of rule 5.
10. **Test by changing one assumption.** Change a single input, confirm the output
    moves in the expected direction and magnitude, and confirm the checks still
    pass. A model that does not respond to its own inputs is wired wrong, and this
    is the only test that catches it.
11. **State the limits alongside the file:** what the model does not cover, which
    assumptions are estimates, and what would have to be true for it to be wrong.

## Data handling

Classification is inherited from the source data — treat a workbook containing
personal, client-level or financial-position data as **Confidential**. Aggregate
before distribution wherever the decision does not need row detail. Never embed
credentials, tokens or connection strings in a workbook or its macros. If personal
data, account numbers or trading positions are pasted into the conversation to be
modelled, flag it and stop.

## Failure modes

- **Constant inside a formula.** Invisible, unsearchable, wrong after the first
  change of circumstance.
- **Overtyped calculation cell.** One row differs from its column and nothing says
  so.
- **Checks that pass silently and fail silently.** A `FALSE` in an unread cell is
  the same as no check at all.
- **Undocumented assumptions**, so nobody can tell an agreed input from a
  placeholder.
- **No refresh procedure**, so next period the workbook is rebuilt from scratch
  and the two versions disagree.
- **Data edited in place**, destroying the only reconcilable copy.

## Boundaries

- The numbers already exist in a report or dashboard and need pre-publication
  checking — use `data-analytics-report-qa`.
- Two teams disagree on what the metric means — settle it with
  `data-analytics-metric-definition` before modelling; a workbook cannot resolve a
  definitional dispute.
- Writing the explanation of a variance the model surfaces — use
  `finance-budget-vs-actual-commentary` or
  `finance-month-end-variance-analysis`.
- A weighted supplier comparison matrix — use `operations-vendor-evaluation`,
  which owns the scoring method; use this skill only to build the file.

## Hand-offs

- **Receives from:** `data-analytics-metric-definition` (agreed definitions that
  become the `Assumptions` rows), `operations-vendor-evaluation` and
  `finance-month-end-variance-analysis` (the analysis that needs a workbook).
- **Routes to:** `data-analytics-report-qa` before any figure from the workbook is
  published, `cross-functional-deck-assembly` when the outputs become slides, and
  `operations-sop-authoring` when the refresh procedure becomes a recurring
  operational task.