Building a construction WIP schedule in Python: percent complete, over and under billing, and the month-end entry

A construction WIP schedule computes earned revenue for each open job as contract price times percent complete, where percent complete is cost to date divided by estimated total cost. Billed minus earned is overbilling when positive and underbilling when negative. One journal entry a month moves the totals into the ledger, and debits must equal credits. I work on ZeraBooks, and contractors send us exactly this report each month. Here is the computation with the figures from our contractor guide. All jobs are fictional. What inputs does a WIP schedule need? from dataclasses import dataclass from decimal import Decimal as D, ROUND_HALF_UP @dataclass class Job: name: str contract: D # including approved change orders est_cost: D # the current estimate, not the original bid cost_to_date: D billed: D def cents(x: D) -> D: return x.quantize(D("0.01"), rounding=ROUND_HALF_UP) Enter fullscreen mode Exit fullscreen mode Estimated cost is the current estimate, not the original bid. That one field decides whether the whole schedule is honest. How do you calculate over and under billing in code? def wip_row(j: Job) -> dict: pct = j.cost_to_date / j.est_cost earned = cents(j.contract * pct) diff = cents(j.billed - earned) return { "job": j.name, "pct": f"{pct:.2%}", "earned": earned, "over": diff if diff > 0 else D("0.00"), "under": -diff if diff list[tuple[str, D, D]]: over = sum((r["over"] for r in rows), D("0")) under = sum((r["under"] for r in rows), D("0")) return [ ("40100 Contract revenue", over, D("0")), ("20300 Billings in excess of costs and estimated earnings", D("0"), over), ("11300 Costs and estimated earnings in excess of billings", under, D("0")), ("40100 Contract revenue", D("0"), under), ] entry = wip_entry(rows) assert sum(d for _, d, _ in entry) == sum(c for _, _, c in entry) Enter fullscreen mode Exit fullscreen mode Debits equal credits by construction: $27,000.00 each side here. Net, revenue falls by $3,000.00 for the month. A journal entry in ZeraBooks: it won't save until the difference between debits and credits is 0.00. Most bookkeepers reverse this entry on the first day of the next month and post a fresh one at the next month end. That keeps the balances equal to the current schedule instead of a running sum of old adjustments. What happens when the estimate changes? Say the kitchen's estimate rises to $170,000 after a tile change and an extra week of labor. Nothing else changes. jobs[0].est_cost = D("170000") wip_row(jobs[0]) # pct 35.29%, earned 70,588.24, over 24,411.76 Enter fullscreen mode Exit fullscreen mode Overbilling jumps by $9,411.76, and revenue to date drops by the same amount, in the month the estimate changed. The job didn't get worse this month; the books caught up with it. If estimates are only updated at year end, this whole correction lands in December. Which guardrails should a WIP script have? def sanity(j: Job) -> list[str]: issues = [] if j.cost_to_date > j.est_cost: issues.append("cost to date exceeds estimate: update the estimate") if j.est_cost >= j.contract: issues.append("estimated cost at or above contract: expected loss, talk to your accountant") return issues Enter fullscreen mode Exit fullscreen mode Percent complete above 100% is always an estimate that wasn't updated. An estimated cost above the contract is a losing job, and how that loss is recorded is a call for the contractor's accountant. Where does the WIP data come from? Cost to date per job comes from wherever job costing lives: the estimating tool, project software or a spreadsheet. The general ledger carries totals by cost type: labor, materials, subs, equipment, permits. The rest of a contractor's month, including retainage and the chart of accounts, is in the guide to bookkeeping for contractors. Key takeaways Percent complete = cost to date / current estimated cost. Earned = contract x percent. Billed minus earned is over (liability) or under (asset). One balanced entry per month, reversed on day one of the next month. Update estimates monthly; a stale estimate makes every report wrong until December. FAQ Is overbilling revenue? No. It is billed but not yet earned, so it sits as a liability (billings in excess of costs and estimated earnings) until the work catches up. Why reverse the WIP entry every month? So the balance sheet always shows the current schedule's over and under billing, rather than a sum of every past adjustment. Does ZeraBooks calculate job costing? No. ZeraBooks has no job or class tags, so job costing stays in your project tool. Its WIP report feeds the month-end entry. Disclosure: I'm on the ZeraBooks team. ZeraBooks bookkeeping services take the contractor's WIP report and post this entry monthly, from $150 a month in U.S. dollars, working from uploaded statements and invoices, with no bank feeds.