2011-12-01
Welcome to part 5 of our duplicate payments serialisation. Having already considered the ‘whys’ and ‘hows’ of duplicate analytics, I thought we might spend time on the ‘whats’. Specifically, what data will be needed to perform your analysis and how it can be obtained. We’ll also consider how you can improve your knowledge in respect of the data you obtain.
You don’t need a lot of information to perform duplicate payments analysis but the minimum requirement is set out below.
To know whether you have enough information to perform your analysis, run through the checklist below. If the answer to any of the propositions is no, then consider requesting additional data.
An extract of ‘Vendor Master Data’ will provide the vendor’s details for follow up and, as discussed previously in our third instalment, will facilitate a duplicate vendor analysis.
An extract of ‘Invoice Data’ will be needed to identify any duplicates. As a minimum we need the amount, the invoice date, the invoice reference, and an indication of whether or not the invoice has been paid. The indication of payment may be obtained from the ledger.
An extract of ‘Payments’, ‘Credit Notes’ and/or ‘Reversals’ is important in determining the status of an invoice. Invoices that have been credited or reversed have been resolved to a satisfactory conclusion and should be excluded from reporting. An extract of the accounts payable subledger, often provides the relevant information in respect of the invoice, payment, and adjustments but sometimes data from the General Ledger may be required as a supplement. You should use your judgement in assessing whether information received is sufficient for your need.
Discussions with IT should provide enough information to understand the data available within the organisation – including the systems that can be extracted, the tables and fields which may be needed. Sometimes a data dictionary is required to inform your data request – so that an understanding can be gleaned about the tables and fields. But again, IT should be able to assist with this.
How the data is extracted and sent is a joint decision between you and IT. Receiving extracts as files may get things moving but you will be reliant on IT to deliver the extracts, now and in the future. Consider ODBC (open database connectivity) perhaps, or other types of automation to refresh your data sources independently. This may take a little longer to set up but will help to ensure consistency, reproducibility and independence. The performance impact of connecting to the production environment can often dissuade IT. So if they say no, perhaps connecting to a backup or disaster recovery box would be preferable?
ACL Desktop can load file types that originate from a broad range of systems and databases, or any ODBC source. We recommend avoiding the use of spreadsheets wherever possible to reduce issues arising from data inconsistencies but appreciate that this may not always be possible.
You won’t need vast amounts of information about your invoices to develop analytics. Information about the vendor, the invoice, the payment or reversal is all that is required to produce an effective solution. But, the more information you can include, the less follow up you’ll need to do to establish whether a payment has been duplicated or not.
Remember, IT is the key to understanding and extracting your data. You can’t do it without them so always consult early in the process. Consider requesting a data dictionary to better understand your data proposition, and always think about consistency and independence when requesting data, getting your extract via ODBC should always be a priority.
