Project Profitability and WIP Tracker

Project businesses often learn a job lost money only after the final invoice. Billing ahead of the work hides a problem, and work done but not billed hides an asset. This tracker shows for every project where it stands at your report date: what it has earned, what it has billed, what it will make and which projects need attention.

What is inside

• Settings: report date and the flag thresholds for margin erosion, overrun, underbilling and the stress percentage.
• Projects: one row per project with contract value, approved variations, budget by cost category, percent complete method and estimate to complete.
• Costs and Billing: paste project costs (date, project, category, amount) and invoices (date, amount, cash received). Rows dated after the report date are ignored, so you can roll back to any month end.
• WIP: for each project the percent complete, earned revenue, over or under billing, forecast profit and margin, profit recognised to date, loss provision and a stress test.
• CostControl: cost against budget by category for each project.
• Portfolio and Dashboard (three native charts): totals, flag counts, the five lowest margins and monthly cost and billing.
• Checks: unregistered projects, bad dates, receipts above invoices and ties between each view and the logs.

How it is deeper than the cheaper Quotation Profitability and Job Costing Workbook: that product costs a job before and after. This tracker runs the whole portfolio at any month end with over and under billing and loss provisions. It is a portfolio tool, not a per-project earned value app.

Facts and settings: it uses cost-to-cost percent complete or a percentage you enter. Variations must be approved before you include them. It does not replace your revenue recognition policy under IFRS 15 or your auditor's view. Retention, advances, claims and multi-currency are not modelled.

What it is NOT: it is a calculation tool, not accounting, tax or legal advice. It is not connected to your bank or accounting system, it is not an FTA-approved system and it is not an e-invoicing tool (e-invoicing is on the Zeyto roadmap only). Where a rule matters it is a setting you enter, with a note to confirm it with the FTA, your accountant or your auditor.

Two workbooks: an EXAMPLE copy filled with invented data so you can see every sheet working, and a BLANK copy with the same formulas ready for your own data. Formulas only: no macros, no volatile functions, no add-ins. Input cells are coloured, formula sheets are protected without a password, and a Checks sheet turns green only when every list ties out.

How it was checked: recalculated with an independent Python formula engine and 522 outputs (every project line, category view, portfolio total, flag count and chart series for 12 invented projects) were compared with a separate Python model, with no difference. There is no Excel or LibreOffice on the build machine, so it was not opened in them and the charts were not viewed on screen.

Limits: the estimate to complete is your judgement and drives the answer, so the tracker is only as good as that estimate.