Analysis had proven to be successful in not only supporting the conduct phase of internal auditors but also during the planning phase. More and more we were being asked by audit teams to perform analytics to support the development of the audit scope and objectives. However, there were still team leaders that avoided the use of analytics. “Analytics won’t be of any use in this audit” was still a familiar response to “can we help you?” As part of a sales push, I produced a monthly report describing the new data sets /applications that had been accessed that month. Audits were listed and detailed of the types of analysis, the results, and the benefits of analytics in time, cost, efficiency, consistency, coverage, etc. were highlighted. Typically, six to seven audits were featured each month and the analysis covered HR, IT, operations, finance and administrative audits. But we had to keep expanding our capabilities and what we could access.
The challenge this year was accessing and understanding the legacy pay system which was still used for about 70% of the employees. The application created an IBM 25,000 byte variable block data set for each employee. The first 500 bytes were a fixed block with basic employee information such as name, employee number, address, etc. The remaining portion of each record consisted of a variable number of 50 byte segments that were one of 26 different types – each with its own layout (e.g. type “A” was a regular pay segment which had the paid date, pay rate, and pay amount; “B” was an adjustment record type with the pay date, adjustment amount, and reason; “C” was an allowance record with the pay date, start and end date of the allowance, type of allowance, and amount; etc.). The hardest part of the analysis was building and testing the table layout. But, not only were we able to read the record and produce a fixed block output for further analysis, we were also able to produce the employee’s summary pay information, including the income tax filing data. Currently, this was done manually by the pay clerks because the complicated legacy system had not been updated to reflect changes in tax laws for several years.
We provided our table layout and pay statement script to the Manager of Pay – which he estimated saved them 1,000 hours of work at year-end. This was the first of many times when audit analysis capabilities were transferred to the client area – a big value-add for internal audit. It would be the beginning of continuous auditing (by audit) and continuous monitoring (by management).
The objective of the pay audit was to determine if the manual controls over the special allowances were working properly (i.e. are employees getting the appropriate allowance amounts). There were approximately 50 different allowances that employees could be entitled to receive such as “isolated work location”, “hazardous work conditions”, “shift work”, etc. Certain conditions had to be met and allowances were turned on/off by pay clerks entering start and stop transactions in the pay system. In addition to concerns about the timely starting and stopping of allowance, there were also incompatible allowances (i.e. you can’t get “isolated work location” and “cost of living” allowance because the cost of living was only for employees working in large cities where the cost of living was higher.
By comparing a file with the allowance type, start and end date and amount with the pay system, we were able to verify whether people had received the correct amount for each allowance entitlement. This identified numerous problems with clerks either failing to stop certain allowances or not doing so in a timely manner. There were also a few instances of people not getting allowances for which they were entitled. The total over-payment was $56K for the previous year.
A short time later, a second pay audit was initiated with the objective of ensuring that the control over overtime payments were working; and that employees were being paid the correct amounts. Pay was based upon your job category and level. There were 50 job categories and most had 3-5 levels. Each job category and level was assigned a pay rate. Overtime was based on the number of hours above the standard 40-hour work week and the day of week (Saturday was 1.5 * regular pay; Sunday and holidays was 2.0 * regular pay).
The HR system had each employee’s job category, level, start date, and pay rate. An extract from the HR system was matched to the detailed pay file extract and we compared the pay rates for each employee. The identified 13 employees (all in the same division) who were being paid at two levels higher than their HR record indicated – $87K in over-payments. We also found two employees who were paid after their termination date, an over-payment of $11K.
Examining the overtime payments via a crosstab by employee number and overtime type as the columns highlighted six employees in one department who were consistently taking Fridays off without pay and working overtime on Sundays at double time. This meant that they were being paid the equivalent of 6 days every week for 5 days of work. We also found one employee in a management category that was getting overtime – management was not entitled to overtime. Total over-payment was $38K. So, the total over-payment for the two pay audits was $192K (for three years – $576K).
Analysis – ACL Commands: TABLE LAYOUT; FILTER; CLASSIFY; JOIN; and SCRIPTS
Lessons-learned: no matter how successful the analytics program, constant effort must be applied to maintain it. I was giving training courses, presentations to management and producing monthly reports highlighting the power, flexibility and effectiveness of analytics and still new auditors, and even some auditors who had been with the company for years, did not want to include analytics in their audit program. Even though senior management agreed to include a statement in all managers’ performance agreements that “analytics should be considered for all audits”, they didn’t really follow-up on this when performance reviews were conducted.
Also, sometimes what appears to be complicated data set (e.g. 25,000 byte variable block) may not that hard to access and analyze. The difficulty may only be in the initial definition of the record layouts and fields, and the verification that it was done correctly.
Finally, getting the client area on-board produced a huge value for internal audit – then and in the future. We started providing analytical capabilities to the client area which improved their efficiency and effectiveness. It also increased the view that audit could and did help.