How to: Apply Benford’s Law in Excel to Detect Fraudulent Activity

We apply Benford’s Law here at the Oregon Audits Division as part of our fraud investigations.

For those who haven’t heard of it yet, Benford’s Law is a natural phenomenon that occurs in certain data sets. Just as the Bell Curve predicts certain distribution of numbers, so does Benford’s. You can use Benford’s to detect fraudulent transactions by looking for outliers.

Benford’s Law predicts that the number 1 will occur more often as the first digit than any other number. In fact, the number 1 is about 6 times more likely to occur than the number 9 (30.1% vs. 4.6%). The law can also be applied to the first two digits and other applications, but we won’t get into that now.

So what data sets conform to Benford’s? Well there are some, like the drainage of rivers, that do not apply to auditing, but there are also plenty of financial transactions that do.  First off, you want to have a dataset that has a large sample size.  Ideally, over 1,000 records.  This is one of the cases when 30 is a very inappropriate sample size.

Second, you want data that is not limited. ATM transactions for example are limited because there are minimum and maximum withdrawals.  They also generally require increments of $20.  Being limited also includes using assigned values like invoice numbers.  All of the digits (1 through 9) should be possible.

The data should also ideally cross multiple orders of magnitude (e.g. 1 to 10, 10 to 100, 100 to 1,000).

Here’s a list of data that should generally conform:

  • Home addresses
  • Bank account balances
  • Census data
  • Accounting related data such as Accounts Receivables
  • Transaction level data

Now that I know what data to use, how can I analyze it? With Excel of course!

Steps:

1 – Load Data in Excel

2 – Calculate first digit

3 – Run Benford’s using Countif

4 – Graph

The following uses real world data that helped to convict several fraudsters in Oregon.

Screenshot of Steps 1 & 2

benfords 1

Using the left function, you can calculate the first digit of a number.

Screenshot of Step 3

benfords 2

Using the countif function, you can calculate the number of first digit in your data. You will need to calculate the percentage too.  The log formula on the right is Benfords Law in numerical form.

Screenshot of Step 4

 benfords 4

Looking at the graph, you can see that the digit 1 is overrepresented. The next step is to drill down on records that do not match Benfords.  A closer examination of these records with a first digit of 1 will yield a large number of $100 transactions.  Those $100 transactions were largely, if not all, fraudulent.  By using Benfords you can quickly identify suspicious patterns to help detect fraud.

Benfords will lead to false positives, so do not assume that if there is an outlier it has to be fraud. Next time, how to do Benford’s in ACL and why you should use the 2-digit Benford’s test.

Data Wonk Featured Fraud Investigation How To

Reblog: Oregon Economic and Revenue Forecast, Dec 2015

“Oregon’s economy continues to make significant gains. Job growth has slowed just a bit from early 2015 rates, yet remains more than strong enough to bring the unemployment rate down and account for the influx of new workers as population growth picks up.”

The Oregon Office of Economic Analysis released its latest quarterly forecast. As positive as the overall picture is right now, there are a few dark spots, including a persistent disparity between urban and rural economic health.

Interested in knowing more? Read the full forecast here.

Source: Oregon Economic and Revenue Forecast, Dec 2015

Data Wonk Featured Noteworthy

How To: Quickly Document Inflation Adjustments

Sam Ewing once said “inflation is when you pay fifteen dollars for the ten-dollar haircut you used to get for five dollars back when you had hair.”

cocacola

Source: Library of Congress

Inflation seems to be a constant in our lives. What used to cost a nickel no longer does (my grandparents loved to tell stories about their youth and the cheap Coca-Cola – see advertisement ).

What does this have to do with auditing? Well when we compare the value of dollars over time, it is important to make sure we have an apple-to-apple comparison. We can do that by adjusting for inflation to make sure the purchasing power of dollars today is equal to the purchasing power of dollars of yesteryear.

Luckily for us, the United States Bureau of Labor Statistics has a handy inflation calculator that lets us easily calculate what a dollar in 1980 is worth in 2015 dollars (answer: $2.89).

If I only need to adjust a few figures, I tend to take a screenshot of the calculator and document them in a PDF. Below is an example.Inflationcalc

In our next installment of auditor how to, we will show you how to document more complex inflation adjustments using Excel.

Ian Green, CGAP and OAD Senior Auditor

Ian Green, CGAP and OAD Senior Auditor

Data Wonk Featured

Material Deprivation, Poverty, Child Care and Inflation

Does the relative lack of material deprivation among impoverished Americans mean they have an easy life? Not necessarily. Many of the poorest Americans have indoor plumbing, cars, and cellphones, and yet still struggle to meet their basic needs.

Read more at the Oregon Office of Economic Analysis weblog about evolving poverty measurements, census data tracking, and our accelerating relationship with technology.

Source: Material Deprivation, Poverty, Child Care and Inflation

Data Wonk Featured