After my year of consulting in numerous private sector companies I felt that my experience not only with ACL, but also with risk assessment and fraud risk assessment in particular had grown. While ACL’s basic command set was powerful, there were a number of techniques that I had used specifically for fraud that were not (at this time) included in the basic ACL command set. They required the use of ACL scripts and since the ACL programming language and was beyond most basic users of ACL, I thought that a book which contained not only case studies and the scripts, but an explanation of how each script worked would be useful to the ACL user community. While not originally designed as such, it was used by many people as a self study course on ACL scripting. Once again the book was well received – with many expert users praising it for helping them to improve their own ACL skills when they were beginners.
I would like to think that the toolkit, originally published by ACL in 2001, encouraged them to expand the command set available in ACL. The scripts included a number of functionalities that would later be added to ACL’s basic command set such as Crosstab (added 2010), Benford analysis (added 2010), Frequency of numeric value (added 2015), and Min/Max ratio (added 2015). (Note: some of the dates may be incorrect – hard to remember exact versions when ACL commands were added, but it was years after the toolkit was developed.) So, much to my surprise, I was a thought leader in the analytics space.
The toolkit, now called “Fraud Analysis Techniques using ACL” is still being published and used. In the latest addition published by John Wiley and Sons, I added a self-study course on ACL scripting – complete with data, exercises and solutions as well as some generic scripts that perform useful function such as “unbucket” and “flatten” a data file. The notion of providing standard useful scripts has also been adopted by ACL in their Script Hub (in 2012) – so again I was ahead of the curve in providing functionality to users.
This is a copy an article I wrote for the Internal Auditor magazine back in 2001 which describes my thoughts behind the development of the Fraud toolkit. The ideas and approaches are still relevant today. I have added notes here and there (in italics) to bring things up to date.
A new approach being used for fraud prevention and detection involves the examination of patterns in the actual data. The rationale is that unexpected patterns can be symptoms of possible fraud. A simple example of the application of this technique is the search for duplicate transactions, such as the same invoice number – vendor number:
Ordinarily, one would expect that invoice number – vendor number combinations, would be unique. Therefore, the existence of transactions with the same invoice number – vendor number combinations would be an unexpected pattern in the data. The identification of possible duplicate transactions would be a possible symptom of fraud that should be examined. However, fraud symptoms are only that – symptoms – and care should be taken to properly investigate the transactions before jumping to conclusions. Transactions that look like duplicates may simply be progress payments or equal billing of monthly charges. It is possible to search for duplicates on one or more key fields.
Even Amounts
Another digital analysis technique is to identify even dollar amounts, numbers that have been rounded up, such as $200.00 or $5,000.00. The existence of even amounts may be a symptom of possible fraud and should be examined.
The MOD() function in ACL can easily identify these types of even numbers. For example: MOD(Amount,100) = 0 will identify transactions that are a multiple of 100, such as $300.00 and $700.00, and also $1,200 and $25,000.
Case Study: Even Amounts – Travel expenses had always been a concern for the auditors as controls were a weak. Employees had a maximum per diem rate when traveling, but had to submit actual receipts to cover the expenses. Maximums were also established for meals: Breakfast $10.00, Lunch $20.00 and Dinner $30.00 and for Hotel $100.00. We used the MOD() function to identify transactions that were multiples of $10.00, $20.00, $30.00 or $100.00. These transactions were compared to the manual receipts to ensure that the amounts expensed were appropriate. The review determined that some people were charging the maximum rates for meals and hotels even though the receipts did not justify the amounts.
Ratio Analysis/Variance Analysis
Another useful fraud detection technique is the calculation of ratios for key numeric fields. Like financial ratios that give indications of the relative health of a company, data analysis ratios point to possible symptoms of fraud. Three commonly employed ratios are:
- the ratio of the highest value to the lowest value (Maximum/Minimum);
- the ratio of the highest value to the next highest (Maximum/2nd Highest); and
- the ratio of the current year to the previous year.
For example, auditors concerned about prices paid for a product, could calculate the ratio of the Maximum Unit Price to the Minimum Unit Price for each product (Stock Number). If the ratio is close to 1, then there is little variance between the highest and lowest prices paid. However, if the ratio is large, this could be an indication that too much was paid for the product in question.
Product Line Max Min Ratio
Product 1 235 127 1.85
Product 2 289 285 1.01
Product 1 has a large difference in the unit price between the minimum and maximum (ratio of 1.85); whereas Product 2 has a smaller variance in the unit prices (ratio of 1.01). Audit should review the transactions for the unit prices of $235 and $127 for Product 1 to ensure the proper payments were made. Paying abnormally high unit prices for products may be a symptom of kickbacks in the contracting area.
The ratio of the maximum to the 2nd highest value can also highlight possible frauds. For example, examining the pattern of payments made to vendors can be revealing. In this case, a large ratio could indicate an anomaly in the data.
Customer Max 2nd Highest Ratio
XYZ Corp. $100,080 $ 26,068 3.84
ABC Corp. $103,429 $101,210 1.02
A large ratio indicates that the Maximum value is significantly larger than the second highest value. Auditors and fraud investigators would be interested in these unusual transactions as they represent a deviation from the norm. Unexplained deviations could be symptoms of fraud. In a number of cases, high ratios have identified payments incorrectly made to the vendor. (Note: ACL has added this functionality to version 11 with the “Statistics” option on the Classify, Stratify, and Age commands. This option gives you the Minimum, Maximum, and Average for each record produced by the three commands. For example, CLASSIFY on Job_Category SUBTOTAL Amount STATISTICS – will give you the minimum, maximum, and Average Amount for each job category.)
Case Study – Doctored Bills – The auditors reviewed the patient billing system to determine if the appropriate charges were being assessed the patient’s healthcare providers. An initial analysis of the data was performed to calculate the ratio of the highest and lowest charges for each procedure. The auditing standards required that procedures with a ratio of Max/Min greater than 1.30 be noted and additional review performed.
This quarter, three procedures had ratios higher than 1.30, the highest ratio being 1.42. A filter was set to identify the records related to the three procedures in question and additional analysis was performed. This quickly determined that one doctor was charging significantly more than the other doctors, for the same procedures. A comparison of the charges from the billing system with the payments recorded in the Accounts Receivable system revealed that the doctor was skimming some of the payment received. The amount recorded in the receivable system was in line with the usual billing amount for the procedures. The doctor was unable to justify the higher prices, or explain the difference in the billing and the receivable systems.
Trend Analysis
Analysis of trends across years, or across departments, divisions, etc. can be very useful in detecting possible frauds. Another useful calculation is the ratio of the current year to the previous year. A high ratio indicates a significant change in the totals.
Case Study – Contracting Kickbacks – John, one of the contracting officers, had devised a great scheme in which he won and so did the companies who were willing to do business under his conditions. Companies who were not willing to provide him with a little extra – would not get the contract.
The auditors decided to use digital analysis as part of their review of the contracting section. One of the analyses calculated the total contract amount by vendor for each of the past two years. A ratio of current year to previous year was calculated and the statistics command was used to look at the minimum, maximum, average and highest and lowest 5 ratios. While the average was close to 1.0 the highest and lowest 5 values showed that some companies had significant decreases in business, while others had experienced significant increases in business.
The auditors review the detailed for all companies that had a ratio of less than 0.7 or more than 1.35. The detailed records were extracted to a file and totals were calculated by contracting officer. For companies that had seen an increase in business, the results revealed that John had raised many of the contracts. In comparison, John had raised no contracts with the companies that had seen a decrease in business. The auditors learned of John’s kickback scheme when they interviewed salesmen from the companies that had ratios less than 0.7. The fraud accusations were proven when we conducted interviews with salesmen from the firms that had increased sales by 1.35 or more. They admitted to paying kickbacks in order to obtain business with our company.
Benford’s Law
Benford’s Law, developed by Frank Benford in the 1920’s, makes predictions on the occurrence of digits in the data. Benford’s Law concludes that the first digit in a large number of transactions (10,000 plus) will be a ‘1’ more often than a ‘2’; and a ‘2’ more often than a ‘3’. In fact, the likelihood of the first digit taking on a value decreases as the value of the digit increases. Benford calculates that the first digit will be a ‘1’ about 30%, whereas ‘9’ only has an expected frequency of about 5% as the first digit (Ted Hill, ‘American Scientist’, July-August 1998, pp.358-363).
However, not all data will have distributions as predicted by Benford’s Law. Sometimes there is valid rationale for certain numbers occurring more frequently than expected. For example, if a company sends a large of amount of correspondence via courier, and the cost is a standard rate ($6.12) for sending a package of under one pound, then the first digit ‘6’ or the first two digits ‘61’ may occur more often than predicted by Benford’s Law. Guidelines for deciding whether the data will comply with the Law include:
- there should be no set maximum or minimum;
- there should be no price break points ($6.12 for all packages under 1 pound, $7.13 for package more than 1 pound and less than 2 pounds); and
- numbers should not be assigned, such as policy numbers, social security numbers, etc.
Given Benford’s Law, we would expect that valid, unaltered data to follow the predicted frequencies. Data that meets the above criteria, but fails to follow the expected frequencies, may include fraudulent amounts (Mark J. Nigrini, PhD., ‘Digital Analysis: a computer-assisted data analysis technology for internal auditors, 1998). An analysis of the frequency distribution of the first digits or second digits can detect abnormal patterns in the data and may identify possible frauds. (Note: Benford analysis is not a silver bullet. If you have a large number of transactions and the fraud is limited (or involves many different amounts), Benford will not be useful. However, in some cases it may identify an issue – the data does not conform to the theory – but you will still have to figure out why. I usually isolate the transactions where the first 1 or 2 digits are higher than expected and perform various summaries. For example, I will produce a summary by GL Acct, by Vendor, by Contracting Officer, etc. to see if there is an unusual pattern.)
Case Study: Signing Authority – The auditors were investigating possible fraud in the contracting section where thousands of contracts were raised every month. They used Benford’s Law to examine the first two digits of the Contract Amount. The results of their analysis revealed that the digits ‘49’ were in the data more often than expected.
Classify on the contracting office for all contracts with ‘49’ as the first two digits determined that the contracting manager was raising contracts for amounts in the range $49,000 to $49,999 to avoid contracting regulations. Contracts under $50,000 could be sole-sourced; contracts $50,000 or higher had to be submitted to the bidding process. He was raising contracts just under the financial limit and directing them to a company owned by his wife.
Lesson-Learned: It pays to stop and look at the results. You need to figure out not only What happened (monetary loss, loss of intellectual property, etc) and Why it happened (control weaknesses, separation of duties, etc.), but also How it could have been identified. This means reverse engineering the fraud to find the who, why and how of the fraud; and the symptoms. What does the fraud look like from a data perspective?
Digital Analysis, an advanced application of data analysis, Can be used to examine the data for symptoms of fraud. In fact, digital analysis is a case where millions of transactions can make the identification of fraud symptoms easier to find then when there are only a few thousand transactions. The patterns in the data become more obvious and focus attention on the fraud.
Next week, my post will be about “the big one that got away”. A multi-million dollar fraud where data analysis clearly identified a problem; and I failed to adequately follow-up on the symptoms. This allowed the fraud to continue for several more years.
Keep working ,remarkable job!