You are currently browsing the Audit Commander weblog archives for the day January 20, 2007.
January 20, 2007 by Mike.Blakley.
Two common audit procedures are to classify data by their frequency of occurrence and to determine if there are any duplicate items. Often, this data may reside within an Excel spreadsheet. Both procedures can be done manually within Excel - for example by first sorting the data, doing subtotals, etc. However, this procedure can be both time consuming as well as error prone.
Here I discuss how this can be done very quickly, and I provide example procedure steps and screen shots of the results. The data I use to illustrate the concepts is the list of debarred medical providers in the State of New Jersey. This list can either be downloaded directly, or else you can use the spreadsheet I have already prepared. The reason for using this list is that it contains elements common in many audit tests - e.g. State code, dates, reason codes, etc. Also, the data is public record. The data elements that I will focus on in this exercise are: state code, effective date and action taken. Four analytical procedures will be performed:
1. Classify state codes by frequency
2. Determine if there are duplicate state codes.
3. Classify action taken by type,
4. Determine if there were any duplicate effective dates.
Posted in Data Classification | No Comments »