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.

Normal Work Week

Normal Work Week

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 .

decimal precision

John worked 30 extra minutes

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

overtime rate

Johns Time and a Half Overtime Rate

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.

overtime calculation

Johns 5 Hours Overtime

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.

2 vs 4 decimal precision

Two users with same rate.

Problem Found with Decimal Precision

manually entered data

Data Manually entered in Quickbooks

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.

Summary

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.