| EXECUTIVE SUMMARY | |
Benford’s law holds that the leftmost digit in many types of numerical data is a 1 nearly one-third of the time, with probability inversely proportional to the value of each increasing digit.
This phenomenon can be harnessed to analyze data to detect values that deviate from normal distribution and thus could indicate fraud. An Excel template that applies z-score values to Benford’s analyses of subsets of data can assess the probability that any one employee, customer or vendor among many may have committed fraud.
The template is flexible by allowing users to set a z-score threshold above which the Benford’s analysis flags suspect data. Mark Lehman, CPA, Ph.D., and Marcia Weidenmier Watson, CPA, Ph.D., are associate and assistant professors, respectively, of accountancy at Mississippi State University, Starkville, Miss. Their e-mail addresses are mark.lehman@msstate.edu and mweidenmier@cobilan.msstate.edu , respectively. Tim Jones, MPA, is a graduate student at Mississippi State University. | Worldwide fraud is on the rise. The magnitude of the problem prompted the AICPA and the Association of Certified Fraud Examiners to create the Institute for Fraud Prevention. To assist in detecting fraud, auditors need to employ innovative techniques like Benford’s law, which predicts the frequency of digits 1 through 9 in the first four places of any number. American physicist Frank Benford in the 1930s observed that lower digits, beginning with 1, appear more frequently than higher ones, starting from the leftmost position of many types of collections of numbers. Provided with a large data set, auditors can use Benford’s law to help detect fraud by analyzing all account transactions to see if they fall into the expected pattern (see “ I’ve Got Your Number,” JofA , May 99, page 79). Another JofA article (“Turn Excel Into a Financial Sleuth,” Aug. 03, page 58) presented a Benford analysis using a “Fraud Buster” Excel template on aggregate data.
To pump up auditors’ detective powers, we present an Excel application that simultaneously applies the technique to each employee, and can be easily adapted for other groups including customers and vendors. A Big Four firm is evaluating this approach, which has been successfully used by an internal auditing department of a large international company. Our application has received positive feedback because it is easy to use and can help identify fraudsters, particularly when used in combination with other methods. An internal auditor from an international retail chain said the procedure allowed him to “work smarter instead of harder—the key to success when you are dealing with vast amounts of data.”
The method could help auditors in such activities as SAS no. 99’s requirement that they discuss the potential for a material misstatement in financial statements due to fraud (see “ Auditors’ Responsibility for Fraud Detection,” JofA , Jan. 03, page 28).
| | Astronomer’s Keen Eye Paves Way for Law Who would’ve thought that an astronomer and a physicist could develop a tool for use in accounting? What came to be called Benford’s law was discovered in 1881 by the American astronomer Simon Newcomb, who observed that the pages of printed logarithmic tables starting with the number 1 were much more worn than later pages. Newcomb then analyzed how numbers were distributed in naturally occurring data and derived the frequencies of what is now called Benford’s law. Unfortunately, Newcomb’s discovery went unnoticed until 1938, when Frank Benford, a physicist, rediscovered the same worn pattern of the logarithmic tables. Benford analyzed 20,229 sets of numbers, including baseball statistics, areas of rivers and numbers in magazines. Surprisingly, these number sets all follow the same first-digit pattern. | | PUMPING UP WITH Z-SCORES
Benford’s law is used by several countries and states (for example, California and New York) to identify tax defrauders. Several software companies—for example, Apex Analytix and Cost Recovery Solutions—use Benford’s law to identify suspicious vendors. Using such software, internal auditors at a large international manufacturing firm run a Benford’s law test for each vendor based on amounts that exceed user-defined exception levels. One of these auditors indicated that the company was experimenting to find the optimum level of deviations from Benford’s law. Exactly how do we increase the detective power of Benford’s law? For each employee, customer, vendor or other party to a transaction we calculate a z-score for each leading digit (1 to 9). The z-score is a statistical measure of how many standard deviations a number is from the mean and allows the auditor to empirically determine—not guess—whether deviations from the pattern are statistically significant. The larger the z-score, the less likely it is that unexpected frequencies are the result of chance. The auditor selects the maximum allowable z-score corresponding to the level of error that he or she is willing to accept. For example, if the auditor is willing to accept a 5% chance of drawing the wrong conclusion, the auditor would set the maximum allowable z-score to 1.96. Any z-score that exceeds the auditor’s maximum allowable z-score may indicate fraud and must be investigated further. Thus our approach eliminates the need to experiment with the appropriate exception level—we just leave it up to statistics! HOMING IN ON FAKE SALES RETURNS
While our workbook can be used in many situations, we selected the setting used by the internal auditor quoted above, who investigated sales returns at a retail store. Selected employees are authorized to process sales returns, with management approval required for returns of $500 or more. Our dataset includes 56,000 hypothetical sales returns over a six-month period and contains the following fields: transaction number, date, employee number, sales return amount, and, if required, manager number. Exhibit 1 shows the result of a Benford analysis with the commonly used ACL commercial data analysis software, performed on the entire population of sales returns. The z-score (Zstat ratio) amounts are relatively small, less than ACL’s 1.96 default z-score. Therefore, an auditor would not suspect any fraud, because the sales return counts fall within the expected pattern and the count of each leading digit falls within its acceptable range. However, the dataset contains a fraud perpetrated by Amy who wrote 60 fake returns (1.1% of her 5,320 returns) for amounts just under the $500 managerial level. To increase the chance of detecting fraud, we propose an automated method that applies Benford’s law to each employee by analyzing the frequency of the first digit of every transaction amount. EXCEL TO THE RESCUE (AGAIN)
The steps include (1) extracting the first digit using two text functions ( Exhibit 2), (2) creating a PivotTable to calculate the actual frequencies for each employee using the COUNT function (Exhibit 3), and (3) calculating a z-score using the actual counts and expected frequencies for each combination of digits for each employee (Exhibit 4). The spreadsheet displays “yes” for any z-score that exceeds the maximum allowable z-score in cell K2, currently set at 1.28. Based on this z-score value, we can conclude that there is less than a 20% chance that frequencies identified in column L with a “yes” are the result of chance. Want to increase the power of your test? Just change the z-score. If you are willing to accept only a 10% probability that the unexpected frequencies are the result of chance, then set the z-score to 1.65. For a 5% or 1% chance, set the z-score to 1.96 or 2.58, respectively. Amy’s fraud (issuing fake returns near $500) has a maximum z-score of 2.59 for the 4 digit, as shown in cell E17. Given that Amy’s score exceeds 2.58, there is less than a 1% probability that her actual return frequencies are due to chance—indicating that fraud is very likely.
WORKING WITH YOUR DATA
Got a large dataset? Although Excel easily handled our 56,000-line dataset, yours may overpower Excel. No problem! Programs such as Microsoft Access and ACL can identify the leading digit and create a cross-tabulation that can be copied into our workbook’s Actual worksheet. Access users can create a crosstab query to replace Excel’s PivotTable. ACL users can substitute that program’s LEADING function for the formula in step 1 to extract the first digit, then create a cross-tabulation table. A MORE DYNAMIC ANALYSIS
Internal auditors, external auditors and managers are under increasing pressure to identify fraud. The combination of the three text functions and Excel’s PivotTable allows a more dynamic data analysis when using Benford’s law. However, like most audit tests, Benford’s law cannot be relied upon to catch all frauds, which often requires a combination of approaches. For example, Benford’s law did not identify the fraud committed by Tom, who scattered his 100 fake returns (2.1% of his 4,833 returns) randomly between $100 and $500 (see employee 1981 in the workbook). Certainly, Tom’s maximum z-score of 0.88 (for the 4 digit) is larger than other employees’ but not large enough to exceed the acceptable z-score. Benford’s law does not work with small datasets, data with assigned numbers or artificial minimums and maximums, and numbers that are truly random. Despite these limitations, Benford’s law can still be a powerful financial sleuth. Both auditors and managers must use tools such as Benford’s law in combination with their professional judgment and investigative skills to uncover fraud. |
