In short
Spreadsheets are the most successful end-user programming tool ever made, and accountants are their most devoted users. We build budgets, cash flows, working papers, fixed asset registers and business plans in them, and we hand them to clients, banks and boards who make real decisions on the numbers.
Yet most spreadsheets are built the way a house would be built if every brick were laid by hand, by a different person, with no drawings. Structured spreadsheet modelling is the alternative: a disciplined way of designing and building spreadsheet models so that every number has one home, every calculation can be traced, and the whole workbook can be checked, explained and rebuilt. This post explains what that means in practice, and why accountants already know most of it.
The trouble with free-form spreadsheets
A blank grid lets you put anything anywhere. That is its genius and its curse. Over a few years of use, the typical model accumulates:
- formulas copied across a row, except the one in column K that someone overwrote with a typed number in a hurry;
- the same figure entered on three sheets, two of which were updated;
- hidden sheets, hidden rows and "temporary" workings that became permanent;
- links to a file on a laptop that left the firm in 2019;
- a macro nobody dares to run and nobody dares to delete.
None of this is carelessness. It is what happens when a tool imposes no structure and the deadline is Friday. The research community has been saying so for decades: studies of real-world spreadsheets consistently find errors in a large share of them, and a reviewer checking cell by cell finds only some of those errors. The problem is not that spreadsheet users are bad at arithmetic. It is that a free-form workbook has no shape a reviewer can rely on.
The big idea: think like a book-keeper
Double-entry book-keeping solved a very similar problem five hundred years ago. A ledger is trustworthy not because the clerk is clever but because the structure is. Every amount is posted to a named account. Corrections go through journals, never by scratching out a balance. Reports are drawn from the ledger, not kept alongside it. And the books are checked - the trial balance either balances or it doesn't.
Structured spreadsheet modelling applies exactly those ideas to the workbook:
- every variable in the model is an account with a code and a description;
- every value lives in one place, a central datastore - rows are accounts, columns are periods (or scenarios, entities, or the working columns of a trial balance);
- corrections are posted as adjustments, debit and credit, with a description;
- reports are views of the datastore, not separate copies of it;
- checks are built in and visible, and the model tells you whether it passes.
If you can read a trial balance, you can read a structured model.
Anatomy of a structured model
A structured workbook always has the same parts, in the same places, doing the same jobs:
| Part | What it holds | The book-keeping equivalent |
|---|---|---|
| Chart of accounts | Every variable: code and description | Chart of accounts |
| Datastore | Every value, one row per account, one column per period | The ledger |
| Adjustments | Debit/credit corrections with descriptions | The journal |
| Logic | The relationships: totals, ratios, depreciation, interest | Postings and closing entries |
| Views | Formatted reports linked to the datastore | Financial statements |
| Extracts | Values in long, flat format for other systems | The export to the tax return |
| Checks | Live PASS/FAIL tests, rolled up to one overall result | The trial balance |
| Read-me | What each sheet is for and how to maintain it | The permanent file note |
Because every model has this shape, a reviewer who has seen one has, in a real sense, seen them all. You know where the inputs are, where the calculations are, and where to look when a number on page three looks wrong.
The rules that make it work
Structure is only useful if it is enforced. A structured model follows a short list of rules - and the point is that they are rules, not good intentions:
- One home per number. A value is held once, in the datastore. Everything else refers to it.
- Every derived number is a live formula. Nothing is pasted as a value. You can trace any figure back to its inputs with Excel's own tools.
- Calculation and presentation are separate. The logic lives with the data; the report sheets present it. Changing a report's layout cannot break the arithmetic.
- Debits positive, credits negative. One sign convention, everywhere, no exceptions and no flipping on the way to the report.
- Structure lives in the account code. Codes such as
AR.OVER90.TRADE.SOUTHlet the model total "all receivables over 90 days" or "everything in the South" with a simple pattern, instead of a hand-picked list of cells. - Colour tells you what you're looking at. Inputs, calculated cells and linked cells are visibly different, so you know which cells you may change and which you may not.
- Protected, but not secret. Every sheet is protected so formulas can't be overtyped by accident, but there is no password, no hidden sheet and no macro. Anyone who needs to see how it works can.
- It must work in LibreOffice too. If a model depends on some obscure Excel-only feature, that is a smell, not a feature.
Rule 7 deserves a word. Protection here is a guard rail, not a lock. The workbook must stand on its own for whoever looks after it next - a partner, a client's finance team, an auditor - without a phone call to the person who built it.
Built, not typed
Here is the part that surprises people. A structured model is not assembled by hand, cell by cell. It is generated from a written specification: the chart of accounts, the data sources, the adjustments, the relationships and the report layouts are declared once, and the workbook is produced from that declaration.
The practical consequences are what matter to an accountant:
- Same inputs, same workbook. Build it twice and you get the same result. There is no drift.
- Next year is a rebuild, not a roll-forward. Rather than copying last year's file and patching it, you update the specification and produce a clean model. The debris of the free-form world never accumulates.
- Mistakes are caught before the workbook exists. A total that refers to an account that doesn't exist, or an adjustment that posts only one side, stops the build. It does not quietly produce a plausible-looking wrong answer.
And the client never needs to know or care how it was built. What they receive is an ordinary workbook - formulas, sheets, formatting - that they own and can use like any other. It just happens to be one they can trust.
What it's good for
Anything where the numbers have a natural ledger-like shape, and where getting them wrong has consequences:
- budgets, forecasts and cash flow projections;
- trial balance working papers, with adjustment columns and lead schedules;
- fixed asset registers and depreciation;
- debtor ageing and bad debt provisioning;
- product and activity-based costing;
- manpower and payroll planning;
- business plans for banks and investors;
- the private ledgers of family businesses and trusts.
If you have ever built one of these and then spent longer checking it than building it, it is a candidate.
What it isn't
It is worth being honest about the boundaries.
- It isn't a replacement for Excel. The output is a spreadsheet. Your skills transfer completely.
- It isn't a black box. If anything, it is the opposite: there is nowhere for logic to hide.
- It isn't a database or an accounting system. Source data is prepared before it reaches the model; the model doesn't replace your ledgers.
- It doesn't do circular references. Interest on an overdraft that depends on the closing balance that depends on the interest is solved by restructuring the calculation, not by switching on iterative calculation and hoping. Many modellers would say that is a feature.
- It isn't worth it for a ten-line calculation. The back of an envelope is still a perfectly good spreadsheet.
A note for academics
Spreadsheet risk has a substantial literature, and a community - the European Spreadsheet Risks Interest Group (EuSpRIG) - that has documented both the frequency of errors and their sometimes very public consequences. Most of the remedies proposed fall into two camps: better practice (modelling standards, style guides, review checklists) and better tools (auditing add-ins that inspect a finished workbook).
Structured spreadsheet modelling sits between the two. It treats the spreadsheet as a compilation target rather than a hand-crafted artefact: the separation of data, logic and presentation is enforced by construction, and validation happens at build time rather than at review time. Its accounting vocabulary - chart, ledger, journal, trial balance - is not decoration; it descends from the script-based planning tools of the 1980s and 1990s, which expressed models as accounts and relationships long before spreadsheets became the default. For researchers interested in reproducibility, there is a further benefit: a model that is regenerated from its specification can be versioned, compared and audited like any other source document.
In conclusion
Structured spreadsheet modelling asks nothing of accountants that the profession didn't already ask of its book-keepers: name every account, keep every value in one place, post corrections properly, draw reports from the ledger, and check that it balances. Apply those habits to the spreadsheet and you get models that can be reviewed in hours rather than days, handed over without anxiety, and rebuilt next year without archaeology.
Later posts will look at each technique in turn, with small sample models to download.
Further reading
- European Spreadsheet Risks Interest Group (EuSpRIG) - conference proceedings and a catalogue of public spreadsheet error cases, eusprig.org.
- Panko, R. R. (1998). What We Know About Spreadsheet Errors. Journal of End User Computing, 10(2), 15-21.
- The FAST Standard - a widely used set of financial modelling conventions, fast-standard.org.
Contact
If you have a model you'd rather trust than tolerate, get in touch.