#1 Reason for NOT Doing Data Analytics

Do you know the #1 reason auditors don’t do data analytics (DA) much?

It is so simple, so obvious, I hesitated to blog about it. Let me know if you agree.

The number #1 reason DA is not used by auditors more is because the number #1 goal of auditors is to get THIS YEAR’S audit plan DONE.

Not to review the most serious risks OR do the most thorough job they can in the time they have.

They just want to get done.

As a result, DA is not made a priority. Many believe that auditors don’t have time to do more than just basic DA.

Matter of Perception?

I wonder if it’s a matter of perception.

First, too many people still think you are not doing data analytics unless you use ACL, IDEA, or some other purpose-built software. According to the Wikipedia, data analysis* is the “process of inspecting, cleaning, transforming, and modeling data with the goal of discovering useful information, suggesting conclusions, and supporting decision making”.

* Some people distinguish between analysis and analytics. I don’t think the difference is important.

The tool you use is secondary.

Some auditors use Excel to do simple and advanced analysis all day long, and yet because they are not using a branded data analytics tool, they think they are not doing DA. (Too many auditors can’t do even simple Excel tasks, but that’s another post.)

Second, some believe that you are not doing DA unless you work with full populations. Some would say that unless you’re using a full’s year of transactions, all the servers, or big data in general, what you’re doing is closer to sampling than DA. I disagree.

For example, you can recalculate interest paid on a month’s worth of transactions. While that’s not a year’s worth of data, you’re still analyzing the entire population for a month versus picking 60 transactions out of the month and recalculating those.

Or you can compare all the SQL servers to the list of servers being logged. Again, you’re not testing all servers (SQL, Exchange, domain controllers, etc.), but you are testing an entire population of SQL servers.

Also, reviewing 100% of one month’s transactions is still more thorough than reviewing 25 or 60 samples, right?

So in these 2 cases, you ARE doing DA, you just aren’t taking credit for it. So if you’re cleaning, transforming,** inspecting, filtering, comparing, and profiling data, you’re doing DA, whether it’s on a smaller scale or larger scale.

** An example of transforming is extracting the first 2 numbers (region number) out of a customer number, the server name (server1) out of a fully distinguished server name (server1.company.com), or extracting the user ID that approved a transaction out of unstructured text.

Another example is using the values of 2 fields to calculate a new field, changing upper & lowercase text to all CAPS, and changing STREET to ST in an address field. Basically, transforming is changing or creating data so it can be used to filter, compare, and analyze. In other words, if you don’t transform some types/forms of data, you can’t analyze it easily.

Third, some believe if you DO analyze the entire populati0n, but end up sampling in the end, you’re not doing DA. Some populations are so large and some processes are so complicated, you can’t FULLY test the entire population because you often have to investigate the details of a transaction manually.

Recently I had 25 million records that I analyzed several ways, eliminating transactions via several different tests, which resulted in 34,000 transactions left. I couldn’t find any other tests to eliminate any more transactions, so I picked a sample out of those 34,000 and tested those.

I still maintain that I tested ALL the transactions with multiple tests, and in the end, picking and testing 60 transactions manually out of 34,000 is providing better assurance than testing 60 out of 25 million. Since I was able to reduce the population and focus my final efforts substantially, I consider that good DA.

No Management Support–Really?

Sometimes DA isn’t a priority due to management support. And that can be a problem in getting a full-blown, integrated-into-the-audit-process DA program going–I get that. CAEs don’t want to make the investment.

But I still don’t pin the problem only on management.

My point in this post is that you DON’T have to have a full-blown DA program to do DA. You don’t need expensive tools and training.

You can do it Excel or a number of other tools you have on hand or can download for free.

In other words, you can start small on your own or in cohorts with a couple other auditors and show that DA provides more assurance, finds more problems, and in many cases, saves time, and in some cases, allows you to do what you couldn’t do manually without tools.

While management is responsible for everything, you CAN LEAD FROM BEHIND.

What the management-is-to-blame naysayers forget is that most leaders, once they see the benefits and cost savings (in some cases), will dive in with a little prodding. If that doesn’t work, then ask your CAE if she wants to be left behind (that’s a whole other post).

The worst case scenario is that you continue to do low-level, do-it-yourself DA, learn some new things, and get done faster than your colleagues.

Does DA Take Extra Time?

The biggest complaint I hear about DA is that it takes too much time. Well, it does and it doesn’t.

1) Anything worthwhile takes time. Why do audit managers think DA is different that anything else? I say we spend more time on DA and less time revising each audit report 10 times. The first 2 revisions may add value, but the other 8 are at the audit director’s whim, with little or no value added.

2) DA is complicated and you have to do some ‘relearning’ every time you use it. Most of the truth in that statement is directly proportional to how often you do it. Again, if you don’t do Excel pivot tables or some of the more involved Excel formulas frequently, you have to do a little refresh. The same is true with the purpose-built tools like ACL.

3) Time isn’t always the critical factor. When you have to join 2 files in Excel using a massive copy and paste, you can easily introduce errors or accidentally drop data. Doing a join of 2 files in ACL is not a difficult or time-consuming process (usually). It’s more reliable and easily repeatable. (Or use Excel PowerPivot, which is already in Excel–a future post).

