How to Launder Money with Excel (Benford’s Law)

Hello and welcome to another informative blog post from Excel University. Today, we’re diving into a somewhat unusual topic – using Excel to launder money. If you aren’t familiar with that term, it simply means taking cash received from an illegal enterprise and running it through a legitimate business. That way, the money appears as if it has come from a legal enterprise, and thus, after paying taxes, you are free to spend it as desired. However, at the end of the post, I’ll show how auditors can spot this deception using Benford’s Law.

Note: I’m pretty sure it goes without saying, but this is meant as a humorous way to teach a few Excel features along with Benford’s law. It isn’t designed to show you how to actually launder cash!

Video

Step-by-step

Our discussion stems from a hypothetical question that, humorously enough, asks how to utilize Excel to launder money. For this, we’ve created a scenario involving a ‘questionable’ business that generates extensive cash flow. Let’s walk through this entire illustration using three exercises.

Exercise 1: Sales Log Table

Firstly, we want to create a sales log in Excel. This log becomes a home for our real sales transactions initially, and eventually, to our bogus transactions as well.

We start by storing our ‘real’ sales in a Table. To do this, we start by selecting any cell in our data, like this:

Then we use the Insert > Table command to store this raw data in a Table. Now the data is stored in a proper Table:

While this step isn’t strictly needed, storing our data in a Table has many advantages such as auto-expansion and the total row.

With our Table ready to go, it is time to head to the next exercise.

Exercise 2: Generating Fake Transactions

The next step gets interesting. Here, we create a series of fake transactions. We simply extend our list of sales IDs by selecting the sequential SalesID values and using the fill handle (lower right of the cell) to drag down. Excel auto-expands the Table and fills the sequence down.

With our fake SalesID values populated, it is now time to create the fake amounts.

We need to think about how we create these fake amounts. One option is to just think of some random amounts and type them in. But, auditors are well aware of human tendencies when creating ‘random’ numbers.

Auditors know that humans are more likely to pick an odd number like 7 when trying to create a fake amount. They are also more likely to avoid extremes. For example, people show a bias toward the center of a range, like picking a midpoint such as 50 when picking a number between 1 and 100. In addition, people may try to avoid picking numbers that might appear non-random, such as sequences (1234) or repeated digits (1111). They might believe that such choices seem too deliberate to be considered random. And there are other tendencies that auditors will look for, such as cognitive bias, overrepresentation of certain figures, clustering illusion, and the influence of recent numbers. So, since auditors are aware of these tendencies, we need to outsmart those pesky auditors. We need a way to create a more random sequence of values rather than picking and typing them in ourselves.

Fortunately for us, Excel has this capability! Let’s talk about the RANDBETWEEN function. It provides a random number that falls between the bottom and top argument values. For example, if we wanted Excel to return a random number between 10 and 99, we could use the following formula:

=RANDBETWEEN(10,99)

So, we enter this formula and fill it down and bam:

Once created, we lock these figures in place by converting the formulas into static values. We can do this by selecting the entire amount column, copying it, and doing a paste values. This crucial step eliminates the formulas and any evidence of our deception.

So we think.

Exercise 3: Unraveling the Deception

While this entire operation may seem clever, auditors have an additional trick up their sleeve known as Benford’s Law. It is a potent tool in the hands of auditors, and can easily reveal such a scheme.

To get a sense of what Benford’s law is, let me start by asking you a question. If you were to look at the first digit of every number in a large data set, what will the likely distribution be? You probably guessed it would be roughly even. That is, the number 1 would show up as the first digit like 10% of the time, 2 would also be 10%, 3 would be 10% and so on. Right? Or, maybe excluding 0, it would be closer to 1/9 = 11%. Maybe a table like this:

1. 11%
2. 11%
3. 11%
4. 11%
5. 11%
6. 11%
7. 11%
8. 11%
9. 11%

Well, to be honest, that is what I guessed as well.

But this is where we need to talk about Frank Benford for a moment. Frank Benford was an American physicist known for formulating Benford’s Law, an observation about the frequency distribution of leading digits in many real-life sets of numerical data. His work revealed that in many naturally occurring collections of numbers, the leading digit has the distribution shown below, which is a phenomenon that has applications in fields ranging from audit to fraud detection.

