How to structure a project margin spreadsheet

Which sheets should the workbook contain?

ICAEW's Twenty Principles recommends a clear flow of inputs, processes, and outputs; a workbook overview with owner, purpose, and version; simple formulas; separate fixed values; built-in checks; peer review; version control; and risk-appropriate protection. The following structure applies those principles to a project-margin use case—see the project margin management guide for the definition and workflow it is meant to support.

All seven sheets are the minimum ICAEW-aligned structure, not a menu to pick from — each protects against a specific failure (no Read_Me means the next person can't interpret the model; no Change_Log means a variance can't be traced to its cause). Keep them as separate logical sections even if a small model visually combines some of them on one screen. Avoid copying the same rate or project price into multiple sheets: ICAEW specifically recommends entering inputs once and referring to calculated results rather than recomputing them in several places.

Which fields inside each sheet are mandatory, and which are optional?

Within that minimum structure, not every column carries equal weight. Some fields are load-bearing — remove them and a downstream check or formula breaks. Others are genuinely optional and only earn their place once the project has the complexity that needs them.

When should you extend beyond this minimum structure?

The seven-sheet structure above is deliberately the floor, not the ceiling. Add a sheet or field only when a specific condition makes the minimum insufficient — adding structure "just in case" is exactly the kind of unnecessary complexity ICAEW's principles warn against.

• Multiple currencies: add a dedicated exchange-rate sheet with an effective date per rate, and reference it from 01_Assumptions, once any cost or revenue line is booked in a currency other than the reporting currency.
• Multiple purchase orders on one project: add a PO_ID field to 02_Baseline, 03_Actuals, and 04_Calculations, and a rollup section in 05_Summary, once the project has more than one PO — see project margin rollups for the weighted-aggregation rule this needs to follow.
• Subcontractor or partner cost distinct from internal labour: split 01_Assumptions and 03_Actuals into internal-labour and partner-cost sections once a meaningful share of delivery cost comes from outside the firm, so the two cost types don't get blended under one rate.
• More than a handful of reviewers: add role-based tab protection and a named-reviewer field to 05_Summary once the workbook is reviewed by people who did not build it — at that point, "the creator remembers the caveats" stops being a safe assumption.

If none of these conditions apply, resist adding the sheet. A sparser, fully-understood workbook is safer than a comprehensive one nobody can fully verify.

What columns and formulas does a margin model need?

Use stable IDs rather than free-typed names as join keys. An actuals table might contain Project_ID, Week_Start, Resource_ID, Hours, Cost_Category, Amount, Source_Reference, Entered_By, and Entered_Date. A rate table should include Resource_ID or Role_ID, Effective_From, Effective_To, Hourly_Cost, and approval evidence.

Place the approved revenue and policy rates in labelled input cells or structured tables, not inside formulas. Use Excel Tables or ranges that expand safely. Microsoft data validation can restrict directly typed inputs to allowed values or ranges; it is a useful preventive control, but Microsoft notes that copied data and changes in referenced cells can bypass some validation behaviour. Validation is not a complete accuracy test.

Worked example: a small controlled model

A project has approved revenue of USD 80,000. The baseline uses 800 hours at an average cost of USD 55 and USD 8,000 of other included cost. Actuals to the current cut-off contain 520 hours at a supported average cost of USD 58 and USD 5,500 of other included cost.

The high actual-to-date margin is not evidence that the project will finish at 55.43%. Future effort is absent. The summary should display this limitation beside the result, plus planned and actual hours through the same period and completion context.

Building the model on a real systems-integration project

Consider an illustrative case: a 40-person IT consultancy uses this exact workbook structure – Read_Me through Change_Log – to track one fixed-price systems-integration project from kickoff to close. The structure only helps if the discipline behind it holds up once the project gets busy.

• At kickoff: under deadline pressure, the team is tempted to skip 00_Read_Me and start entering numbers straight into 04_Calculations. Set the owner, version, and review date first, per the sheet-purpose table above, so the workbook stays interpretable by someone other than its creator once the original analyst moves to another engagement.
• Mid-delivery, first actuals cycle: a project manager, in a hurry, types the week's hours directly into 04_Calculations instead of 03_Actuals to save a step. This breaks the input-once principle from ICAEW's guidance. Route every entry through 03_Actuals only and let 04_Calculations reference it, or the "detail sums to the displayed total" check will start failing quietly.
• At a scope change: the client approves an added integration, and someone overwrites the numbers in 02_Baseline instead of logging the change. Preserve the original baseline and record the change in 06_Change_Log with the old value, new value, reason, and approver; that log is what lets a later reviewer trace the resulting margin shift, the kind of driver covered in tracing project margin variance.
• At project close: the actual-to-date margin on 05_Summary looks strong because a large invoice-only cost has not been entered yet. Display the same caution used in the worked example above – an actual-to-date result is not a final margin – directly beside the number rather than leaving it to be inferred.

Controls, testing, and limitations

Build these checks into the workbook

• Project revenue is present and positive before calculating a percentage.
• Every actual resource and cost category maps to one valid master-data row.
• Every hours row finds exactly one applicable effective rate.
• Detail totals reconcile to the summary; the check result must equal zero.
• No formula column contains an unexpected constant or different formula.
• The data cut-off, baseline version, and unresolved errors are visible on the summary.

Test zero, blank, negative, duplicate, out-of-range, and boundary-date inputs. Protect formula cells against accidental edits, while remembering that Microsoft says worksheet protection is not a security feature. Sensitive rate access requires appropriate file and access controls, not hidden columns or a sheet password alone.

As coordination and access needs grow, use the spreadsheet replacement decision framework. For a workbook already in use, follow the project margin spreadsheet audit checklist. If a structured application becomes appropriate, review how Managed Margin works; it still depends on manually supplied actuals and does not provide external integrations or predictive forecasts.

Sources and methodology

• ICAEW, Twenty Principles for Good Spreadsheet Practice. Used for risk-proportionate design, documentation, input-process-output separation, simple formulas, checks, peer review, version control, and access principles.
• Microsoft Support, More on data validation and Protect a worksheet. Used for feature capabilities and limitations.
• The workbook structure and examples are original educational material published by Managed Margin on 24 August 2026.