Ledger Setup
Part of Accounting technology improvement
Auditing manual spreadsheet handoffs
Trace spreadsheet inputs, formulas, versions and outputs to find risky accounting handoffs and decide what to correct.
Audit a spreadsheet handoff by tracing an input from source, through the workbook, into the accounting record or report that uses it. Record who changes the file, which version was approved and how the recipient checks the output. The result should be a specific finding and a practical correction.
Map the handoff
Choose a recent completed period. Ask the preparer and recipient to show the files they used, including exports and emailed copies. Record the source system, extraction date, workbook version, manual edits, output and destination entry. Note when the recipient received the file and what they checked before accepting it.
A settlement worksheet might combine sales, refunds and fees before a total reaches the ledger. Compare its source report and ledger entry for the same entity, currency and period. A balanced workbook total alone does not show that every source item was included once.
| At the handoff | Inspect |
|---|---|
| Input | Source report, filters, dates, count and total. |
| Transformation | Formulas, pasted values, exclusions and adjustments. |
| Version | Who could edit, which copy was approved and what changed later. |
| Output | Recipient, destination reference and expected result. |
| Exception | Missing source, unexplained difference or rejected file and its owner. |
Review the cells and decisions that matter
Start with inputs and outputs that could change a posted amount or a decision. Compare selected source rows with the workbook, then inspect formulas on the path to the final total. Look for a formula that stops short of new rows, a fixed value inside a calculation, an unexplained exclusion or an outdated lookup range.
Check controls in proportion to the workbook's use and risk. A recurring workbook may need stable source references, visible control totals, an approved version, protected calculation areas and a named owner. ICAEW's spreadsheet guidance identifies data, formula, process and communication errors; arithmetic checks alone do not cover all of them.
Source Report vs. Workbook: Key Differences to Check
- Data completenessAre all source rows included in the workbook? Check counts and totals.
- Formula accuracyDoes the formula reference the full range of data or stop at a fixed row?
- Version controlIs there one approved version? Who can edit it?
- Manual overridesAre values pasted instead of calculated? Are exclusions documented?
- Output verificationDid the recipient check the output before acceptance? What was checked?
Decide what to change
For each finding, state the consequence, affected periods, owner and remedy. If staff use conflicting copies, establish one controlled location and an approval point. If they repeatedly paste the same export, assess whether a supported import can carry the needed source references and expose missing or repeated items. Keep necessary accounting judgements with an authorised reviewer.
Before removing a workbook, identify any source detail or explanation held only there. Australian record-keeping guidance requires relevant transaction and bank records, but it does not say that every intermediate workbook must be retained. Keep the workbook or an equivalent trace where it is needed to explain a calculation or adjustment.
Close with a short findings register and a check for the next handoff. Review earlier outputs if a formula or mapping error may have affected them. The audit succeeds when the recipient can retrace the route from source to result without relying on the preparer's memory.
