Certified Payroll in Excel: How to Fill Out the WH-347 Without Penalties

If you run work on a federally funded job, the certified payroll template Excel WH-347 is not paperwork. It is the document that decides whether your progress payment clears this week or sits frozen while a compliance officer asks why your concrete finisher was paid laborer scale. The WH-347 is the U.S. Department of Labor form that proves you paid prevailing wages under the Davis-Bacon Act. File it wrong, and the consequences are not a warning letter. They are withheld payments, back wages, liquidated damages, and in the bad cases, three years of debarment from federal work.

The DOL publishes a blank WH-347 and a page of instructions. What it does not give you is a system that calculates prevailing wage, overtime, and fringe benefits across multiple classifications without arithmetic errors. That gap is where contractors get hurt. This article shows how to build that system in Excel so the form fills itself and the numbers survive an audit.

What a WH-347 mistake actually costs

Certified payroll applies to any federal or federally assisted construction contract over $2,000 covered by Davis-Bacon. That includes a huge share of school, road, water, and public housing work. Every week you have workers on site, you submit a WH-347 within seven days of the pay date, signed under a Statement of Compliance. That signature matters. Falsifying it is a federal crime under 18 U.S.C. 1001, not a clerical issue.

Here is what a single recurring error costs a mid-size contractor on a nine-month job.

Run the misclassification number. One worker paid $9/hr under scale for 1,400 hours on the job is $12,600 in back wages on a single person. The DOL finds it by comparing your classifications against the work descriptions in your daily reports, and they look back across every WH-347 you filed. A spreadsheet that catches the error in week one is the difference between a clean job and a settlement.

The four numbers the WH-347 actually requires

Most contractors treat the WH-347 as a transcription job, copying numbers off their regular payroll. That is the mistake. Regular payroll computes one rate. Davis-Bacon computes a wage package, and the package has parts that your standard payroll software does not separate. You need four numbers per worker, per week, per classification.

1. The correct classification and its wage determination

Every covered job has a wage determination, the DOL document listing the prevailing base rate and fringe rate for each labor classification in that county. A worker doing carpenter work gets carpenter scale even if you call him a "helper" on your books. If a worker splits time across two classifications in a week, the WH-347 needs a separate line for each, with hours allocated correctly.

2. The base hourly rate

This is the cash wage. It must meet or exceed the base rate in the wage determination for that classification.

3. The fringe rate, and where it went

Davis-Bacon wages are base plus fringe. You can pay the fringe in cash on the check, or pay it into a bona fide benefit plan (health, pension, approved training). What you cannot do is claim a fringe credit for benefits you did not fund. The WH-347 has you mark, on the Statement of Compliance, whether fringes are paid in cash or to plans. Track the dollars, not just the intention.

4. Overtime computed the Davis-Bacon way

Under the Contract Work Hours and Safety Standards Act (CWHSSA), overtime past 40 hours is paid at 1.5 times the basic rate. The fringe is added on top at straight time for every hour, including overtime hours, but the fringe itself is not multiplied by 1.5. Get this backwards and you either underpay the worker or overstate your cost. The correct overtime rate is (base * 1.5) + fringe.

Building the certified payroll calculator in Excel

Set up two areas. A wage determination table that holds your approved rates, and a weekly entry grid that pulls from it and computes gross pay. The point of the structure is that you enter hours once and every dependent number calculates, so the same fringe and overtime logic applies to every worker on every report.

Wage determination table (rows 4 to 7)

The total package in column E is just =C4+D4, but it is worth showing because that total is the number that has to be met. You can pay $30 base and $11.25 fringe instead of $28.50 and $12.75, as long as the package equals or beats $41.25. Excel makes that flexibility safe to use because you watch the package total recalculate as you shift dollars between cash and fringe.

Weekly entry grid

For each worker line, lay out columns for the seven days, then total hours, then the pay math. Here is the layout for a worker starting in row 12.

The formulas that do the work

Total hours in K12 sums the daily entries:

=SUM(D12:J12)

Straight-time hours in L12 caps at 40:

=MIN(K12,40)

Overtime hours in M12 is everything past 40:

=MAX(K12-40,0)

Now pull the rates from the wage determination table by classification, so you never hand-type a rate. Base rate in N12:

=VLOOKUP(C12,$B$4:$E$7,2,FALSE)

Fringe rate in O12:

=VLOOKUP(C12,$B$4:$E$7,3,FALSE)

