To create a successful analytics program in internal audit, you must have a plan. A plan that points to analytic North.
That requires WRITTEN goals.
In an earlier post I outlined 10 Signs Mgmt Doesn’t Really Support Analytics.
One of the signs that indicates management isn’t really serious about analytics is that management does not require every staff member to have measurable analytic goals.
If you’re an auditor and you are not yet using Excel PowerPivot, you are missing the next greatest thing since spreadsheets arrived.
If you are NOT an auditor, and you don’t use PowerPivot, you’re in the same boat with the auditors mentioned above, and it is sinking.
In other words, if you use Excel, you should be learning Excel PowerPivot. It’s that big.
Let me explain why.
NOTE: I updated this post quite a bit with new info…
If you’re a new IT auditor or want to become one, I’ve listed a number of my earlier posts for your consideration. If you’re an experienced auditor, here’s an overview of the profession through my eyes.
These posts will:
- Provide basic information regarding IT audit and security and links to other sources.
- Help you avoid some of the hidden pitfalls that control owners and auditors face.
- Give you ideas and approaches for some common and uncommon audits.
- Give you a few chuckles.
If you start at the top and read through each post, you’ll get a good taste of the positives and negatives of IT auditing. Since you can’t do it in one sitting, yoo could bookmark the list and work your way through it as you have time.
You can easily use Excel’s Flash Fill tool to transform data fast, without formulas.
Did you catch that? Without formulas!
Flash Fill has been around a few years, but few people, including auditors, seem to be aware of it.
This tool is so easy to use, you could learn it AND teach it to your mom in 4 minutes. Really.
You can check for blank and invalid data in Excel several ways.
Depending on the size of the file and your preferences, you can either scroll through the dropdown list, sort each column from A to Z and then Z to A, or apply a filter.
Sometimes, you need to use a combination of these methods.
It’s important to know how these methods treat data differently and to be aware of their limitations.
Before you analyze data, you must first validate it.
Otherwise, your analysis may not be accurate, and you may miss some important insights or errors.
This post is part of the Excel: Basic Data Analytic series.
Before analyzing your data, you need to check the following:
- Duplicate transactions do not exist.
- Required fields/key fields do not contain blanks, spaces, zeroes, unprintable characters, or other invalid data.
- Date fields contain real dates, and the range of dates is appropriate.
- Amount fields don’t contain inappropriate zero, positive, or negative amounts, and the range of values is appropriate.
- Each field is stored in the correct format. This prevents data from being converted on the fly into something else unexpectantly (e.g., user ID JUL15 becomes 15-Jul).
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.