What is a Construction Work-In-Progress Schedule

A work in progress schedule is essentially a list of projects that a contractor has. It shows the profitability on a job by job basis and how far along they are in being completed. These reports help surety companies to understand a contractor's operation and the health of their backlog.

So what you’re looking at here is an Excel spreadsheet, which essentially breaks down each project in your backlog. You want to check with your surety agent whether you should fill this out for every single project or only projects over a certain dollar amount. A lot of times we will tell our contractors to only include projects over let’s say $100,000 or $250,000 for example.

1. We’ll start here by entering a sample contract. I’ll name it contract 001 “Performance Bond Project”, and it has a contract value of $1 million under column number 1. In this column you should include any approved change orders that you’ve received. You don’t want to include unapproved change orders, because if they don’t end up getting approved, you’ll have recognized revenue that you will later have to back out.
2. In column 2 you will put your total estimated cost for the entire project. Again, if you have change orders or the project is doing better or worse than you originally estimated, you’ll want to adjust for that in this total cost field instead of just putting the estimated cost at bid time. So for example, if you originally bid it with cost of $800,000 but now the project is going better than expected because you saved on some material or you beat your production and you think your final cost is going to be 750,000, you’ll enter the $750,000 instead.
3. Column 3 is the gross profit on your job and it automatically calculates by taking your contract amount minus your cost for the project.
4. In column four you will enter the cost incurred to date. We’ll enter $375,000.
5. Column 5 automatically calculates by taking the cost you’ve incurred to date on the project in column four divided by the total estimated cost in column two. In construction accounting this is called cost to cost. You can see that is 50%.
6. That percentage is really important, because the way this works is it will take that percentage and use it to determine the profit that you’ve earned to date on the project. So $250,000 x 50% equals the $125K already in column six.
7. The same thing happens with column seven. It is taking the revised contract price in column one and multiplying it by 50% to have earned revenue on the project of $500,000.
8. Next in column eight you will enter the amount you have billed on the project. Let’s say there was a large mobilization and you billed a little extra upfront, so you’ve billed $600,000 so far.
9. From here the rest of the formulas are completed for us. Column 9 where it says billings in excess of cost and profit to date is simply saying that your billings are greater than the amount earned in column seven. So $600,000 $-500,000 equals $100,000.
10. Column 10 would be the opposite situation where you have underbilled a project. So let’s assume you billed $400,000 instead. Now you’ve earned $500,000 as shown in column seven but billed only $400,000, so the one hundred thousand dollars moves over to column 10 showing you are underbilled. This can be a little tricky to understand at first, but if you play around with it and enter a few more contracts, you’ll get the hang of it pretty quickly.
11. Column 11 is simply the cost to complete the job which is just column to subtracting column for the cost you’ve incurred to date. Column 12 is just the amount you have left the bill on the project.

As you can see, the work in progress schedule provides a great snapshot to you and your surety company what is happening in your business at a project by project level. In our next video we will talk about what surety companies look for in contractor’s work in progress schedules.

Take care.