Making the situation even more complicated, there can be instances where employees override system built-in controls. Also, judgment is required in order to identify what constitutes a "duplicate". For example, a transposed invoice number may have been entered once and then the correct invoice number entered a second time. Not all systems will detect such a duplicate payment.

Besides duplicate payments, some payments may be mis-keyed as to amount, e.g. $12,500 instead of $125.00. These types of errors can be often be identified through a review of outliers or by performing the "relative value" test.

An additional test which can be relevant in certain instances, is to obtain a distribution as to the last two digits of the invoice amount, in order to see if there appears to be rounding or other unusual patterns.

Estimates as to losses due to duplicate payments vary, ranging from as little as .1% to 1% or more. It is essential to detect when instances of this may be happening, as the dollar amounts involved can become quite large.

Audit Objective

The objective is to identify potential instances of duplicate payments and possibly determine the control weaknesses that allowed this.

Audit Procedures / Audit Program

1. Obtain invoice data in electronic format, note data elements.
2. Sort the data by vendor.
3. Perform an automated search for duplicate payments, by specifying the names of the fields, i.e. which field is the vendor, which field is the invoice number, and which field contains an invoice date.
4. Determine potential outlier amounts by vendor.
5. Summarize the data in order to determine the range of values and the variability expected.
6. For each vendor, perform a test of relative values, in order to identify potential keying errors.
7. Write a report explaining your observations, and provide conclusions and recommendations.

Example Data (Excel and Flat File)

Excel WorkBook Sheets InvoiceData
Tab separated file TestCRInvData.tab

Scripts to do the Data Extraction

Description of Analytical Procedures Performed

First sort the file provided in Vendor sequence
This can be done by opening the file in Excel, sorting and then saving as a tab separated file, or use EZ-R Stats for Windows Tools | Sort, and specify the input file as "InvoiceData.tab", the output file as "SortedInvoiceData.tab" and the sort key as surveyor.

Using EZ-R Stats for Excel, choose the menu option Tools | CashRecovery.
Specify input file "SortedInvoiceData.tab", output file "PotentialDuplicates.tab"
Variable to be tested will be InvoiceAmount, Vendor is Vendor, InvoiceDate is InvoiceDate
By variable name is Vendor.

Screen Shots of Analysis

Screen Shots of Output

Discussion of Results

Alternative Approach - Batch Commands / Scripts

The analysis can also be done using a command line:

sort /ezs/temp/sort.ezs TestCRInvData.tab SortedInvoiceData.tab 99999 Vendor which uses a DOS window.

then run the cash recovery analysis

CR.exe -e/ezs/temp/CR.ezs -iSortedInvoiceData.tab -o/ezs/temp/crresults.tab Vendor Invoice No Amount DatePaid MDY 10

This command also results in a report in a DOS Window.

The analysis can also be performed using scripts:

options obs=max
libname test 'c:/DW/CS';
proc sort data=CRInvData;
by Vendor;
out = test.SortInv;

*;
* cr0.ezs;
* cash recovery procedure;
* run analysis on vendor payment data;
libname test '{%libout}';
libname in '{%libin}';
proc cr data=test.sortinv fmt=mdy;
by vendor;
var InvoiceNo Amount DatePaid;
out = test.invtest;
run;