Accounts Payable Audit Tool
Introducing mightymacros Free Duplicate Invoice Auditing Tools
Accounts payable systems have limitations, people make mistakes, and the biggest mistake is to rely upon a single resource to capture and prevent all duplicate payments.
Invoice processing mistakes can be difficult to trace and even harder to find. ERP and accounting systems do not always prevent vendor duplication and accounts payable systems are not well equipped to interrogate and investigate their own information.
Common causes for invoice duplication can include:
- 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 document dates and or amounts
- Incorrect entries or entries made into the wrong field
- Missing characters or numbers in the invoice reference
- Additional characters or numbers in the invoice reference
- Numerical or character order sequence changes in the invoice reference
- Incorrect vendor postings
- Duplicate vendor postings
- Incorrect document types (PO & Non-PO)
- Incorrect currencies
- Using non-specific unassigned reference numbers such as a date entry in the invoice number field is bad practice and results in poor data management and control making the process of duplicate identification extremely difficult.
- Any combination of the above
For speed and efficient processing, mightymacros tools are all locally installed on your PC and operate from within Microsoft Excel's ribbon as displayed below:
Accounts Payable Duplicate Invoice Analysis Process
The accounts payable tools rely 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