| 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://apps.aicpa.org/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://apps.aicpa.org/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 , above). 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 , above), 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.
| 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. | |
|
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). 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. |
Manage the talent, hand off the HR headaches
2023 tax software survey