Or put another way, if you don’t acquire them in the next 1-5 years, you will no longer be an auditor.
Pretty bold statement, isn’t it?
What types of skills am I talking about? Here’s a few of the more simpler ones.
- Obtaining data by connecting directly to databases and writing your own queries.
- Population Validation — does the data pertain to my audit objective, and did I get all the pertinent data?)
- Validating data –determining which records in a file are not in the correct format (e.g., which records should be in date format, but are not).
- Understanding the data from 20,000 feet (what date ranges, averages, low and high values are included).
- Extracting only the data elements you need from a field (e.g., extracting ‘server1’ from ‘server1.acme.com’), also called transforming data.
- Concatenating fields together so that only one comparison is done across several fields rather than comparing each field separately (e.g,, combining user ID, database, and access level (JON316MasterUpdate) so that the current access can be compared to the access the user had last year).
You can concatenate with Excel’s Flash Fill function. Here’s another Excel example using the ampersand (&) character.
- Joining files together based on one or more common fields so that you can analyze all the fields together.
- Compare multiple files against each other to determine which fields in each file match those in another file (or don’t match).
- Compare values in a single field or multiple fields to determine which ones are mostly alike (fuzzy lookups)
- Finding outliers in the data (what transactions or values are drastically outside of the averages or standard deviation).
- Finding patterns and trends (customers who bought X also bought Y, or changes made to systems on Wednesdays failed the most often).
- Determining which areas (e.g., at the regional, office, or manager level) had the most errors, server failures, etc.
- Searching GL entries, expense reports, and email for key words that might indicate problems or fraud.
- Analyzing unstructured data, such as email, social media streams, images, videos, etc.
If you aren’t performing most of these operations/tests in your current audits, you are probably spending more time reviewing data than you need to. You can analyze your data much faster than you already are–and analyze ALL your data, instead of just sampling some items.
To do data analytics, you don’t need ACL, IDEA, Arbutus, Tableau, SQL, or other purpose-build software. You can use Excel or other products.
But you need to know the principles that drive data analytics and how to use some tools to accomplish your objectives. Having a tool like ACL or IDEA that is built for data analytics allows you to work faster, do deeper analysis dives, write a script that makes the process repeatable, and schedule your data extraction, analysis, and reporting so that it runs automatically.
The Near Future
So what will happen in 1-5 years if you don’t learn these types of skills?
- You’ll be stuck with all the manual testing that cannot easily be done via data analytics, such as did the proper person sign the form? But by that time, digital signatures will probably be checked via analytics.
- You will provide background information on financial processes and ideas to the data analytics team, which will do the testing; you will then write up the findings, but won’t get the glory. In other words, you’ll be more of a wordsmith than an auditor.
- You won’t be presenting findings to management because you probably never learned how to create graphics; analytics and graphics go hand in hand.
- You won’t get the dollars to upgrade your equipment; the data analytics team will.
- You pay increases will drop as more of the pie goes to the data analytic auditors.
- Eventually, in the years to come, if you’re looking for an auditing position, you won’t even be hired.
And once data analytics becomes mainstream in auditing, the emphasis will be on delivering the analytics faster. So in the end, if you’re still using just Excel and other manual options, you’ll be left behind as more analysis is automated (scripted) and scheduled.
Now, I realize not everyone is cut out for data analytics, which requires more technical knowledge than most of today’s auditors have. I know from trying to teach auditors in several companies that some people just don’t get it. And that’s okay today, but it won’t be okay in a few years. Not in this profession.
My point: Audit is changing and becoming more technical, even on the financial and operational side of the house; if you don’t understand how to obtain, validate, and analyze data, you might want to start learning it, or start thinking about switching careers.
Agree or disagree?
Again, what I listed above is the simpler stuff. I haven’t mentioned population distribution analysis, standard deviation, or other statistical analyses.
I’m just trying to sound an alarm…
So what do you do?
- Check out the links that I embedded in the above list.
- Work through my Excel: Basic Data Analytics series.
- Learn or improve your use of Excel Pivot Tables.
- Download the free Excel Fuzzy Lookup add-in from Microsoft.
- At the end of the installation, check the box that allows you to open the install folder on your computer. Open the README.doc, which contains instructions for running a fuzzy lookup. The install folder also contains the Excel spreadsheet mentioned in the instructions that you can use for practice.
- Check out the ACL posts on this blog, especially those related to computed fields and conditional computed fields.
- If you have an ACL license, the ACL Academy at the ACL website is excellent. I learned ACL years ago from their online training, and it has only improved.
- Take some basic data analytics training from ISACA, IIA, MISTI, or other similar organizations.
- Investigate Excel Power Pivot (free) and Microsoft’s Power BI tool (basic version is free).