Following on the success achieved by the development of the standard extract of financial data from SAP, I decided to design and develop a standard extract from PeopleSoft – our company used SAP for finances and PeopleSoft for HR and another package for Pay (don’t ask why). For HR data, we really only needed three files to address most of the audit requirements:
- Person – providing information on the employee such as gender, date of birth, start date, job classification, position number, etc.)
- Position – providing information on the position such as position number, title, security clearance, level, etc.
- Dept – providing information of the department such as title.
Since it was a snapshot of the data base and not transactions, I extract the information of a quarterly basis. Also, if current data was required, I could take a snapshot on any day.
The data was meant to support various audits that required information on personnel as well as payroll audits (e.g. compare actual pay to the HR salary levels). I also produced summaries to provide trend analysis and identify risks. This include: employment equity percentages; percentage that could retire within two years; percentage of employees in acting positions; percentage of vacant position; average turnover, etc.
Once again I ran afoul with lawyers and my authority to access personal information. This despite the fact our audit charter clearly stated that we had “unfettered access to any and all information required”. This issue was exacerbated by the fact that my HR extracts from PeopleSoft were not linked to a specific audit. The issue was compounded by the government requirement that personal information only be used for the purposes for which it was collected. Apparently, the PeopleSoft data – when collected – did not indicate that it would be used for “audit purposes”. When confronted with this fact, I realized that for pay, travel and entertainment, health claims – basically all personal data being collected by numerous systems – “audit” was never identified as a “purpose”. It seemed ridiculous, but the law meant that audit couldn’t have personal information, such as the employee name and number, in order to perform a payroll audit because the pay system stated that personal information was collected “to pay employees and produce income tax reports”. The system did not state that the personal information could be used for “audit purposes”.
I was confused as I had never heard of this “consistent use” requirement before – turns out it was a new law to protect personal information. At first I thought that I would have to go through each system and request a change – a lengthy process involving the system owner, lawyers and who knows how many other people – to each and every system we used that contained personal information. But, while my manager thought about the effort involved in even trying to do this, I had an epiphany – we could write an overarching statement that would say that all personal information collected by the company could be used for audit purposes. (I was careful not to say “used for audits” since I wanted to identify and assess risks by looking at data on an ongoing basis (i.e. not tied to a specific audit). The main system owners, such as PeopleSoft, had no problem – it was less work for them – and even the lawyers presented very little argument. As a result, internal audit now have a clear statement in place that supported their authority to access and use personal information.
Audits: in addition to obtaining access to PeopleSoft and developing a mechanism to support audit’s access to personal information, I also supported several audits with analytics.
For an audit of fleet cars, we accessed the credit card data. Each car had its own credit card to be used for gas and automotive repairs. The transactional data included the number of gallons of gas, the fuel type and octane level, the price per gallon, date, etc. It was easy to identify credit cards that had been used several times in a day (some were being used 3-4 times within 5 minutes). This lead to a fraud investigation which included a stake-out and resulted in several employees being charged with fraud when they used the company credit card to fill not only the company car, but also their spouse’s and children’s cars. We also identified cars with n abnormal number of repairs and replacement parts (e.g. three mufflers within 6 months; two sets of tires within 3 months). A fraud investigation determined that the parts had been used to fix employee cars.
For an IT audit, we took a snapshot of the main tables of the current system and compared these to the new system’s tables. Both were relational data bases. In the new system, one of the table had one less record that the old system. Turns out the first record was treated as the field names which meant that the index keys were off by 1 so every record in the parent was matched to the wrong record in the child table.
On a personal note, I won the IIA for “Contribution to the Profession” for my many years of encouraging auditors to embrace analytics.
ACL Commands – FILTERS; EXPRESSIONS; SUMMARIZE; RELATE; and JOIN
Lesson-learned – you have to constantly be looking at things that can affect your ability to access data. This could be a system conversion; a merger/acquisition; changes to federal laws; etc. I have been impacted by each of these on more than one occasion. The result can mean many months of limited or no access to data if you do not know far enough in advance of the change to plan for it.
Analytics is only limited by your imagination. It can be used for more than financial data. It can be used it to compare the source code in production with the ‘approved’ source code; to looking at staffing and succession planning for HR; to perform employee health and welfare audits; and even to determine if a military unit was ready to go to war.