A project margin spreadsheet should separate documentation, controlled assumptions, the approved baseline, actuals, calculations, outputs, and change history. Enter each input once, reference it consistently, use simple formulas, and add visible checks. Assign an owner, version, cut-off date, and reviewer so the workbook remains understandable and its results can be reproduced.
Terms used in this guide. fee: the amount charged to the client; 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 good project margin spreadsheet keeps its parts separate: a notes sheet, the assumptions, the approved plan, the actuals, the calculations and the results. Each input is typed once and used everywhere. Simple formulas and visible checks make it easy for someone else to trust.
Who this helps: Project managers, analysts and small-firm owners who track margin in Excel or Google Sheets.
Questions this guide answers:
- How do you build a project margin spreadsheet?
- What columns should a margin tracker have?
- Is there a project margin Excel template?
Margin words: gross, contribution, managed margin, budget, forecast and the roles named here are defined once in the margin vocabulary.
Start with the problem a template solves
A project coordinator needs four facts in one place: the fee sold, the hours planned, the hours actually worked and the margin. When they live in four separate files they disagree. This guide describes a structure of linked sheets that keeps them together. It is a design you can build in a spreadsheet, not a downloadable workbook.
Spreadsheet words you will meet in this guide
| Word | Plain meaning | Example |
|---|---|---|
| Stable ID | A code that never changes for a project, person or role | P-0042 instead of “Portal rebuild”, which someone will retype differently |
| Join key | The ID two sheets share so rows can be matched | Project_ID appears in both the plan and the actuals sheets |
| Lookup | A formula that finds a value by its ID | Fetch the cost rate for Role_ID R-07 |
| Excel Table | A named block of rows that grows automatically | Actuals can be added without rewriting formulas |
| Master data | The one list of projects, people and roles all sheets use | A single Roles list with cost rates and dates |
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.
| Sheet | Purpose | Minimum content | Required? |
|---|---|---|---|
| 00_Read_Me | Explain the workbook | Owner, purpose, scope, margin definition, version, review date, color conventions. | Mandatory |
| 01_Assumptions | Hold controlled inputs | Resource IDs, roles, effective cost rates, overhead policy, cost categories. | Mandatory |
| 02_Baseline | Preserve the approved plan | Project price, planned hours by resource and period, planned non-labor costs, approval evidence. | Mandatory |
| 03_Actuals | Record delivery evidence | Period, resource ID, hours, supported cost, source, entry date, status. | Mandatory |
| 04_Calculations | Apply formulas once | Planned and actual cost, margin amount, margin rate, variance, checks. | Mandatory |
| 05_Summary | Present review outputs | Cut-off, baseline version, current results, material drivers, open decisions. | Mandatory |
| 06_Change_Log | Explain controlled changes | Date, author, cell or assumption, old value, new value, reason, approver. | Mandatory once any baseline can change — in practice, every live project |
These seven logical areas are an editorial design for this use case, not an ICAEW-mandated tab count. A small workbook may combine them while keeping inputs, calculations, outputs and change history understandable. Enter each input once and reference it consistently.
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.
| Sheet | Mandatory fields | Optional fields — add only if needed |
|---|---|---|
| 01_Assumptions | Resource ID, role, effective cost rate, effective-from date | Overhead policy (only if overhead is allocated at all), cost category (only if costs span more than one category), effective-to date (only once a rate has ever changed) |
| 02_Baseline | Project price, planned hours by resource and period, approval evidence | Planned non-labor cost breakdown by category (a single non-labor total is enough on a small project) |
| 03_Actuals | Period, resource ID, hours, supported cost, entry date | Source reference and entry status (recommended, not really optional): retain these even with one editor so the reviewer can trace inputs and distinguish draft from approved actuals. |
| 04_Calculations | Planned cost, actual cost, margin amount, margin %, at least one reconciliation check | A separate variance-by-driver breakdown (worth adding once variance investigation becomes a recurring review step, not before) |
| 05_Summary | Cut-off date, baseline version, current margin result | Material-drivers commentary and an open-decisions list (earn their place once the workbook is reviewed by someone other than its builder) |
| 06_Change_Log | Date, changed item, old value, new value, approver | A stated reason (required for any baseline change, recommended for the rest, not just for compliance — it is what lets a future reviewer distinguish a correction from a real scope change) |
When should you extend beyond this minimum structure?
Extend the proposed structure only when a specific reporting, control or data need justifies it. A named owner, traceable inputs and reproducible formulas matter more than a particular number of tabs.
- 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 labor: split 01_Assumptions and 03_Actuals into internal-labor 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.
- For more reviewers, document who may receive each dataset and assign a reviewer to the summary. Sheet protection can reduce accidental edits, but does not secure sensitive rates. Use appropriate file permissions or a system with tested access controls.
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.
Labor cost per row = hours x applicable cost rate
Margin amount = approved revenue - included costs
Margin % = margin amount / approved revenue x 100
Margin variance = current margin - baseline margin
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 behavior. Validation is not a complete accuracy test.
Worked example: a small controlled model
Assume a USD 80,000 full-project fee. Record recognized revenue and cost to the same review date separately from that contract value. With illustrative recognized revenue of USD 50,000 and included cost of USD 35,660, the actual-to-date margin amount is USD 14,340 and margin is 14,340 / 50,000 × 100 = 28.68%.
If estimated remaining cost is USD 20,000, forecast total cost becomes USD 55,660. The forecast margin amount is USD 24,340 and margin is 30.43% of the full USD 80,000 fee. State what is included in the remaining cost, including relevant role allocations and other charges.
The USD 44,340 difference between the full fee and cost recorded so far is headroom for remaining costs and profit. Calling that 55.43% actual margin would combine a full-project revenue amount with partial-project costs. The workbook should prevent that mismatch with separate baseline, actual-to-date and completion-forecast columns.
Building the model on an illustrative 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.
This page is a design specification, not a downloadable workbook, statutory-audit program, accounting policy, or guarantee of accuracy. Adapt it to your contract terms, cost policy, software version, and control environment, and obtain specialist review for a high-risk model.
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: pricing strategies, a finalized baseline, approved timesheets and a forecast at completion replace the separate tabs, though actuals still depend on the entries your team records.
- How to Calculate Project Margin (Formula + Example)
- What Is a Project Rate Card? How to Build One
- Project Margin Management Software: How to Evaluate It
At the office: Ben builds a tracker he can hand over
An illustrative story with invented figures, to show how the idea works in a real office.
Ben, a project coordinator, builds a margin tracker for his team. He makes five sheets: Notes, Assumptions (rates and fee, typed once), Plan, Actuals and Results.
On the Results sheet, planned margin is plan revenue minus plan cost, divided by plan revenue. Actual margin uses the same formula with actuals. A check cell turns red if the hours on the Actuals sheet do not add up to the timesheet total.
When Ben goes on leave a colleague opens the file, reads the Notes sheet and updates it without calling him. That is the test of a good template.
Who builds and maintains the workbook?
| Step | Owner | Checkpoint |
|---|---|---|
| Design the sheets | Finance analyst | Read me, assumptions, actuals, calculations, outputs and change log exist |
| Load the baseline | Project manager | Approved plan is stored once and not overwritten |
| Enter actuals | Team and project manager | IDs match master data; each hours row has a rate |
| Check and hand over | Reviewer | Built-in checks pass; version and owner are recorded |
Frequently asked questions
What columns does a margin model need?
Role, hours, rate, cost, fee and variance, with the cost basis and cut-off stated.
When should I stop using a spreadsheet?
When review effort, errors or access control exceed what the sheet can handle. See when to replace a margin spreadsheet.
What should a project margin spreadsheet contain?
Separate sheets for a read-me (owner, purpose, version), assumptions (rates, rules), actuals (hours and costs by ID), calculations, outputs and a change log, with built-in checks. Use stable IDs, not typed names, to join the sheets.
Continue the sequence: guides to read next
- How to Audit a Project Margin Spreadsheet: testing the sheet you build
- Replace a Project Margin Spreadsheet: When and How: knowing when it is time to move on
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.