Amey-Thakur Spreadsheet modelling
Spreadsheets are the most used and least engineered software in most organisations. Errors are common, consequential, and hard to spot, because nothing enforces structure and everything looks equally authoritative.
Method
- Separate inputs, calculations, and outputs. Distinct areas or sheets, with inputs clearly marked, so a reader knows what is an assumption and what is derived.
- Never hardcode a number inside a formula. Every constant is an input cell with a label, since buried constants are the classic undiscoverable error.
- Keep one formula per row or column. Consistency lets you copy across and makes an inconsistent cell visible as an anomaly.
- Name ranges for anything referenced repeatedly. Named references read as meaning rather than as coordinates.
- Build in checks. Totals that must reconcile, balances that must be zero, flagged visibly, which is the spreadsheet equivalent of a test (see assertion-density).
- Document assumptions on the sheet. A model without its assumptions is unusable by anyone else and by you in six months.
- Version deliberately. Spreadsheets are copied and edited in parallel, so a clear naming and ownership convention prevents competing truths.
Boundaries
Spreadsheets suit modelling and analysis, not production data processing or multi-user workflows, where a database and an application belong (see schema-design). Complex models become unmaintainable and should move to code. Financial models used for decisions need review by someone who did not build them.