



One of our small business clients—we’ll
call him Bob—recently expanded his onestore, familyrun
retail operation into a fourstore chain. As many small
business owners have to do, Bob had to relinquish some
handson 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
pointofsale (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 digitalanalysis 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 multipledigit 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 redflag an irregularity? When
someone creates false transactions or commits a
dataentry 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. 
Exhibit 2

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 builtin minimums and
maximums, they also might not conform well to the law’s
predictions. For example, consider a pettycash 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 frauddetection
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 here
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 here
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 , above).
Guided by the VBA code, Excel will analyze the data
using three tests: firstdigit, seconddigit and
firsttwodigits. Once it completes its analysis, the
program will open the second worksheet, FirstDigit Test
(see exhibit 2 , above), and display the results: a
table with the Benford predictions for firstdigit
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. 
Exhibit 3

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 firstdigit 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 seconddigit test, click on
the SecondDigit Test worksheet tab (see exhibit 3 ,
above). Notice that in this analysis, the digit zero is
included in the table of expected digits; as a result,
the Benford formula for the seconddigit test is more
complex. An analysis of the bar chart shows the sample
data deviate from Benford’s predictions for seconddigit
frequencies—further evidence of irregularity.
Now click on the FirstTwoDigits Test worksheet (see
exhibit 4 , below). The following formula calculates the
Benford predicted rates for the first two digits: Log10
(1+1/twodigits).
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 seconddigit 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. 
Exhibit 4

The reallife 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 fraudbuster 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. 
Journal of Accountancy
By Anna M. Rose and Jacob M. Rose 
(Sorry,
There is No Support For Free Downloads) 

