What it's for
This WIP schedule template puts every job you have under way on one page and sets what each has earned against what you have billed on it. The cost-to-cost method gives the percent complete: cost to date divided by estimated total cost. That percent times the contract amount is the earned revenue, and the gap between earned revenue and billed to date is the over or under billing on the job.
An owner or office manager fills it in from the job cost report and the billing records as of a date, one line per job. The file says so itself: this is a management schedule, and how your financial statements report revenue is your accountant's call.
Take billed to date from your pay applications. The contract amount is the sum with approved changes, which your change order log keeps. To see when the cash behind a job arrives, use the construction cash flow template. The file has no retainage column; our guide to retainage in construction covers what it is. For one job at a time, our over/under billing calculator does the same arithmetic without the file.
What's inside
Two sheets, both protected with no password. Pale yellow cells are yours to fill in; all other cells are locked.
WIP schedule is an internal sheet, so it carries our logo with no spot for yours. Above the table are COMPANY, AS OF and PREPARED BY. Under JOBS IN PROGRESS are 20 lines. You fill in JOB, CONTRACT AMOUNT, ESTIMATED TOTAL COST, COST TO DATE and BILLED TO DATE. The next eight columns calculate: PERCENT COMPLETE, EARNED REVENUE, ESTIMATED GROSS PROFIT, GROSS PROFIT TO DATE, OVER BILLING, UNDER BILLING, COST TO COMPLETE and BACKLOG. A Totals row adds every money column. Money shows in whole dollars so eleven money columns fit one landscape page, while the formulas still round to the cent and the totals add the cents. The rows through the header stay in view as you scroll, and the sheet prints landscape on one page.
About this template holds three steps, the example figures, the method, the version, the license and links.
How to fill it in
- Type COMPANY, PREPARED BY and an AS OF date. No figure reads the date, so it labels the page and nothing else.
- Replace or clear the three example lines. Any left in are counted in the totals.
- For each job type the JOB name and the four money figures. Each takes a number, 0 or more. CONTRACT AMOUNT is the contract sum with approved changes included.
- Enter COST TO DATE and BILLED TO DATE as of the same date, the one in AS OF.
- Read PERCENT COMPLETE, EARNED REVENUE and GROSS PROFIT TO DATE for each job, then the over and under billing columns.
A line counts once it has a job name or any one of the four figures, and a blank figure counts as 0. A job with no estimated total cost shows blank for percent complete and for earned revenue, gross profit to date, over billing, under billing and backlog. Its estimated gross profit and cost to complete still show.
OVER BILLING is billed to date above earned revenue, and UNDER BILLING is earned revenue above billed to date, so on any line one of the two reads $0. The totals do not net one against the other. Percent complete is not capped at 100%: a job whose cost to date passes its estimate shows a percent above 100%, earned revenue above the contract amount and a negative backlog. The file does not flag it.
The worked example
The first three lines are labeled Example, and the About tab says to replace them with your own jobs. The sheet shows whole dollars, so the figures below read as it displays them.
| Job | Contract amount | Estimated total cost | Cost to date | Billed to date |
|---|---|---|---|---|
| Example: job A | $480,000 | $408,000 | $204,000 | $260,000 |
| Example: job B | $125,000 | $100,000 | $80,000 | $90,000 |
| Example: job C | $62,500 | $52,000 | $13,000 | $12,000 |
| Job | Percent complete | Earned revenue | Est. gross profit | Gross profit to date | Over billing | Under billing | Cost to complete | Backlog |
|---|---|---|---|---|---|---|---|---|
| Job A | 50.00% | $240,000 | $72,000 | $36,000 | $20,000 | $0 | $204,000 | $240,000 |
| Job B | 80.00% | $100,000 | $25,000 | $20,000 | $0 | $10,000 | $20,000 | $25,000 |
| Job C | 25.00% | $15,625 | $10,500 | $2,625 | $0 | $3,625 | $39,000 | $46,875 |
| Totals | $355,625 | $107,500 | $58,625 | $20,000 | $13,625 | $263,000 | $311,875 |
Job A has spent $204,000 of an estimated $408,000, so it is 50.00% complete and has earned $240,000. It has billed $260,000, which is $20,000 over billing. Job B has earned $100,000 against $90,000 billed, so it is $10,000 under. The totals row also adds $667,500 of contract amount, $560,000 of estimated total cost, $297,000 of cost to date and $362,000 billed to date.
Frequently asked questions
Does it calculate anything?
Yes. The eight columns after the four money figures and the totals row are live formulas. Everything else is yours to type.
Does it work in Google Sheets or Numbers?
Both open .xlsx files. This one has no macros, and its formulas use only IF, OR, AND, ROUND, MAX and SUM.
Can I list more than 20 jobs?
The sheet has 20 lines. For more, keep a second copy, or unprotect the sheet (there is no password), insert a row between two lines so the totals take it in, copy the eight calculated cells from the row above, then protect it again. Added rows may push the print onto a second page.
Does it track retainage or change orders?
Not in separate columns. Contract amount is the contract sum with approved changes, as you type it, and billed to date is the figure you enter. There is no retainage column.
Can I put my logo on it?
It carries our logo, with no spot for yours. With the protection off (there's no password), you can swap in your own.
Is it accounting or financial advice?
No. The file is arithmetic on the figures you type. It makes no loan, tax, accounting, lender or surety claim and does not check your figures against your books. It uses the cost-to-cost method, and how your financial statements report revenue is your accountant's call.