When Sarbanes-Oxley was passed in 2002, many companies were forced to take an in-depth look at internal controls.  Despite efforts to tighten controls, duplicate payments still occur by the millions.  The Department of Health and Human Services’ Office of the Inspector General “…estimated that Medicare made $89 million in potential duplicate payments for 2,000 other procedure codes.” [1] In a rush to find the overpayments, many companies have emerged:  A/P Recap, Automated Auditors, AP Recovery, ACL, Idea, and more.  That these companies are thriving is a testament to the fact that duplicate payments still occur at an alarming rate.

Many software packages have some controls over duplicate invoices but it usually takes some in-depth querying to find them all.  For example, many accounting packages do a duplicate invoice check and prevent you from keying in a duplicate invoice number for the same vendor.  But just add an “A” or change a penny and you are on your way to a duplicate payment.  Another common mistake is found in vendor files; duplicate vendor numbers for the same vendor is the number one cause of duplicate payments. 

This article describes tested logic used to catch ALL of your duplicate payments and duplicate vendors.  The logic outlined in this article has been used to identify millions in overpayments, for companies as large as MCI and Komatsu, but will also work for smaller corporations.

Duplicate Invoices

Common Mistakes and Solutions

#1:  Many people mistakenly run a query where only the invoice # is different (vendor #, invoice date, and amount are the same) but end up with a lot of junk on their reports that they have to comb through.    

Solution:  Try extracting only the numbers of an invoice number and matching on this. You can do this using a combination of a MID() function in Visual Basic and the ISNUMBER or ISNUMERIC function.  This way you will catch more true duplicates and less “junk”. 

#2:  Another common query used is one where only the invoice date is different.  This is a good query but often catches legitimate rent or lease payments. 

Solution:  Try identifying dates that are similar, for example, dates that are less than 30 days apart.  They are much more likely to be duplicates than invoices paid one year apart.  Also:  attempt to eliminate rent/lease payments from your duplicate report by eliminating long lists of duplicates.  If there are 4 or more duplicates per grouping (like the example below), the dupes are most likely legitimate rent/lease payments.

Vendor Name:                       Invoice #:              Invoice Date:                        Invoice Amount:

Realty, Inc.                            RENT                     3/1/2003                                 $2,010.00

Realty, Inc.                            RENT                     4/2/2003                                 $2,010.00

Realty, Inc.                            RENT                     5/1/2003                                 $2,010.00

Realty, Inc.                            RENT                     6/1/2003                                 $2,010.00

Realty, Inc.                            RENT                     7/3/2003                   $2,010.00                               

#3:  Credits are not factored in to the program.  If you match on EXACT amount, you may find duplicates but not the credit that reversed the duplicate.  Example:

            Vendor:                  Amount:                                Date:                       Invoice #:

                ABC, Inc.               $525,199.10            6/25/2002               INV001

                ABC, Inc.               $525,199.10            7/22/2002               INV001A

                ABC, Inc.               -$525,199.10           7/23/2003               INV001CR

Solution:  Try matching on the absolute value of the amount instead.  We actually take out credits and the invoice it applies to so that these are eliminated before the duplicate payment logic is run.

9  Core Algorithms

The next section outlines a suggested approach to identifying duplicate invoices.

You will need 4 fields at a minimum to identify dupe invoices: 

            Vendor / supplier number

            Invoice number

            Invoice date

            Invoice amount

This approach combines 9 core sets of duplicate payment detection algorithms.  The table below lists the basic structure of the algorithms:     

In the section below, we describe each algorithm and give examples of ACTUAL duplicates found using each.

Algorithm #1:  EEEE

This is the most accurate duplicate where the vendor number, invoice number, invoice date, and invoice amount are all exactly the same. 

Vendor Name	Invoice #	Invoice Date	Invoice Amount
GOOD FRAGRANCES INC	54891	4-May-01	$2,750.00
GOOD FRAGRANCES INC	54891	4-May-01	$2,750.00

Algorithm #2:  DEEE

This algorithm identifies invoices where the vendor number is different but the invoice number, date, and amount are all exactly the same.

Vendor Name	Invoice #	Invoice Date	Invoice Amount
H & L PACKAGING	29869-25	19-Aug-04	$2,139.25
PETTY CASH	29869-25	19-Aug-04	$2,139.25

Algorithm #3:  ESEE

This algorithm identifies invoices where the vendor number is exactly the same, the invoice number is SIMILAR but not exactly the same, and the date and amount are exactly the same.   

Vendor Name	Invoice #	Invoice Date	Invoice Amount
TURNCO PACKAGING CORP.	8012306	22-Nov-02	$8,870.40
TURNCO PACKAGING CORP.	08012306A	22-Nov-02	$8,870.40

When comparing invoice numbers to see if they are similar, try extracting only the numbers 1 through 9, and then comparing this newly created field.  For example, we create a field called “INVCODE” which contains only the numbers 1 through 9, eliminating all letters and all zeros.  We create the INVCODE using the following logic in visual basic:

