Accounts Payable Duplicate Invoice Audit Tool
Introducing mightymacros Dat Add-in
Regardless of the level of trust and confidence one might have in an ERP or an accounts payable system for detecting, capturing and preventing duplicate invoice payments, you can never be reasonably sure that the outcome is optimal, unless validated against the results of an alternate auditing tool. So why not compare your accounts payable software and find out just how well it is serving you in preventing unwanted duplicate invoice payments by trialling mightymacros Dat add-in or by downloading the freeware version, mightymacros Dat Lite.
Accounts payable programs do have their limitations, and duplicate payments can pass through their safety net.
People will make mistakes, and believe it or not, so can machines and software programs, that rely heavily upon the information and logic that we provide to them, to make them work.
Mistakes on invoice entry or vendor setup can result in duplicate invoices:
- Mistakes in alpha and numeric substitutions of I, l, 1, O,0, Z, 2, B,8, 5, S in the invoice reference number.
- Special characters and spaces inserted or omitted
- Different entries for dates and/or amounts
- Entries to the wrong field
- Incorrect vendor postings
- Duplicate vendors or companies
- Incorrect document types (PO & Non-PO)
- Incorrect currencies
- Any combination of the above
mightmacros add-in tools are all locally installed within Microsoft Excel as an Excel ribbon menu as below:
Accounts Payable Duplicate Invoice Analysis Process
The accounts payable tool relies on a standard set of invoice information that you upload into an Excel spreadsheet from an ERP or accounting system. The information is then uploaded into a SQL database, where it is analyzed by the program and returns a report back into an Excel workbook. The SQL database that Dat add-in uses is a localDB version of SQL Server Express having around 5 times the capacity of an MS Access database. Among other advantages, the localDB version does not require any configuration or administration by the user.
The output report displays a list of potential duplicate invoices that will require further investigation. The analysis process involves the user investigating the items before a confirmation can be made. For reporting purposes, the tool provides a standard list of comments in the form of a drop list that can be assigned once the status of the item being investigated has been confirmed. A history of the analysis is stored by uploading the comments made. Maintaining a comment history prevents rework, on items that are still open when the report is regenerated. Commenting also helps to identify the cause and frequency as well as to provide information on the performance of the tool.
The Accounts Payable Audit Tool Overview
- Uploads invoices and bills to be paid to an SQL Server database
- Analyses the data for similarities and differences across key variables that include the Date, Vendor, Amount and Invoice Number
- Extracts a report of potential duplicate items to an output table along with any pre-existing comments, then sorts and splits the data onto separate tabs for analysis
- Comments are then made against each item recorded for statistical purposes and for potential vendor fraud evaluation