A Lifetime of Analytics

At the end of the year, I tend to reflect back on what I have done or not done.  It occurred to me that 2023 will be my 35th year using ACL Analytics (Galvanize, Diligent) on a daily basis. Throughout the years I have had many failures and successes. I have learned from identifying lessons-learned when things worked and even more importantly when they did not. I also took responsibility for my failures and shared my successes with the auditors on my teams.

In the late 1980’s and into the early 1990’s internal audit was considered an early warning for senior management. Audit searched for things that were going wrong and reported them which is probably why audit go a reputation for being a cop and having a ‘got ya’ mentality.  This changed significantly when the IIA positioned audit to identify weakness in controls. An even bigger change occurred in the early 2000’s when the audit focus transitioned to identifying and assessing the risk to the achievement of enterprise objectives. Audit was encouraged to be part of the management team and have a seat at the senior management table. So the focus of internal audit continues to evolve, but the audit process has not.

Back to the early 1990’s – personal computers were entering the workplace and data analytics was making an appearance in audit. Early adopters, like myself, saw it as a powerful tool to assist audit in testing controls and, later, identifying risk. I expected all auditors to incorporate analytics into their audit process, particularly those just entering the profession. However, team leaders and audit management are stuck in the past (Why did the auditor cross the road? Because that it what he did last year.)  Study after study in the last 30 years has identified a shortfall in the ability of auditors to effectively use data analytics.

My hope for the increased  use of analytics was rekindled when COVID sent many workers to a ‘work-from-home’ model. Surely analytics would become an absolute necessity. During a time when many businesses were experiencing serious issues, many auditors used this as an excuse not to audit (“management is too busy now”) rather than assisting in identifying and analyzing risks to assist management.  In addition, even though working remotely, unless audit had already built the required extract, transform and load processes, they were not able to use analytics.

The IIA standards espouse the use of analytics, the ACFE ‘Report to the Nations’ shows that proactive analytics can reduce the cost and duration of fraud, and every study on the audit profession states that analytics is a much-needed skill.  So, there is no doubt the value is well understood and demonstrated. I believe that anything worthwhile takes effort and analytics is no exception. Early, failures often means that you are pushing the envelope and provides an opportunity to gain experience. Do not let it detract from the longer-term objectives. I worry when internal audit, in trying to catch-up, is quick to jump on the latest bandwagon: Big Data, Robotics, AI, and other buzz words). Sometimes these are just a step too far. If you do not have a solid basis of analytics to build upon, the result is too often failure and a further reluctance to invest in data analytics.

For 90 percent of my career, I was a team of one and sometimes as many as three. My own analyses have become increasing complex and more integral to the audit objectives. (I should note that simple analytics, like filters, still form an import element in my analytics toolbox.)  Early analytics focused on simple filters and sorting of the data to spot overly large or small transactions. (My first analytic result identified $75K in overpayments – which would have paid my salary and the purchase of the audit software.) My middle years included an analysis to identify obsolete inventory using a hierarchical data base that had 1M nodes and thirty-two layers which identified over $300M in obsolete inventory and improved reprovisioning times dramatically. I was also involved in identifying a $340M fraud that had been ongoing for more than 10 years. Now, I focus on using analytics, not only to identify risks and control weaknesses, but to take it a step further and identify the root cause of the control weaknesses and emerging risks. The analytic results are not simply a bunch of Excel files or a visualization of the problem; they identify specific actions management should take to bridge the gap between risk and control.

I am no longer working as a full-time internal auditor – and there are times I miss it. Currently, I am pulling together and continuing to grow a vast array of analytics that provide assurance across core business lines, including accounts payable, vendor management, accounts receivable, customer management, P-Card, travel and entertainment, contracting, payroll, financial monitoring, and ERP controls. The results provide risk-ranked dynamic visualizations to highlight the materiality and impact of the weaknesses; as well as specific targeted recommendations to address the root cause of the weaknesses.

Dave Coderre (www.caats.ca)

This article has 8 Comments

  1. Nice article Dave. I wonder if you would be sharing your thoughts on the assurance analytics that you are developing on AP, AR, Vendor management, Payroll, Financial monitoring as you keep progressing?

  2. Excellent post. Reads like end-of-the-year nostalgia
    After roughly eight years, I feel like I can accomplish most things with ACL Analytics, but I know that there are missed opportunities because there are still many advanced scripting tools and methods that I lack.
    I looked at the consultation and courses you have made available, but was wondering if you are available for training one-on-one to help me go from advanced to expert, maybe from examples that you found challenging. I’m being kind of vague because I don’t know what I don’t know. However, I do know that you could help me bring my coding to the next level.

    1. Rob:
      Pretty sure we have met at one or more ACL Connections. In any case, I recognize the name.
      The easiest way to improve your ACL coding expertise is to have specific tasks you have to do. I would be happy to share some analytics requirements that may improve your ACL scripting, but as you say I need to understand better where you are currently at. I have seen people who were experts with a year of experience and people who were still basic users (with 10 years experience).
      Maybe we could have a Team meet to discuss and for you to show me what you are currently doing. Then I could map out some challenging analytics and assist you (if needed) with them.
      Dave

  3. Always a pleasure reading your posts Dave! It would be interesting to visualize some of your data one day, (most of my visualization work is done in Power BI, though the data prep is done in ACL for prototyping, and then SQL for production use). ACL is still my preferred prototyping tool after all these years, due to the flexibility of the tool to ingest data, and the readability of the code (Bonus: explainability for the auditors that want to know what the code does, even non scripters can read my comment blocks and get where the script is going). I keep reminding myself I need to pick up another copy of your book :). My other copy was loaned out at some point.

    1. Jeremy
      After years of not thinking visualization was important, I agree with you have been converted. A good visualization, will drill down allows non-technical users to get more out of the data.

  4. I just found this blog last night and oh my gosh, where has this been all my life! It took me a long while to find good content like this that also aligned with my work/education history. I am so excited to mass consume the articles. I’m someone that has their CPA but started off in IT Audit at a Big 4. Recently transitioned into a SME, consulting-like role in an internal audit function at a Fortune 500 and have been chasing the dream of marrying my CPA, my IT audit work experience, and the future I want in data analytics. Thank you for starting this blog and sharing your stories!

    1. Jack: Audit – internal or external – is an exciting profession and make for a great career. It also opens many doors because of the experience you get in audit: risk, IT controls, efficiency, effectiveness, communication, access to senior level clients, etc.
      Happy to hear that you find my muses useful. Enjoy/

Leave a Reply to Rob Turnbull Cancel reply

Your email address will not be published. Required fields are marked *