Technically, we were still in the planning phase of the A/P audit – but had already identified several areas of risk that needed to be analyzed further.
The early payments represented a potential fraud. If you paid within 15 days, you should receive an early payment discount of between 1.5 -2.5% depending on the vendor’s terms. In addition to reviewing the invoices with ‘immediate’ payment terms, we calculated the difference between the latter of the receipt of goods or invoice received date, and the check date. Then we stratified using intervals of 0-5, 6-10, 11-15, 16-20, 21-25, 26-30, and >30 days. The total number and amount of transactions paid within 15 days was determined. The analysis showed that only 4.6 percent of the transactions were paid within 15 days, however, this represented almost 16 percent of the total payments made.
The auditors review the transactions that were paid within 15 days and found that early payment discounts were claimed in 87% of the cases. A Classify determined that the other invoices were all processed at the same A/P office; belonged to only three vendors; and were processed by two A/P clerks. The unclaimed early payment discounts, calculated at 2%, totaled $832,000.
The team leader had concerns about two possible fraud scenarios. In the first, the A/P clerk processes the original transaction for the full amount of the invoice and subsequently requests a credit from the vendor, for the early payment discount amount, and keeps the credit. The second scheme involves deliberating pay invoices early, without claiming the early payment discount, and receiving a kickback from the vendor.
To identify the first type of fraud, the team leader send out confirmation letters to the three vendors that had been paid early, requesting them to provide details on the terms and amount of the payment. All three vendors replied that they had initially been paid the full amount, but had subsequently sent the company a check for the amount of the discount. The auditors asked the companies for copies of the canceled checks; the two A/P clerks had endorsed them all.
A Classify on the ‘manual’ payments determined that all manual payments were being performed at one of the smaller A/P offices. When questioned about it the manager said that manual payments were being used for emergency salary advances. The region had many casual workers and a legacy pay system that was notoriously slow – meaning that summer workers who started in June might not be paid before September. Manual checks were used as a workaround.
As determined earlier, the amount of late payment penalties was increasing each year. A classify by A/P office told us that three of the nine invoice processing centers were responsible for $6.8M of the late payments. A Stratify on Amount told us that while there were more small-dollar invoices paid late, over $6.1M in penalties was associated with invoice amounts greater than $250K. A Stratify on days late also told us that more than 80% of these large invoices (with penalties totaling more than $5M) were more than 90 days late. So we knew: three A/P offices had problems with late payments of large invoices than were more than 90 days late – and we hadn’t even started the conduct phase yet.
During the walkthu at the three A/P offices we found that at the busy time of the year, boxes of invoices which arrived at the A/P office on a daily basis would not be processed for more than 50 days. While this explained a majority of the late payment penalties, an analysis by Responsibility Center determine that four large capital projects were also a problem. Their invoices were already 30-40 days late when they were sent to the A/P office.
The audit made recommendations to encourage these large projects to approve their invoices in a timely manner. It also recommended that, at the busy time of the year, the boxes of invoices should be opened when they arrived and large dollar invoices pulled and processed first. These two recommendations reduced the late payment penalties by $4.8M.
We still had to address management’s concern about possible duplicates. They thought this was a potential problem because of the number of times vendors were returning checks because they had already been paid. The system had controls to test for duplicates, but still it was happening. The system criteria for duplicates were: same vendor number, same invoice date and same amount. In two (or more) invoices had all three criteria the same – the system warned the A/P clerk that this could be a duplicate.
Our first test used the system criteria and found duplicates totaling $200K. A follow-up determined that the clerks had seen the warning but thought they had an “original” invoice so it was not a duplicate. Next we cleaned the invoice number by removing spaces, slashed, dashes, number signs and other special characters; and turned all letters to uppercase. Then we performed duplicates using: same amount and cleaned invoice number. While this identified many false positives, within two weeks we had found more than $1M in duplicates that had to be recovered. An analysis of these duplicates identified several problems including duplicates in the vendor table and a lack of procedures on invoice number entry.
So why am I talking about an audit from 1997 in 1999 – particularly when we had implemented a new financial system? The answer is that when we ran the duplicate test, and stratified on invoice amount we found the same problems. Duplicates were still occurring primarily because the old vendor table which had duplicates had not been cleaned before being uploaded to SAP. However, we found another cause as well – the document date in SAP should be the “invoice date” not the date the SAP document is created. However, clerks did not understand this and were letting the document date default to the entry date – so an invoice entered today and the same invoice entered tomorrow would have different invoice dates and not be considered ‘duplicates’ by the system.
We also looked at separation of duties by performing a crosstab by userid by document type. This identified users who were: creating vendors, processing goods receipt documents, and entering invoices. A quick review of the user master table also found users with more than one userid. This was an unexpected source of separation of duties problems.
Finally we tested the business requirement that all invoices over $25K must reference a Purchase Order. A simple filter Amount > 25000 and PO_DocNo = “ “ revealed that this control was not working. The IT folks told us they had turned off the control because it was slowing the system down – however they did not tell either the Procurement or A/P manager.
Since the control was critical for commitment accounting and determining free balance amounts, we recommended that they turn the control on. A test 6 months later revealed that all invoices over $25K were indeed referencing a P.O. however at two invoice processing centers many of invoices over $25K were referencing the same P.O. which had been setup as $1. The IT folks had turned off the requirement that the total of the invoices referencing a P.O. should be less than 115% of the P.O. value.
We also ran several fraud tests including: A/P clerk name is same as vendor; vendor is only used by one A/P clerk; vendor invoice numbers out of sequence; and fake vendors with similar names to known vendors (e.g. IBM Corp).
ACL Commands: FILTER, EXPRESSIONS, STATISTICS, DUPLICATES, CLASSIFY, STRATIFY, EXTRACT, EXPORT, RELATE, and CROSSTAB.
Lessons-Learned – this audit clearly demonstrated that data analysis can support the identification and quantification of risk. By looking at the key fields that supported the accounts payable process we identified risks that had not been identified by management including: payment terms, payment method, under utilization of p-cards, controls over commitment accounting, separation of duties, and duplicates. In addition, we were able to drill down into the data to determine where to go, what to look at, who might be involved in the fraud, etc.
Data drives most business processes and accessing and analyzing the critical fields that support the business process allows the auditor to better understand the efficiency and effectiveness of the process and to determine whether the IT controls are working.
Data analysis also proved to be a valuable tool for post-implementation audits. We were able to test system controls in the new SAP implementation (e.g. Separation of duties, mandatory fields, and 3-way matching)
Lastly, it is hard to put a value on the use of analytics. We certainly found $1M in duplicates that was recovered; and save $5.6M in late/early payments. So our 3-year savings was close to $20M, but we also improved efficiency by reducing the number of low dollar invoices (saving $6M/year); improved controls over commitment accounting (mandatory PO for large invoices); identified SOD issues; and discovered fraud. We also found problems with the master tables and conversion to SAP. All of these value-added results for an audit where management thought the only risks were duplicates and late payments.
I was cleaning out my floppies – no longer have a computer with a floppy drive – and found these. The earliest I still had was version 3.4 for 1994, but I had already been using ACL for three years by then.