Over/under billing (WIP) schedule
Enter each job's contract amount, estimated total cost, cost to date, and billed to date. The schedule shows percent complete, earned revenue, and how far billing is ahead of or behind the work, job by job and in total.
Your numbers stay on this device. Nothing is uploaded.
Jobs
| Job | Contract amount, with change orders | Estimated total cost | Cost to date | Billed to date | % complete | Earned revenue | Over-billing | Under-billing |
|---|
Portfolio totals
What gets checked
- Percent complete equals cost to date divided by estimated total cost on every job, proved on a second code path that multiplies back instead of dividing.
- Earned revenue equals contract amount times cost to date divided by estimated total cost, to the cent, proved the same second way.
- Over-billing or under-billing equals billed to date minus earned revenue on every job.
- Every portfolio total re-adds from the jobs, and total over-billing minus total under-billing equals total billed minus total earned revenue.
- A warning is shown when a job's cost to date is above its estimated total cost or its billing is above its contract amount.
How it works
- List the jobs. Add one row per job with its contract amount including approved change orders, estimated total cost, cost to date, and billed to date.
- Read each job. Percent complete is cost to date divided by estimated total cost. Earned revenue is the contract amount times that fraction. The difference between billed to date and earned revenue appears as over-billing or under-billing.
- Read the portfolio totals. Totals for every column are shown with the net over-billing or under-billing across all jobs.
- Check the certificate. Each check is listed with its result. Jobs running past their estimated cost or billed past their contract are named in a warning.
- Export or print. Download Excel or CSV, or print to PDF. The printout carries the verification result.
Worked example
Three made-up jobs at month end. The first is billed ahead of the work, and the other two are behind it.
| Job | Contract | Estimated cost | Cost to date | Billed to date | % complete | Earned revenue | Over-billing | Under-billing |
|---|---|---|---|---|---|---|---|---|
| Maple Street clinic | $250,000.00 | $200,000.00 | $80,000.00 | $110,000.00 | 40.00% | $100,000.00 | $10,000.00 | |
| Harbor warehouse | $180,000.00 | $150,000.00 | $100,000.00 | $112,500.00 | 66.67% | $120,000.00 | $7,500.00 | |
| Ridgeview school addition | $95,500.00 | $81,000.00 | $27,000.00 | $30,000.00 | 33.33% | $31,833.33 | $1,833.33 |
Across the three jobs, $252,500.00 is billed against $251,833.33 of earned revenue. Over-billing totals $10,000.00 and under-billing totals $9,333.33, a net over-billing of $666.67. The portfolio is 48.03% complete by cost ($207,000.00 of $431,000.00).
The example uses made-up names and numbers.
Questions
Which method does the schedule use?
Cost-to-cost percent complete: cost to date divided by estimated total cost. Earned revenue is the contract amount times that fraction. Other ways of measuring progress, such as units installed, are not covered.
How is earned revenue rounded?
The contract amount in cents is multiplied by cost to date and divided by estimated total cost in whole-number math, then rounded to the nearest cent. The displayed percent is rounded separately to two decimals and is not used to compute earned revenue.
What happens when cost to date is above the estimated total cost?
The job shows more than 100% complete and earned revenue above the contract amount, exactly as the formula gives it, and a warning names the job. Nothing is capped or adjusted.
Does the contract amount include change orders?
It is whatever is typed in that column. The column is labelled for the contract amount including approved change orders, and the estimated total cost would then cover the same scope.
Where is my data stored?
On your device only. A draft is kept in this browser's storage. Nothing is sent to a server.
This tool calculates and checks numbers. It is not legal, tax, or medical advice.