Construction WIP Report: 7 Proven Steps to Automate It from Sage Intacct
Every contractor over about $40M in revenue runs the same play at month-end. The close finishes. Then somebody — usually whoever owns the close — starts chasing project managers for cost-to-complete numbers so the construction WIP report can be rebuilt in Excel, one job at a time.
Three PMs don’t reply. Two send figures that haven’t moved since March. The file gets rebuilt anyway, because the bank wants it, the surety wants it, and the CPA will want it at year end. By the time it’s signed off, it describes a month that ended a week and a half ago.
None of that is a competence problem. Every number in that report already exists inside your ERP. The problem is that nothing assembles them, so a human does it by hand, every month, forever.
This guide walks through the seven steps to automate a construction WIP report from Sage Intacct — the accounting decisions that have to be settled first, the data model underneath it, the reconciliation that makes it trustworthy, and the views that make it useful. Every figure shown is synthetic.
What a construction WIP report actually is
Strip away the formatting and a WIP schedule answers one question per job: how much of this contract have we earned, and how much have we billed? The gap between those two numbers is your billing position.
earned revenue = % complete × contract value
over / (under) billing = billed to date − earned revenue
Overbilled means you’ve invoiced ahead of the work — the owner is financing you, and that surplus is a liability, not profit. Underbilled means the reverse: you’ve performed work you haven’t invoiced, and you’re financing the owner out of your own working capital.
Neither is automatically bad. Both are dangerous when nobody sees them until closeout.
Step 1 — Settle the accounting questions before you build anything
Most failed construction dashboards fail here, not in Power BI. Before a single visual gets designed, four questions need written answers from your accounting lead:
- Which account ranges constitute revenue and cost of sales? Not “4000 and up” — the exact ranges, including where overhead stops and G&A begins.
- Which ledger is authoritative? Sage Intacct can carry multiple books. Sub-ledger books used for commitment tracking will inflate your totals if you sum them alongside the accrual book.
- Which date governs the period? The date a transaction was entered and the date it posted are different fields and produce different months. Pick one and apply it everywhere.
- How is percent complete calculated? Cost-to-cost is the default, but some contractors use units installed or labour hours. Mixing methods across jobs makes the portfolio number meaningless.
These sound like small definitional details. They are the single largest source of dashboards that disagree with the close.
Step 2 — Fix the estimate problem, because it’s the real bottleneck
Contract value, billings and cost-to-date all live in the ERP and can be extracted automatically. Estimated total cost cannot. It’s a judgement made by a project manager about work that hasn’t happened yet, and no integration will produce it.
That single field is why the WIP report is late every month. Automating everything except the estimate collection means automating everything except the bottleneck.
Practical options, in ascending order of reliability:
- A structured monthly form — SharePoint list or similar, one row per active job, pre-populated with last month’s figure and current cost-to-date so the PM is editing rather than starting blank.
- A statistical journal import — the estimate posted into Intacct as a statistical account, so it lives in the ERP with the rest of the job record.
- Cost-code-level forecasting — the most accurate and the hardest to sustain. Worth it only if your PMs already work this way.
Whichever you pick, the report should show when each estimate was last updated. An estimate that hasn’t moved in three months on a job that’s burned $900K since is not an estimate, and the report should say so.
Step 3 — Model it as a warehouse, not a query
A report writer pointed straight at Intacct will render quickly and reconcile badly. The construction WIP report needs a dimensional model underneath it for three reasons specific to this domain:
- Monthly snapshots. Margin fade is the difference between the margin you bid and the margin you’re now projecting. You cannot compute it without a permanent record of what each job looked like at each prior month-end. Snapshots must be additive and never overwritten.
- Slowly-changing dimensions. Jobs get reassigned between PMs. Project managers change regions. If the dimension is overwritten, last March’s report changes every time somebody moves.
- Auditability. When the surety asks why a number moved, you need row counts and timestamps for every load, not a shrug.
Reference architecture
Step 4 — Reconcile to the general ledger before you build a single visual
This is the step almost everybody skips, and it is the reason so many contractors have a dashboard nobody opens.
A construction WIP report that disagrees with the close by even a small amount will be abandoned. Not argued with — abandoned. Leadership stops opening it after the second time, and the Excel file comes back.
The reconciliation is a defined set of comparisons between two sources that should agree:
| Test | What it compares | What it usually catches |
|---|---|---|
| Ledger tie-out | Revenue less cost of sales less overhead, from transaction detail, against reported net income | Wrong account ranges, wrong book, wrong date field |
| Transaction integrity | Source records against loaded records | Duplicates from connector behaviour, unreversed prior-period entries |
| WIP cost to GL cost | Cost-to-date per job against GL cost of sales per job | Costs posted to the wrong job or no job at all |
| WIP revenue to P&L | Earned revenue on the schedule against recognised revenue | Period assignment drift |
| Contract value integrity | WIP contract against project master against executed change orders | Individual jobs carrying a stale contract value |
| Estimate integrity | Estimated total cost against cost-to-date and prior months | Placeholders, blanks, estimates below cost incurred |
| Billing position roll | Over/under billing against the balance sheet asset and liability accounts | Journal entries that were never posted back |
Run these before the first visual is designed, and get your accounting lead to sign off on the result. It’s the slower path. It’s also the only one that produces a report people keep using.
We do this as a standalone, fixed-fee engagement — the full ten-test methodology and a worked example are on our WIP reconciliation audit page.
Step 5 — Build the schedule view first
The WIP schedule itself is the centrepiece. Everything else is a supporting view.
Synthetic data
| Job | Contract | Cost to date | Est. total cost | % Compl. | Earned | Billed | Over / (under) | GP % |
|---|---|---|---|---|---|---|---|---|
| P-1147 Riverside MOB | $12,400,000 | $8,190,000 | $10,500,000 | 78.0% | $9,672,000 | $10,100,000 | $428,000 | 15.3% |
| P-1092 Northgate DC | $8,750,000 | $7,050,000 | $7,500,000 | 94.0% | $8,225,000 | $7,410,000 | ($815,000) | 14.3% |
| P-1216 Cedar Park Civic | $6,200,000 | $2,542,000 | $6,100,000 | 41.7% | $2,585,000 | $2,480,000 | ($105,000) | 1.6% |
| P-1058 Warrenton Plant | $4,900,000 | $2,772,000 | $4,200,000 | 66.0% | $3,234,000 | $3,390,000 | $156,000 | 14.3% |
| P-1263 Belmont Academy | $3,150,000 | $621,000 | $2,700,000 | 23.0% | $724,500 | $690,000 | ($34,500) | 14.3% |
Step 6 — Add margin fade, because it’s the earliest warning you get
Margin fade is the gap between the margin you bid and the margin you’re currently projecting. It is the most useful number on the schedule and the one most contractors cannot produce, because computing it requires the historical snapshots from Step 3.
Synthetic data
Margin projected now
Step 7 — Automate the refresh, then leave the report alone
The point of automation isn’t speed for its own sake. It’s that a report produced by a pipeline is reproducible — the same inputs always produce the same output, and when they don’t, an alert fires.
A workable cadence for most contractors:
| Data | Cadence | Why |
|---|---|---|
| Cash and AR | Daily | Collections decisions are made daily |
| Job cost and commitments | Weekly | Matches how quickly cost actually posts |
| Full GL and WIP snapshot | Monthly, after close | The snapshot must reflect a closed period, not a moving one |
| PM cost estimates | Monthly, before close | The gating input — everything waits on it |
The KPI map: what belongs on a construction WIP report
| KPI | The question it answers | Owner | Source |
|---|---|---|---|
| Percent complete | How far along is this job, by cost? | Accounting Director | ERP job cost + PM estimate |
| Earned revenue | How much of the contract have we actually earned? | Accounting Director | Derived |
| Over / under billing | Are we financing the owner, or are they financing us? | Finance Director | Billings + derived earned revenue |
| Projected gross margin | Is the margin we bid still there? | Project Manager | Contract less estimated total cost |
| Margin fade | How much margin has eroded since bid? | Finance Director | Monthly snapshots |
| Revenue backlog | How much work remains to be billed? | Executive team | Contract less billings |
| Change order aging | How much margin depends on unsigned change orders? | Project Controls | Change order register |
| Estimate age | Which jobs are running on stale numbers? | Accounting Director | Estimate input timestamps |
Four ways construction WIP reports go wrong
1. The estimate is stale and nobody flags it
The single most common failure. A job carries an estimate from three months ago while cost keeps posting against it. Percent complete climbs, earned revenue climbs, and margin looks fine right up until it doesn’t. Surface estimate age as a column and the problem becomes visible.
2. Contract values disagree across systems
The WIP schedule says one number, the project master says another, and the executed change order log says a third. All three are “right” in their own system. Reconcile them or the whole schedule is built on sand.
3. Percent complete exceeds 100%
Cost-to-date has passed estimated total cost. Arithmetically this means the job is over budget and the estimate was never revised. It should be an alert, not a row buried on page four.
4. The schedule doesn’t tie to the income statement
Earned revenue across all jobs should reconcile to recognised revenue for the period. When it doesn’t, the cause is usually period assignment — the entry date and the posted date pulling transactions into different months.
Implementation roadmap
| Weeks 1–2 | Weeks 3–5 | Weeks 6–8 | Weeks 9–12 | |
|---|---|---|---|---|
| Focus | Definitions and reconciliation | Warehouse and pipeline | Model and measures | Views, security, handover |
| Tasks | Settle account ranges, book, date field and percent-complete method. Run the reconciliation tests. Document every definitional decision. | Build the star schema. Stand up incremental extraction and orchestration. Establish the monthly snapshot process. | Write the measures for percent complete, earned revenue, billing position, margin and fade. Validate against the signed WIP. | Build the schedule view, executive summary and drill-through. Configure row-level security. Train finance and hand over documentation. |
| Deliverable | Findings memo and data source map | Warehouse in your tenant | Reconciled semantic model | Live report + documentation |
Find out where your WIP schedule disagrees with your GL
Send your two most recent WIP schedules and the matching income statement. In thirty minutes we’ll walk you through what we find — internal math that doesn’t hold, estimates that stopped moving, jobs quietly eroding. No system access, no NDA, no pitch.
Book a Free WIP Teardown →
You get a written summary within 48 hours, whether or not you engage.
Frequently asked questions
Can a construction WIP report be fully automated?
Almost. Contract value, cost-to-date and billings extract automatically from the ERP. Estimated total cost is a project manager’s judgement and can’t be. What automation removes is the assembly, the formulas and the reconciliation — which is where the days go.
How often should a WIP schedule be produced?
The formal snapshot is monthly, after close, because it feeds revenue recognition. But the underlying view can refresh continuously, so leadership sees current billing position and margin between closes rather than a picture that’s two weeks old.
Does Sage Intacct produce a WIP schedule natively?
Sage has added native WIP functionality that automates the monthly statement and creates the over and under billing entries. If producing the statement is your bottleneck, start there. What it doesn’t do is put WIP alongside cash, backlog, change order aging and margin by project manager on one screen with drill-through to the transaction — which is a different problem.
What’s the difference between margin fade and a cost overrun?
A cost overrun is actual cost exceeding budget. Margin fade is the projected final margin declining from what was bid — which can happen from cost creep, scope absorbed without a change order, or an estimate finally being corrected. Fade shows up earlier, which is what makes it useful.
What if we’re on Vista or Sage 300 CRE instead of Intacct?
The construction accounting is identical and the model is the same. Only the extraction layer changes. The definitional questions in Step 1 apply regardless of ERP.
How long does it take to build?
Eight to twelve weeks for a full build, with a working report on real data at roughly the halfway point. The reconciliation in Step 4 runs first and takes about two weeks on its own — see the reconciliation audit for what that involves.
Where to start
If your construction WIP report takes more than a day to produce, the bottleneck is almost certainly estimate collection, not reporting. Fix that first and much of the rest becomes tractable.
If it takes a day but nobody trusts it, the problem is reconciliation, and no amount of dashboard work will solve it.
Either way, the diagnosis is cheap and the guessing is expensive. More on how we approach this on our Sage Intacct Power BI page.
