Several TeamMate Analytics customers have shared that they were unsure what data they were going to receive when going into an audit even when they thought they were clear about the data needed. When they received the data, it sometimes included content that wasn’t requested or needed and the data sets were very large – possibly hundreds of thousands of rows with 10, 20, 30 or more columns!
So, the question arises – “How can I quickly understand the data I’ve received?”
Understanding your data
As part of your Standard Operating Procedures or best practices, we suggest you start every audit with testing five key areas to get a better understanding of the data you’ve received.
- Identify data in the wrong format
- Identify blank cells
- Identify date ranges
- Identify minimum and maximum values
- Identify daily, monthly or yearly anomalies
In a matter of minutes, you’ll have a better understanding of the data, and you’ll quickly determine if the data is complete and if there are areas of concern to review further. The best part of these tests is that they are easily repeatable by your entire audit team!
Data in the wrong format
You’ve got text, numbers, dates, alpha-numeric content; but what is each data field supposed to be? An amount MUST be a number, a date MUST be a date, and sometimes a text field MUST be “just” text. Wrong formats can cause your data to be less than reliable, give you a false sense of security and it can lead you down a wrong path which wastes time, effort and creates frustration.
At first thought, you might think blank cells won’t tell you much. After all, they’re blank. If the cell is a “required” field, you’d want to review every transaction with the blank cell. If its optional based on a control, it would be valuable to know which entries might need testing to verify they meet control requirements. For example, if a PO is only required above a certain threshold, you’d want to test for all blank cells where the amount is greater than the threshold.
You may request and receive your data with the expectation that it covers a specific time - maybe a month, quarter or year. Upon review of the data, you notice that transaction or posting dates are outside of the expected range. These transactions should be reviewed to identify if there was an error in data entry or if the date is accurate but outside of a control.
Minimums and maximums
With balances, transaction amounts, inventories, number of logins and number of approvals; minimums and maximums can give you great insight into what is going on with your data or within operations. If the minimum or maximum appears outside of the norm or outside of a control, further research should be completed on those transactions.
Daily, monthly or yearly
Transactions or postings on weekends or after hours may indicate a problem depending on your hours of operation and business practices. Trends in this data can also tell you quite a bit. If you expect busier days or months due to your business operations, your data should show it. This might include busier transaction days or times of year (budget year end or beginning or holiday season for retail). If you don’t have expected busier times and the data indicates a trend, you may want to look further in to the data.
The solution is fairly simple and it doesn’t require much effort using Excel or other tools to make testing each of these five areas on every audit a standard procedure.
- For each column/data field identify, summarize and quantify the format for all cells.
- Count the number of cells that are empty for each column/data field and provide a link to a list of all the lines of data with a blank cell.
- For each date range, identify the earliest and latest dates in the listing.
- For each data and number formatted column, identify the highest and lowest valued items.
- Identify, summarize and quantify all transactions by the day of the week, month and year.
With the proper data analytics tool, these audit analytics can be conducted in a matter of minutes, easily repeated while running only a single process on any spreadsheet by every auditor.
View an example of running these tests on your data within TeamMate Analytics.