1. 30%
2. 18%
3. 12%
4. 10%
5. 8%
6. 7%
7. 6%
8. 5%
9. 5%

So, Benford’s Law provides the expected distribution of the first digits in any large, naturally occurring set of numbers. Thinking about this the other way around, a large set of numbers that doesn’t conform to this distribution may be suspect. The difference could be due to human manipulation or some other factor (like all products have the exact same price). But either way, when a large set of numbers doesn’t conform to Benford’s law, further investigation is warranted.

So, we can compare the sales log’s leading digit distribution to Benford’s distribution. To compute the sales log’s distriubtion of leading digits, we can use the LEFT function and a PivotTable.

LEFT

The LEFT function returns the first character of a cell. So, we can add a new column, called something like Left1, like this:

Then, we can write the following formula into the first data cell in that column:

=LEFT([@Amount])

And bam:

With the leading digit pulled into its own column, we can get help from a PivotTable for the next step.

PivotTable

We’ll use a PivotTable to compute the distribution of leading digits. To do so, select any cell in the Table and select Insert > PivotTable. Select a destination cell for the report, and click ok.

Then, we’ll drag-and-drop the Left1 field into both the Rows area and the Values area, like this:

The PivotTable looks a bit like this:

This shows the actual count of transactions, but we want to see these values displayed as a percentage of the grand total. So, we right-click any cell in the values column and select Show Values As > % of Grad Total. We can also control the cell formatting by right-clicking any cell in the values column and selecting Number Format. And the result is the distribution of leading digits of our sales log:

Now, let’s manually enter the Benford’s distribution next to the sales log for comparison:

We can quickly see that these numbers do not conform. Busted! Further inquiry is warranted.

Note: the smaller the sample size, the more variance to Benford’s is expected. As the volume of transactions increases, the variance to Benford’s distribution is expected to decrease.

To summarize, Excel is indeed a versatile and powerful tool. It can be used creatively – even for debatable purposes. However, its real power lies in its ability to analyze and present complex data sets vividly and simply. Whether you need to track sales transactions or uncover intricate schemes using Benford’s Law, Excel never disappoints.

If you have any alternatives, questions, or enhancements, please share by posting a comment below … thanks!

Sample file

FAQs

What is Benford’s Law and how does it apply to Excel data analysis?

• Benford’s Law predicts the frequency distribution of the first digits in a large set of natural numbers. In Excel, this law can be used to analyze datasets for anomalies that may suggest data manipulation.

Can you actually use Excel to launder money?

• The post humorously explores the concept of using Excel for money laundering as a way to teach Excel features and Benford’s Law, not as a guide for illegal activities.

How can auditors use Benford’s Law to detect financial fraud?

• Auditors can compare the leading digit distribution of transactional data against the expected distribution according to Benford’s Law to identify discrepancies that may indicate fraud.

What are the steps to create a sales log table in Excel?

• The post outlines starting with real transaction data, inserting it into a table for its benefits, and then expanding it with fake transactions for illustrative purposes.

How do you generate fake transactions in Excel for educational purposes?

• The RANDBETWEEN function is used to create a sequence of fake transaction amounts, demonstrating how to simulate data in Excel.

Why is it important to convert formula results to static values in Excel?

• Converting formulas to static values can help prevent the original method of data generation from being easily detected, especially in hypothetical scenarios meant for educational demonstrations.

How does Excel’s LEFT function and PivotTable feature assist in applying Benford’s Law?

• The LEFT function extracts the first digit of amounts, and the PivotTable computes the distribution of these digits, facilitating analysis according to Benford’s Law.

What does a discrepancy from Benford’s distribution indicate in a dataset?

• Variance from Benford’s distribution may suggest the data has been manipulated or requires further investigation to understand the deviation.

Is Benford’s Law applicable to all datasets?

• Benford’s Law applies to large, naturally occurring datasets with a variety of magnitudes. It may not apply to datasets with uniform distribution, fixed or small ranges.