Last month’s article focused on the causes of duplicate payments. Now, we’ll take a closer look at ways to improve your payments processes and technological steps you can take to prevent duplicate payments.

It is impossible to catch all erroneous payments. However, steps can be taken to reduce or eliminate many errors. A series of simple modifications can prevent erroneous and mistaken payments, which ultimately have a positive impact on cash flow.

There are two distinct approaches that can minimize duplicate payments: manual and automated procedures. Both have their requisite strengths and weaknesses, so for optimal results, it's best if you can utilize both techniques.

Manual Procedures

Below is a list of tried-and-tested manual procedures to help you prevent duplicate payments from going out the door:

• Develop rules for invoices without invoice numbers — Create a list of rules for handling invoices without vendor invoice numbers. This will increase your chance of catching more duplicate entries. Examples include using a combination of the vendor number, invoice date and purchase order number. Regardless of the approach, the key is to use it consistently.

• Doublecheck all large dollar transactions — Typically, a small percentage of transactions account for a large percentage of total expenditures. Pick an amount based on invoice amount stratification and carefully check all transactions involving more than that amount of money.

• Pay on time — The more invoices paid on time, the more likely duplicate invoices and statements will not be sent from the vendor, a top reason for duplicates.

• Staff appropriately — Maintain staffing levels with processing volumes. Use temporary staff in months that require more assistance due to the cyclical nature of the business.

• Track rejects and refunds — Periodically analyze erroneous transactions that your staff catches for root causes. As you find a vendor’s account, you can eliminate a significant percentage of the errors while processing the issues.

• Use a payment recovery firm — Such firms specialize in finding and recovering erroneous payments and are paid a percentage of the amount of money they recover. Given their contingent nature, many companies have decided it is cheaper to hire a recovery firm than to do the work themselves.

Automated Procedures

Very few organizations actually track and/or report the erroneous payments they find using computer tools. Below is a step process to automating such reviews:

Step 1: Determine what reports you will need

Match the process gaps with the appropriate reports. Consider these basic, intermediate and advanced reports:

Basic

• Same invoice number, amount, invoice date and vendor.
• Same invoice number, amount and vendor.
• Same invoice number and vendor.
• Same amount and vendor.
• Same purchase order, vendor and amount.

Intermediate

• Same numbers in the invoice number, amount, invoice date and vendor fields (removes many unneeded characters in invoice number such as punctuation).
• First four digits of invoice number, amount, invoice date and vendor (e.g., identifies duplicates where a user enters the invoice number and then a duplicate payment with an invoice number with an “A” at the end).

Advanced

• Same invoice number, amount, invoice date and different vendor (identifies same invoice payments to vendors with a different name in the accounts payable system such as “ATT” and “AT&T”).
• Same invoice number, amount, vendor and different check (identifies same invoice payments made on different days to vendors that may normally go undetected).
• Report of all vendors that are comprised solely of duplicate payments.

Step 2: Get data to support your reports

Given the reports you selected, you now have to identify the data fields you need. Generally, you should work with the invoice payment header file so that each record is listed as a separate invoice. Include all voids associated with the payments in the file to ensure that there are no false positives because of reports having transactions listed twice. Assuming a selection was made from the above reports, at a minimum, you would need the following data fields:

Step 3: Select software

Microsoft Excel can be used to run duplication analysis, yet it must be noted that Excel can only handle up to 65,536 rows per record in the 2003 version and over one million records in the 2007 version. To complete the basic report duplication analysis, first sort the file as you would like to run the analysis (e.g., invoice number and amount). Then, in a blank column, create an “AND” statement to identify any instances where the current row is duplicated on the bottom row. A sample “AND” statement, assuming only the invoice number is column A and the amount is column B, is as follows:

=AND(A1=A2,B1=B2)

Once this statement is placed in C2 for analyzing the first two rows, it is copied for the length of the data. Then, the auditor must review column C (in the above example) for all instances where column C reads “TRUE.” This process is fairly manual and time-consuming, but it works.

Another approach is to select a better tool such as ActiveData for Excel, ACL, IDEA Software or Microsoft Access that makes the process simpler due to built-in commands to complete the duplication tests.

Step 4: Review the results

Review all of the report results. This leads to some valuable lessons, with two of the top ones listed below:

• There are a lot of items to review … I will never get through this! Focus on the large-dollar payments first and then on a sampling of the smaller payments. Start with the more restrictive reports (e.g., invoice number, invoice date, amount and vendor) that point to more serious control problems and work toward the less restrictive reports (e.g., invoice number and amount).

• Most of the payments have been voided or repaid by vendors! For the returned payments, ask the accounts payable team for their listing (usually entered with a code into the system for easy reporting). For voided transactions, one approach is to first ensure the voided transactions are in the data file received and then run reports using the absolute value of the invoice amount. Although this will lead to a manual analysis, it will show payments that have been voided. A more advanced technique is a report that removes all voided transactions (and related invoices) prior to running the analysis.

While it's not possible to wave a magic wand and wish duplicate payment problems away, it is possible to minimize them by using the above manual and automated techniques.

Richard B. Lanza, CPA, CITP, CFE, PMP, president of Cash Recovery Partners, LLC, helps companies identify their hidden financial assets, mostly using technology and referring them to specialists. He has a decade and a half of experience in audit technology and recovery auditing, becoming a leading authority in these areas. His free Web site helps companies to identify cost recovery and prospective savings in all areas of their business.