Removing False Positives in Duplicate Payment Testing

2013-08-01

Removing False Positives in Duplicate Payment Testing

There is nothing more deflating that to identify an audit finding and then to find it was simply a “data mistake”. False positives can stymie any auditors work and confidence in using data analytics. Like any cloud’s silver lining, the false positive provides a bread crumb in the trail towards improved process analytics.

In the duplicate payment area, most-occurring false positives include:

• Remove voided checks / e-payments
• Remove cancelled invoices
• Remove cancelled checks / e-payments
• Remove intercompany account

…and then a more elusive false positive which is the removal of credit value invoices. While the first four false positives could be removed from the initial data extract with a simple EXTRACT statement IF there was a void/cancel/intercompany, the removal of credits to an invoice is based on the pairing of a debit to a credit transaction in the data. This is a little more complicated and will be detailed after explaining the situation. See below where we show two situations where some transactions need to be removed to help the analysis.

Please note in the below two situations, the same invoice number is used for the invoice transaction:

To effectuate the above results, the following script code can be used. Please note that this code was taken from a more comprehensive script you can download at the below link that will remove the credit/debit invoices and do a quick search for duplicate payments on invoice number and amount.

You can download a zip folder containing a text file with the script here:

http://www.acl.com/assets/blog/ACL_Blog_RichLanza_Script.zip

What the code does is first create a sorted version (Sorted.fil) of the input file on a join key of invoice number and the string (ASCII) version of the absolute value of the invoice amount. This is summarized on these key fields to arrive at a total invoice amount and as an OTHER field, the absolute value of that invoice amount (Sum.fil).

At this stage in the processing, we now need to identify situations where the sum of the invoice amount exceeds the single absolute invoice amount for that transaction. If this happens, it means that for all of the negative and positive transactions (see above examples), the result does not lead to a duplicate invoice number / value combination so these transactions are removed from the analysis. We also need to identify unique invoice number and amounts (no credits) so they are extracted as well (Extracted.fil). This provides a final key table to join back to the original table on invoice number and the ASCII version of the amount (NEW FILE WITHOUT VOIDS). This final result table has all invoices that have invoice numbers and amounts that are unique in the table, as well as, situations where there still exists a duplicate invoice value for an invoice number, after removing all associated credits.

Concluding Thoughts

• False positives can wreak havoc on an analytic review but can be used as learning opportunities to polish data and provide more confidence in the results.
• Simple extracts usually remove the majority of false positives.
• The remaining more complex false positives like credit/negative value invoices can be removed with a few more steps in ACL and are worth the effort as probably no one else in the company has done such an exercise.
• The goal should be to have the best and cleanest data in the organization as the auditor using the “in the trenches” knowledge of process owners to hone and refine their data.