Want to Find Payroll Anomalies? Using ACL to Conduct a Risk-Based Payroll Audit

1. Want to Find Payroll Anomalies?Using ACL to Conduct aRisk-Based Payroll Audit 2005 N.A.L.G.A. Conference Gerald Schaefer, City of Atlanta

2. ACL at the City of Atlanta Gerald Schaefer, Financial Systems Audit Manager

3. ACL at the City of Atlanta • Formerly Internal Audit Division – Dept. of Finance • Now City Auditor’s Office • Independent office; established by City Charter • Report to audit committee • First city auditor hired in October 2001 • Medium audit shop – 11 professionals; 2 support • Inherited former Internal Audit Division staff; reorganized early 2003 to accomplish new mission Background

4. Payroll Audit: Background • Law and Finance Departments identified theft in pension fund by former employee; we did an audit to identify the magnitude of the fraud • Since fraud was discovered in the pension audit our objectives for the payroll audit were: • Are incompatible payroll duties and functions adequately separated to limit opportunities for fraud and abuse? • Are controls sufficient to prevent fictitious employees from being added to the city’s payroll? • We used ACL for two purposes: • To verify the identities of employees • To identify payroll anomalies Old Fashioned Audit Work ACL Background Background Background

5. Ghost Employees • City ordinance mandated that our office conduct a payout to employees • Problematic since the city has over 8,000 employees spread far and wide • Did a survey on the N.A.L.G.A. listserv regarding experiences with payouts—the overwhelming response was: • We did what any audit shop would do—we stole an idea from another city • We decided to visit employees at their work site unannounced and verify their identity DON’T DO IT!!!!! Background Background

6. Employee Verification • We got the following data from our IT department: payroll data; a list of active vendors and addresses; a list of frozen, abolished, and vacant positions • We submitted a list of city employees and their SSN, DOB, and gender to the Social Security Administration • We placed the results from the SSA and the data from IT into ACL and ran twenty different filters (or queries) to identify medium to high risk employees Employee Verification

7. Employee Verification Criteria Employee Verification Employee Verification

8. How We Scored Employees in ACL • Created three tables—high, medium high, and medium by using the ACL command of Extract and Append to existing file • The tables had the following results: high (372 records), medium-high (318 records), and medium (146 records) • Created a score field in each table and set the default value to “10” in the high table, “5” in the medium-high table, and “3” in the medium table • Summarized each table by the field SSN and subtotaled the field of score • Summarized the three tables into one table by the full name field and subtotaled the score again Employee Verification Employee Verification

9. How We Used ACL • Each filter identified between 1 and 146 employees Used the ACL command of Duplicate to identify: • Employees that had payments to more than one SSN • Employees that had the same account but different last names • Employees that had the same address but different last names • Any employee with a score of 10 or more points was included in our sample. • At the end of the audit we had 321 separate tables and the ACL project including the underlying data files was 6.1 GB. Employee Verification Employee Verification

10. Linked Tables to Pay Locations • Identified high risk employees and requested payroll to provide the work locations of all city employees • Related tables by the field pay location to identify where the high risk employees worked • Had to contact some departments directly to identify locations, but always asked for all of the employees at the location Employee Verification

11. Verifying Employees This is the form we used to conduct the employee verification Employee Verification Employee Verification

12. The Verification Process • Our entire office participated in the verification process • Divided the city into four sections and had a team of two auditors assigned to each section • Did it over two days (Tuesday and Wednesday) • If the person was not there, we left a letter instructing them to come to our office to do the verification • Lengthy process: we started the verification process on 12.8.04 and finished on 1.25.05 • For the most part everyone cooperated… Employee Verification

13. What We Found • One employee quit rather than have us verify his SSN • We ran his SSN through some databases and found that his SSN was being used by several people • We also submitted a list of name mismatches to a company to have the names ran against a list of deceased individuals • We found that one former city employee was using the SSN of his deceased mother • We also found another former city employee using a SSN that was used by several people • We forwarded these employees to SSA’s Allegation Management Division Employee Verification Employee Verification

14. Payroll Anomalies Overview • City uses PeopleSoft 7.62 • Scope of our review was January 2002 through July 2004 • Over this time frame there were  • For paychecks we requested 116 fields and for direct deposits we requested 118 fields • All the fields came from PeopleSoft except for the field: “cashed?” which was a yes or no field 561,404 records Data Validity

15. File Review • Right after we got the data from DIT we randomly selected 30 active employees and 30 terminated employees. • For these employees we pulled their payroll file and their human resources file to verify that the information in PeopleSoft, which was the source of our data, was accurate. • We used ACL to do the sample • Sampling—Sample records • Select “Random” under Sample Parameters • Select “Record” under sample type • Enter number of sample in “Size” Data Validity Data Validity

16. Sample Results Data Validity

17. Data Validation Data Validity

18. Data Validation-Verify Data Validity Data Validity

19. Data Validation-Statistics Data Validity Data Validity

20. Using the Fraud Toolkit for ACL • Identifying missing checks and direct deposit advices • ACL steps • Select Fraud Toolkit—Right click on the Start script—Select Run—Click on OK • Select Gaps • Select input file (can apply a filter) • Select maximum number of items to list (We chose 10,000) • Select field to test for missing records • Results appear in ACL log, not as a table • Results • We had: 5,454 missing checks and 975 missing direct deposits • Talking to IT we also identified 4,292 manual checks we did not have Data Validity Data Validity