Often, doing something right is more important that doing it fast. Or are you just trying to finish the audit without regards to why you’re doing the audit in the first place?

4) In some cases, DA can save you time. I know auditors who cannot do an Excel vlookup to save their lives. They still compare 50 items between 2 documents manually. Even if you have to refresh your understanding of vlookup first, doing a vlookup of 50 items is faster than doing it manually, and more accurate. And a heck of a lot more fun.

And while you’re doing 50, why not do the entire population in the file? And after comparing all items in file A to file B, why not then compare all items in file B to file A? If you use vlookup, you can do both comparisons faster than you can do the manual compare of 50 items.

So in this case, DA not only saved you time, but allowed you to compare the entire population of both files. Please remember this example the next time this issue comes up!

If you write an Excel macro or develop an ACL script, you can actually automate parts of an audit, or in the case of ACL or IDEA, an entire audit. While the development of these take time, they can save you even more. Calculate the ROI before you start.

Here’s a project I did in ACL that saves a LOT of time. Every month!

5) DA can save time by help narrow the scope of an audit. Profiling the data via a pivot table or ACL command can often help you see the data you need to look at more closely as well as data you can safely ignore.

So now what? Here’s your call to action:

1) Take credit for the DA you are doing; show progress.

2) Describe the expected results of doing more DA, such as expanded assurance, increased likelihood of finding problems, reuse, time savings, etc. Then ask if you can spend more time pursuing those goals, even if it means dropping some of the lower-risk manual testing.

4) Track your results. Few audit groups track the number of DA projects they do, the types of DA procedures they are doing, and how many DA procedures are performed in each audit.

As a result, how do they measure their DA progress and maturity? How do they know they are performing the right procedures? That basic population validation, profiling, and outlier analysis is consistently being performed across all audits?

Also, do you track which issues you find by doing DA that you probably would not have identified if you had only sampled? Or not transformed the data first?

So in conclusion, doing more DA can help you not only complete the audit plan, but add MORE value to your work. DA can also, in some cases, help you do it all faster. Or automate the project entirely with scripting.

The next time people tell you that they don’t have time to do DA, what they mean is that they want to do the same as last year (SALY).

They want to avoid learning something new.

They want to provide less assurance, and fall behind everyone else in the industry.

Sounds pretty risky to me… 



Filed under ACL, Audit, Data Analytics, Scripting (ACL), Technology, Written by Skyyler

11 responses to “#1 Reason for NOT Doing Data Analytics

  1. Audit Monkey

    You are ignoring the elephant in the room on the above post. (Unless I’ve missed it at 23.43 in London.)


  2. Audit Monkey

    Care to have a guess as to the shape and size of the elephant? (00.05 in London on this occasion.)


  3. Audit Monkey

    Getting the data. Reading the above, it is not clear whether you managed to obtain the 25 million transactions and placed these on a separate database for ACL purposes or managed to review processing routines for certain classes of data. However, the issue for most auditors is how to extract or obtain the data from the live database to a test database for sampling purposes. All to often the auditor is reliant on someone in IT to extract the data and put it into a rather large spreadsheet. Hence, the auditor decides to perform other routines, like not bothering!


    • My dear monk,
      Yes, getting the data is always a challenge. So many times I am told it cannot be provided, but usually the IT guy is blowing you off or doesn’t know how to do it. When that happens, I ask questions about whether he tried this or that.

      When I don’t understand the technology or systems involved, I go to a DBA or programmer friend who does and ask them what to do. Then I go back to the IT guy and almost always get the data.

      Regarding the 25 million records above, I was provided that data from various databases by IT. I thoroughly vetted the queries used to extract the data and verified some of the data back to the applications and published reports that summarize the data (earning amounts, sales, commissions, etc.)

      I loaded the data into ACL and analyzed it there, but am currently working on getting my own SQL database instance to dump the data into so that I can slice and dice it myself, as I know enough SQL to get by.

      Mainly, I want to query the SQL database directly for certain types of records as I’m trying to understand the data. Searching for a single record in ACL across 25 million takes too long, but in SQL database, it will be much faster.

      So, some of the analysis I will do in SQL, but mostly I will use the database to create smaller extracts of data that I will then load into ACL.

      I will probably also use ACL’s ODBC capabilities to run SQL queries and directly pull the data from that SQL database. It just depends which is easier.

      Probably using ACL ODBC, because I can script it for future use.


    • Monk,
      One other thing. My whole point in the above post was that (although your comment indicates it was not clear) even WHEN auditors GET data, they often won’t take the time to analyze it cuz they have to get the audit done and “data analytics is extra, not really necessary, and I don’t have time for it.”


  4. Thanks guys (all respondents). This is all good stuff. I’m seeing a gap in the conversation, in the space where data analysis meets audit objectives. There’s quite a lot about how but much less about why. I’ve seen this whenever DA is being discussed. Keep up the good work!


  5. Pingback: Will Robotics (RPA) Replace ACL? | ITauditSecurity

  6. Pingback: 10+ Signs Mgmt Doesn’t Really Support Analytics | ITauditSecurity

  7. Pingback: Dilbert Does Big Data | ITauditSecurity

Leave a Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.