Accounts Payable Duplicate Invoice Audit Tool
Introducing mightymacros Dat Add-in
If you are looking for an accounts payable audit program that digs deeper to find duplicate invoices and payments, then you have come to the right place. ERP and accounting systems are heavily relied upon as a backstop for invoice processing, and we as users rarely understand nor do we question how well the system logic is performing. Accounting systems undertake a myriad of complex tasks and although they may offer great protection over common causes for duplicate entries, they do have their limitations, and duplicate invoices will pass through their safety net. It is important also to consider that people are often involved in the setting up, handling and distribution within the invoice document supply chain and invoice information is not always clear and consistent and easy to process, which can result in mistakes in invoice processing, vendor processing and document entry.
Mistakes on entry into a system can result in invoice duplications:
- Alpha and numeric substitutions of I, l, 1, O,0, Z, 2, B,8, 5, S in the invoice reference.
- Special characters and spaces inserted or omitted from the invoice reference
- Incorrect field entries (dates or amounts in the reference field)
- Typos
- Incorrect dates
- Incorrect vendor postings
- Duplicate vendors or companies
- Incorrect document types (PO & Non-PO)
- Incorrect amounts or currencies
- Any combination of the above
It would be simplistic to trust an accounting system to manage and prevent all duplicate entries. The identification in some cases can only be verified by a deeper and detailed analysis, by providing a list of potential duplicates. This invoice auditing tool is lightweight and affordable and uses SQL logic. There is no secret algorithm or mystery to the operation of this audit tool, so put simply, it strips the reference field of special characters and interrogates invoice data with more than 50 SQLs and looks for subtle changes to the invoice reference and other variables extracting them as matching items, keeping it all together, making it simple to analyse, record and track.
The invoice audit tool is designed to assist accounts payable and P2P staff, in the identification and prevention of unwanted duplicate payments. The tool is installed within MS Excel and offers a non-intrusive option for scanning your invoices for duplicate transactions, meaning no external contractor or third party gets to handle your sensitive information.
mightymacros Dat add-in is designed for regular use, ideally to be used for validation prior to a weekly payment run.
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