21. Eye-popping Results • Did we have any positions filled by more than one person? • ACL steps • Select Fraud Toolkit—Right click on the Start script—Select Run—Click on OK • Select Duplicates • Select Input File and Name Output File • Select which fields you want to analyze (can choose up to four) • Select a field, which has to be different from the fields selected in the previous step (optional). We chose employee ID • Results • We found 1,200 instances involving 193 employees in 91 positions for 2 or more pay periods Anomalies

22. Using ACL to Identify Payroll Anomalies • Terminated Employees • Are terminated employees paid after they leave city employment? • ACL steps • Sorted payments in descending order by pay ending date and used the ACL command of summarize on the field of employee ID • Created a unique field in the payments table and in the terminations table. The unique field was SSN + Position number. Related the tables by this field • Created the field of: Days paid after termination: pay ending date – termination date if…termination date > `200020101` • Created the field of: Paid after termination : if…termination date < pay ending date then the value of “Yes” • Created the filter: Paid after termination=“Yes” and Net Pay >0 and Hours >0 and Type2 <> “REG” and Cashed= “Yes” Anomalies Anomalies

23. Terminated Employees Anomalies Anomalies

24. Using ACL to Identify Payroll Anomalies • Compensatory Time • Wanted to identify if any exempt employees received a pay-out of unused comp time when they left city employment • The filter we used to identify this was: (Type_of_Pay2 = "CMP" AND Hours2 > 80) OR (Type_of_Pay3 = "CMP" AND Hours3 > 80) OR (Type_of_Pay4 = "CMP" AND Hours4 > 80) OR (Type_of_Pay5 = "CMP" AND Hours5 > 80) OR (Field_50 = "CMP" AND Field_51 > 80) OR (Field_58 = "CMP" AND Field_59 > 80) • The results of this filter were saved as a separate table and an additional filter was created in this new table, which was: FLSA_Status = "X" AND ( Termination_Date >= `20020101` OR Action_Date>= `20020101`) Anomalies

25. Payroll Anomalies • Overpayments • We ran several filters to identify a large number of regular hours reported in one pay period • We found a lot of mistakes, such as vacation pay-outs being listed as regular hours, employees missing their first paycheck then being paid for two pay periods in one pay period, which resulted in pay for 160 hours • We did identify 7 instances where employees were overpaid $33,535. Anomalies Anomalies

26. Yikes! $317,534 Anomalies

27. What?! $227,620.14 Anomalies Anomalies

28. 413.93 413.93 Anomalies Background Employee Verification Data Validity Anomalies Lessons Learned

29. 400 hours Anomalies Background Employee Verification Data Validity Lessons Learned

30. Payroll Anomalies • Negative leave balances Anomalies Anomalies

31. Payroll Anomalies • Negative leave balances of employees who were terminated Anomalies Anomalies

32. Quick and Dirty Analyses • Identified 13 employees who received 19 payments with the SSN field blank • Used the ACL function of ISBLANK( SSN) • Identified a payment made to the SSN of 999-99-9999 (identified by sorting the field of SSN) • Identified 8 employees with multiple employee IDs by using the ACL function of duplicates on the SSN field after summarizing by the field employee ID • Identified 34 employees with multiple SSNs Anomalies Anomalies

33. Payroll Anomalies • Uncashed payroll checks • In Georgia, any payroll check uncashed for more than one year must be remitted to the state as unclaimed property • We ran a filter that identified all uncashed checks and used the ACL command of summarize on the field of employee id to identify the number of employees • We then used the command of Analyze—age • We identified: 1,156 checks To 724 employees Totaling $469,071 Anomalies Anomalies

34. Payroll Anomalies • Overtime • We identified 307 employees who were exempt under the FLSA but were paid overtime • We identified 297 nonexempt employees who worked less than 40 hours in a week but were paid overtime • We identified 600 nonexempt employees who worked more than 80 hours in a two week period that were not paid overtime Anomalies Anomalies

35. Background Employee Verification Data Validity Anomalies Lessons Learned

36. Anomalies Background Employee Verification Data Validity Lessons Learned

37. Anomalies Background Employee Verification Data Validity Lessons Learned

38. Anomalies Background Employee Verification Data Validity Lessons Learned

39. Quality tests • These tests did not produce any findings but have merit nonetheless • Identified employees who received the biggest increase in salary and reviewed their personnel files to verify that the raises could be documented • Reviewed the supporting documentation for any pay adjustments over $1,000 • Reviewed any payroll payment over $10,000 • Pay-outs of sick leave balances • Check reversals net to zero • More than one payment per period to the same person Anomalies Anomalies

40. Lessons Learned • Don’t be coy—Ask for everything: more detail=less false positives=less work • Do your homework before you start—identify all ordinances, policies, and practices, then look at the data • Dig deeper—We ran the queries, researched the results in PeopleSoft, pulled personnel files, talked to management • Don’t assume anything is done correctly • Trust your instincts—if something does not seem right it probably is not • Keep your mouth shut until you have all of the facts Lessons Learned

41. Lessons Learned Part Deux • When you do complex filters, know the answer before you run the filter and then review results • Always validate that your filter produced what you thought it did by examining the actual data • Meet with management to discuss results and to identify plausible explanations—the sooner the better • Use common sense • Keep your eyes open • Use ACL Support – there is no shame in asking for help Lessons Learned Lessons Learned

42. Questions • For further information, contact: • Gerald Schaefer gschaefer@atlantaga.gov • 404.330.6876 Y’all come down and see us!

43. Muchas Gracias!!! • Thanks for • Your Time!