Ideas for Business
Use Benford's Law & Charts in Excel to Improve Business Planning
Benford's Law reveals an amazing characteristic of data. Not only does it help to identify fraud, it could help you to improve budgets and forecasts.
Unless you're a public accountant, you probably haven't experimented
with Benford's Law.
Auditors sometimes use this fascinating statistical insight to
uncover fraudulent accounting data. But it might
reveal a useful strategy for investing in the stock market. And it might
help you to improve the accuracy of your budgets and forecasts.
This article will explain Benford's Law, show you how to calculate
it with Excel, and suggest ways that you could put it to good use.
From a handsonExcel point of view, the article describes new uses
for the SUMPRODUCT function and discusses the use of local and global
range names.
Benford's Law Explained
Also called the "firstdigit law", Benford's Law says that for many reallife
sources of data, the first digit will be "1" about 30% of the time and
that small firstdigits will occur more frequently than large firstdigits.
The law is named after Dr. Frank Benford, a physicist working for
the General Electric Company. In 1938 he noticed that the pages of
logarithms for low numbers were dirtier and more worn than pages for
high numbers, and that pages for numbers starting with 1 were the most
heavily used of all.
Intrigued, he analyzed thousands of sets of numbers. These included
lists of the areas of rivers, street addresses in a phone book, baseball
statistics, population statistics, and numbers used in magazine
articles. In all cases, the smaller that the first digit of the number
was, the more likely was that first digit to appear.
Benford
derived a formula that states the expected frequency of each beginning
digit. In Excel, we would express this formula as...
Probability =LOG(1+1/FirstDigit)
...where FirstDigit can be a whole number 1 through 9.
This table shows the results of the formula. To illustrate, the
probability of 17.6% in the table for a first digit of 2 was calculated using this formula:
=LOG(1+1/2)
Testing Benford's Law With Stock Data
I tested Benford's Law using two sets of stock data from the Wall Street
Journal.
This page gave me data for the New York Stock Exchange and
this page gave me data for the Nasdaq Capital Market, formerly
called the Nasdaq SmallCap Market. In both cases, the data was for July
5, 2007.
To test Benford, I found the firstdigit totals for a variety of data
for all stocks in the two tables, then used charts to compare how
frequently each digit appeared with the frequency that the Benford
formula predicts.
To get started, I used links provided in the two WSJ pages to download a worksheet
containing each
dataset. I moved the two worksheets into one workbook; saved the
workbook under a new name; named one worksheet NYSE and the other
Nasdaq; deleted the columns I didn't care about
in each sheet; and formatted the table in each worksheet. To illustrate, here's
my formatted table for the NYSE worksheet:
To show the table in this figure, I hid rows 7 through 2100. To do so, I selected those rows and chose
Home, Cells, Format, Hide & Unhide, Hide Rows.
An Aside: Assigning the Range Names
So that you can understand the formulas that follow, I need to interrupt
this description to explain how I handled my range names.
To make my formulas easier to use and understand, I decided to assign
the column titles as range names for the columns of data. And because I saved the NYSE and Nasdaq tables in two sheets of the same
workbook, I needed to assign names that were local to each worksheet.
Most range names are workbookglobal. That is, if you assign the name Close to a range in
one worksheet, you can refer to the name in any worksheet in that
workbook. To illustrate, if I assigned the name Close to a column of
data, I could use that name in any formula in the workbook, like this:
=SUM(Close)
But in this workbook, I wanted names that were local to each worksheet. With
worksheets named NYSE and Nasdaq, I wanted to be able to specify which
range I wanted, using references like these:
=SUM(NYSE!Close)
=SUM(Nasdaq!Close)
The timeconsuming way to assign local names is with the New Name dialog. To
illustrate, I could select the range C3:C2104 in the NYSE worksheet
above,
choose Formulas, Defined Names, Define Name, Define Name...; enter Close in
the Names edit box; choose NYSE as the scope, and then choose OK. Then I would need to
repeat that process for each of the other columns I wanted to name.
The quick way to assign these names is to use the Create Names From
Selection dialog.
This dialog assigns a name globally if a global version hasn't already
been defined; otherwise, it assigns the name locally. Therefore, in
cases like this, I always set up a block of Reserved Names that I define
globally, but plan never to use; then I assign the real names locally.
To make sure that I don't accidentally use the global versions, I enter
=NA() in the named cells. That way, my formula returns the #N/A error
value if I use the global version by mistake.
To set up the global versions of the names, I first added a new worksheet to the workbook and
named it Control. Then I copied the row of titles shown in the table
above to a column in the Control worksheet.
To
do so, I copied the range A2:H2 above, selected cell A3 in the Control
sheet, shown here; chose Home, Clipboard, Paste, Paste Special; chose Values and Transpose
in the Paste Special dialog; then I chose OK.
I entered the formula =NA() in cell B3 and copied it down the column as
shown here. I added some minor formatting, then assigned the text in
column A as names in column B. To do so, I selected the range A3:B10;
chose Formulas, Defined Names, Create From Selection; made sure that only Left Column was selected
in the Create Names From Selection dialog; then chose OK.
In the NYSE worksheet, illustrated above, I selected the range A2:H2104,
chose Formulas, Defined Names, Create From Selection,; made sure that only Top Row was selected
in the Create Names From Selection dialog; then chose OK. I used a similar process
to name the table of data in the Nasdaq worksheet.
Testing Benford's Law, Resumed
After I named the ranges of data for the two exchanges, I summarized it
in two tables. Here's the table with the NYSE data:
Column E contains the numbers 1 through 9.
The formulas in columns A and B count the number of cells that have
numbers beginning with each of the nine digits. The first two formulas
return the closing price and the volume, respectively:
A4: =SUMPRODUCT(0+(VALUE(LEFT(NYSE!Close+0,1))=$E4))
B4: =SUMPRODUCT(0+(VALUE(LEFT(NYSE!Volume+0,1))=$E4))
In cell A4, the LEFT function returns the leftmost digit as text and
the VALUE function converts the text to a number. Then the formula compares
that leftmost digit to the number in cell E4. Because SUMPRODUCT works with
arrays, the formula performs this calculation on each cell in the Close
range, and stores TRUE or FALSE in a temporary list in memory. Adding zero to
those values returns a list of 1 and 0 values, respectively. Finally,
the SUMPRODUCT sums those ones and zeros, returning a count of all
numbers in the NYSE's Close range that begin with the value 1.
The first time I entered these formulas, Excel returned a #VALUE! error.
As I looked through the data I saw that it used the text "..." to show
missing values. Rather than creating a complex formula to deal with this
problem, I used Excel's Find and Replace dialog to replace all such text
with 0 (zero). Later, I saw that the PE column used "dd" to indicate a
loss in the prior four quarters; so I also replaced "dd" with 0.
The formula in cell B4 performs the same calculation for the NYSE's
Volume range.
Columns C and D contain dollar and percentage changes in stock price.
Because this data consists of decimal fractions that can be less than
zero, we must use a slightly different formula.
C4: =SUMPRODUCT(0+(VALUE(LEFT(100*ABS(NYSE!NetChg+0),1))=$E4))
D4: =SUMPRODUCT(0+(VALUE(LEFT(100*ABS(NYSE!PctChg+0),1))=$E4))
The ABS
function finds the absolute value of each number, removing any minus
signs. And multiplying each number by 100 moves the first digit to the
left of the decimal point. The rest of these formulas is like those for
columns A and B.
I copied the range A4:D4 down the column as shown. Then in the range
A13:D13 I used SUM functions to return the total of each column. (These
columns show slightly different values because data is missing from some
of the columns.)
Now that I had the summary data, I could write the formulas for the
chart.
F4: =LOG(1+1/E4)
This formula is Excel's version of Benford's formula, as I described
earlier.
Copy it down the column as shown above.
G4: =A4/A$13
This formula finds the percentage of values in the Close column that
begin with the digit "1". When you copy this formula to the range G4:J12
it will adapt to all nine digits for each of the four categories of NYSE
data.
Finally, I looked for a way to score how closely the actual distribution
of first digits matches the Benford pattern. I settled on this
formula, which I copied to the range H13 through J13:
G13: =SQRT(SUMXMY2(G4:G12,$F4:$F12)/9)
The SUMXMY2 function subtracts each value in one column from the
corresponding value in the other column, squares each difference, and
then sums the results. The function's name is derived from: SUM of X
Multiplied by Y, each quantity squared.
Dividing the result by 9 returns an average value for each of the nine
squared deviations. And finding the square root of this average value returns an
average deviation between the two columns of nine numbers.
I call this final score the Average Deviation, or AD. The closer the AD
is to zero, the more closely our actual data matches Benford's number pattern.
Honestly, statisticians probably would object to this
calculation. But it's simple to use and makes sense to
nonstatisticians: It merely represents the average amount that our data
deviates from the Benford pattern.
To give you an idea of how a chart of your data might correspond with a
specific AD score, the figure below illustrates six data series, in
green, and the Benford numbers in orange. You can see how the green line
strays farther from the orange line as the AD grows from .9% to 12.4%.
Near the end of this article I'll explain how you might use the AD for
business planning.
Charting Benford Data
After I created the NYSE table, I made another one for NASDAQ data. Then I created this
display of the charts of both data sets:
What do these charts tell us?
In both charts, Benford's calculation
is plotted as a thick orange line. But in both charts, the line is
barely visible because plots of the actual data match Benford's
calculation so precisely...with
one exception, the green line showing the closing price.
Notice that the NYSE has fewer stocks than predicted with closing prices that
begin with "1" and more stocks with closing prices that begin with "3", "4", and
"5".
Further, compare the Average Deviation for the the Close column in
cell G13 of the table above with the other ADs shown in that figure. The
AD for the Close column is 4.1%. This value is notably higher than the
other three ADs.
I suspect this pattern for closing prices arises because the average NYSE
stock is expensive, averaging more than $96 per share. As a consequence, companies often split their stock when
its price gets above $100. This makes "round lots" of the
stock (100 shares) more affordable for small investors.
We see an opposite pattern in the smallcap Nasdaq stocks, which have an average price of less than $10 per share. In
those firms, the chart shows that many Nasdaq
companies apparently prefer their stock prices to begin with "1".
After all, a $1.49 stock seems more important than a stock costing $.49,
and a $12.95 stock seems more important than a stock priced at $3.95.
The NYSE pattern got me wondering whether stock splitting made financial sense for
those
companies and their investors.
The plot of the PE ratio in this chart seems to show that NYSE investors
valued stocks with prices beginning with "4" significantly above other
stocks. Also, in general, it appears that the larger the first digit, the more
expensive a stock seemed to investors, and the less they valued it.
Now consider the yeartodate change in the stock price. People who
invested in stocks priced with larger firstdigits appeared to have
higher returns than people who invested in stocks priced with smaller
firstdigits.
That is, there's some indication that investors would benefit by buying
stocks with prices beginning with digits 5 through 9.
Keep in mind that this is a quickanddirty analysis based on only one
dataset. And the calculation ignores dividends. A more complete analysis
could produce different results. But at the minimum, this chart
seems to show that it might be worthwhile for serious investors to
explore whether using Benford's Law could help to improve investment
results.
Where Benford's Law Works Best
Benford's firstdigit number pattern is typically found in reallife
data independent of human or categorical influence. Here are some more
specific requirements:
Sufficient variability.
The data should vary by at least an order of magnitude. The more
variation, the better.
No
builtin maximum, minimum, or oftenrepeated numbers.
The Benford pattern wouldn't be found in a measure of the height of adults or
in the ages
of the members of a local senior center.
A record of purchases would be distorted by the frequent purchase of
one item for one price. Similarly, in a list of company expense reports, if
company policy states that expenses of $25 or more
must include a copy of the receipt, expect many expenses to be listed at
$24.95. This would distort the firstdigit pattern, of course.
Large sample size.
You want enough numbers to give the Benford number pattern a chance
to show itself. Unfortunately, I haven't found a source that offers
wellreasoned advice about how small a sample size can be for
firstdigit analysis.
This chart illustrates a nonmathematical way to address this issue.
It shows the average deviation for sample sizes of 10 through 610
numbers for both the closing prices and volumes for the NYSE data.
As you can see, the AD started at about 10% for a sample size of 10
for both series and dropped quickly to about 5% for a sample size of 60.
From there, the green line for the closing price quickly approached its
true value of about 4.1%. And the brown line for the volume continued
downward, coming close to its actual value of 0.5%.
Based on this limited test, it looks that about 50 numbers will give
you some idea about the degree to which the Benford pattern applies to
your data. But 500 numbers should give you an excellent idea.
This assumes, of course, that your data is in no particular order. If
it's sorted by the column you're testing, you would need to test the
entire list, no matter how long it is.
Results of standard transactions or calculations.
Numbers generated in the normal course of business tend to follow
Benford's Law.
In contrast, lists of check numbers or purchase order numbers would not
work. These are numeric labels and not true values.
And particularly, numbers made up out of thin air would not
follow Benford's pattern. This is why Benford's Law is useful for auditors
searching for fraud, and why it might also be useful for improving
business plans.
Using Benford's Law to Improve Business Planning
Back when I was a CFO for small companies, I quickly learned to distrust
the budgets and forecasts that certain managers gave me. These people
would open my budget forms and quickly enter their guesstimates. I
variously called these either NoThought Plans or FiveMinute Plans.
My challenge was to know which plans were the result of real thought and
which were the result of nothought guesses. I first assumed
that any number ending with several zeros was a nothought guess. But
managers quickly learned to add randomly chosen numbers to their
guesses, making their plans seem more precise.
Now, years later, I realize that Benford's Law might have helped me to
find the nothought plans.
To see how this might work, consider two sales managers.
One manager spends five minutes with his sales forecast. He takes a wild guess about how each
product, region, or sales person will perform; he reduces
his guesses by a fudge factor; and then he quickly enters his "forecast"
by month.
Because this data comes mostly from the manager's imagination, you
wouldn't expect it to follow Benford's Law.
The other manager is much more precise. She carefully considers past
sales performance by region and month. She estimates the
business she expects to do with each of her largest customers. She looks at
price quotes outstanding and the likelihood that each quote will produce
a purchase order. She considers the likely returns from planned
marketing projects. She gets detailed estimates from her sales staff.
And she might even use some form of statistical timeseries forecasting.
Because this forecast is more closely linked to realworld data, you
would expect it to follow the Benford distribution more closely than the
other manager's guesstimate.
The same logic could apply to budgeting expenses.
Therefore, here's how you probably could improve your business plans:
(1) Calculate the Benford distribution for each plan that your managers
submit, and then calculate an Average Deviation score for each plan.
(2) Sort the results so that the largest AD is at the top of the list.
(3) Work your way down the list,
quizzing managers about the methods they used to create their
plans. And request revised plans as necessary.
(4) Continue down the list until you either find managers who truly have thought about their plans, or until you run out of
time...whichever comes first. If you do run out of time, you'll know
that you probably have improved the worst of the plans.
Alternatively, if you know you'll run out of time, you could
isolate the plans that have the highest ADs and sort them by the total
amount of the plans. That way, you can work down a list of the largest of the
worst.
Honestly, I've never used this method on the job. But it has every
reason to work. If you decide to give it a try, please use the Comments
link at the top of this article to let me know how it went.
Tags: #excel, #benford's law, #NYSE, #SUMPRODUCT, #LOG, #VALUE, #LEFT, #SQRT, #SUMXMYS, #range names,
