May 2016 – Data Analytics

Year 13 – 2000 – Back to Work after a Year of Consulting

This was another exciting year for me. First, in 1999, I had decided to take a year off without pay and do some sub-contracting for ACL (I forgot to mention this in 1999 post). It gave me the opportunity to really expand my analysis skills. Also, I worked on the development of DirectLink for SAP which really forced me to develop a better understanding of SAP – something that has been valuable ever since.

I also submitted short articles to the IIA’s Internal Auditor magazine and won two Honourable Mention Roundtable Awards for stories on “Travel Bonus” and “It is Really a Good Deal”. In 2001 I would garner my first Ted Keys Roundtable Award for “who’s Managing the Goods” and my second in 2005 for “of Mice and Money”. My last honourable mention was in 2005 for “Winning over the CIO”. The article highlighted audits I had worked on where – surprise – analytics was instrumental in arriving at the audit findings.

Continue reading Year 13 – 2000 – Back to Work after a Year of Consulting

Year 12 – 1999 – Part 2 – Drilling down into A/P risks

Technically, we were still in the planning phase of the A/P audit – but had already identified several areas of risk that needed to be analyzed further.

The early payments represented a potential fraud. If you paid within 15 days, you should receive an early payment discount of between 1.5 -2.5% depending on the vendor’s terms. In addition to reviewing the invoices with ‘immediate’ payment terms, we calculated the difference between the latter of the receipt of goods or invoice received date, and the check date. Then we stratified using intervals of 0-5, 6-10, 11-15, 16-20, 21-25, 26-30, and >30 days. The total number and amount of transactions paid within 15 days was determined. The analysis showed that only 4.6 percent of the transactions were paid within 15 days, however, this represented almost 16 percent of the total payments made.

The auditors review the transactions that were paid within 15 days and found that early payment discounts were claimed in 87% of the cases. A Classify determined that the other invoices were all processed at the same A/P office; belonged to only three vendors; and were processed by two A/P clerks. The unclaimed early payment discounts, calculated at 2%, totaled $832,000.

The team leader had concerns about two possible fraud scenarios. In the first, the A/P clerk processes the original transaction for the full amount of the invoice and subsequently requests a credit from the vendor, for the early payment discount amount, and keeps the credit. The second scheme involves deliberating pay invoices early, without claiming the early payment discount, and receiving a kickback from the vendor.

To identify the first type of fraud, the team leader send out confirmation letters to the three vendors that had been paid early, requesting them to provide details on the terms and amount of the payment. All three vendors replied that they had initially been paid the full amount, but had subsequently sent the company a check for the amount of the discount. The auditors asked the companies for copies of the canceled checks; the two A/P clerks had endorsed them all.

Continue reading Year 12 – 1999 – Part 2 – Drilling down into A/P risks

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 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