How To Use Excel To Detect Fraud

In this Tutorial We will look at some of the ways to detect Fraud In our data using Excel. This is a follow along tutorial and you need only excel Installed on your systems.

Ageing Analysis

Ageing analysis of bills outstanding is done to identify the bills for which amount is due for a long period of time. These bills can be classified as bad debts or provisions can be created for such losses in the books of accounts depending on the results of ageing analysis.

Download The CSV Ageing — Copy.csv from

shorya1996/DetectFraudWithExcel (github.com)

Now once you have the data with you , add 6 new columns

1. “Days outstanding”
2. “Not Due”
3. “1 to 30”
4. “30 to 60”
5. “60 to 90”
6. “>90”

On Column F in the second cell write the following formula

=IF(TODAY()>B2, TODAY()-B2,0)

Now Drag and fill all the rows in Column F

This column shows how many days are outstanding after the due date, in case Todays date is under the due date, then Days outstanding will be 0 other wise the difference between the dates.

On Column G second Cell Write the following formula:

=IF(F2=0, C2, 0)

This Column Shows how much amount is not due , it will be 0 in case days outstanding is not 0, else it will non zero.

Now on Column H , write the following formula

=IF(F2<= 30, C2, 0)

Now on Column I, write the following formula:

do it for Column J and K as well by changing the upper and lower bounds.

After this find the sum of column F, G, H, I, J and K

This is how you can perform ageing analysis, this was a very simple example to deal with ageing analysis but a good starting point in terms of fraud detection.

How To Create Pareto Chart

A Pareto Chart is a graph that indicates the frequency of defects, as well as their cumulative impact. Pareto Charts are useful to find the defects to prioritize in order to observe the greatest overall improvement

Take the invoice amount column out of the Ageing csv file and add a new tab , we will perform the analysis on this column.

select the column and click on insert > recommended charts and select pareto chart.

Benfords Law

Benford’s law (also called the first digit law) states that the leading digits in a collection of data sets are probably going to be small. For example, most numbers in a set (about 30%) will have a leading digit of 1, when the expected probability is 11.1% (i.e. one out of nine digits).

Benford recommendations are:

So , lets find out the first digit from our data set , copy the invoice number column from ageing dataset and keep it in a new column

In Column B cell 2 write the formula:

=LEFT(A2:A157,1)

Now click on Insert tab , and pivot table and select First Digit in rows and Values

Now lets find out the Percentage distribution of each Count by dividing each count with grand total 156

Detect Outliers using Box plot

Take Invoice amount Column from the ageing data set and put it in a new sheet.

Select the column and click on recommended charts in insert Tab. Choose Box and Whisker chart from all charts.

This is how you can use excel to analyse your data and detect fraud.