Year 23 – 2010 – HR Data

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.


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.

Year 22 – 2009 – SAP Extract

Imagine my excitement when I had 7 responses to my previous post on Payroll and then my utter disappointment when I found out that all we in Russian and had nothing to do with the content of my blog.  This continued for several days and suddenly switched to English posts about Credit Unions.  In total I had over 65 spam bot posts including two that wanted to help me monetarize my site.

On the positive side, I was talking to Franco who said that he reads my blogs every Monday and the most recent post gave him some ideas of a payroll analysis he wanted to perform.

Audit example – Standard SAP extract:  By now, I had been extracting SAP data for 10 years and had developed a “Standard SAP Extract”.  While SAP has more than 70,000 tables, I was using 2 main tables and 9 master tables.  Using this set of data, I had supported hundreds of audits.  In addition, I had changed companies twice and was able to use the exact same extract (and all of my ACL scripts) at the new companies.  Unfortunately, it took between 6-12 months to get the extract built at the new company.

Continue reading Year 22 – 2009 – SAP Extract

Year 12 – 1999 – Part 1 – Data analytics to assess risk

Wow – never realized how much work this would be.  I mean, I am only posting once a week – but it still takes a lot of time.  Not getting many comment, but I hope people are enjoying and learning from the posts.  I had hoped more people would share their experiences so we could learn from each other.

I was now interested in expanding my use of data analytics beyond testing of controls.  There were numerous times when I had identified control weaknesses that were fraud risks and a number of times where we actually from a fraud occurring.  This led me to the development of my third book: “Fraud Detection: Using Data Analysis Techniques to Detect Fraud” in 1999.   The text included theory and numerous cases studies which illustrated how ACL could be used to identify symptoms of fraud in the data.  Examples such as STATISTICS on Receipt_Qty to find a receiving clerk fraud were included.

Once again, ACL agreed to publish the text and it received a favourable review from both the audit and investigative communities.  It is still in print and people tell me that it has helped them with the fraud analytics.  One expert from E&Y told me that he using it with clients to takes about fraud risks and they usually go from “No fraud here” to “we really need to set up a proper fraud risk assessment and monitoring program”.

As I mentioned previously, our company has just implemented several ERP systems.  In particular, we were using SAP for our financial system.  About two years ago I had performed a test of the A/P process and had found a number of issues.  Management’s initial concerns centered on possible duplicate payments and paying invoices early without the discount or paying them late and incurring late penalty charges.   Keep in mind the fact that interest payments in the late 1990’s were much higher than today – can’t remember for sure but probably closer to 10%.  Also, I could have posted this in 1996 and 1997, but the lessons learned applied to 1998 so I am posting now.

Continue reading Year 12 – 1999 – Part 1 – Data analytics to assess risk

Year 9 – 1996 – Promoting CAATTs

I had been writing articles for the Internal Auditor (IIA) and other audit-related magazines for several years now, but I wanted to do more to educate and encourage auditors in the use of analytics.  One day I realized that if I assembled all of my previously published IIA articles, I had about 50% of the content necessary for a book on analytics.  So I started developing an outline and writing more content.  It took about six months to combine what I had and write the other 50% and the result was “CAATTS and Other BEASTs for Auditors”.   The book was published by Global Audit Publications, the publishing arm of ACL Services.  It was their first publication – other than software manuals.  Now I was a published author.

CAATTs and Other BEASTs explained how various types of software – from word processing to data analysis – could be used to support the planning, conduct and reporting phases of the audit.  It was well received by auditors who were looking for guidance in the use of analytics; and I was encouraged to write more articles and even another book (but not right away).  Even though it had a limited audience, the final sales total, after several years, was over 5,000 copies.

The next audit I supported was an environmental audit of hazardous materials.  The objective was twofold: ensure that hazardous materials were properly stored and disposed of in accordance with environmental laws and regulations.  At the beginning of the planning phase, I asked the auditors where they were going (i.e. where would the onsite audits would be conducted).  They told me that they were going to three large sites (one on the east coast, one in central region and the other on the west coast) and three smaller depots close to the large warehouses.  They explained that this would ensure all regions were covered and that small and large sites were audited.  Sounded good, but based on my analysis, one of the large sites and two of the smaller ones did not have any hazardous materials.  This wouldn’t make for a very good audit.

Continue reading Year 9 – 1996 – Promoting CAATTs