In my first post on payroll reconciliation, I suggested a good reconciliation for small payrolls (up to 150 employees). For payrolls of a larger size, up to 3000 employees, you may want to use the “Compare” method.
For this, you’ll need some reports, possibly custom, from your payroll system. These reports will detail earnings by type, deductions by type, with amounts for each type. When the report is created, you’ll need to create a workbook that compares the current pay period to the prior one. Use one worksheet for the earnings and one for the deductions.
See the example below:
|Earnings||Prior Payroll||Current Payroll||Difference||Reason|
|Regular||92,625.00||100,285.00||7,660.00||New Hires Jones, Smith, Williams|
|Straight Time (ST)||12,625.00||13,650.00||1,025.00|
|Overtime (OT 1.5)||15,000.00||16,000.00||1,000.00|
|Double Time (OT 2.0)||2,500.00||2,600.00||100.00|
|Holiday Pay||–||54,269.00||54,269.00||GF holiday|
|Sick Pay (Union)||875.00||875.00||–|
|Vacation Payout||1,200.00||2,000.00||800.00||George (sep 4/6/16)|
|Bonus (Sign-on)||100,000.00||–||(100,000.00)||Remove Jason bonus|
|Shift Premium 1||3,695.00||3,605.00||(90.00)|
|Shift Premium 2||2,500.00||2,250.00||(250.00)|
|Stock||25,000.00||100,000.00||75,000.00||Francis, Bryant, Evans stk updates|
|Referral Bonus||3,000.00||–||(3,000.00)||Remove Smith payment|
|Short-Term Dist||12,000.00||13,500.00||1,500.00||Fredericks, Washington, Kellogg STD|
|Tuition Reimb||–||5,000.00||5,000.00||J. Green Winter 15-16|
|Group Term Life (Imp Inc.)||630.00||678.00||48.00||New Hires Jones, Smith, Williams|
I’ve included some sample types of earnings that would make up the total gross. As you see, each category has a prior entry as well as a current entry. Subtracting the “prior” from the “current” provides the amounts in the difference column.
If you have entered all the changes, your column totals should tie to the actual totals on your payroll register. If your entries don’t tie, you’ll need to go back to your original document (payroll register) and troubleshoot.
Using your own categories, you can create this for yourself. You can easily adapt it to your needs. For example, you can “insert copied cells” (Excel command) to create an ongoing report for each period, with all the prior periods included. Or you can simply compare the current to the immediate previous each period; depending on your company’s requirements, you can make the report as comprehensive as needed.
Make it your own; payroll is important enough that the results should always be transparent to anyone who needs to review or verify the data. In the “Reason” column, include what makes up the differences. All these should be easily located in your payroll register.
As you see in the example above, each category has easily traceable names which will be supported by the appropriate paperwork as a backup. Your auditors will request this verification of expenditures.
There is no explanation given for the differences in straight time, overtime and double-time since those should be expected to vary from period to period. These can also be easily supported by the time reports.
The final type of reconciliation we’ll discuss is the period over period cumulative or the “Roll-Forward” reconciliation. This is a more complex report, but it works for mid- to larger size companies, and can be adapted for divisional reconciliations, as opposed to full-company reports.
In this type of reconciliation, some things we did previously will still hold true. For example, we will still recap earnings and deductions by category. However, instead of comparing the previous to the current payrolls, we will be verifying the current payroll and reconciling to the year-to-date recap from our provider.
Keep in mind that the reason for creating this report is to prove that your payroll’s totals are legitimate, reasonable and can be authenticated. Gather your documents by type, for example, New Hires, Terminations, LOA’s, Salary Increases, etc.
This part of the reconciliation will allow you to reach the Gross Payroll Total, probably your most important payroll item. Create a spreadsheet group with links for each type, going back to your cover or recap page. See the picture below for a sample.
Now, in order to create your reconciliation, note the total by category, then reconcile to the year-to-date on your reports. You may have to dig a little for differences, but keeping this workbook up to date will eliminate most of the problems with any differences.
Example 1 – Recap Totals
Below is a sample of the “Hires” detail that would flow up to the recap above. You can continue this method all the way through in order to verify your current and cumulative payroll totals.
It’s always important to be able to verify that your payroll totals are valid and reasonable. The detail you provide as support will also be helpful to your auditors. They will have proof of your payroll’s accuracy that can be supported by your payroll registers and year-to-date reports. Good Luck!
Example 2 – Hires Detail