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.

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.