Category: General

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.

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.

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 21 – 2008 – part 1 – Ensuring Integrity

 Accessing different systems, trying to address auditor requirements, and performing complex analysis – they all present risks.  And while I have had a great deal of success, there also have been many mistakes.  I once heard it said, “learn from others mistakes – you don’t have enough time to make them all yourself” or something similar.  This is why I always try to post a lesson-learned and this post is no different.

Here are three audits where my analysis was less than perfect – but where I learned valuable lessons.

  1. Expense advances – I was supporting an audit of advances and extracted all transactions related to travel expenses from the SAP system. The filter was a combination of a document type and a GL code.  When I presented the auditor with the extracted data, I told her “Here are all of the travel advance transactions; the total is $23M – be sure to verify this with the client.  Six months later after additional analysis and other audit procedures – the draft report was given to senior management.  They replied, “$23M, it should be much closer to $61M.”  Turns out there were two types of advances (excluding salary advances), travel advances and sensitive expenditures.  We had only extracted the travel advances.  Now I could (and probably did) argue that this was not my fault – I had told the auditor to verify the data.  But as the SAP and data expert – I should have done more to ensure that I was providing a complete set of data to support the audit.  Part of doing this would have been to ask the auditor to supply the “audit objective”.  In this case, the objective was not “to verify the controls over travel advances”; but “to verify controls over expense advances”.  After this mistake, I was also sure to get the audit objectives and to ensure that my understanding – and the data that I would be extracting – agreed with the auditor’s understanding.  From then on, I also checked to see if the auditor had verified the accuracy and completeness of the data.

Continue reading Year 21 – 2008 – part 1 – Ensuring Integrity

Year 20 – 2007 – Inventory

It was hard to believe, but I had now been at this (data analytics to support audit) for 20 years.  And I still found it interesting, challenging, frustrating, rewarding and aggravating – all at once.

I was constantly being asked to access new systems and perform analysis for different types of audits.  At the same time, I had my regular monthly routine tasks of extracting, downloading and cleansing data we used on a regular basis.  For example, the SAP extract – full year-to-date extracted and download every period – would take most of the day to perform by the time I got to period 8.  I could only download one period at a time because of CPU limitations – so I would start a background extract of period 1 and work on other things.  When it finished, I would extract period 2 and download period 1; and so on until I reached the current period (AX and DirectLink would have made things much simpler).  In addition, I had to extract and download the 12 master tables (vendor, customer, cost centre, GL, etc) that I needed every quarter.

Once all year-to-date extracts had been performed, I had a script that combined the periods and transformed the detailed transaction (BSEG table) and the header (BKPF Table) into a more useful data set where the customer and vendor information was on every line of a document.  The script also produced a snapshot of the controls and summary files (by GL; by Cost Centre; by Vendor; etc.).  Next I would combine data from the previous “X” years to produce multi-year summaries (by GL by year; by Cost Centre by year; etc.).

Continue reading Year 20 – 2007 – Inventory

Year 16 – 2003 – Recruitment Process

People, even those that perform analytics, often think that data analysis can only be applied to financial-type audits.  I have tried to highlight other types of audits where analytics played a significant role including transportation, inventory, and hazardous materials (environmental).   In that vein, I offer you analysis that was part of an HR recruitment audit.

he organization was an international/national police force.  Like many police forces, it needed a fairly continuous flow of recruits.  The problem with this agency was that the recruitment process – which leads to a six month training program – was overly long. In fact it was 18-22 months from the time a potential recruit entered the process until they were offered begin the training program.  During this time, they were not paid, and, as a result, many suitable recruits exited the recruitment process because they found other jobs.

Working with the HR section, the auditors determined that they were 36 separate steps in the recruitment process.  Some were fair minor – like completing an application form – while other were more time consuming – like the security clearance process.  I was able to obtain the recruitment data for the past 3 years.  The data contained the start and end date for each step for each recruit.  In reviewing the recruitment data I was able to determine that the steps were done in series – not in parallel.  This meant that before a recruit could enter step “n”, step “n-1” had to be completed.  Our first recommendation was to change the process to permit steps to be done in parallel.  For example, rather than waiting for the results of the written test (which could take up to two weeks), recruits could start on the physical test phase.

Continue reading Year 16 – 2003 – Recruitment Process

Year 11 – 1998 – ERP Systems Arrive

Disaster!  After 10 years of hard work to develop a decent understanding of the company’s finance, inventory, two HR and three pay systems, we switched to SAP (for finance and payroll) and PeopleSoft for HR) and another ERP system for inventory.  It was bad enough that we were changing systems, but to implement separate systems for the major functions seemed idiotic and created much more work for me and the rest of the CAATTs team.  In addition, we had roadblocks, particularly from IT who were busy trying to implement the new systems.  I was hard to argue with them.

