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.
Terms used in this guide. cost: the expense included in the stated project model; margin: revenue left after the included costs; margin percentage = that amount / revenue × 100; baseline: the approved original plan kept for comparison.
In plain words
A project margin spreadsheet is only useful if its numbers are right. To audit one, you agree what it is for, check the structure, trace some of the inputs back to their source, recalculate the key formulas yourself and look at the results with a critical eye. It is a check, not a rebuild.
Who this helps: Finance analysts, project managers and team leads who inherit or rely on a margin spreadsheet.
Questions this guide answers:
- How do you check a project margin spreadsheet for errors?
- What should you review in an Excel margin model?
- How do you test a spreadsheet formula?
Margin words: gross, contribution, managed margin, budget, forecast and the roles named here are defined once in the margin vocabulary.
Start with one wrong rate
A finance analyst checks five rows of a project tracker. A consultant's cost rate changed from USD 70 to USD 75 on 10 August, but the workbook applies the newer USD 75 to the earlier week as well: 40 hours × USD 5 = USD 200 overstated in one row. Across a year of rows and several people it becomes a real number. An audit exists to find that kind of error before a decision relies on the total.
Spreadsheet words you will meet in this audit
| Word | Plain meaning | Why an auditor cares |
|---|---|---|
| Circular reference | A formula that depends on its own result | It can hide wrong totals or make the workbook unstable |
| Watch Window | An Excel panel that shows chosen cells while you change others | It lets a reviewer see a total react to a test input |
| Reasonableness test | A quick check that a result is the right size (for example, one person’s hours in a week cannot exceed 168; a role or team total can legitimately be higher, and many policies set a much lower limit) | It catches errors that every formula still “calculates” |
| Boundary date | The date a rate or cost starts or stops applying | Rates that change on 10 August must apply from 10 August |
| Hidden sheet or name | A sheet or label that is not visible by default | Logic can sit where nobody looks |
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-labor 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 program
| 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 authorized 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. Behavior | 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 authorized 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 labor 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.
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 program 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. |
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 quote-to-delivery workflow with versioned rates, a finalized baseline and approved time; it does not guarantee data accuracy, and actuals are only as good as the entries behind them.
At the office: Grace finds a rate error
An illustrative story with invented figures, to show how the idea works in a real office.
Grace, a finance analyst, is asked to review a project tracker before a contract renewal. She picks five rows and traces each rate back to the approved rate card.
Four match. The fifth uses USD 55 an hour for a role whose approved rate is USD 65. On 300 hours that is USD 3,000 of cost the sheet leaves out, which makes the project look a little more profitable than it is.
She writes the finding down with the cell reference, the effect and the fix, and asks the owner to correct it. She also notes that the cell was typed in by hand, not looked up, which is why the error happened.
Who audits a margin spreadsheet, and when?
| Step | Owner | Checkpoint |
|---|---|---|
| Agree purpose and risk | Finance lead | What decisions the workbook supports, and who built it |
| Test the structure and data | Independent reviewer (not the author) | Inputs, formulas, links, hidden sheets and a sample of source data |
| Report findings | Reviewer | Cell, evidence, risk, cause, owner, correction, due date |
| Retest | Reviewer | Each correction is checked again |
Frequently asked questions
Which Excel controls help?
Data validation, protected cells, change tracking and error checking help prevent and detect mistakes, but they do not replace review. See the Microsoft documentation listed under Sources and methodology.
How often should a sheet be audited?
When it changes materially, when ownership changes and on a regular cycle proportionate to the decisions it drives.
What should an audit report contain?
Scope, what was tested, findings with severity, limitations, corrective actions and owners.
How do you audit a project margin spreadsheet?
Agree its purpose and risk, map inputs, calculations and outputs, test a sample of source data and formulas, and write findings with the cell, evidence, risk, owner and a retest date. The reviewer should not be the author.
- Project Margin Management Software: How to Evaluate It
- How to Calculate Project Margin (Formula + Example)
Continue the sequence: guides to read next
- How to Structure a Project Margin Spreadsheet: how to structure the sheet you are auditing
- Replace a Project Margin Spreadsheet: When and How: when an audit points to replacement
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 program, checklist, and worked example are original educational material published by Managed Margin on 24 August 2026.