Testing for Duplicate Payments
Regardless of how well designed and operated, any disbursement system runs the risk of issuing duplicate payments. By some estimates, duplicate payments amount to an estimated ½ to 1% of all payments made. Although this may not sound like much, for an organization processing $1,000,000 in payments annually, some $5,000 - $10,000 may represent duplicate payments. Although most vendors will return duplicate receipts, this is not assured in every case.
What are the principal reasons duplicate payments occur? There can be multiple reasons, but the most common are that 1) duplicate vendors have been established, 2) invoices are not perceived as being paid timely, which encourages vendors to re-issue invoices, 3) lack of coding standards or adherence to coding standards for inputting vendor and invoice
information and 4) garden variety key punch errors occur (which will not be detected by most systems).
Typical Audit objectives are to assess the adequacy of controls over payment processing systems to help ensure that all payments are accurate. One step to help accomplish that objective is to test the extent, if any, that controls are not working. This can be done by quantifying potential duplicate invoice payments and duplicate vendor master file entries. Before testing controls over duplicate payments, the auditor should first test controls over the vendor master file to prevent or detect duplicate vendors being set up.
Recommended Approach
The identification of any potential duplicate records is based on identifying two or more records having one or more key criteria in common. For example, if two vendors have the same IRS taxpayer identification number (TIN) they are likely to be either the same vendor or else affiliated. Likewise, if two vendors have the same bank account and bank routing number, they are also likely to be the same (or affiliated) because few unaffiliated vendors will share a bank account.
There are quite a number of attributes that may be tested and the purpose of this monograph is briefly outline them, along with a suggested approach for the most cost effective means of accomplishing the tests. Note that in most cases, the auditor will need to determine which attributes are most likely to be the most effective in any particular situation. Often, a certain amount of trial and error testing will be required.
Types of matches
Once tests over the vendor master have been completed, then tests for duplicate invoice payments should be performed. Tests for duplicate invoice payments can be performed by identifying two payments which are similar. Note that unless system controls are not working, it is unusual to encounter an exact duplicate on all key fields – vendor number, invoice number, invoice date and invoice amount. However, it is possible to identify potential duplicate payments based upon matches of invoice payments using one or more columns of information. Matches for potential duplicate invoice payments can be classified as two major types:
• “Exact” match • “Fuzzy” match
Exact match
An exact match is where the contents of one or more columns are identical for two payments. An example is two invoice payments having the same invoice number, same invoice date and same invoice amount (but possibly different vendor numbers). This example can arise when duplicate vendor numbers have been established.
It is also possible to have a match when certain information, e.g. invoice number, has been transformed. A common example is when embedded spaces are considered. Take the example of two invoices in the same amount having the same invoice date and from the same vendor. One invoice number “12345A” and the second invoice number is “12345 A”. Many invoice processing systems will consider these to be distinct invoice numbers. A similar situation can arise with case, e.g. invoice “12345a” and “12345A”. All of these invoice numbers should first be transformed before they are compared. Examples of types of transformations include:
• Removing all characters except letters and digits • Converting all letters to upper case
• Examining only the left 4 digits
• Rounding the dollar amount to the nearest $10.
Fuzzy match
Other instances of similarity, but not exact match, include transpositions. Few, if any, automated processing systems will check for transpositions, whether they occur in the invoice number, vendor number, invoice amount or invoice date. Transpositions are a special case of a more general test called “Levenshtein distance”. Levenshtein distance is a mathematical algorithm for measuring the extent of similarity between two pieces of text and will can detect not only transpositions, but suffixes, prefixes, character insertions, deletions, etc. The primary disadvantage of this technique is that it can require substantial CPU processing. However, this disadvantage can be offset by using a continuous auditing technique, and doing the analysis as an unattended process, likely during “off hours”. There is no “canned” approach that will work in every instance. Instead, the auditor should try various techniques to see which is most effective in identifying potential duplicate invoices in a particular situation. Often these techniques can then be converted into a continuous auditing process which can be very effective.
Key values to be tested
In a test for duplicate vendors, values to be tested typically include (if available) IRS taxpayer identification (TIN), bank account and bank routing number, social security number, street address, city, state, zip code, contact name and any other information which may be unique. In a test for duplicate payments, values to be tested typically include vendor number, invoice number, invoice date and invoice amount.
Rather than search for exact matches (which are rare), the auditor should consider first doing a transformation and then doing a test for exact match. Examples of transformations
include:
• Convert all characters to upper case
• Consider only a fixed number of the leftmost characters • Remove all characters except digits
• Remove all characters except letters
• Remove all characters except letters and digits • Round the amount (nearest $10,$100, $1,000 etc.)
Fuzzy matches include:
• Transpositions
• Measures based on Levenshtein distance • Soundex (for vendor match)
• Metaphone (for vendor match) • Similar text
Note that the transpositions may be combined into a single test e.g. first remove all but digits and then compare the leftmost five characters, etc.
Narrowing down the results
Often tests such those described here can result in too many records to be effectively reviewed. Therefore the auditor should consider limiting the results to just invoice amounts in excess of a certain amount, e.g. $1,000 or within a certain date range, e.g. the last twelve months. Also, certain vendors may need to be excluded from the test as they represent little risk – e.g. affiliated companies, IRS payroll or tax deposits, etc.
Road test
The procedures described here have been “road tested” with a small publicly held company which had about 450,000 invoices covering a three year period.
Implementation
All of the techniques described here can be implemented using open source (free) software such as MySQL (database) and PHP (scripting language). It is quite feasible to test fairly large transaction volumes (up into the millions) using a lap top or desk top computer.
All the procedures above have been incorporated into “Web CAAT” which is an open source audit software package that can be freely downloaded and used without restriction.