However, it did reinforce the notion that auditors need to be flexible, nimble, and willing to accept change – all things we expect of our clients when we make recommendations.  It also reminded me of the importance of personal relationships and multiple methods of accessing data.

I spent a good portion of a year reviewing previous requests for data and analysis support (to determine what was required by auditors); working with auditors of different stripes (financial, operational, compliance, HR, etc.) to find out what data they required; and the technical folks (programmers, analysts, business owners, etc.) to re-negotiate access to the various systems and data.  I also mapped our current data to the new applications (e.g. in the previous system we had access to the responsibility centre which was the Cost centre in SAP; record number/document number, invoice date/document date, etc.).  For SAP this involved obtaining read access to the system and bring up an invoice – pressing F4 on each field and then selecting “technical data” to get the German field names and table names.

Continue reading Year 11 – 1998 – ERP Systems Arrive

Year 10 – 1997 – The importance of data

Even now, I firmly believe that the potential for the Y2K disaster was real.  The only reason that its effects were minimized was a result of the hundreds of thousands of hours spent checking and rechecking programming code to address the “00” year problem before it occurred.

For those of you too young to remember, prior to the year 2000, many databases and applications only used two digits for the year, so “10” was “1910”.  This was initially because of the high cost of storing data.  Storage space was expensive and read/write operations slowed down the processing speeds.  As a result, dates were often stored with only a two digit year (e.g. 032155 or 08055 (in DDDYY format)). Why store “1955” when “55” was sufficient and saved two bytes of space and reduced the read/write time.  However, with the coming of 2000, the extra two digits would be important.  A year stored as “01” could be “1901” or 2001”.   While this could be critical particularly in the financial world where interest and other calculations require date fields, financial transactions were not the only concern.  Many programmers, myself included, had learned to build error traps and exit routines that used code such as If Date = “00” then exit.  Many of these programs were still in existence and the year would soon be “00”.  This could cause critical programs to exit or execute error routines.  Concerns ranged from VCRs not working to planes dropping out of the sky and nuclear plants exploding.

Continue reading Year 10 – 1997 – The importance of data

Year 6 – 1993 – Promoting Analytics

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.

Continue reading Year 6 – 1993 – Promoting Analytics

Year 5 – 1992 – building a sustained team

The analytics team was now at three people – so I could build some overlap in our knowledge of the application systems that were we accessing on a regular basis.  For example, this meant that I was no longer the only person who understood the financial system.  Now we had at least two people for each of the 10-12 systems we were accessing on a regular basis.  For all but the inventory system (an IMS database we accessed with ACL for MVS IMS interface) we were getting monthly extracts – either by running the extract jobs ourselves, or as a standard production job.

The first question I had to address in building a team was the level and experience of the people that should be part of the analytics function. A related question was: Should an auditor be taught programming (data extraction and analysis) or should a programmer be taught to conduct audits? Failures in implementing analytics have one thing in common — management did not assign the right person or people to the task. Too often, a junior programmer with limited or no audit experience — addressing only the IT aspects of the job — is assigned to develop the analytics function. Given the nature of the task — dealing with business process owners, system programmers, and review team leaders — the analytics function must be staffed at the appropriate level and with the necessary experience. The biggest hurdle is having the business process knowledge to identify the types of analytics to run.  Because of management’s support, I was able to hire people at senior auditor or team leader levels.  One was a programmer with IT audit experience, the other a programmer willing to learn about audit.

Continue reading Year 5 – 1992 – building a sustained team

Pre – 1988 …. before my analytics journey had begun

Analytics in audit attracts people from a variety of backgrounds and I am no different in that regard. I started my career as a games room supervisor at the Boys and Girls Club and worked my way up to a guidance counsellor position. I was mainly working with disadvantaged children (and some juvenile delinquents) ages 6 –17. While the work was rewarding, the pay was not. I was working about 60 hours a week (including Saturday 09:00 – 5:00) for less than $12K/year. Also after 10 years, I felt it was time to move on to new challenges. I decided to go back to school, improve my marketability and skills, and (hopefully) get a better paying job.

I obtained my MBA from University of Toronto and went into the job market with new skills and high hopes. It was a time when jobs were more plentiful and I was fortunate to get a position in IT support. You might think this is a strange combination – a background in guidance and counselling and now working in IT, but I always felt that I was a bridge between the two disciplines (HR and IT). I knew just enough about the soft and technical sides of things that I could talk to both groups.

Continue reading Pre – 1988 …. before my analytics journey had begun