EXECUTIVE
SUMMARY 
Benford’s law holds
that the leftmost digit in many
types of numerical data is a 1 nearly
onethird of the time, with probability
inversely proportional to the value of
each increasing digit.
This
phenomenon can be harnessed
to analyze data to detect
values that deviate from normal
distribution and thus could indicate
fraud. An Excel template that applies
zscore values to Benford’s analyses of
subsets of data can assess the
probability that any one employee,
customer or vendor among many may have
committed fraud.
The
template is flexible by
allowing users to set a zscore
threshold above which the Benford’s
analysis flags suspect data.
Mark Lehman, CPA,
Ph.D., and Marcia Weidenmier
Watson, CPA, Ph.D., are
associate and assistant professors,
respectively, of accountancy at
Mississippi State University,
Starkville, Miss. Their email
addresses are
mark.lehman@msstate.edu
and
mweidenmier@cobilan.msstate.edu
, respectively. Tim
Jones, MPA, is a graduate
student at Mississippi State
University.

Worldwide fraud is on the rise. The magnitude
of the problem prompted the AICPA and the
Association of Certified Fraud Examiners to create
the Institute for Fraud Prevention. To assist in
detecting fraud, auditors need to employ
innovative techniques like Benford’s law, which
predicts the frequency of digits 1 through 9 in
the first four places of any number.
American physicist
Frank Benford in the 1930s observed that lower
digits, beginning with 1, appear more frequently
than higher ones, starting from the leftmost
position of many types of collections of numbers.
Provided with a large data set, auditors can use
Benford’s law to help detect fraud by analyzing
all account transactions to see if they fall into
the expected pattern (see “
I’ve Got Your Number,” JofA , May
99, page 79). Another JofA article (“ Turn
Excel Into a Financial Sleuth,” Aug. 03,
page 58) presented a Benford analysis using a
“Fraud Buster” Excel template on aggregate data.
To pump up auditors’ detective powers, we
present an Excel application that simultaneously
applies the technique to each employee, and can be
easily adapted for other groups including
customers and vendors. A Big Four firm is
evaluating this approach, which has been
successfully used by an internal auditing
department of a large international company. Our
application has received positive feedback because
it is easy to use and can help identify
fraudsters, particularly when used in combination
with other methods. An internal auditor from an
international retail chain said the procedure
allowed him to “work smarter instead of harder—the
key to success when you are dealing with vast
amounts of data.” The method could help
auditors in such activities as SAS no. 99’s
requirement that they discuss the potential for a
material misstatement in financial statements due
to fraud (see “
Auditors’ Responsibility for Fraud
Detection,” JofA , Jan. 03, page 28).

Astronomer’s Keen
Eye Paves Way for Law
Who would’ve thought that
an astronomer and a physicist
could develop a tool for use
in accounting? What came to be
called Benford’s law was
discovered in 1881 by the
American astronomer Simon
Newcomb, who observed that the
pages of printed logarithmic
tables starting with the
number 1 were much more worn
than later pages. Newcomb then
analyzed how numbers were
distributed in naturally
occurring data and derived the
frequencies of what is now
called Benford’s law.
Unfortunately, Newcomb’s
discovery went unnoticed until
1938, when Frank Benford, a
physicist, rediscovered the
same worn pattern of the
logarithmic tables. Benford
analyzed 20,229 sets of
numbers, including baseball
statistics, areas of rivers
and numbers in magazines.
Surprisingly, these number
sets all follow the same
firstdigit pattern.
 
