No having to rely on samples allowed us to perform audits more efficiently and effectively. The audit results were more easily defendable (no arguments about the representativeness of the sample and the validity of the extrapolation). It also supported better coverage and more comprehensive audits.
Vacation audit – the company allowed employees to carry forward vacation days – but different unions had negotiated different amounts. Depending on the union to which they belonged, some employees were only allowed to carry forward one year’s worth of vacation credits while others were allowed to carry forward up to five year’s worth. As part of an audit of vacation leave, the VP of Finance was interested in determining the liability (vacation days * daily pay rate) and if employees were carrying more vacation days than permitted.
Using HR data, we were able to determine the union and years of service – which gave us the vacation entitlement. The calculation was a little complex because the vacation entitlement depended on years of service and the union to which the employee belonged. Next, we calculated the balance (entitlement – usage) for each employee. Finally, we calculated the liability (Balance * daily salary rate).
The analysis identified a large number of employees who were carrying more than the maximum vacations days they were allowed and determined that the financial liability had been significantly understated.
Payroll audit – the company had more than 40 different job categories and each had several pay levels (e.g. Machinists salary had five rates depending on the experience level of the employee). Employees would automatically move up a salary level on their anniversary date and the rates also changed based on union negotiations – usually once every 3-5 years. The manager in charge of pay was concerned about people being paid more than their appropriate salary level.
With thousands of employees across multiple locations – managed by regional pay centers – and the many job categories and levels, a sample of pay records would not be representative. Instead we obtained the pay file for the most recent pay period and took a snapshot of the HR data base. The HR data gave us the job category and level, the effective start date, and the current pay-per-period. The pay data gave us the current gross pay.
A simple Classify by Job Category and Level – allowed us to determine not only the number of people in each job category and level, but also the Total pay. Using the statistics option – gave us the minimum, maximum and average pay for each category and level. Given the pay structure – the minimum and maximum pay for each category and level should be the same – there was only one pay rate for each category and level. However, the results identified more than 20 employees who were being paid more than the other employees at the same category and level. Twelve were “red circled” meaning that they had been in a higher paying position and when they job category/level changed they were allowed to retain their previous pay rate. This left eight employees who were being overpaid. In particular, it identified a Clerk level 2 who was being paid $162K – the maximum salary for a clerk level two was $38K. The follow up determined that a manager who was not allowed to hire a consultant because of restrictions on his budget for professional services had used his salary budget instead. Using management override, he ‘hired’ the consultant, and declared the new “employee” as ‘red circled’ which allowed him to manually enter the salary rate.
The analysis also identified an ‘unknown’ category/level. The HR specialist category was supposed to have 4 levels, but one of the employees was being paid at level 5.
Finally, we used the effective start date and start category/level to determine the current level. This identified 14 employees who were being underpaid and nine employees who were being overpaid. An analysis of the exceptions by region determine that of the 32 pay errors, 21 were in one region – the other 11 were spread over 4 regions. Also, a particular pay clerk was involved in all of the overpayments in that region.
HR audit – the company had a lot of seasonal workers and the Manager responsible for staffing wanted to be sure that hiring for these positions was fair and transparent. There were rules around competitive staffing and, in particular, nepotism was strongly discouraged. The audit obtained the HR data for the last batch of hiring and compared the employee information to existing employees. After cleaning the address and phone number fields, the analysis identified 41 seasonal employees who had been hired by relatives – against policy.
ACL Commands – FILTER, STATISTICS, CLASSIFY, RELATE, and EXPRESSIONS
Lesson-Learned – sometimes the physical separation of data (HR and Payroll) weaken the controls. We identified instances where the payroll data had been modified with affecting the HR data and vice versa. In most the cases this was done using management override – in one case the user id of manager who had left the company three before was being used by a payroll clerk. It is important to delete user ids when people leave or change positions. As a result of this finding, we did a quick analysis to identify all user ids not belonging to current employees.
We were told by HR that the calculation of pay rates and vacation entitlements was performed manually by the regional pay clerks and was too complicated to be performed, or even tested, electronically. A few years back they had tried to build a program that would produce exception reports and had failed. What they had failed to realize was that technology had changed and the analysis capabilities had improved. That had tried to develop a COBOL program to perform the analysis. ACL provided us with a much more powerful analysis capability which made the analysis much simpler.