Over/under billing calculator for every open job

One row per open job, with five columns: job, contract value, estimated total cost, cost to date and billed to date. An approved change orders column is optional. Title rows, dollar signs, commas, blank lines and a total row are all fine.

In QuickBooks Online, the numbers come from three places:

• Contract value and billed to date: turn on progress invoicing (Settings > Account and settings > Sales > Progress Invoicing), then run Estimates & Progress Invoicing Summary by Customer from Reports.
• Cost to date: run Profit and Loss by Customer through month-end. Turn on Track expenses and items by customer under Settings > Account and settings > Expenses first.
• Estimated total cost: your project manager’s current forecast for each job. QuickBooks doesn’t hold this number.

Copy them into one sheet in Excel and save it as CSV. Our field guide on job costing in QuickBooks Online for contractors covers getting labor and materials onto each job.

It uses the cost-to-cost method, the completion factor the federal tax regulations for long-term contracts describe (26 CFR 1.460-4):

1. Revised contract = contract value + approved change orders
2. Percent complete = cost to date ÷ estimated total cost, capped at 100%
3. Earned revenue = percent complete × revised contract
4. Over (under) billed = billed to date − earned revenue

A positive result is an overbilling: billings in excess of costs and estimated earnings, a liability. A negative result, shown in parentheses, is an underbilling: costs and estimated earnings in excess of billings, an asset.

The totals stay separate, one for each side of the balance sheet. Under the revenue standard, each contract is shown on its own as a contract asset or a contract liability.

The journal entry moves both accounts from what is booked now to what the schedule says, with the difference going to contract revenue.

• Estimates are current. One stale estimate moves percent complete, and with it the whole result for that job.
• Change orders are in. Approved change orders left out of the contract value make a job look overbilled.
• Costs reach the job. Labor and materials posted without a customer or project make a job look less complete than it is.
• Late-stage underbillings. Old Republic Surety says a large underbilling on a job near completion can be a sign its gross profit has declined.
• The balance sheet agrees. Old Republic says the over and under billing on a CPA-prepared WIP schedule match the balance sheet exactly, and that internal statements often don’t.

The Check column flags jobs whose cost passed the estimate, jobs heading for a loss, jobs underbilled at 90% complete or more and jobs billed past the contract value.