Accounts Payable Duplicate Invoice Audit Tool


Introducing mightymacros Dat Add-in

mightymacros Dat add-in is designed to identify uncaptured duplicate invoices before they become duplicate payments.

If you are looking for a tool that digs deeper to find duplicate invoices, then you have come to the right place. ERP and accounting systems are often heavily relied upon as a backstop, and users rarely understand or question system logic. Large systems, in general, are not specifically designed for detecting duplicate invoices. These systems may provide some assistance, but there are limitations, and duplicate invoices will pass through their safety net. Invoice document information is not always clear and consistent. At times it can be difficult to read and process, resulting in mistakes on 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, using a list of potential duplicates. mightymacros DAT add-in is lightweight and affordable and is designed to provide a list of potential duplicates invoices. mightymacros Dat add-in uses SQL logic, no secret algorithm or mystery to it, 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.

Dat add-in is designed to assist accounts payable and P2P staff, in the identification and prevention of unwanted duplicate payments. The tool installed within MS Excel, offers a non-intrusive option for scanning your invoice information 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.

Duplicate Analysis Process

 The 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 the user.
The Dat report output displays a list of potential duplicate invoice items 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.

General Overview

mightymacros Dat add-in menu:


The program menu is designed for ease of use, and needs only four button clicks to generate the duplicate report:

Click-1: Upload all new invoices, including open items.

Click-2: Upload open items or bills to be paid.

Click-3: Run the analysis process using 'Prepare'

Click-4: Run 'Create' to output the report

Most ERP systems will not prevent duplicate invoice entries when there is a difference in more than one of the key variables, such as vendor, reference, amount and date.  mightymacros Dat add-in cross-checks all of the key variables, looking for similarities and differences and reports on the items found.

Free Joomla! templates by Engine Templates