To increase the amount and depth of the analytics performed, steal some agile methods, and apply them to your audits.
If you’re not familiar with agile methods, check out the first 5 topics listed here (just click Next at the bottom of each page; the topics are quick to the point and full of pictures).
Briefly, agile projects are performed in cycles, or iterations, rather than in a long, linear-waterfall fashion, which is: do all planning, then field work, then reporting. Each iteration of the project creates some value and includes feedback, which is used in the next iteration to increase the value of the project.
Here’s a list of my basic data analytic procedures for Excel.
As I add more posts to the series, I’ll update this list.
I created this series because:
1) I often get asked by new AND EXPERIENCED auditors how to do these tasks,
2) when I review workpapers, I realize too many auditors are not aware of these functions,
Excel’s Text to Columns function allows you to separate pieces of data in a single column into multiple columns.
This function helps when key data is buried in a field with other information and you need to extract the key data into a separate column before you can analyze it.
For example, you obtain a list of email addresses, and all you want are the user IDs. Or you get a list of servers, and the server name is server.domain.com, and you need just the “server” name. Or you need to separate LastName, First Name into separate columns. That’s where Text to Columns saves the day.
This article is the fourth post in the Excel basic data analytic series.
To identify unique values in an Excel table, follow the steps below.
This article is the third post in the Excel basic data analytic series, which starts here.
The steps for identifying unique values are similar to identifying duplicates. The first difference shows up in step 3 below.
While the previous post in this series described how to remove duplicate values in Excel, this post describes how to identify duplicates.
The remove duplicates function doesn’t tell you which values are duplicates, it just removes them. Sometimes you need a list of the duplicates so you can review them in detail or include them in your workpapers.
So we’ll look at how to create a list of duplicates across all values/columns and in specific columns.
On my walk to work, I cross a lot of 1-way streets. I always look both ways. Sometimes, when a friend or colleague is walking with me, I get teased me about this. I always reply with this question: Have you ever driven down a 1-way street the wrong way? For some reason, I never get a reply and another subject surfaces.
When I crossed one of those streets the other day, I realized that some people look at audit/security/risk the same way. They only look one way because of the people or rules or controls or norms that govern the activity. They fail to think outside of the cubicle and look the other way–the path seldom traveled.