#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…