1 / 41

10 th Anniversary 1999 - 2009

10 th Anniversary 1999 - 2009. Git er’ Done - Streamlining Payroll Auxiliary Processes Presented by: Brad Smith, Payroll Director Dickinson College, Carlisle, Pennsylvania. General Announcements:. Please turn off all cell phones/pagers

samara
Download Presentation

10 th Anniversary 1999 - 2009

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 10th Anniversary 1999 - 2009 Git er’ Done - Streamlining Payroll Auxiliary Processes Presented by: Brad Smith, Payroll Director Dickinson College, Carlisle, Pennsylvania

  2. General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Feel free to ask questions during the presentation Thank you for your cooperation

  3. General Announcements: • Please turn off all cell phones/pagers • If you must leave the session early, please do so as discreetly as possible • Please avoid side conversations during the session • Feel free to ask questions during the presentation Thank you for your cooperation

  4. Info Item 1 • Info Item 2 • Info Item 3 • “First in America” (1783) – First College • Chartered in the United States of America • 2,353 Full-Time Students • Representing 41 States and 40 Countries • Banner user since 2005, Payroll and HR ‘live’ in January, 2006 • 240 Faculty, 10:1 student/faculty ratio • Over 950 FT and PT employees and 1,000 student employees • Payroll has 3 full-time employees

  5. Payroll Auxiliary Processes • EBP!! (Everything But Payroll!!) • Special Requests • Governmental Reporting • Post-Payroll Processing • i.e. Retirement Funding • Pre-Payroll Processing • i.e. Time Collection • Non-Resident Alien Taxation (1042-S)

  6. Items for Discussion • Web Time Entry – Automated Emails • To Web Time Entry Individuals • To Approvers • Post Payroll Request for Approval • Domestic Relations Earnings Report • Quarterly Local Services Tax Return • Pennsylvania Unemployment Compensation – Quarterly e-TIDES files • Retirement Funds Processing • TIAA-CREF • Fidelity • 1042-S Reporting (International Students, Scholars, and Visitors)

  7. Web Time Entry – Git ‘Er Done • Get the Hours Submitted on Time • 500 – 600 Students • 90% of FT and PT Support Staff • Get the Hours Approved on Time • Supervisors • Proxies • Administrative Assistants • What do you do when all else fails? • Payroll Does the Heavy Lifting

  8. Web Time Users - Reminders • Hours Need to be Submitted and Approved by Noon on Monday of the Pay Week. • Users who have not Submitted • Automated ‘Reminder’ at 8 a.m. Monday. • Approvers who have time waiting to be approved. • Automated ‘Reminder’ at 11 a.m. Monday. • Hours Submitted but Not Approved. • Must be Paid • Approved by Payroll • Generated E-Mail to Approver by 1 p.m.

  9. Reminder to User (8 a.m. Monday) • Automated E-Mail • SQL Extract (Wrapped in a Batch Job) • PERJOBH • PERJOBH_STATUS_IND = ‘I’ (count > 0) AND…. • PERJOBH_STATUS_IND = ‘P’ (count = 0) • PERHOUR • SPRIDEN • GOREMAL • PEBEMPL • PTRCALN – Contains the Due Date (Email reminders run only on this date). • NBRJOBS

  10. Reminder E-Mail to Web Time Users Bugs Bunny Bugs

  11. Reminder to Web Time Approvers • Only to Approvers who have time waiting to be approved. • SQL Extract (wrapped in a Batch Job) • PERJOBH • PERJOBH_STATUS_IND = ‘P’ (count > 0) AND … • PERJOBH_STATUS_IND = ‘A’ (count = 0) • PERROUT • PERROUT_APPR_ACTION_IND <> ‘A’ AND … • PERROUT_QUEUE_STATUS_IND <> ‘Q’ • SPRIDEN (for Web Time User) • SPRIDEN (for Approver) • GOREMAL • NBRJOBS • PEBEMPL • PTRCALN (contains the due date for submitted hours)

  12. Reminder E-Mail to Web Time Approvers Monty Python Monty

  13. Approvers Who Do Not Approve • Employees who Submit Time must be paid whether it is approved by their Supervisor or not. • Payroll approves the time • Generates Email to Supervisor who must approve the hours after the fact, or request a pay adjustment. • SQL Extract (Executed by Payroll Staff – from COGNOS Menu) • PERROUH (Approver Information) • PERJOBH (Job Information) • PERHOUH (Hours Submitted) • SPRIDEN (for Employee) • SPRIDEN (for Approver) • GOREMAL • Word Merge to generate Emails

  14. Email to Approver for “Post’ Approval (Student) From: Hillary, Jeanne Sent: Monday, October 12, 2009 3:50 PM To: Campbell, Madelyn Subject: Final Student Payroll Approval - Pay Period 09/27/09 Through 10/10/09 Hello Madelyn, Below is the hours summary for Margaret Borgeson  for the most recent pay period.  We need your authorization for these hours.  If you need further detail please refer to the detailed time sheet in the Employee Self Service application. Please reply with your approval or adjustments. IWS                        -              FWS                       17.00 Thanks! Payroll Department

  15. Email to Approver for “Post’ Approval (Staff Employee) From: Kessler, Lisa Sent: Tuesday, October 13, 2009 10:02 AM To: Shultes, Kenneth Subject: Final Time Sheet Approval Hello Kenneth, Below is the hours summary for Leslie Swartz for the most recent pay period.  We need your authorization for these hours.  If you need further detail please refer to the detailed time sheet in the Employee Self Service application. Please reply with your approval or adjustments. Regular Hours                                 56.00                               Holiday                                 - Overtime – 1.5                                   3.00                               Vacation                               24.00 Overtime – Holiday                          -                                   Sick Leave                                 - Overtime - Emergency                  -                                   Floating Holiday                        - Overtime – Straight                        -                                   Emergency Leave                     - Jury Duty                                              -                                   Emergency Closing                   - STD – Full                                            -                                   Funeral                                -                              STD – Half                                            - Thanks! Payroll Department

  16. Domestic Relations Earnings Report • Required to be returned as soon as possible. • Legal requirement. • Six Months worth of Gross Pay • Includes Detailed Deductions and Net Pay • Summarized by Month • Includes Employee Demographic and Job Information. • Time to Produce this Report down to 1 – 2 minutes from 45 – 60 minutes.

  17. Domestic Relations Earnings Report • SQL Extract • SPRIDEN • SPRADDR • SPBPERS • PEBEMPL • PHRHIST • NBRJOBS (or ODSMGR_PAYROLL_EMPLOYEE_POSITION) • PHREARN (or ODSMGR_DC_PEARNHX) • PHRDEDN (or ODSMGR_PAYROLL_DEDUCTION) • Formatted using COGNOS (could use any reporting tool)

  18. Domestic Relations – Finished Report

  19. Local Services Tax – Quarterly Report • Dickinson deals with the Capital Tax Collection Bureau • Specialized form that they send us to be completed. • Requires each employee to be listed with their address, SSN, and the amount withheld during the Quarter. • Includes Employer specific information.

  20. Local Services Tax – Quarterly Report • SQL Script (Executed by Payroll Staff) • SPBPERS • SPRIDEN • PHRDEDN • PTRCALN • SPRADDR • Report is Formatted using COGNOS. • Used to take 2 – 3 hours to complete this report • Cut and pasting from Report Output • Formatting in Excel. • Now report is produced in 1 -2 minutes (including reconciling to quarterly control totals.

  21. LST Quarterly Report – Finished Product

  22. Pennsylvania Unemployment CompensationQuarterly e-Tides File • Must be filed each Quarter with the State of Pennsylvania. • May be transmitted and funded electronically via the PA State e-TIDES portal. • Detail file listing each employee, gross earnings, taxable earnings, and weeks worked. • Summary file of totals and the number of employees working on the 12th of each month. • Files are submitted separately on the e-TIDES application. • The Detail SQL also creates an Excel file for review.

  23. Pennsylvania Unemployment CompensationQuarterly e-Tides File • SQL Extracts – Formatted as comma-delimited text files. (Executed by Payroll) • Detail Extract • SPBPERS • SPRIDEN • PHRHIST • PHRDEDN • Summary Extract • PHRDEDN • PTRCALN (find the payrolls that include the 12th of the month)

  24. PA-UC Detail File 21-08230R,165883,239431384,Aarons,Paula,M,7750.02,0.00,13 21-08230R,166660,460556968,Abromitis,James,M,7272.00,0.00,12 21-08230R,136119,175624307,Acri,Joanna,M,575.00,0.00,2 21-08230R,165604,190748935,Acuna,Keven,U,1687.73,0.00,12 21-08230R,125813,315182434,Acuna,Max,,3428.40,0.00,12 21-08230R,134422,341182131,Acuna,Rosario,,4672.82,0.00,12 21-08230R,126754,026626980,Adams,Alisa,M,4039.99,0.00,7 21-08230R,94369,202366245,Adams,Barbara,Ann,5691.00,0.00,12 21-08230R,163118,231636221,Adams,Brandon,T,444.70,0.00,6 21-08230R,445,168486303,Adams,Carol,A,9325.02,0.00,13 21-08230R,130788,187600935,Adams,Sherry,K,796.13,0.00,4 21-08230R,125272,197362752,Adams,Susan,J,5304.00,0.00,12 21-08230R,398,164304435,Adler,Betty,L,1967.45,0.00,12 21-08230R,181600,195684993,Aldin,Gregory,R,6750.00,0.00,13 21-08230R,2329,116526230,Aldrich,Mark,C,19950.00,0.00,13 21-08230R,1144,479403831,Allan,George,J,642.86,0.00,2 21-08230R,166487,139822963,Alnor,Eric,M,11250.00,0.00,13 21-08230R,139562,168483435,Alonzo,Susan,M,4920.00,0.00,12 21-08230R,158479,469371237,Ambwani,Suman,,15700.02,0.00,13 21-08230R,125253,204343530,Andrew,Marilou,,1599.00,0.00,12 21-08230R,481,171340841,Andrew,Robert,N,14212.50,0.00,13 21-08230R,133943,614587888,Aoto,Hiroe,,7500.00,0.00,13 21-08230R,691,186249259,Armolt,Betty,A,3642.80,0.00,12 21-08230R,846,200368449,Armolt,Ricky,L,16924.98,0.00,13

  25. PA-UC Summary File 21-08230R,0.000000,973,948,992,10909292.61,6548.19,0.00,0.00 • Columns: • PA State ID • Tax Rate • Number of Employees on 12th of 1st month of the Quarter • Number of Employees on 12th of 2nd month of the Quarter • Number of Employees on 12th of 3rd month of the Quarter • Total Gross • Total Taxable Wages • Total U.C. Tax

  26. Retirement Funds Processing • Dickinson uses 2 Providers • TIAA-CREF • Fidelity • Regular Retirement • Emeriti Funds • Employee may contribute to both providers • Employer may contribute to both providers for an Employee, based on the Employee’s election. • Retirement must be transmitted and funded no later than the pay date. • Providers have their own formatting requirements.

  27. Fidelity Processing • There are 3 separate plans. • Each plan requires it’s own file. • Submitted and funded via the Fidelity Web Portal. • On-line file correction and re-submission. • SQL script wrapped in a Banner Process. (DC_HRETCON) • Multiple Record Types for Each Employee • Tables required vary by record type • PHRDEDN • SPRIDEN • SPBPERS • PTRCALN • SPRADDR • PEBEMPL

  28. TIAA-CREF Processing • Employer and Employee Contributions • A single file contains all plan contributions. • Submitted and funded via the TIAA-CREF Web Portal. • SQL script wrapped in a Banner Process. (DC_HRETCON) • One Record for Each Employee • Tables • PHRDEDN • SPRIDEN • SPBPERS • PTRCALN

  29. Retirement Banner Process (DC_HRETCON) Enter an ‘F’ or a ‘T’ Plan Type (for Fidelity ONLY)

  30. DC_HRETCON • Executed by Payroll personnel immediately after Payroll is at Disposition 70. • Deduction Registers are run from COGNOS • DC_HRETCON produces the formatted files and stores them in the Payroll Network folder • Payroll uploads the Fidelity and TIAA-CREF files via the Web Portals. • Payroll funds the retirement accounts via Wire Transfers.

  31. TIAA-CREF Example File K3910100 239431384 00090420000000000000000000000000000 00000001015200906031966Aarons,Paula M K3910100 168486303 00108790000000000000000000000000000 00275001015200903181955Adams,Carol A K3910100 116526230 00232750000000000000000107000000000 00235001015200902211959Aldrich,Mark C K3910100 139822963 00056250000000000000000025000000000 00000001015200902201978Alnor,Eric M K3910100 469371237 00176170000000000000000000000000000 00000001015200908201981Ambwani,Suman K3910100 171340841 00165810000000000000000010000000000 00000001015200902131945Andrew,Robert N K3910100 200368449 00197460000000000000000280000000000 00000001015200901081956Armolt,Ricky L K3910100 302446207 00236250000000000000000000000000000 00687501015200903311962Arndt,Theresa S K3910100 217941737 00209710000000000000000166670000000 00000001015200911061971Arnold,Thomas M K3910100 043846871 00176170000000000000000000000000000 00000001015200903031977Arsenault,Todd M K3910100 102404164 00245000000000000000000500000000000 00000001015200903121950Arthur,Mary F K3910100 197548393 00186960000000000000000140000000000 00000001015200905121965Bair,Sarah D K3910100 591055780 00176170000000000000000000000000000 00645831015200910251976Ball,David M K3910100 605106198 00182880000000000000000125000000000 00000001015200906111972Ball,Jeremy R K3910100 198624189 00137500000000000000000000000000000 00050001015200908071969Ballew,Nora K K3910100 527021807 00214250000000000000000250000000000 00000001015200904271957Barber,Teresa A K3910100 205629491 00125000000000000000000000000000000 00000001015200902031968Barone,Angela S K3910100 557567189 00288170000000000000000833000000000 00208001015200902181942Barone,Charles A K3910100 181669087 00117980000000000000000000000000000 00000001015200908291973Barrick,Kristine K3910100 185683003 00104170000000000000000000000000000 00050001015200911281977Bartlow,Susannah R

  32. Fidelity Example File 66640BL21 179-52-0896 01ELLIS KELLY J 000000000MF 66640BL21 179-52-0896 02D 0103200500000000000000000816196800000000 66640BL21 179-52-0896 03P011140 Redwood Dr 66640BL21 179-52-0896 04PCARLISLE PA 17013 66640BL21 179-52-0896 0000020 220 301 0000000000500 00000000000000 66640BL21 198-64-1584 01HEBERLIG RANDOL P 000000000SM 66640BL21 198-64-1584 02D 0201199600000000000000000902197100000000 66640BL21 198-64-1584 03P011834 Mary Ln 66640BL21 198-64-1584 04PCARLISLE PA 17013 66640BL21 198-64-1584 0000020 220 301 0000000000500 00000000000000 66640BL21 204-52-1920 01ELLIS EDWARD E 000000000MM 66640BL21 204-52-1920 02D 0506200500000000000000000130196100000000 66640BL21 204-52-1920 03P011140 Redwood Dr 66640BL21 204-52-1920 04PCARLISLE PA 17013 66640BL21 204-52-1920 0000020 220 301 0000000001500 00000000000000

  33. 1042-S Estimates and EOY Production • Required to be Filed at Year-End • Must make periodic estimates/deposits to avoid penalties • Non-Resident Aliens • Unqualified Scholarships and Grants • Prizes • Awards • Need to Query • Accounts Payable • Student Accounts

  34. 1042-S Estimates and EOY Production • Manual Procedure took 5 weeks in 2008 to produce 100 1042-S reports for 2007 taxes. • Receivable and Student Accounts records were reviewed and compiled manually. • 1042-S statements were sent out on March 14th. • During 2007 and 2008 deposits were complete guess-work since no real estimate could be obtained. • January 2009 – Used automated (COGNOS – driven) SQL extract for 1st time. • 1042-S statements were sent out on January 19th. • Total time to complete was 4-days. • Review • Compile • Approve

  35. 1042-S Estimates and EOY Production • During 2009 • Prepared Estimate using this system every 2 months – completed in 4 Hours. • Deposits to IRS reflect actual tax liability of scholarships, grants, etc. • Background work will be completed prior to end-of-year processing. • Estimate 2 days to complete EOY work in January, 2010.

  36. 1042-S Estimates and EOY Production • SQL Extract • Wrapped in COGNOS • Using ODS • Receivable_account_detail (Student Account) • Specific Detail Code Groups • Transaction_history (Accounts Payable) • Specific Accounts and Rule Classes • DC_GNAME (Demographic Information) • ODSMGR.PERSON • Creates an Excel file • Detail Worksheet by Person • Summary Worksheet

  37. 1042-S Detail Worksheet

  38. 1042-S Summary Worksheet

  39. 1042-S Finished Product • Additional Columns Added • Citizenship Country (this will be automated) • Treaty Information (Treaty Benefits vary based on country and type of Earnings) • Taxable Amount adjusted for Treaty Benefits • Comments/Notes • Portion of Personal Exemption • Net Taxable (Adj. Taxable Amount – Personal Exemption) • Tax Amount (14% or 30%)

  40. 1042-S Finished Product

  41. Thank You for Attending PA-BUG! • Questions • Comments • Contact Information • Brad Smith • Dickinson College • Director – Payroll • 717-245-1157 • smithb@dickinson.edu

More Related