by Sunburst Software Solutions, Inc.
Archives by Month

QuickBooks Tip: How to Calculate Over/Under Billings

Print Friendly

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.

QuickBooks tipsThe 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.

Right click to display a larger image

Right click to display a larger image

5.  Export this report to Excel.

Right click to enlarge the image

Right click to enlarge the image

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.

Right click to enlarge the image

Right click to enlarge the image

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.

Right click to enlarge the image

Right click to enlarge the image

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.

Right click to enlarge the image

Right click to enlarge the image

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.

  1. Contract Totals
  2. Totals through the end of the prior year (if applicable)
  3. Actual accumulated contract totals through the end of the current year
  4. Over/Under Billings
  5. Totals for the current year
  6. Percentage complete
  7. 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.

Leave a Reply

About Your Host:

Nancy Smyth, Certified QuickBooks ProAdvisor

Nancy Smyth, Sunburst Software Solutions, Inc.
QuickBooks Construction & Payroll Expert


I've been using and supporting QuickBooks products since the early 1990's. I've worked with thousands of contractors, assisting them with QuickBooks setup, Certified Payroll Reporting requirements, AIA Billing and Weighted-Average Overtime.


QuickBooks is a powerful product, but learning how to use it in your construction business can be difficult. I hope you find resources available here to be helpful.

Search…….
Loading
Stay updated!

Sign up to receive our latest QuickBooks Tips, Tricks & News via email:


Enter your email address:

Delivered by FeedBurner


We promise NOT to sell, rent, or otherwise distribute your name and email address!

FREE 30-Day Trials

Request FREE 30-day Trials of QuickBooks add-ons for Certified Payroll, AIA Billing & Payroll Wage Management. Save time and increase accuracy on time-consuming error prone tasks; so you get paid on time!

Free 30 day trials of QuickBooks integrated add-ons for certified payroll, aia billing and weighted-average overtime

Certified Payroll Training:
Learn how to complete a Certified Payroll (Prevailing Wage) Report and fulfill your reporting requirements - 2 hour live webinar - $89.00 per person. Click here for more info.

certified payroll requirements webinar
QuickBooks Training

Frustrated with QuickBooks?

QuickBooks Premier ContractorLearn how to get the MAXIMUM possible results from QuickBooks in your construction business! Visit our new QuickBooks training website, Learn to use QuickBooks in your construction business.

QuickBooks Time Tracking


Save 10%/FREE 14-day trial!


QuickBooks Time Tracker