One of the biggest causes of confusion for contractors working on government funded construction projects is “how to calculate and display fringe benefits” on a certified payroll report.
In recent months, I have repeatedly seen posts from QuickBooks users on the Intuit Community Forums asking how the fringe benefit rate is calculated and then displayed on the Certified Payroll Report in Excel, which is now a standard function of QuickBooks Premier and Enterprise when used in conjunction with Enhanced Payroll, and I really haven’t seen any good responses. Hence today’s blog post.
Background:
Contractors who work on government funded construction projects are required to pay their employees a “prevailing” rate of pay; based on where the project is located and the type of work they are performing. This “prevailing rate of pay includes both an hourly wage rate AND an hourly fringe benefit rate. These rates are found on the Prevailing Wage Determination that is included in the contact package and you MUST pay your employees these rates of pay.
A wage decision might look like the screenshot below; just a listing of Work/Trade Classifications, along with hourly wage Rates and Fringes:
For the remainder of this article we will be working with various examples based on the Bulldozer Operator classification which has a base rate of pay of $16.22 and a fringe rate of $3.35 and how this information is to be reported. We will focus on the Federal or U.S. Department of Labor form WH-347 and we will compare the actual WH-347’s requirements to the alternate/substitute WH-347 produced by QuickBooks.
*The rules according to the U.S. Department of Labor regarding the calculation and reporting of Prevailing Wage Fringe Benefits on the WH-347
Column 6 – Rate of Pay (Including Fringe Benefits): In the “straight time” box for each worker, list the actual hourly rate paid for straight time worked, plus cash paid in lieu of fringe benefits paid. When recording the straight time hourly rate, any cash paid in lieu of fringe benefits may be shown separately from the basic rate. For example, “$16.22/$3.35″ would reflect a $16.22 base hourly rate plus $3.35 for fringe benefits. When overtime is worked, show the overtime hourly rate paid plus any cash in lieu of fringe benefits paid in the “overtime” box for each worker; “$24.33/$3.35”. In addition, check paragraph 4(b) of the statement on page 2 the payroll form to indicate the payment of fringe benefits in cash directly to the workers.
Item 4 FRINGE BENEFITS – Contractors who pay all required fringe benefits: If paying all fringe benefits to approved plans, funds, or programs in amounts not less than were determined in the applicable wage decision, show the basic cash hourly rate ($16.22) and overtime rate ($24.33) paid to each worker on the face of the payroll. Check paragraph 4(a) of the statement on page 2 of the WH-347 payroll form to indicate the payment.
How rates of pay and fringe benefits are to be displayed on the certified payroll report when fringes are paid in cash.
This example shows the correct method of reporting the base hourly rate ($16.22) PLUS the hourly fringe rate ($3.35) being paid in cash to the employee ($16.22/$3.35). {Note: the employees rate of pay in QuickBooks is $19.57). Pay particular attention to Column 6 – Rate of Pay/Cash Fringes.
Now let’s look at the same certified payroll report as it is generated by QuickBooks. Pay close attention to the Pay Rate and Fringe Rate Columns – the Pay Rate comes through as the full $19.57 and in order to show the Fringe Rate of $3.35 you would need to manually adjust both the rate of pay and the fringe rate.
How rates of pay and fringe benefits are to be displayed on the certified payroll report when fringes are paid to approved plan, funds or programs.
This example shows the correct method of reporting only the base hourly rate ($16.22). Pay particular attention to Column 6 – Rate of Pay/Cash Fringes, as you should ONLY be reporting/showing the $16.22 rate of pay.
Now, let’s look at this same report generated by QuickBooks, keeping in mind that you could choose any of 3 options when creating this report.
OPTION 1 – QuickBooks recognizes company contribution items that you have set up for the fringes (Health & Welfare, Pension, and Vacation Fund) do NOT select/click on any of the items in the Fringe Benefits Paid box.
The Rate of Pay ($16.22) is displayed correctly, but the Fringe Rate column contains $0.00. This is “ok”, however, it is very misleading, because it could be taken to mean that you are not paying any fringes. Remember the rules state that you are to report the hourly fringe benefit rate that you pay in cash on the report.
OPTION 2 – QuickBooks recognizes company contribution items that you have set up for the fringes (Health & Welfare, Pension, and Vacation Fund) you click on each of these 3 items to indicate that these are your fringe benefit items.
The Rate of Pay ($16.22) is displayed correctly, but the Fringe Rate column contains $3.35. This is “ok”, however, it is very misleading, because it could be taken to mean that you are paying the fringes in cash to the employee. Remember the rules state that you are to report the hourly fringe benefit rate that you pay in cash on the report and that if you pay the fringes to approved plans you report just the hourly rate of pay.
OPTION 3 – QuickBooks recognizes company contribution items that you have set up for the fringes (Health & Welfare, Pension, and Vacation Fund) you click on each of these 3 items to indicate that these are your fringe benefit items and you choose to Apply these Fringe Benefit items to this project only (do not pro-rate by job).
The Rate of Pay ($16.22) is displayed correctly, but the Fringe Rate column contains $4.47 – which is incorrect. This is very misleading, because it could be taken to mean that you are paying the fringes in cash to the employee AND paying a higher fringe rate than required. Remember the rules state that you are to report the hourly fringe benefit rate that you pay in cash on the report and that if you pay the fringes to approved plans you report just the hourly rate of pay.
The bottom line of my finding when reviewing the way the QuickBooks calculates and displays the fringe benefit portion of prevailing wage on the “built-in” certified payroll report – is that you REALLY have to understand the reporting requirements before you submit your reports and that you should review the reports VERY carefully before submitting them.
Authors Note:
The certified payroll screenshots, with the WHD logo in the upper right corner, have been generated with Certified Payroll Solution, a QuickBooks integrated application specifically designed to help contractors comply with prevailing wage requirements. For more information, click here.
*(Source: United States Department of Labor, Wage and Hour Division (WHD), Instructions for Completing Payroll Form, WH-347 – http://www.dol.gov/whd/forms/wh347instr.htm)
Retention or retainage is usually a specific percentage, for example 10%, of the total contract that is held back by the project owner in reserve to protect the owners interest. Retention is not held in a lump sum, but rather held at the stated percentage for the amount requested on each application for payment. Your contract should set the terms, including the percentage and when the hold-back will be paid.
Contractors using QuickBooks often run into difficulties when handling retainage/retention; simply because the software doesn’t have a means to automatically deal with it.
Because QuickBooks doesn’t have a built-in retention function, like many of the more expensive construction specific software program, QuickBooks users must initiate work-arounds and make QuickBooks track retainage that is held on each progress invoice.
Over the years, I’ve seen several work arounds that various contractors, their bookkeepers, and even their accountants have implemented, such as:
- Simply leaving the retention amount of each invoice sitting in their open A/R.
- Billing for just the amounts on each line item that they will be paid for.
- Creating a Customer called Retentions Receivable and then making some fancy Journal Entries each billing period to move the retainage from the originating customer to the Retentions Receivable customer.
- Using a QuickBooks Discount Item to deduct retainage on individual invoices and mapping it to the Chart of Accounts as either an Income Account or Expense Account.
- Creating an Other Current Asset Account, called Retainage (Retention) Receivable and through the use of “Items” automatically move the money to this account on each invoice that is generated.
- Creating a Sub-Account of Accounts Receivable called Retainage (Retention) Receivable and then through the use of Items and additional invoices move the retainage amounts into this newly created Accounts Receivable sub-account.
Each of these methods has their own drawbacks, however, the first three (4) methods described cause the most problems with the contractors accounting records and are methods that I highly recommend that you avoid.
The easiest method that I know of, is tracking Retainage as an Other Current Asset Account on your Chart of Accounts – Balance Sheet section; however, MUST get with your accountant and have him teach you to do a journal entry that will remove the amount from Income.
To implement this system:
- Add an Other Current Asset account to your Chart of Accounts called Retainage or Retention Receivable.
- Create an Other Charge OR Service item in your Item List called “Less Retainage”, map this to the account you created in Step 1, and in the Rate box enter -10.0%.
- Create another Other Charge or Service item in your Item List called “Retainage Due”, again mapping it to the account you created in Step 1.
- Make sure that you have a Subtotal item in your Item List.
- Create your Invoice or Progress Invoice billing for the full amount before any retainage is withheld. On the first blank line at the bottom of the Invoice, select your Subtotal item and then your Less Retainage item – the balance on the invoice that goes to A/R is now the amount after retainage, and the retainage dollars are moved to the Other Current Asset Account.
- You can generate Reports on the Retainage Receivable account showing who owes you what by going to your Chart of Accounts, click on the account created in Step 1 to highlight it, click the Report button at the bottom of the window and choosing QuickReport .
- When you are ready to bill for retainage, create a “normal or regular” invoice using the Retainage Due item and entering the appropriate dollar amount from the report.
As I stated earlier in this article, this is the easiest method – because it’s just adding two additional items to the bottom of your invoice and all the math and work is done for you; however, the amount of retainage that you deducted shows up in your Profit & Loss Report in your Income Account (even if you run the reports on a Cash Basis) which does require that a Journal Entry be created to remove this from your Income. You should consult your accountant for the proper entry.
A reader wrote to ask the following QuickBooks payroll question.
How can I change the hourly wage for employees (and their OT rates) in the middle of a pay week when using QuickBooks?
I have 54 union employees that I have to do this for due to an hourly wage pay scale increase.
Oh, and I will also need to change some of the company paid union fringe benefit amounts as well.
—————————————————————————————
Answer:
There are a couple of ways that you can accomplish this when using QuickBooks, however, because you are dealing with union hourly wage rates, it really depends on:
- Are you still working on contracts that will use the existing hourly rates of pay and fringe benefit amounts?
- Are the new hourly wage rates for contracts that you start between now and the next pay scale increase?
- Are the new hourly wage rates effective for all current projects as well as any new contracts between now and the next pay scale increase?
Some people would simply suggest that you use the existing Payroll Wage Item and manually split out the number of hours using two different hourly wage rates when you create the paycheck.
Other people would suggest that you Edit each QuickBooks Employee Record, going to the Payroll & Compensation Info tab and in the Earnings box select the existing Payroll Item Wage name and assign the new hourly wage rate – this gives you two instances of the same Payroll Wage Item with two different rates of pay assigned to it – QuickBooks will allow this (personally I think it is a flaw in the program) even though QuickBooks will not be able to determine which rate is should use when you select the payroll item in Timesheets and/or paychecks.
I do not recommend using either of the above methods as they are too error prone and do not leave you a reliable means of tracking what an employee was earning during a specific period of time.
The method that I recommend is to create new Payroll Wage Items and Company Contribution Items with the applicable rates of pay and hourly benefit amounts. Once you have created these items, edit each employee and add the new items to their Payroll & Compensation Info tab. When you enter time in the Weekly Timesheets, choose the old payroll item/pay rate for the applicable days and then choose the new payroll item/pay rate when they become effective.
Setting up QuickBooks in this manner makes the program work for you, instead of you having to always remember to manually make the pay rate changes.
Benefits include:
- A clearly visible audit trail for your Union Fringe Benefit Reports.
- Accurate pay checks.
- Less stress.
_______________________________________________________
Author’s Note:
Having to manually create new payroll items AND then update 54 employee records with new payroll wage items and rates of pay is a time-consuming process as you have to do each payroll item and employee one at a time.
Check out Wage Manager Solution, a QuickBooks integrated application designed specifically to automate this process.
Watch a 10 minute video which provides an overview of how Wage Manager Solution works.
A reader asked the following question regarding paying employees using different frequencies:
We are new to QuickBooks payroll and would like to set up different pay frequencies for our employees, as follows:
- Company officers – monthly salary
- Clerical staff – hourly basis, bi-weekly
- Field Workers – hourly basis, weekly
How do we accomplish this when using QuickBooks?
_______________________________________________________
Answer:
Yes, you can accomplish this when using QuickBooks Basic, Enhanced, or Assisted Payroll.
QuickBooks allows you to create a “Pay Frequency” and a “Payroll Schedule” to meet your various payroll needs.
Begin by going to the QuickBooks Employee Center and clicking on the Employees tab.
Pick an employee and choose the Edit Employee button, switch to the Payroll & Compensation Info tab. In the upper right corn you will see a Payroll Schedule option, this is where you will create a new Payroll Schedule and corresponding Pay Frequency.
From the Payroll Schedule dropdown menu choose Add New, below is the setup for a bi-weekly payroll schedule:
There are two very critical dates that you must enter when you create a new Payroll Schedule, they are:
- What is the pay period end date? This date should be the date the the first bi-weekly payroll will end on.
- What date should appear on paychecks for this pay period? This date should be the date that the first bi-weekly paycheck will be run.
The screenshot above indicates the following information:
The bi-weekly pay period will end on Saturday July 10, 2010 and we will process payroll checks (date of checks) on Wednesday July 17, 2010.
Once you have created your Payroll Frequency/Schedule you will then edit each employee’s record, going to the Payroll & Compensation Info tab as select the appropriate frequency (Weekly, Bi-Weekly, or Monthly).
When you are ready to actually pay your employees, go to the Employee Center and click on the Payroll tab, each of your Payroll Frequencies will be displayed, click on the appropriate frequency (Weekly, Bi-Weekly, or Monthly) and click the Start Scheduled Payroll button.
We hope you have found this QuickBooks Payroll Tip to be helpful, if so, please feel free to leave us a comment below.
A reader asked the following question:
We are a small contractor in San Diego and we are wondering how we get around having to always do an inventory adjustment to get our purchases to the correct COGS account? We have (2) departments but they are both doing construction projects: Service dept does smaller installs and Contracts dept does the bigger jobs so I have been doing a JE to move the material from the Contracts material COGS account to the service COGS account.
Is there an add-on for QB Enterprise 8 that we could use?
Thank you so much for your website/blog. I’ve been reading it faithfully every day!
____________________________________________________________________
There are several inventory add-ons for QuickBooks Pro, Premier and Enterprise; check out these 3rd party add-ons at the Intuit Marketplace.
Recording costs and income with an item
Based on your comment about “always having to do an inventory adjustment in order to get your Purchases to the correct COGS account”, makes me think that perhaps your items are not correctly set up.
Make sure that your QuickBooks Inventory Items are set up so that they capture both the cost/purchase account as well as a sales account; this method is called a “double-sided” item.
When any type of QuickBooks Service, Inventory, Non-Inventory, Other Charge Item is set up this way you are able to capture the purchase price as well as the sales price.
When you enter a bill from a vendor for the inventory item (or write a check) you should be using the ITEM tab and not the Expense tab.
I’m confident that if you aren’t currently using the QuickBooks Purchase Order function that you would find that beneficial also.
Additionally, rather than using Journal Entries to classify whether it was the Service or Contracts Department – consider using “classes” to handle that.
