QuickBooks Tip: How to Calculate Over/Under Billings
The construction industry often requires that your financial statements be based on Percentage of Completion accounting practices – where you recognize revenue progressively throughout the life of the job.
The Percentage of Completion is determined by dividing the costs to date over the current budget (Estimate). The current budget is defined as the original budget (Estimate) plus any approved change orders. An adjustment to the financials for overstated/understated revenue must then happen, because customer monies are often collected in advance of the work in job costing, you’ll want to state the true revenue on the financials carefully.
Accounting guidelines recommend that you make your percentage of completion calculations based on Revenue Earned = (Total Costs to Date / Current Project Budget) x Current Customer Contract Sales Price. In reality, you are computing the percentage complete on the project and then multiplying that percentage against the contract sales price to determine the amount of revenue to record in an Over/Under billing.
The Cost to Complete Summary report, also included in QuickBooks, comes close but has some shortcomings – so I really don’t recommend that you rely on it, because:
- The report uses a manually entered percentage complete
- The report does not provide the amount that is earned
- and, you have to eliminate jobs that are complete.
You can easily calculate Over/Under Billings and Percentage of Completion from QuickBooks data IF:
- You create an Estimate for EVERY job/project
- You’ve modified the QuickBooks Estimate Template to include Cost and Markup columns and you utilize those columns when building your Estimate
- All of your “Items” have been set-up to capture BOTH revenue and costs (double-sided items)
- You enter ALL job related employee time and vendor costs using double-sided items
- You create Job Types for Over/Under and Completed (with sub-types for years) and assign them correctly to your jobs
IF all of these things are currently in place in your QuickBooks file, this is how you can create a simple Over/Under Billing Report with Percentage of Completion WITHOUT hours of manual calculations.
1. From the Reports menu, choose Job Costing, and then choose Job Estimates vs. Actuals –OR – from the Reports menu –> Jobs, Time & Mileage –> Job Estimates vs. Actuals Summary, depending on your version of QuickBooks.
2. Collapse the report detail.
3. Then click the 1) Customize Report button –> 2) click the Filters tab –> 3) scroll to Job Type –> 4) Select Multiple Customer Types –> and 5) click (select) Over/Under.
4. You now have a report just showing the totals for your current jobs that are subject to Percentage of Completion Calculations.
5. Export this report to Excel.
6. In Excel, modify column H: Act. Costs (column E) divided by Est. Costs (column C). The Excel formula for the first entry is: =E3/C3, the formula for the second entry is: =E4/C4, etc. This yields the percentage complete for each project.
7. Next, modify column J: Est. Revenue x % Complete (column H). The formula for the first entry is: =I3*H3, the formula for the second entry is: =I4*H4, etc. This displays the Earned Revenue for each project.
8. The last step is to modify column M: Act. Revenue – Earned Revenue. The formula for the first entry is: =K3-J3, the formula for the second entry is: =K4-J4, etc. This displays the amount that was Over Billed OR Under Billed for each project.
NOTE: A negative amount in this column represents the amount Overbilled AND a positive amount represents the amount Underbilled.
With this information, you can see that customers have been overbilled by $381,615.92.
If you are adjusting the book financials, you can create a Journal Entry with a Debit to Revenue (Income) and a Credit to Unearned Revenue -OR – Billing in Excess of Costs (which would be an Other Liability account on your Chart of Accounts).
When the percentage-of-completion is the method of accounting, the accounting principle of full disclosure requires the presentation of a work-in-process (WIP) schedule in a company’s financial statements. This schedule discloses the details of each contract stage of completion and profitability to date as well as in the current period of reporting. Its format varies, but at a minimum should include at least the following seven components.
- Contract Totals
- Totals through the end of the prior year (if applicable)
- Actual accumulated contract totals through the end of the current year
- Over/Under Billings
- Totals for the current year
- Percentage complete
- Balance to finish
We’ve created an Excel based WIP Calculator Template, which we’ll be releasing on the Learn to use QuickBooks in your construction business website in the next couple of days, so be sure to visit our companion site and look for the WIP Calculator Template in the New Offerings section.
I hope you’ve found this post to be helpful – if so please take a moment to leave a comment or share it on your favorite social media platform with others.