PUMPING UP WITH ZSCORES
Benford’s law is used by several countries
and states (for example, California and New York)
to identify tax defrauders. Several software
companies—for example, Apex Analytix and Cost
Recovery Solutions—use Benford’s law to identify
suspicious vendors. Using such software, internal
auditors at a large international manufacturing
firm run a Benford’s law test for each vendor
based on amounts that exceed userdefined
exception levels. One of these auditors indicated
that the company was experimenting to find the
optimum level of deviations from Benford’s law.
Exactly how do we increase the detective
power of Benford’s law? For each employee,
customer, vendor or other party to a transaction
we calculate a zscore for each leading digit (1
to 9). The zscore is a statistical measure of how
many standard deviations a number is from the mean
and allows the auditor to empirically
determine—not guess—whether deviations from the
pattern are statistically significant. The larger
the zscore, the less likely it is that unexpected
frequencies are the result of chance. The auditor
selects the maximum allowable zscore
corresponding to the level of error that he or she
is willing to accept. For example, if the auditor
is willing to accept a 5% chance of drawing the
wrong conclusion, the auditor would set the
maximum allowable zscore to 1.96. Any zscore
that exceeds the auditor’s maximum allowable
zscore may indicate fraud and must be
investigated further. Thus our approach eliminates
the need to experiment with the appropriate
exception level—we just leave it up to statistics!
HOMING IN ON FAKE SALES RETURNS
While our workbook can be used in many
situations, we selected the setting used by the
internal auditor quoted above, who investigated
sales returns at a retail store. Selected
employees are authorized to process sales returns,
with management approval required for returns of
$500 or more. Our dataset includes 56,000
hypothetical sales returns over a sixmonth period
and contains the following fields: transaction
number, date, employee number, sales return
amount, and, if required, manager number.
Exhibit 1 shows the result of a Benford
analysis with the commonly used ACL commercial
data analysis software, performed on the entire
population of sales returns. The zscore (Zstat
ratio) amounts are relatively small, less than
ACL’s 1.96 default zscore. Therefore, an auditor
would not suspect any fraud, because the sales
return counts fall within the expected pattern and
the count of each leading digit falls within its
acceptable range. However, the dataset contains a
fraud perpetrated by Amy who wrote 60 fake returns
(1.1% of her 5,320 returns) for amounts just under
the $500 managerial level. To increase the chance
of detecting fraud, we propose an automated method
that applies Benford’s law to each employee by
analyzing the frequency of the first digit of
every transaction amount.
EXCEL TO THE RESCUE (AGAIN)
The steps include (1) extracting the first
digit using two text functions ( Exhibit
2), (2) creating a PivotTable to calculate the
actual frequencies for each employee using the
COUNT function (Exhibit
3), and (3) calculating a zscore using the
actual counts and expected frequencies for each
combination of digits for each employee
(Exhibit 4). The spreadsheet displays
“yes” for any zscore that exceeds the maximum
allowable zscore in cell K2, currently set at
1.28. Based on this zscore value, we can conclude
that there is less than a 20% chance that
frequencies identified in column L with a “yes”
are the result of chance.
Want to increase the power of your test? Just
change the zscore. If you are willing to accept
only a 10% probability that the unexpected
frequencies are the result of chance, then set the
zscore to 1.65. For a 5% or 1% chance, set the
zscore to 1.96 or 2.58, respectively. Amy’s fraud
(issuing fake returns near $500) has a maximum
zscore of 2.59 for the 4 digit, as shown in cell
E17. Given that Amy’s score exceeds 2.58, there is
less than a 1% probability that her actual return
frequencies are due to chance—indicating that
fraud is very likely.
WORKING WITH YOUR DATA Got
a large dataset? Although Excel easily handled our
56,000line dataset, yours may overpower Excel. No
problem! Programs such as Microsoft Access and ACL
can identify the leading digit and create a
crosstabulation that can be copied into our
workbook’s Actual worksheet.
Access users can create a crosstab query to
replace Excel’s PivotTable. ACL users can
substitute that program’s LEADING
function for the formula in step 1 to
extract the first digit, then create a
crosstabulation table.
A MORE DYNAMIC ANALYSIS
Internal auditors, external auditors and
managers are under increasing pressure to identify
fraud. The combination of the three text functions
and Excel’s PivotTable allows a more dynamic data
analysis when using Benford’s law. However, like
most audit tests, Benford’s law cannot be relied
upon to catch all frauds, which often requires a
combination of approaches. For example, Benford’s
law did not identify the fraud committed by Tom,
who scattered his 100 fake returns (2.1% of his
4,833 returns) randomly between $100 and $500 (see
employee 1981 in the workbook). Certainly, Tom’s
maximum zscore of 0.88 (for the 4 digit) is
larger than other employees’ but not large enough
to exceed the acceptable zscore. Benford’s law
does not work with small datasets, data with
assigned numbers or artificial minimums and
maximums, and numbers that are truly random.
Despite these limitations, Benford’s law can still
be a powerful financial sleuth. Both auditors and
managers must use tools such as Benford’s law in
combination with their professional judgment and
investigative skills to uncover fraud. 