Audit a project margin spreadsheet by first defining its purpose, users, decisions, and risk. Then review structure, trace sampled inputs to source evidence, recalculate key formulas independently, test edge cases, inspect outputs for reasonableness, review changes and access, document findings, correct them, and retest. High-risk or highly complex models need an appropriately qualified independent reviewer.
What should be agreed before testing cells?
ICAEW's spreadsheet-review guidance recommends starting with the big picture: purpose, construction, risk, and the author's capability. Diving straight into formulas can miss a more important problem, such as the wrong margin definition, an incomplete cost scope, or a workbook used for decisions it was never designed to support—see the broader project margin management guide for how that definition connects pricing and delivery in the first place.
- Which decision and reporting period does the workbook support?
- Who owns, updates, reviews, and consumes it?
- Which revenue, cost, margin, baseline, and actuals definitions apply?
- Where do rates, hours, commercial values, and non-labour costs originate?
- What financial, client, regulatory, privacy, and access risks exist?
- What constitutes a material error for this review?
The level of work should be proportionate to risk. ICAEW says extremely complex or very high-risk spreadsheets should be reviewed by specialists. This checklist supports internal control review; it does not define a statutory financial-statement audit.
A seven-stage project margin spreadsheet audit programme
| Stage | Test | Evidence to retain |
|---|---|---|
| 1. Structure | Map inputs, calculations, outputs, hidden sheets, names, queries, links, macros, and protection. | Workbook map and exceptions. |
| 2. Source data | Sample approved price, resource, rate, hours, and other cost entries back to authorised evidence. | Sample list, source reference, result, reviewer. |
| 3. Completeness | Reconcile source populations and totals; test missing, duplicate, and unmapped rows. | Zero-balance checks and exception log. |
| 4. Formulas | Inspect consistency, constants, ranges, effective-date logic, errors, circularity, and repeated calculations. | Formula samples and independent recalculation. |
| 5. Behaviour | Test zero, blank, negative, duplicate, boundary-date, and extreme values. | Expected versus actual results. |
| 6. Outputs | Perform reasonableness, ratio, and trend review; check labels, cut-off, version, and limitations. | Analytical review and explanations. |
| 7. Change and access | Review file history, formula edits, approvals, sharing, locked ranges, and sensitive-rate visibility. | Change sample, access matrix, findings. |
Trace in both directions
Select a summary result and trace it down to calculation rows and source inputs. Then select source records and trace them forward to the summary. The first direction tests support for reported outputs; the second helps detect omitted records. Document sampling criteria rather than selecting only clean-looking rows.
Worked test: an effective-rate error
A consultant records 40 hours in the week beginning 3 August and 40 hours in the week beginning 10 August. The authorised cost rate is USD 70 until 9 August and USD 75 from 10 August. The workbook uses the latest rate for all 80 hours.
| Week | Hours | Correct rate | Correct cost | Workbook cost |
|---|---|---|---|---|
| 3 August | 40 | USD 70 | USD 2,800 | USD 3,000 |
| 10 August | 40 | USD 75 | USD 3,000 | USD 3,000 |
| Total | 80 | - | USD 5,800 | USD 6,000 |
The workbook overstates labour cost by USD 200 because its lookup ignores effective dates. The reviewer should identify the affected population, correct the lookup or data model, independently recalculate the period, assess whether prior reports need action, and record the change. Testing one current-rate row would not have found the boundary defect.
Original audit checklist for the margin summary
- Recalculate margin amount and percentage outside the workbook for a sample project.
- Confirm the revenue denominator and every included cost category.
- Reconcile planned and actual hours at the same cut-off.
- Inspect formula consistency across the entire calculation range.
- Confirm actual-to-date results are not labelled as forecasts.
- Verify budget consumption is not described as earned-value percent complete.
- Check that findings, corrections, approvals, and retest results are retained.
Which Excel controls help, and where do they stop?
Three built-in Excel features are commonly cited as spreadsheet controls. Each helps with part of the audit programme above, and each has a documented limit that the reviewer must still work around.
| Tool | What it helps with | Where it stops |
|---|---|---|
| Formula-error tools (Watch Window, formula evaluation) | Flags issues such as inconsistent ranges and provides Watch Window and formula-evaluation support. | Cannot determine whether a business rule or source value is correct. Independent recalculation and source tracing are still needed. |
| Show Changes | Identifies who changed cell values or formulas, where, and when, in supported Microsoft 365 workbooks. | Not every object or operation is shown, some older or unsupported edits can affect the visible history, and Version History may be needed for earlier versions. Treat it as evidence within a broader change-control process, not an infallible audit log. |
| Data validation and worksheet protection | Data validation restricts directly entered values; worksheet protection reduces accidental formula edits. | Copied entries or later changes in referenced cells may bypass validation, and Microsoft explicitly distinguishes worksheet protection from a security feature. File access and encryption controls are separate considerations. |
Auditing a legacy tracker before a managed-services contract renewal
Consider a five-year-old Excel margin tracker inherited for a managed-services contract that is up for renewal in ninety days. Nobody currently on the account built the workbook, and finance wants assurance before the renewal price is finalised. Running the audit programme above against this specific situation surfaces problems that are typical of an aged, hand-me-down tracker rather than a freshly built one.
- At audit kickoff, three near-identical copies of the workbook are circulating across a shared drive, email attachments, and a SharePoint folder, with no obvious record of which is authoritative. Handling: before any cell testing, use stage 1 of the audit programme to map structure and confirm a single working copy; treat unresolved version conflicts as a scope-defining finding, not a detail to sort out later.
- During formula testing, the tracker turns out to hold one "current rate" cell that has been manually overwritten at every renewal for five years, so every historical month now silently recalculates at today's rate instead of the rate that applied at the time. Handling: this is the same defect pattern as the effective-rate error in the worked test above — apply stage 4's effective-date logic check across the full history, not just the current period.
- At the renewal decision point, the new contract term proposes updated labour rates, and rows for the outgoing and incoming terms risk being blended in the same margin trend line. Handling: treat the renewal like any other approved baseline change — label the new rate basis explicitly rather than comparing it silently with the old one, and read the result alongside the review cadence the account will use going forward.
- At the change-and-access stage, the original workbook owner has left the firm, and Show Changes history does not reach back far enough to cover the full contract term. Handling: per the Excel-controls table above, do not assume a clean history where none exists — document the visibility gap itself as a finding and escalate to a more senior reviewer if the renewal value warrants it.
- At close-out, the renewal signature deadline creates pressure to report corrected figures without retesting them. Handling: the audit checklist's final step — correct, then retest — is not optional under deadline pressure; an uncorroborated correction is still an open finding.
None of these issues requires a different audit method. They are the ordinary cost of a workbook that outlived the person who understood it, which is exactly the population a proportionate, evidence-based audit programme is built to handle.
How should findings and limitations be reported?
For each finding, record the affected cell or process, evidence, risk, cause, owner, agreed correction, due date, and retest result. Separate confirmed errors from design improvements. A clean review means only that the defined work found no unresolved material exception; it does not guarantee the workbook contains no error.
This educational checklist is not legal, accounting, information-security, or external-audit advice. Scope and competence should match the workbook's risk, and source systems, macros, connections, privacy controls, or formal financial reporting may require specialist procedures.
Improve a new workbook with the project margin spreadsheet structure. If audit findings show recurring control burden, use the spreadsheet replacement framework. Managed Margin offers a structured but still manually updated workflow; it does not integrate external source systems or guarantee data accuracy.
Sources and methodology
- ICAEW, How to Review a Spreadsheet. Used for the purpose-and-risk-first sequence, structural, data, analytical, formula, and documentation review stages.
- ICAEW, Twenty Principles for Good Spreadsheet Practice. Used for proportional testing, peer review, checks, version control, and access.
- Microsoft Support, Detect formula errors in Excel, Show Changes, data validation, and worksheet protection. Used for feature capabilities and limitations.
- The audit programme, checklist, and worked example are original educational material published by Managed Margin on 24 August 2026.