A construction WIP (work in progress) report lists every open job with its contract value, estimated cost, cost to date, billings to date and the resulting over or under billing. Yes, AI can help a small contractor build one from job data, but only the arithmetic and tidying. The cost estimates, and the sign-off, stay with you and your accountant.
The reason the report exists is simple. Your invoices tell you what you asked customers to pay. They do not tell you how much of each job you have actually earned, or whether the job is still going to make the profit you priced. The WIP schedule puts those two things side by side, job by job, and shows which jobs are carrying other people's cash and which are quietly eating yours.
What each column of a WIP schedule means
Most WIP schedules share the same ten or so columns. Learn them once and every version you meet, from an accountant or a bank, becomes readable.
| Column | What it holds | Where the number comes from |
|---|---|---|
| Contract value | Original price plus approved change orders | Contract and signed variations |
| Estimated total cost | Cost to date plus your best estimate of cost to finish | Your estimate, updated each month |
| Cost to date | Materials, labour, subcontractors and equipment charged to the job | Accounting system, job cost report |
| Percent complete | Cost to date divided by estimated total cost | Calculated |
| Earned revenue | Percent complete times contract value | Calculated |
| Billed to date | Total invoiced so far, including retention withheld | Invoices or applications for payment |
| Over or under billing | Billed to date minus earned revenue | Calculated |
| Estimated gross profit | Contract value minus estimated total cost | Calculated |
| Gross profit to date | Earned revenue minus cost to date | Calculated |
| Backlog | Contract value minus earned revenue | Calculated |
Only three columns are typed in from outside: contract value, cost to date and billed to date. One is a judgement: estimated total cost. Everything else is a formula. That split matters for AI, because formulas are the part a spreadsheet does perfectly and a chat assistant occasionally gets wrong, while the judgement is the part nobody should outsource.
A three-job worked example with the sums shown
Consider an illustrative small contractor with three open jobs at month end. All figures are made up for the example.
Job A: a commercial fit-out
Contract value $400,000. Estimated total cost $320,000, so estimated gross profit is $80,000 (20 percent). Cost to date $160,000. Percent complete is $160,000 divided by $320,000, which is 50 percent. Earned revenue is 50 percent of $400,000, or $200,000. The firm has billed $230,000. So billings exceed earned revenue by $30,000: the job is overbilled. The firm is holding $30,000 of the customer's money for work not yet done. That can look like healthy cash flow, but it is a liability, and the work to earn it still has to be paid for.
The Complete Excel Course with AI Integration
From formulas and pivot tables to Power Query, DAX, VBA and AI with Copilot, ChatGPT and Claude — hands-on from the first lesson.
By Sagnik Bhattacharya and Virginia Thorn, Primetime Emmy Award winner
Buy nowJob B: a school-holiday refurbishment
Contract value $250,000. Estimated total cost $200,000. Cost to date $150,000, so the job is 75 percent complete. Earned revenue is $187,500. Billed to date is $160,000. Earned minus billed is $27,500, so the job is underbilled. The firm has done the work and not yet asked to be paid for it. This is the dangerous side for cash: you have earned $187,500 but asked for only $160,000, and if 5 percent retention is held on those invoices, cash received is about $152,000 against $150,000 spent. The $27,500 gap may be a late application or a dispute, and either way it is your money funding the customer's job. A construction cash flow forecast shows when that gap starts to hurt.
Job C: a small extension that is slipping
Contract value $90,000. The original cost estimate was $60,000, giving $30,000 profit (33 percent). Now the project manager has revised the estimate to $78,000 after rework. Cost to date is $52,000. Percent complete is $52,000 divided by $78,000, which is 66.7 percent. Earned revenue is $60,000. Billed to date is $72,000, so the job is overbilled by $12,000. Estimated gross profit has faded from $30,000 to $12,000 (13.3 percent). This is the line the owner needs to see, and it would not show on the invoice list at all.
The totals
Billed to date across the three jobs is $462,000. Earned revenue is $200,000 plus $187,500 plus $60,000, or $447,500. Overbillings are $30,000 plus $12,000, or $42,000. Underbillings are $27,500. The net position is $14,500 overbilled. A bank reading only the net figure might think things are fine, so good schedules show over and under billings separately. Netting them hides that Job B has $27,500 of work nobody has paid for yet.
| Job | Contract | Est. cost | Cost to date | % complete | Earned | Billed | Over / (under) |
|---|---|---|---|---|---|---|---|
| A | $400,000 | $320,000 | $160,000 | 50.0% | $200,000 | $230,000 | $30,000 |
| B | $250,000 | $200,000 | $150,000 | 75.0% | $187,500 | $160,000 | ($27,500) |
| C | $90,000 | $78,000 | $52,000 | 66.7% | $60,000 | $72,000 | $12,000 |
Why the estimate to complete is the number that matters
Look at Job C again. If the project manager had left the estimate at $60,000, percent complete would be 86.7 percent, earned revenue $78,000, and the job would show as underbilled by $6,000 with a healthy $30,000 profit. Changing one input flipped the job from "fine" to "faded and overbilled". The formulas did not change. The estimate did.
This is why accountants and sureties ask who prepared the estimates and when they were last revised. Cost-to-cost percent complete is only as good as the forecast of cost to finish. Two habits protect you. Ask each project manager for a short written reason whenever the estimate moves by more than, say, five percent. And look hard at any job that sits at 90 percent complete for several months, because it often means the last 10 percent is costing more than planned. Catching that earlier is the point of weekly early-warning flags for cost and delay, which feed straight into a better estimate to complete.
Where AI genuinely helps
Building a WIP schedule is a data-wrangling job. The numbers live in three places: the accounting system (cost to date), the invoicing records (billed to date) and the contract file (contract value and variations). AI helps most with the joining and tidying, and with explaining the result.
Cleaning and matching the exports
Job names rarely match across systems. One export says "Maple St extension", another says "MAPLE STREET EXT", a third uses job number 1042. Give the assistant the two exports and ask it to propose a matching table, then check the matches yourself. This is the same kind of work described in cleaning messy data, and it saves an hour or two a month on a busy firm.
Building the spreadsheet
Ask ChatGPT or Claude to write the formulas for a WIP sheet rather than calculate the figures itself. A formula in a spreadsheet gives the same answer every month. A model doing mental arithmetic on forty rows does not. Here is a prompt that works.
I run a small contracting firm. Build me an Excel WIP schedule with one row per job. Input columns (blue text): Job, Contract value, Approved change orders, Estimated cost to complete, Cost to date, Billed to date.
Calculated columns (black text): Revised contract value, Estimated total cost, Percent complete (cost to date / estimated total cost, capped at 100%), Earned revenue, Over or (under) billing, Estimated gross profit, Gross profit margin, Backlog.
Give me the exact formula for row 2 in each calculated column, a totals row that sums over and under billings separately, and a conditional format that highlights any job whose margin fell more than 5 points since last month. Guard against divide-by-zero.Check every column letter in what comes back. In an illustrative run, the assistant returned =IF(F2=0,0,MIN(1,E2/F2)) for percent complete. With the inputs in the order given (cost to date in E, billed to date in F, revised contract value in G, estimated total cost in H), that divides by billed to date, which is wrong. The corrected formula is =IF(H2=0,0,MIN(1,E2/H2)), with =E2+D2 in H. The totals row was right: two SUMIF formulas on the over or under column, one for positive values and one for negative, keep the two sides apart. Paste the formulas into your own file and test them with the three-job example above. If your sheet gives $200,000, $187,500 and $60,000 for earned revenue, it works. For more on getting an assistant to build workbook formulas, see the ChatGPT Excel guide.
Explaining movement from last month
Once you have two months of schedules, give both to the assistant and ask: "Which jobs changed margin by more than 3 points, and what moved: contract value, estimated cost, or cost to date?" That turns a table of 30 rows into five sentences you can act on. Ask it to show its working so you can check the three or four that matter.
Drafting the notes that go with the schedule
Banks and sureties usually want a line of explanation for any job with a big swing. Give the assistant your project manager's notes and ask for a two-sentence commentary per flagged job, in a neutral tone. A fair draft reads: "Estimated cost increased by $18,000 following rework to the roof structure; the variation has been submitted but not yet approved, so it is excluded from contract value." Edit it. If the facts in the sentence are not in your notes, delete them.
Where AI must not be trusted
Four places call for a human, and the report should say who did each.
- The estimate to complete. Only someone who knows the job can say whether 40 percent of the cost is really left. A model will take whatever number you supply and produce a tidy result.
- Whether a change order counts. Unapproved variations usually should not be in contract value yet. A model cannot tell approved from hoped-for unless you label them.
- Revenue recognition policy. Cost-to-cost is a common method, but your accountant decides what applies to your accounts and your lender.
- Anything signed or submitted. A schedule sent to a bank or surety is a statement about your business. Treat the AI draft as a working paper.
A realistic mistake: the schedule that looked great
Here is an illustrative case. A firm built a WIP sheet with an assistant's help and found its total estimated gross profit was 4 percent higher than its accountant's figure. The cause was a single row: a job with a $40,000 materials purchase invoiced by a supplier but not yet posted, so it was missing from cost to date. The project manager's cost to complete already treated those materials as bought, so the $40,000 dropped out of estimated total cost as well. Forecast profit on that job came out $40,000 too high, and percent complete too low, so the job looked both more profitable and less far along than it was.
The fix is a cut-off check before you start. List supplier invoices and subcontractor applications received after month end that relate to work done before it, and make sure they are included. Ask the assistant to compare the cost-to-date total against the accounting system's trial balance for job costs. If they differ, stop and find out why.
Handling retainage and stored materials
Two items often distort the schedule. Retention (money the customer holds back from each payment, called retainage in some contracts) is normally included in billed to date, because you have invoiced it, even though you have not received it. Show it as a separate column so receivables can be reconciled. Materials stored on site or off site that you have bought but not yet installed raise cost to date without raising progress, which inflates percent complete. Some schedules take uninstalled materials out of the cost-to-cost calculation. Ask your accountant which treatment applies, then tell the assistant in the prompt so the formulas follow it.
A short example. Job D has a $30,000 window delivery sitting in the yard. Cost to date includes it. If total estimated cost is $300,000 and cost to date is $120,000, percent complete reads 40 percent. Strip out the windows and it is $90,000 over $270,000 (the estimate net of the windows), which is 33.3 percent. On a $360,000 contract that is $144,000 earned versus $120,000, a $24,000 difference in earned revenue from one delivery. Whichever method you choose, apply it the same way every month.
A monthly routine that takes an afternoon
- Day 1 to 3 after month end: post all supplier and subcontractor invoices, then export the job cost report and the invoice list.
- Update estimates: each project manager confirms or changes cost to complete in writing, with a one-line reason for any movement.
- Load and match: paste the exports into the sheet, using the assistant to propose job matches, and confirm each one.
- Run the sheet and the checks: total cost agrees with the ledger, percent complete never above 100, no job with negative backlog, billed to date agrees with invoices.
- Read movements: list jobs where margin changed more than 3 points, and get an explanation for each.
- Review: the owner reads it first, then the accountant, before anything goes to a bank or surety agent.
Run this for three months before you rely on it. By the third month you will know your own typical swings and can tell a real problem from a timing blip.
What a bank or surety agent wants to see
Lenders and surety providers differ in what they ask for, so ask them what format and date they want. In general they look for four things. Whether you are chronically overbilled on many jobs, which can mean you are using customer money to fund running costs. Whether underbillings are growing, which suggests slow invoicing or disputes. Whether margins on current jobs match what you reported on past ones. And whether estimates are revised honestly, rather than held flat until a job collapses.
A clean schedule with commentary on every swing builds trust faster than a perfect-looking one with no explanations. Before you share, have your accountant or surety agent review it, because they know the format the recipient expects and will spot anything that needs a footnote. For the wider picture of how costs and billings flow in your accounting system, read AI in construction accounting, and for finding which jobs truly made money, job costing with AI.
Asking the assistant to audit a finished schedule
Once the sheet is built, use a second pass as a sanity check. Paste the table (job numbers only) and ask the assistant to act as a sceptical reviewer, not a builder. A prompt such as "Find rows where billed to date exceeds contract value, percent complete is above 95 percent with more than a month of work left, or margin is below 8 percent, and say what each might mean" gives you a short list to chase.
An illustrative response on the three-job example might read: "Job C: estimated margin is 13.3 percent, down from 33.3 percent in the original estimate; check whether the variation for rework has been priced. Job B: underbilled by $27,500 at 75 percent complete; confirm the next application has been submitted." Notice that the assistant is not deciding anything. It is pointing. Each pointer becomes one phone call to a project manager.
You can also test the logic with a deliberate error. Change one job's cost to date to a negative number, or put billed to date at twice the contract value, and see whether the assistant spots it. If it misses obvious errors in a test, it will miss subtle ones in a live schedule, and you should rely on the built-in checks in the sheet instead.
A sample commentary for a quiet job
Not every job needs a long note. For a job with no change, a line is enough: "On budget; 62 percent complete; next application due on the 28th." Keep a short list of standard phrases so the commentary stays consistent from month to month. Ask the assistant to draft them once, edit them to your voice, and reuse them. Consistent wording makes real changes stand out when you read the notes in a row.
What a small firm needs on day one
You do not need job-costing software to begin. A firm with six or eight open jobs can run this in a spreadsheet using the three exports, and add software later if the monthly routine proves valuable. The discipline matters more than the tool: a cost code on every purchase, an invoice for every progress claim, and a written estimate to complete each month.
Keep the data and the report private
A WIP schedule includes customer names, contract prices and margins. Check the plan you use before uploading it. Business plans from the main vendors do not train on your content by default, and consumer plans let you switch training off, but the safest route is to give the assistant job numbers rather than customer names. That also makes the sheet easier to share around the office.
If you would like help setting up a monthly routine like this around your own accounting system, that is the kind of thing an AI implementation consultation covers. The method above works fine without one: three exports, one sheet, one review, once a month.
WIP report questions from small contractors
How often should a small contractor update a WIP report?
Monthly is the usual rhythm, ideally within a week of month end once costs are posted. Update it more often on a job that is changing fast, such as one with large variations or a cost overrun. A stale WIP schedule is worse than none because it hides problems.
What is the difference between overbilling and underbilling?
Overbilling means you have invoiced more than the work you have earned so far, so you hold the customer's cash and owe them work. Underbilling means you have done more than you invoiced, so the customer owes you. Overbilling is a liability on the balance sheet, underbilling an asset.
Can AI calculate percent complete for me?
It can run the formula from cost to date and estimated total cost if you give it clean figures. It cannot judge whether your estimate to complete is realistic. That number comes from the project manager, and it drives every result in the report.
Who should review my WIP report before a bank sees it?
Your accountant, and your surety agent if you have bonding. They know how the lender or surety wants revenue recognised and will spot estimate changes that need explaining. Send the AI-built draft with its workings so they can check it quickly.
Further reads
- Simpro for Contractor Billing: A ChatGPT Check Before Each Invoice — A check before each invoice keeps billed to date accurate.
- Fergus for Builders: Job Costing and Progress Claims, With Fergus AI — Progress claims and job costing inside one trade tool.
- AI Driven Accounting Software for Contractors: What Job Costing Needs — What job costing needs from accounting software.
- Can AI Produce Accurate Estimates for a Small Building Firm? — Better estimates make percent complete more trustworthy.
- AI Estimating Software for Small Builders: Six Options Compared — Six estimating tools compared for small builders.
- Construction Contract Review With AI: Which Clauses It Flags and Misses — Payment and retention terms that shape your billings.
- Best AI for a Small Construction Company's Financials and QuickBooks — How AI connects to QuickBooks for a small construction firm: three routes compared, the plan job costing needs, and ten entries no AI should post.
- General Liability Audit for Contractors: What the Auditor Asks For — What a general liability premium audit asks a contractor for, and how to gather gross receipts, subcontract costs and certificates before the auditor calls.
- WIP Report Template for Service Firms: Build One From Timesheets — A WIP report for a service firm built from a timesheet export: ageing buckets, write-offs, roll-forward and lock-up days, with sample figures.
- AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.
Sources: standard percentage-of-completion and over/under billing conventions used in contractor accounting; ChatGPT and Claude plan pages. Illustrative figures only.