This is the cell that prevents misclassification. The rate is tied to the classification you typed in column C, pulled from your approved determination table. Type "Carpenter" and you get carpenter scale. There is no way to quietly pay carpenter work at laborer rate without it showing on the line.

Gross earned in P12 applies the Davis-Bacon overtime rule:

=L12(N12+O12) + M12(N12*1.5+O12)

Read it in business terms. Straight-time hours earn base plus fringe. Overtime hours earn time-and-a-half on the base, plus fringe at straight time. With the example values: 40(34.20+15.40) + 4(34.201.5+15.40) = 4049.60 + 466.70 = 1984.00 + 266.80 = $2,250.80. Note the difference from the naive method. If you wrongly compute overtime as 1.5x the full package, you get 449.60*1.5 = $297.60 for those 4 hours instead of $266.80, overstating cost by $30.80 on one worker for one week. Across a crew of 12 over nine months, that single formula error moves five figures.

The compliance checks that keep you out of trouble

The calculator computes pay. A certified payroll system also needs to flag problems before the report leaves your office. Add a compliance column that runs tests on each line.

Check that the cash plus fringe actually meets the determination package. In a helper column Q12:

=IF((N12+O12)>=VLOOKUP(C12,$B$4:$E$7,4,FALSE),"OK","UNDER SCALE")

That compares what you are paying against the total package required for the classification. Any "UNDER SCALE" result is a back-wage liability sitting on the report. Catch it now, not in an audit.

Flag suspicious overtime so you confirm it is real before signing:

=IF(M12>20,"VERIFY OT","")

And build a checklist you run before every submission. These are the items DOL investigators check first.

• Every worker is classified by the work performed, not the title on your books.
• Apprentices are registered in an approved program, and the apprentice-to-journeyman ratio matches the program standard.
• Fringe benefits claimed are actually funded into a bona fide plan, or paid in cash on the check.
• Overtime past 40 hours uses the CWHSSA formula, base times 1.5 plus straight-time fringe.
• Deductions are limited to those permitted (taxes, court orders, voluntary authorized items). No "tool rental" or unexplained cash-back deductions.
• The report is signed by an officer with authority and submitted within seven days of the pay date.
• The payroll number is sequential, and the last report for the job is marked "Final."

Apprentices and split classifications, where the form gets ugly

Two situations break a naive spreadsheet, and they are exactly the situations DOL flags most.

First, apprentices. An apprentice can be paid below journeyman scale only if registered in a DOL-approved or state-approved program, and only at the percentage of journeyman rate their program step allows. If you run more apprentices than your ratio permits (say one apprentice per five journeymen), the extra apprentices must be paid full journeyman scale. Add a column for program step percentage and compute the apprentice rate as =VLOOKUP(C12,$B$4:$E$7,2,FALSE)*R12 where R12 holds the step percentage. Then run a ratio check against your journeyman count for that classification. If the count of apprentice lines exceeds the allowed ratio, flag the overflow worker to journeyman scale.

Second, split classifications. A worker who frames in the morning and digs in the afternoon is a carpenter for those hours and a laborer for the rest. The WH-347 wants two lines, one per classification, with hours and rates split. Do not blend them into one average rate. Build the grid so the same name can appear on two rows with different classifications in column C, and the VLOOKUP handles the rest automatically. The total hours across both lines should reconcile to the worker's timecard.

Where the spreadsheet ends and the system begins

You can build the WH-347 calculator from the formulas above in an afternoon, and for a single classification on a small job that is enough. The trouble starts when you have eight classifications, a few apprentices on a ratio, fringe paid partly in cash and partly into two different plans, and four federal jobs running at once. At that point the loose spreadsheet becomes its own risk, because every job needs the same logic and one broken formula propagates into every report.

The SheetCraft Construction Budget Tracker includes a certified payroll module with the wage determination lookup, CWHSSA overtime formula, fringe tracking, and the under-scale and apprentice-ratio flags already wired in. It maps the weekly grid straight to WH-347 columns so the form output matches the DOL layout, and it ties labor cost back into your job budget so prevailing wage work shows its true burden on each project, not just on the payroll report. If your current process is one person copying numbers into the DOL PDF on Friday afternoon and hoping the fringe math holds, that is the spreadsheet you want open instead. The back wages on a single misclassified worker cost more than the template, and that is before the contracting officer freezes a payment.