Using Excel and Benford's Law to Detect Fraud: Learn the Formulas, Functions, and Techniques That Enable Efficient Benford Analysis of Data Sets

In 2015, Atlanta's Six Flags White Water theme park opened a new 10-story slide called the Dive Bomber. The ride begins with a virtual free fall nearly straight down before it gradually levels out toward the bottom. Most people who look at the Dive Bomber see a thrill ride--one they may or may not want to experience. CPAs who gaze upon the Dive Bomber may see something else--an image they could possibly use to detect and prevent fraud. What could a water slide possibly have to do with fighting fraud? The answer lies not in the nature of the slide, but in its shape. The Dive Bomber's curve closely matches the curve Frank Benford discovered in 1938 that depicts mathematical expectations related to naturally occurring (or nonfabricated) numbers (see the sidebar, "The History of Benford's Law"). Affixing the shape of this water slide in your mind now may help you detect fraudulent data later. Though the merits of the analogy may not be immediately obvious, this article explains Benford's Law, how it relates to the shape of a water slide, and how to make simple Excel-based calculations that can help spot and stop fraudulent activity. WHEN TO USE BENFORD'S LAW TO SPOT FRAUD Briefly explained, Benford's Law maintains that the numeral 1 will be the leading digit in a genuine data set of numbers 30.1% of the time; the numeral 2 will be the leading digit 17.6% of the time; and each subsequent numeral, 3 through 9, will be the leading digit with decreasing frequency. This expected occurrence of leading digits can be illustrated as shown in the chart "Benford's Law." The resulting curve pictured in this green bar chart closely resembles a steep water slide and is sometimes referred to as the Benford curve. Today, armed with any version of Microsoft Excel, CPAs can count the leading digits contained in virtually any data set, chart the findings, and compare the results to Benford's curve to see if that data set obeys the expectations set forth by Benford's Law. The Excel-based procedures described in this article for counting and charting a data set's leading digits are the same for any size data set and can include general ledgers, trial balance reports, income statements, balance sheets, invoice listings, inventory listings, depreciation schedules, investment statements, accounts payable and receivable reports, timesheet data, portfolios, expense reports, and virtually any other group of data containing naturally occurring numbers. A few caveats regarding the application of Benford's Law are as follows: * The larger the better. Benford's Law works better with larger sets of data. While the law has been shown to hold true for data sets containing as few as 50 to 100 numbers, some experts believe data sets of 500 or more numbers are better suited for this type of analysis. * Equal opportunity. To conform with the law, the data set you use must contain data in which each number 1 through 9 has an equal chance of being the leading digit. Otherwise, Benford's Law doesn't apply. For example, consider a listing of the heights of all the NBA basketball players in history. In this case, because NBA players range in height from 5 feet 3 inches (Muggsy Bogues, who played from 1987 to 2001) to 7 feet 7 inches (Manute Bol and Gheorghe Muresan, who played during the 1980s and 1990s), there are no player heights that begin with a 1, 2, 3, 4, 8, or 9; hence those digits have no chance of being the first digit in such a listing, making Benford's Law inapplicable. Likewise, a listing of invoices for a web hosting company that charges its customers either $49.99 or $79.99 would be an unsuitable data set because the numerals 4 and 7 (or perhaps the numerals 5 and 8 if you include sales taxes) would have a greater opportunity to be the leading digits in such a listing. * No definitive proof. Benford's Law calculations can never definitively prove or disprove the presence or absence of genuine numbers. …