How to reconcile Excel with a payroll system?
I had a past student bring in a problem spreadsheet that they could not reconcile with their Quickbooks Payroll totals. This student had payroll data in an Excel spreadsheet and then entered employee data into Quickbooks to cut checks. At the end of every month the totals in Quickbooks and Excel were off slightly. I was asked to help find the problem and suspected the problem was related to decimal precision.
Importance of Decimal Precision in Excel
I changed the names and actual payroll data from real data for privacy however the following example should help show the problem. We are going to look at two users John and Mike. In the screen shot below each user worked 40 hours. The totals do not differ formatting the currency amounts as two or four decimals.
Now lets say that John worked an extra 30 minutes on one day of the week so he worked 40.5 hours. If the currency is formatted as two decimal places Excel will round up to nearest penny however, formatted as four decimal places, the exact amount will be shown. John actually earned half a penny less as shown in the example below .
Another possible cause of discrepancy
The decimal precision can also cause a discrepancy in the case that there is a user that has a pay rate that ends in an odd number of cents, works overtime and gets paid time and a half. For example, Johns pay rate is $15.21 but at time and half his rate is $22.815
If John works 5 hours overtime and received a separate check just for his overtime what should he get? The screen shots below show the exact amount John should receive.
If your spread sheet is using 2 decimal places then again he actually earned half a penny less than what he would get in his check. Adding Mike back in and lets say they both make the same rate. NOTICE: below that at 2 decimal places John and Mikes pay is rounded to $935.42 but the total for both users is $1870.83. You may notice that $935.42 + $935.42 should equal to $1870.84. Excel calculated the correct amount behind the scenes but visually shows 2 decimal places because of the rounding.
Problem Found with Decimal Precision
In this case the user would look at the Excel data with two decimal precision and then enter the amount in Quickbooks. When the data is entered each user is paid $.005 more per week then they really should. This may not sound like much but this is only for 2 users. At the end of every week the payroll would be off by $.01 per two users. Imagine if this company had 10000 users and there were even 10% of them (1000 users) that this affected. That would be (1000 x .005) or $5 per week or $260 ($5 x 52 week) per year. The payroll spreadsheet would be correct as Excel properly accounts for the $.005. But because the data was manually entered into Quickbooks there is seemingly missing money.
This business did not have 10000 users but the Quickbooks reports and the Excel reports were still not reconciling. The missing cents each month were written off because the payroll clerk could not figure out the problem however most larger companies would question where the missing money went. I advised the student to adjust the decimal places visible to ensure data is copied into the payroll system correctly. The Excel worksheet and Quickbooks reports would reconcile properly after this was updated.
I hope this helps some of my past, present and future students. Please post any questions or comments below. See more of my Excel Tips and Tricks.