Turn Excel Into a Financial Sleuth

August 14, 2003

0 2 minutes read

One of our small business clients—we’ll call him Bob—recently expanded his one-store, family-run retail operation into a four-store chain. As many small business owners have to do, Bob had to relinquish some hands-on control when his business grew. He had to hire new employees for each store, and he worried about the possibility of bookkeeping errors and, even worse, fraud.

Adding to his concern was his need to install modern electronic technologies to link the four locations. Instead of trusted family members responsible for a single cash register, Bob now had many operators at point-of-sale (POS) terminals and purchasing agents in different locations handling electronic disbursements to hundreds of vendors—an ideal environment for irregularities.

The POS system produced spreadsheets that tracked daily sales, returns and disbursement data—all of which could be aggregated by employee. While the POS tool could generate custom financial reports useful for decision making, it was unable to spot clues about irregularities.

EXCEL TO THE RESCUE
That’s where we came into the picture as consultants. We suggested running a digital-analysis process based on Benford’s Law, which can detect irregularities in large data sets. We told Bob he didn’t need to buy any special software to use the process, and that with a few modifications, Excel could do the job. As it turned out, the process paid off handsomely. Within a few weeks it revealed irregularities in a sample of cash disbursements to vendors, and after further investigation, Bob concluded that one of his new employees probably was committing fraud.

Exhibit 1

This article will explain how you can turn Excel into a financial detective by using Benford’s Law and customize Excel programs to perform sophisticated digital analyses that can uncover errors and fraud.

Benford’s Law predicts the occurrence of digits in large sets of numbers. Simply put, it states that we can expect some digits to occur more often than others. For example, the numeral 1 should occur as the first digit in any multiple-digit number about 31% of the time, while 9 should occur as the first digit only 5% of the time. We also can apply the law to determine the expected occurrence of the second digit of a number, the first two digits of a number and other combinations.

How can such predictions red-flag an irregularity? When someone creates false transactions or commits a data-entry error, the resulting numbers often deviate from the law’s expectations. This is true when someone creates random numbers or intentionally keeps certain transactions below required authorization levels. When Excel spots the deviation, it raises a red flag. Considerable statistical research supports the effectiveness of Benford’s Law, making it a valuable tool for CPAs. The technique isn’t guaranteed to detect fraud in all situations but is useful in analyzing the credibility of accounting records.