Structured Spreadsheet Modelling - What Is It?

Every accountant has inherited a spreadsheet they were afraid to touch. It doesn't have to be that way.

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:

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:

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:

  1. One home per number. A value is held once, in the datastore. Everything else refers to it.
  2. 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.
  3. 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.
  4. Debits positive, credits negative. One sign convention, everywhere, no exceptions and no flipping on the way to the report.
  5. Structure lives in the account code. Codes such as AR.OVER90.TRADE.SOUTH let 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.
  6. 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.
  7. 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.
  8. 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:

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:

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.

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

← All posts · Home

Contact

If you have a model you'd rather trust than tolerate, get in touch.

Connect on LinkedIn →