WIP schedule template: the columns and formulas, ready for Excel

If you build the work-in-progress schedule in Excel or Google Sheets, the template is what keeps the numbers honest from month to month. A good one holds the formulas, so you only type the inputs: contract amounts, cost to date, billed to date and cost to complete. A bad one mixes typed numbers and formulas in the same column, and breaks the first time someone inserts a row.

This guide lays out a template you can build in a few minutes, column by column, with the formulas, the checks worth adding and the errors that most often creep in.

The layout

Put one job per row and one period per sheet or file. Keep the inputs in the left-hand columns and the calculated columns to the right, so it is obvious what someone should type and what they should leave alone.

Input columns

Calculated columns

Column N is the backlog for that job. Summed across jobs, it is the work-on-hand figure lenders and sureties often ask for alongside the schedule.

Add a totals row at the bottom with =SUM() on every money column. Do not total percent complete or gross profit %; calculate them from the totals instead, for example total estimated gross profit divided by total contract.

A worked row

With the formulas above, an example warehouse addition looks like this:

The template returns an estimated total cost of 2,500,000, 35% complete, earned revenue of 1,067,500, and billing of (77,500), meaning 77,500 under billed. Retainage held is 99,000, and estimated gross profit is 550,000, or 18%.

The under billing is the line someone will ask about. The underbillings guide covers what it can mean and how to explain it.

Checks worth building in

A template that only calculates will happily produce a wrong schedule. Add a column or conditional formatting that flags these:

• Missing estimate. Cost to complete is blank. A blank reads as zero, which makes the job look 100% complete and pulls all its revenue into this period.
• Negative cost to complete. Almost always a typing error.
• Projected loss. Estimated gross profit below zero. The full expected loss is normally recognized now; flag it for your CPA.
• Percent complete went down. Compare with last month's file. Cost to date only goes up, so a drop means the estimate jumped.
• Estimate moved sharply. Estimated total cost moved by more than, say, 10% from last month. Pick a threshold you will actually look at.
• Costs with no contract. Cost to date on a row with no contract amount: a job that was never set up, or overhead that landed on a job.

A simple way to compare with last month is a hidden column that looks up the same job in the prior period's file with XLOOKUP or INDEX/MATCH on the job column. Look up by job number, never by row position, because rows move.

Mistakes that break templates

Typing over a formula. Someone overrides earned revenue on one job to make it tie, and next month the override is still there. If a number must be overridden, add a separate override column and a note column, and have the formula use the override when it is filled in.

Inserting rows outside the totals range. A new job added below the totals row is left out of every total. Use an Excel table (Insert > Table) so formulas and totals extend to new rows on their own.

Pasting the export over the inputs. Exports from accounting systems change shape when a job is added or renamed. Paste the export onto its own sheet, and pull cost and billing into the schedule with lookups on the job name or number.

Stale cost to complete. Last month's estimate, carried forward because the project manager did not answer, makes a job look further along than it is. Mark carried-forward estimates so the reader knows.

Mixing contract amounts. Decide whether pending change orders are in or out, write it in the file's notes, and apply it to every job.

Keeping each month

Save each period as its own file or sheet, named by period end, and do not edit it after it has gone to the bank or surety. If something needs correcting, correct it in the next period and say so in the notes. The history is what lets you show profit fade, compare a finished job's final margin against what the schedule showed while it was open, and answer questions about last quarter.

What a template does not do

A template does the arithmetic. The monthly work around it is still manual: exporting the job report, pasting it into the right rows, emailing each project manager their jobs and last month's numbers, chasing the ones who do not answer, and saving a clean copy to send. For the full routine, see what a WIP schedule is and how to build one.

How WIP Monthly fits

WIP Monthly does the cycle around the template. It reads cost to date and billed to date from your QuickBooks Online Profit and Loss by Customer (or by Class) export, emails each project manager a link to enter cost to complete, and reminds them each weekday morning until they do, up to five times. It runs the checks above as warnings on the schedule, keeps overrides beside the original figures with a note, and when you finalize the month makes a PDF and an Excel file with live formulas, so the file still works as a template if you need to adjust it.