Spreadsheet-based accounting

Plenty of businesses still keep their books — or important parts of them — in spreadsheets: a start-up before it buys software, a subsidiary on a group template, the accruals and prepayments schedules that feed even the largest ERP. A spreadsheet is flexible, but it has none of the guard rails an accounting system builds in. Nothing stops an unbalanced entry, a mistyped account code or a total overtyped with a number. So a spreadsheet ledger has to prove itself: every important figure is either checked by a formula or agreed to something outside the file.

Structure first. Keep three kinds of area apart: - Inputs — the journal tab: one row per line, with date, journal number, account code, description, debit and credit. Only these cells are typed into. - Reference data — the chart of accounts tab: code, name, type and reporting line. Account codes on the journal tab are chosen from this list (data validation), not typed freely. - Calculations and outputs — the trial balance tab totals the journal by account with one consistent formula per column (a conditional sum by account code). Nothing on this tab is typed by hand.

Check cells are formulas whose job is to be zero. The core set: total debits minus total credits on the journal tab; the same difference for each journal number (so two errors that cancel overall still show up); total debits on the trial balance minus total debits on the journal (catches lines whose code isn't on the chart, which a conditional sum silently drops); a count of lines with a missing or invalid code; and key balances agreed to independent control totals — cash to the bank statement, receivables to the customer listing, payroll to the payroll report. A summary cell turns red if any check isn't zero, and nobody reports from the file while it's red.

Controls on the file itself. Lock formula cells and protect the sheets so only input cells can change. Save a new version each period (or each day during the close) rather than overwriting. Keep a change log tab — date, who, what changed, why — for any change to formulas, structure or the chart. A reviewer looks at the checks and the log, not just the final numbers.

If you're learning spreadsheet formulas separately, everything here works with ordinary conditional sums and lookups. The skill this lesson teaches is the control design around them.