Transcript Beyond Number Crunching Best Practices in Data Analysis

Want to Find Payroll Anomalies?
Using ACL to Conduct a
Risk-Based Payroll Audit
2005 N.A.L.G.A. Conference
Gerald Schaefer, City of Atlanta
ACL at the City of Atlanta
Gerald Schaefer, Financial Systems Audit Manager
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
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
Background Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
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?
Old Fashioned Audit Work
 Are controls sufficient to prevent fictitious employees from
being added to the city’s payroll? ACL
We used ACL for two purposes:
To verify the identities of employees
To identify payroll anomalies
Background
Background Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
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: DON’T DO IT!!!!!
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
Background
Background Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
Employee Verification Criteria
Risk Level
Queries
High (10 points)
SSN not listed, name & SSN
mismatch, no withholding, no fed
Medicare, no deductions,
duplicate accounts, duplicate
addresses
Medium-High (5 points)
SSN & gender mismatch, SSN &
DOB mismatch, duplicate position
numbers, duplicate SSNs, vendor
address, abolished position
Medium (3 points)
PO box and direct deposit, nonGeorgia address, city hall address
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
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.
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
Verifying Employees
This is the form
we used to
conduct the
employee
verification
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
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…
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee
Employee Verification
Verification
Data Validity
Anomalies
Lessons Learned
Payroll Anomalies Overview





City uses PeopleSoft 7.62
Scope of our review was January 2002 through July
2004
Over this time frame there were  561,404 records
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
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
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”
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
Sample Results
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
Data Validation
ACL Command
How to Get There
Verify
Data—Verify—Verify Fields
Statistics
Analyze—Statistical—Statistics—Statistics
On..
Classify
Analyze—Classify—Classify On…
Count
Analyze—Count Records…
ISBLANK( string )
Edit View Filter—Functions
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
Data Validation-Verify
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
Data Validation-Statistics
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data
Data Validity
Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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”
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
Terminated Employees
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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`)
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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.
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
Yikes!
$317,534
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
What?!
$227,620.14
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
413.93
413.93
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
400 hours
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Lessons Learned
Payroll Anomalies
Negative leave balances
Negative Balance
Number of Employees
Dollar Amount
Compensatory Time
386
$127,304
Vacation
41
$17,885
Sick
300
$444,673
Reserve Sick
21
$11,392
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
Payroll Anomalies
Negative leave balances of employees who were terminated
Negative Balance
Number of Employees
Dollar Amount
Compensatory Time
50
$29,164
Vacation
11
$5,877
Sick
21
$36,570
Reserve Sick
1
$89
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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
Background
To 724 employees
Introduction
Employee Verification
Data Validity
Totaling $469,071
Anomalies
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Employee Verification
Data Validity
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Anomalies
Lessons Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons
Lessons Learned
Learned
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
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons
Lessons Learned
Learned
Questions
For further information, contact:
Gerald Schaefer
[email protected]
404.330.6876
Y’all come down and see us!
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Muchas Gracias!!!
Thanks for
Your Time!
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned
Background
Introduction
Employee Verification
Data Validity
Anomalies
Lessons Learned