Rich Lanza joins us on the ACL Blog, with a new series called Practical Data Alchemy Using ACL Software. 

In my long travels with ACL software, I am almost embarrassed to admit that one of the largest audit findings made to date was using the ACL Analytics AGE Command and done in about one or two minutes. While much more complex routines and systems have been built in ACL Analytics to detect advanced error/fraud schemes, sometimes the simple approaches work the best. The approach saved tens of millions for the company lucky enough to run its data through ACL Analytics and was identified in the first two weeks of installing the product.

Like most companies starting in analytics, the suggestion was made to first review the accounts payable spend. We therefore worked to extract an accounts payable invoice history file that was provided in my first week on the job. This file listed for each row/record:

  • total invoice value
  • invoice number
  • vendor number
  • check number
  •  invoice date
  • entry date and
  • check / payment date


With the data in hand, we focused first on data validation which, mind you, is one missing links in audits using analytics. This validation process worked to stratify, age, identify average spend by vendor, and even reconciling some physical invoices to the data. We wanted to make sure that what was on physical invoices was what was in the system and furthermore, that we understood the full population for analysis. Everything checked out from a validation perspective and we concluded that the population was complete for review.

In a stratification we completed using the ACL Analytics STRATIFY command, we realized that there was a large set of negative values in the data. In our discussions with management, we were shown an accounts payable open aging report that ended at 180 days. So essentially all items over 180 days old were grouped/netted into one value by vendor and in most cases, was a payable value by vendor. Further, at the end of the report, when totaling the entire population of vendor invoices and credits 180 days and over, there was a payable value. Since the net over 180 was a payable value, the negative values were seen as less of a concern given at the end of the day, we still owed vendors money.

That is where our auditor professional skepticism kicked in so we decided to focus more testing on the negative values. Using the AGE command and in about 1-2 minutes, we simply ran it twice and added two IF statements below:

AGE ON Entered_Date CUTOFF 20130706 INTERVAL 0,30,60,90,120,180,365,730,995,10000 SUBTOTAL Invoice_Amount IF Invoice_Amount>0 TO SCREEN

AGE ON Entered_Date CUTOFF 20130706 INTERVAL 0,30,60,90,120,180,365,730,995,10000 SUBTOTAL Invoice_Amount IF Invoice_Amount<0 TO SCREEN

We realized that by extending the aging out beyond the 180 days to one, two, and three years that the vendor credits (or receivables from vendors) extended out for over three years with larger credit balances in past years. To improve our understanding of how this related to each vendor, we then created a small script below (or the below statements could be added to the command line one at a time):



CROSSTAB ON Vendor_Number Vendor_Name COLUMNS ENTERED_YEAR SUBTOTAL Invoice_Amount TO “Vendor Cross Year.FIL” OPEN

The crosstab query provided a summary of the net balances by vendor for each year in the data. We realized using this query that some vendors in past years had material net vendor credit balances, none of which was ever known from the original accounts payable open report that ended at 180 days. By focusing efforts on these net credit vendors, we were able to recover millions for the company which was never known to exist to the business process owners.

Concluding Thoughts

  • Don’t rely on business process reports to understand the business process data.
  • ACL analysis need not be advanced to have material results to the organization.
  • Consider whenever you can to look at data in different ways such as reviewing all negative values separate from positive values.