Turn Excel into a financial sleuth: an easy-to-use digital analysis tool can red-flag irregularities.

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. (For more on Benford's Law, see "I've Got Your Number," JofA, May99, page 79.) 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.

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.

A NOTE OF CAUTION

Benford's Law is not effective for all financial data. If the data set is small, the law becomes less accurate because there are not enough items in the sample and so the rules of randomness don't apply--or at least apply with less predictability.

Also, if the data include built-in minimums and maximums, they also might not conform well to the law's predictions. For example, consider a petty-cash fund where all disbursements are between a $10 minimum and a $20 maximum. All first digits would be either 1 or 2, and the expected distribution of first digits would not apply. Likewise, when a company's major product sells for, say, $9.95, most sales totals will be a multiple of 995, again offsetting the value of the process. Finally, when a data set consists of assigned numbers, such as a series of internally generated invoice numbers, the data will not follow a Benford distribution.

For a demonstration of how the fraud-detection spreadsheet works, you can download an Excel file that contains sample data and the Visual Basic for Applications (VBA) code that automates the calculation of the data from http:// www.aicpa.org/download/pubs/jofa/2003_08/Fraud_Buster. xls. For those who want to create their own VBA code or alter the downloaded program to perform other digital analysis tests, download an instruction manual "How to Create the Fraud Buster Application" from http://www. aicpa.org/download/pubs/jofa/2003_08/How_to_create_Fraud _Buster_Application.doc.

Once you've downloaded the file, you can perform tests on any spreadsheet data. Further, you can easily import database data into Excel and then analyze them. You even can download live Internet data for that purpose.

To start the test, open the Enter Data worksheet--using either the sample data or after importing your own data--and press the Run Fraud Buster button (see exhibit 1, page 58).

[ILLUSTRATION OMITTED]

Guided by the VBA code, Excel will analyze the data using three tests: first-digit, second-digit and first-two-digits. Once it completes its analysis, the program will open the second worksheet, First-Digit Test (see exhibit 2), and display the results: a table with the Benford predictions for first-digit frequencies, the actual sample frequencies, the differences between the sample and Benford frequencies and a bar chart that graphically compares the financial data with the law's predictions.

[ILLUSTRATION OMITTED]

It's immediately obvious from the bar graph that the digits in our disbursement data do not conform to Benford predicted rates. The digits 5, 6 and 7 appear much more frequently than expected, while the digit 1 is noticeably absent. This type of result indicates that it may be necessary to investigate further.

The first-digit test analyzes the reasonableness of the data, which can be very valuable to internal and external auditors. Additional tests of the digits can help to isolate the cause of deviations from Benford's expectations.

To see the results of the second-digit test, click on the Second-Digit Test worksheet tab (see exhibit 3, at right). Notice that in this analysis, the digit zero is included in the table of expected digits; as a result, the Benford formula for the second-digit test is more complex. An analysis of the bar chart shows the sample data deviate from Benford's predictions for second-digit frequencies--further evidence of irregularity.

[ILLUSTRATION OMITTED]

Now click on the First-Two-Digits Test worksheet (see exhibit 4, below). The following formula calculates the Benford predicted rates for the first two digits: Log10 (1 + 1/twodigits).

[ILLUSTRATION OMITTED]

With these four worksheets, you are armed and ready. Import the data you wish to analyze into the Enter Data worksheet and press the Run Fraud Buster button.

The second-digit test confirms the existence of deviations from expectations. The digits 6 and 7 appear far more often than expected. Finally, the analysis indicates that 56 and 67 appear as the first two digits far more often than expected. It may be possible an employee is creating fictitious disbursements, and he or she has a tendency to overuse 5, 6 and 7 when creating false disbursement data. Alternatively, there may be a $1,000 limit on unauthorized disbursements to vendors, and an employee is creating false disbursements that are comfortably below the cutoff.

The real-life Bob investigated a sample of the disbursements that started with the digits 56 and 67 and soon discovered disbursements to an unfamiliar vendor. Additional sleuthing revealed the vendor did not exist, and the employee actually was sending payments to a personal account. Digital analysis using Benford's Law and the fraud-buster spreadsheet swiftly exposed the crime and its source. Bob spent only minutes learning to use the spreadsheet. It now is a part of his personal arsenal against fraud and employee errors.

ANNA M. ROSE, CPA, PhD, and JACOB M. ROSE, PhD, are assistant professors at Montana State University at Bozeman and principals of Progression Consulting Group.