This was another exciting year for me. First, in 1999, I had decided to take a year off without pay and do some sub-contracting for ACL (I forgot to mention this in 1999 post). It gave me the opportunity to really expand my analysis skills. Also, I worked on the development of DirectLink for SAP which really forced me to develop a better understanding of SAP – something that has been valuable ever since.
I also submitted short articles to the IIA’s Internal Auditor magazine and won two Honourable Mention Roundtable Awards for stories on “Travel Bonus” and “It is Really a Good Deal”. In 2001 I would garner my first Ted Keys Roundtable Award for “who’s Managing the Goods” and my second in 2005 for “of Mice and Money”. My last honourable mention was in 2005 for “Winning over the CIO”. The article highlighted audits I had worked on where – surprise – analytics was instrumental in arriving at the audit findings.
The year off working as a consultant gave me a wider view of audit and analytics. In particular, I got to develop several scripts for the Office of the Inspector General in the US. The audit identified numerous instances of waste, abuse and fraud. Using this experience, I develop a series of P-Card scripts to look for transactions that were non-compliant with policy and/or indicators of fraud (see screenshot).
The tests analyze 100% of transaction to look for a variety of know issues, including: duplicates or split transaction, multiples of $100, transactions over the limit; transactions for travel or transportation expenses, invalid card numbers and unused cards. The scripts use the Merchant Category Code (MCC) as well as keyword searches. Over the past 16 years, while the scripts have been tweaked and new tests added, the p-card scripts have identified frauds in more than 16 companies (that I know of). This included public and private sector organizations – both large and small. I am my scripts have identified more fraud as I shared with users around the world but don’t usually hear about the results.
Back at work (after a year of consulting, teaching, and writing) I was assigned to an inventory audit with a twist. Management was concerned about the length of time it was taking to provided needed parts to critical pieces of equipment. The standards called for items to be shipped from inventory within 4 days of the initial order; however at the largest warehouse the shipping time was closer to 6 days. Local management stated that the warehouse was overfull and that this made it difficult to find and get to the parts being requested. They wanted an additional building to handle the parts and materials – estimated cost was $70M. Audit was asked to assess the situation.
A majority of the items stored in the warehouse were parts and accessories for specialized machines, vehicles, ships, etc. In many cases the parts were obtained when the equipment was initially purchased. The company that designed the equipment provide enough spare parts for the estimated life of the equipment at the time of the original purchase. As a result, the parts were not available from commercial sources so the concept of moving to just-in-time inventory was not possible.
Using analytics we calculated the turnaround time and were not surprised to find stock numbered items where initial parts had been purchased ten or more years ago. However, a variation on this test found parts for which there had been no issues for five plus years. This meant that no one had requested even one “XYZ” part in the last five years and yet there were still many “XYZ” parts in inventory.
The inventory system used a hierarchical database system- IBM Information Management System (IMS). The high-level node was the parent equipment and the parts that were used by the equipment were the children. The structure could be up to 32 levels (parent, children, grandchild, great grandchild ….. etc). Fortunately, ACL for MVS had an IMS interface and we were able to read the database and extract the information required.
In addition to having over 1M different stock numbered items in inventory, parts could be used to support a number of different pieces of equipment. For example, a carburator might fit into six different engines which fit into twelve different vehicles. To complicate things even more, the twelve vehicles might have eight different life cycle managers (LCMs). An LCM was assigned to a major piece of equipment and managed the equipment for its entire life cycle: design, development, purchase, in-service use, repair and disposal. An LCM could be responsible for several related or unrelated pieces of equipment. One of the parts that had not been used in the past five years initially was a child of five types of equipment that had been managed by three LCMs. (For example, engines that belonged in five different types of trucks). All five types of equipment (e.g. trucks) were no longer being used by the company and had been disposed of – hence the parts (e.g. engines) were not being used. However, it also meant that the parts required by the engines (e.g. carburators) were also no longer required unless they also fit into other engines.
The process of using an LCM to manage major equipment had many benefits, but the audit determined that common parts supporting equipment managed by more than one LCM were no being identified as “no longer needed” when an LCM declared the major equipment “obsolete”. This was because the common parts might be needed by another piece of equipment. Until all parent equipment was declared obsolete, the parts were still required. In addition, while standard reports had been developed to help LCMs manage their equipment, no one had looked at reports that would report on parts supporting equipment managed by multiple LCMs.
We performed an analysis that identified all child-parts that had no parent records. This involved some complicated logic because of the structure of the database, but proved extremely valuable. In the end, the audit identified more than $300M worth of parts that could be scrapped because the all possible parent equipment had been declared obsolete. Using a 10% cost for storage of equipment, this represented a $30M savings. In addition, the parts were scrapped sold – bring in $12M in revenue. An additional benefit was also accrued – the warehouse was no longer full. Removing the obsolete equipment freed up 27% of the warehouse space. The improved the delivery time for requested parts and obviated the need to build another warehouse (saving $70M). The total saving was identified as $172M ($30M * 3 + $12M + 70M).
ACL Commands: ACL IMS interface; FILTER; EXPRESSION, STATISTICS; STRATIFY, CLASSIFY; DUPLICATES; SUMMARIZE; EXTRACT; EXPORT; RELATE; JOIN; and SCRIPTS
Lesson-learned – As I mentioned earlier, I learned lots from my time doing consulting work. More than anything, I learned that auditors are facing the same problems everywhere. They all have issues accessing and understanding the data, analyzing and presenting results, and getting recommendations implemented. I was worried that experience related to the public sector would not translate easily to the private sector – I was wrong. I had always examined the strategic objectivities and the operational environment before starting an audit; and by continuing to do this, I made the transition from public to private sector easily. I also worked in many different sectors: healthcare, retail, manufacturing, transportation, education, etc. I was a fantastic experience – but I was travelling all the time (38 trips in 10 months) and wanted to get reacquainted with my wife and kids.
The second lesson-learned was that complicated data structures can be dealt with – particularly if you have the proper interface. The inventory system was a very large database and it was 15 years old. It was a legacy system that was being maintained, but barely. Using a powerful tool like ACL, we were able to easily handle the volume or records and the complexity of the database structure.
The system no longer met the evolving requirements of the inventory managers (LCMs). This was a perfect opportunity for audit to show how it could add value – dollar savings, process efficiency, and system controls and reporting. After developing a script to identify obsolete equipment, we worked with the IMS programmers to have the report developed in IMS so the LCMs could run this every time equipment was decommissioned. We explained our logic and approach and then used our analysis to verify the accuracy of their report. At the time we called this “transfer of audit technology”, but it would be term continuous monitoring ten years later.