2011-11-01
Welcome to part 4 of our guide to duplicate payment analytics. As always we hope it proves to be useful and informative.
Having introduced the concept of duplicate payment analytics and considered the risks posed by duplicate vendor accounts in your master data, we examine the mechanics of detecting duplicate payments.
Experience dictates that the majority of duplicate payment events occur because identical invoices are entered onto the payment system. This might sound obvious, but it’s useful to keep this in mind as you develop ever more complex analytics. The 80/20 rules dictates that you’ll find 80% of your duplicates with 20% of the effort. This is never truer than when searching for duplicate payments.
Unsurprisingly, it’s the systems with few or no controls that have the highest frequency of duplicate payments. Commonly too, even where controls exist, duplicate payments arise where users purposefully or accidentally bypass controls – perhaps in an attempt to clear their backlog, to meet targets, or to just be helpful. But how do duplicate payments arise? To answer this question, we must first consider why invoices themselves are duplicated. Sometimes it’s simple clerical errors on the part of the vendor that sends a document twice, but more infrequently a second invoice is triggered by a late or disputed payment.
Once an invoice arrives on someone’s desk it is keyed onto the system. Without effective checks or controls it’s easy to understand why payments are sent. The accounts payable process is a conveyer for taking invoices from the door to payments from the bank. The team that deals with it are tasked with ensuring payments are transacted smoothly and efficiently. And they do. Responsibility for ensuring payments are authorised, is normally relegated to the system, or other business users. Perhaps it’s a regular payment paid twice, or a purchase order that hasn’t been raised. Maybe the vendor’s paperwork isn’t correct, or orders are being rushed through by the business. Whatever the problem, accounts payable are always expected to make it happen. And they do.
Given this, it’s not surprising then that mistakes occur. But perhaps more interesting is the fact that the majority of these are detected. Accounts payable isn’t a colander where millions of duplicate payments pass undetected; in fact, quite the opposite is true. For every duplicate I’ve found though analysis, a dozen or more are reversed by accounts payable. But a small and persistent minority do appear to fall through the gaps. Analytics like those we will come to describe are then the only way to capture such anomalies. Controls are only as good as the people and systems which enforce them. This means of course that they don’t always work.
Identifying ‘potential’ duplicates is easy. It’s filtering out the noise of false positives that is the difficult part. The following six considerations will help your design.
We can be certain that duplicated invoices have the same quantities and values as their counterparts.
Invoice1.Amount = Invoice2.Amount
Keep in mind the net amount that should be compared as the application of VAT can vary. Analysis in a single currency will also help.
It may sound obvious but it’s the same vendor that issues the duplicate invoice. But is it the same vendor in your accounts payable master data? Keep in mind that vendors may have been duplicated (Part 3: Vendor Master Data – The Duplicate Payment Mine Field). Generate a duplicate vendor ID for accounts with commonality – name, address, phone number, bank account, VAT registration. Compare the invoices of linked accounts on this basis:
Invoice1.Vendor = Invoice2.Vendor
Or
Invoice1.VendorDuplicateID = Invoice2.VendorDuplicateID
The invoice reference may be the same, or similar. However, how it is input into but the user could vary. Consider the invoice reference 123/A. It could be entered as:
Consider ‘cleansing’ the invoice reference so that only numbers remain. 123/A or 123.A or 123-A or similar references become 123. Then in all cases the cleansed reference will match.
Keep in mind that invoice references may be missing or different from your duplicate:
Invoice1.Invoice Reference = Invoice2.Invoice Reference
Or
Invoice1.Invoice Reference ~ Invoice2.Invoice Reference
Or
Invoice1.Invoice Reference Invoice2.Invoice Reference
The duplicate invoice will often have the same document date. However, when it’s not the difference is unlikely to exceed twice your normal payment terms.
Invoice1.Document Date = Invoice2.Document Date
Or
Invoice1.Document Date Invoice2.Document Date then unlikely if difference > 2x payment terms
In systems where credit notes and invoice are linked, you should eliminate previously detected duplicates from your report through a process of matching and filtering. This will significantly reduce the amount of work required in investigating your potential duplicates. Where credit notes aren’t linked programmatically, users will sometimes incorporate credit and invoice numbers into the reference field; usually beginning “CN”. These could be matched off against duplicated invoices. Where these entries are not included, the value of the credit note and the date of issue can be used as an indication of matching – but is much less effective.
Journals relating to duplicate invoices can be reversed. As data is rarely deleted from the database, these duplicate invoices will be picked up by the analytic. To remove previously reversed duplicates from our report we should look for evidence of a reversal and use it as a means of filtering. This will require additional knowledge about the data – i.e. transactions types, and matching references.
Removing previously detected duplicated isn’t vital, but is helpful in reducing workload and increasing the effectiveness of your analysis.
Duplicates rarely occur more than once. However, filtering out the noise of regularly occurring transactions to focus on the duplicated payments can be difficult. To limit the volume of false positives, consider hardening your match criteria; passing only those which exceed a threshold value; or restricting matches to identical document dates and invoice references.
Commonly detected events include regular monthly payments such as utilities and rent. With similar invoice references they can be difficult to filter – even harder to detect the genuine duplicate. Better to concentrate on the description to identify common ground like the month e.g. “Jan” or “January”. Time spent searching these regular payments will often prove to be worthwhile as utilities and rent will be subject to less scrutiny. These are the payments that are often duplicated.
Arbitrary rules can be used to score candidates in terms of their likelihood of duplication. For example, those candidates with the same document date might score x10, whereas those with a document date +/- 31 days might score x1, any greater than 31 might score x0.5. This can be effective at lifting matches out from the noise. A tool like ACL lends itself to the task quite well as rules can be tweaked and developed before your report is passed into production.
Unfortunately no, you can’t only look at payments. Several invoices and credit notes may be rolled up into a single payment. The relationship is not therefore unique. Analysis should be focussed on the invoice, with consideration given to mitigating circumstances such as – ‘payments on hold’, ‘entries reversed’, ‘credit notes issued’ to avoid unnecessary investigative work.
…duplicate payments occur when controls breakdown, controls are circumnavigated or are absent from the process. Invariably, they will have arisen through a chain of errors which began with the receipt of a duplicate invoice. Checks by the accounts payable department will prevent the majority of these from being paid, but a small and persistent minority will make it through. It’s this small fraction of transactions that your analytics will be looking to recover.
Developing your duplicate payment analytics with consideration given to the aforementioned areas will give you the best possible chance of detecting your duplicate payments.
Good luck and happy hunting!
