By far, this is a common call we get in support and even though we see so many, it is a hard one to solve, that is why you call me??! I don't have a magic wand that I can say Employee 123 is the problem because on April 23 you did a manual check...etc etc...and that is what you are all looking for! :-)
I have thought a lot about this and like other blogs I have done, lets try to compile a comprehensive list of items you can check on / for and maybe this will help you in making your numbers tie to the penny so the auditors are not after us at quarter/year end time.
1. Probably the biggest reason is these two reports do not pull from the same tables, easy enough.
The 941, how I like to say it calculates on the fly, just like your W-2 does.
Gross - TSA deductions + Taxable Benefits (that is why they match a lot of the time)Where as your Payroll Summary pulls from the UPR30100 Check history table and that is populated at the time the payroll was processed.
Here is a common example which makes those two off.January 1, you created a deduction (50.00) that was NOT sheltered from tax, ran a payroll and was paid 500.00. All your gross, federal and FICA is all showing 500.00 taxable wages.Then you realize that should have been TSA (tax sheltered), oops, you call support, no worries, here is a script you change it, cool we are good to go.At this point if I now run the 941 it will show 450 (500-50=450) calculated on the file how the record looks today.The payroll summary will show 500 as at the time of the payroll the record was not sheltered.Now you can see come quarter end how this could be a problem with your reports.
2. Andrea has a great blog of how the 941 is calculated in detail, line by line.
3. You can run the reconcile process in Dynamics GP, this will compare summary (UPR00900) to history (UPR30100) to make sure they are in synch. They should be, but sometimes if there was a posting interruption, the UPR30100 is typically the last table to get updated and missed.Tools | Utilities | Payroll | Reconcile - just mark the box to print the report and not the actual reconcile.
4. The 941 nor the W2 will pull the amount from Federal Wages field under Cards | Payroll | Summary window.
5. Did you do a manual check to adjust and forgot to update employee or employer wages? Here are some scripts to help identify the problem employee. (Audit trail code UPRMC)
This is a script by employeeSelect GRWGPRN, FICAMWGP, FICASSWP, FDWGPYRN, EFICASSWH, FCASWPR, EFICAMWPR, FICAMWPR, FDWDGPRN, * from UPR30100 where year1='2019' and employid ='acke0001'
This is a script that will give you all your manual checks for the year
Select GRWGPRN, FICAMWGP, FICASSWP, FDWGPYRN, EFICASSWH, FCASWPR, EFICAMWPR, FICAMWPR, FDWDGPRN, * From UPR30100 where AUCTRLCD like 'uprmc%' and year1='2019'
This will review your manual checks and tell you if something does not match.
select * from upr30100 where fcaswpr <> eficasswh and AUCTRLCD like 'uprmc%' and year1='2019'
select * from upr30100 where ficamwpr <> EFICAMWPR and AUCTRLCD like 'uprmc%' and year1='2019'
**A column that has an E in front of it stands for Employer amounts**
6. It does not hurt to create the year end wage file and verify your numbers each quarter. Then we know we tied out in Q1 verses trying to tie out all four quarters in December. Make sure you are not running payroll, then create the year end and review your numbers/ reports. Once you are completed, make sure to remove the year end - Remove the Year-End Wage file for the appropriate year (Microsoft Dynamics GP >> Tools >> Utilities >> Payroll >> Remove Year End Information).
7. When you have the year end wage file created in number 6, I would recommend you to print the validation report in the print W2 window. Sometimes you will have an employee that has an error such as: FICA/SS Tax W/H of 615.92 <> calculated of 915.92This will narrow down the search as we know this employee has a problem of $300.00 which tells us the withheld or wages were not updated correctly as we are taking your Social Security wages * .062 to = withheld.
8. Another item when we have the year end wage file created, this below script compares the accumulated amount with the amount in the history tables (where your payroll summary report pulls from) this may trigger an employee to kick out with our difference.
select * from (select EMPLOYID, (Federal_Wages_1+Federal_Wages_2+Federal_Wages_3+Federal_Wages_4
+Federal_Wages_5+Federal_Wages_6 +Federal_Wages_7+Federal_Wages_8+Federal_Wages_9 +Federal_Wages_10+Federal_Wages_11+Federal_Wages_12) FedWages from UPR00900
where year1='2019') PaySum inner join (select EMPLOYID,wgtpcomp From UPR10101
where rptngyr='2019') Wages on PaySum.EMPLOYID = Wages.EMPLOYID where PaySum.FedWages <> Wages.WGTPCOMP
9. A couple times I have seen we have a duplicate audit trail code in the UPR30200 and this too could cause your 941 to be off.Tools | Utilities | Payroll | Edit Liabilities.
10. If the 941 Schedule B is off, this pulls directly from the UPR30200 table. Tools | Utilities | Payroll | Edit Liabilities (You can edit this window)
11. Typically when I work with customers they know in their head Gross all my pay codes minus these 3 deductions this should be my taxable wage. What if accidently an employee is not taxed correctly at the employee level for a pay code, deduction or benefit. A lot of times we want them exactly like the setup, but we all know you can change them at the employee level and this could have accidently happened and causing our reports to be off.
Below are some sample scripts that compare your setup to the employee and kick out which employees are NOT set up the same to setup, those may be our instigators in causing our report to be off?
select A.PAYRCORD, A.TAXABLE, A.EMPLOYID from UPR00400 A INNER join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.TAXABLE<>B.TAXABLEselect A.PAYRCORD, A.SBJTFDTX, A.EMPLOYID from UPR00400 A inner join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.SBJTFDTX<>B.SBJTFDTXselect A.PAYRCORD, A.SBJTSSEC, A.EMPLOYID from UPR00400 A inner join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.SBJTSSEC<>B.SBJTSSECselect A.PAYRCORD, A.SBJTMCAR, A.EMPLOYID from UPR00400 A inner join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.SBJTMCAR<>B.SBJTMCARselect A.PAYRCORD, A.SBJTSTTX, A.EMPLOYID from UPR00400 A inner join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.SBJTSTTX<>B.SBJTSTTXselect A.PAYRCORD, A.SBJTLTAX, A.EMPLOYID from UPR00400 A inner join UPR40600 B on A.PAYRCORD=B.PAYRCORD AND A.SBJTLTAX<>B.SBJTLTAX
select A.DEDUCTON, A.TXSHANTY, A.EMPLOYID from UPR00500 A INNER join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.TXSHANTY<>B.TXSHANTYselect A.DEDUCTON, A.SFRFEDTX, A.EMPLOYID from UPR00500 A inner join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.SFRFEDTX<>B.SFRFEDTXselect A.DEDUCTON, A.SHFRFICA, A.EMPLOYID from UPR00500 A inner join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.SHFRFICA<>B.SHFRFICAselect A.DEDUCTON, A.SHFRSTTX, A.EMPLOYID from UPR00500 A inner join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.SHFRSTTX<>B.SHFRSTTXselect A.DEDUCTON, A.SFRLCLTX, A.EMPLOYID from UPR00500 A inner join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.SFRLCLTX<>B.SFRLCLTXselect A.DEDUCTON, A.SHFRFICAMED, A.EMPLOYID from UPR00500 A inner join UPR40900 B on A.DEDUCTON=B.DEDUCTON AND A.SHFRFICAMED<>B.SHFRFICAMED
select A.BENEFIT, A.TAXABLE, A.EMPLOYID from UPR00600 A INNER join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.TAXABLE<>B.TAXABLEselect A.BENEFIT, A.SBJTFDTX, A.EMPLOYID from UPR00600 A inner join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.SBJTFDTX<>B.SBJTFDTXselect A.BENEFIT, A.SBJTSSEC, A.EMPLOYID from UPR00600 A inner join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.SBJTSSEC<>B.SBJTSSECselect A.BENEFIT, A.SBJTMCAR, A.EMPLOYID from UPR00600 A inner join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.SBJTMCAR<>B.SBJTMCARselect A.BENEFIT, A.SBJTSTTX, A.EMPLOYID from UPR00600 A inner join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.SBJTSTTX<>B.SBJTSTTXselect A.BENEFIT, A.SBJTLTAX, A.EMPLOYID from UPR00600 A inner join UPR40800 B on A.BENEFIT=B.BENEFIT AND A.SBJTLTAX<>B.SBJTLTAX
As we approach Q1 in 2019, I hope you are able to use this information to identify the issue quicker and spend less time reconciling reports.