for i = 1 To Len(invoicenum)

if isnumeric(Mid(invoicenum, i, 1))=true and mid(invoicenum,i,1)<> 0

Then tempstr = tempstr + Mid(invoicenum, i, 1)

Next

invcode = LTrim(tempstr) 

Then we match on INVCODE instead of invoice number using the following SQL code:

SELECT * FROM INVOICE_TABLE A, INVOICE_TABLE B

WHERE A.INVCODE=B.INVCODE AND

            A.INVOICENUM <> B.INVOICENUM

Make sure you specify that the invoice numbers cannot be exactly equal or you will end up identifying duplicates you already identified in algorithm #1 (EEEE).

Algorithm #4:  EESE

This algorithm identifies invoices where the vendor numbers and invoice numbers are exactly the same, the invoice dates are similar, and the amounts are exactly the same.  We usually define dates as “similar” if they are less than 30 days apart, but this variable can be changed to any number of days.  For a more accurate listing, we often decrease the date tolerance to 14 or 21 days.

Vendor Name	Invoice #	Invoice Date	Invoice Amount
CC PACKAGES, INC.	4206	12-Dec-02	$12,716.98
CC PACKAGES, INC.	4206	9-Jan-03	$12,716.98

 Algorithm #5:  EEES

This algorithm identifies invoices where all fields are exactly the same except amount, but the amount is SIMILAR, not exact and not completely different.

We define amounts as “similar” if they meet one of the following criteria:

1)             they are within 3% of one another, +/-

2)             one is twice as much as the other one

3)             they start with the same first 4 digits  (e.g. $1,234.50 and $123.45)

Here are three examples of each type of amount-similar duplicate:

1) amounts are within 3% of one another:

Vendor Name	Invoice #	Invoice Date	Invoice Amount
NORWALK, INC.	970003511	24-May-01	$1,650.26
NORWALK, INC.	970003511	24-May-01	$1,620.00

2) one invoice is half of the other:

Vendor Name	Invoice #	Invoice Date	Invoice Amount
TRANSPORT SOUTHWEST	23401/2/3	14-Jan-05	$4,143.00
TRANSPORT SOUTHWEST	23401/2/3	14-Jan-05	$8,286.00

 3) invoices start with the same first 4 digits:

Vendor Name	Invoice #	Invoice Date	Invoice Amount
HLC LTD.	Blank	22-Jul-02	$15,233.76
HLC LTD.	Blank	22-Jul-02	$1,523.38

 Algorithm #6:  ESES

This logic catches duplicates with the same vendor #, similar invoice #, same date, and similar amount.

In this example, the amounts are considered similar because one invoice is half of the other.

Vendor Name	Invoice #	Invoice Date	Invoice Amount
CC PACKAGES, INC.	11903	8-Sep-04	$4,290.00
CC PACKAGES, INC.	11903A	8-Sep-04	$8,580.00

 Algorithm #7:  ESSE

This logic identifies duplicates with the same vendor #, similar invoice #, similar date, and exact amounts.

Vendor Name Invoice # Invoice Date Invoice Amount CI SERVICES 22762 25-Jun-03 $2,725.13 CI SERVICES 22762A 17-Jul-03 $2,725.13

 Algorithm #8:  EESS

This logic identifies duplicates with the same vendor #, same invoice #, similar date, and similar amount.

In this example, the amounts are considered similar because they are within 3% of each other.  Also, the dates are within 30 days of each other.  It is possible that these could represent two separate legitimate payments, but having the same invoice number indicates they are true duplicates.

Vendor Name	Invoice #	Invoice Date	Invoice Amount
VULCAN INDUSTRIES, INC.	59282	14-Jun-04	$10,828.44
VULCAN INDUSTRIES, INC.	59282	13-Jul-04	$10,830.00

Algorithm #9:  DESE

This algorithm identifies duplicates with different (not similar and not exact) vendor #s, same invoice #s, similar dates, and exact amounts.

Vendor Name	Invoice #	Invoice Date	Invoice Amount
SMITH LABORATORIES, INC.	90796747	18-Jul-02	$5,367.32
ASBURY CHEMICALS	90796747	31-Jul-02	$5,367.32

 Algorithm #10:  Matching with blank invoice numbers:

Another algorithm that we have recently added to our repertoire is matching with blank invoices.  Normally, A/P systems will not accept invoices without invoice numbers.  Apparently, some checks still are cut without invoice numbers – look at the example below:

Vendor Name	Invoice #	Invoice Date	Invoice Amount
CHEMCUT SOUTHWEST, L.	3316983	1-Jun-05	$10,641.00
CHEMCUT SOUTHWEST, L.	Blank	1-Jun-05	$10,641.00

In another example, BOTH invoices were left blank, leading to an $84,000 duplicate payment:

Vendor Name	Invoice #	Invoice Date	Invoice Amount
C&C LABS, INC.	Blank	29-Apr-05	$84,246.60
C&C LABS, INC.	Blank	2-May-05	$84,246.60