A common audit procedure is to stratify numeric amounts (e.g. transactions, account balances) in order to determine a sampling plan, identify any unusual amounts, or simply see how the audit population is structured. Stratifying numeric data contained on an Excel spreadsheet is a simple audit test that can be performed in just minutes. The underlying process is to first count the number of observations, and then based on the number, establish the number of bins into which the data should be classified. Then, for each numeric value on the spreadsheet, identify which bin the amount falls into and update the cumulative count and amount for that bin. This audit exercise is based upon survey data results compiled by the National Science Foundation. The measures are socioeconomic infrastructure, technological infrastructure and productive capacity. To perform the audit procedure, follow these steps:
1. Download the Excel workbook and select the first sheet, which is named at0609.
2. Select the range $B$6:$C$39, and copy it to the clipboard (Ctrl-C or Edit|Copy).
3. Open EZ-R XL and click "Paste". If you don't have EZ-R XL, you can download a full working copy at no cost.
4. For each of the column headers, type over the description in order to provide a unique column name.
5. Click the cell labeled "Orientation" for which stratification data is needed. (You will then see an "X" marked in the column named selected).
6. From the drop-down menu on the left, select "stratify".
7. Click the "Run" bottom at the bottom of the dialog.
8. You should now see the stratification results displayed for each bin. The report will show the starting and ending amount for the bin, as well as the cumulative count and amount relating to that bin.
9. Document the results in the work-papers, including the data source, and perhaps a screen print of the output.
Below is the end result that you should have obtained...
This analysis shows that the population is skewed towards the larger amounts, because the bin with the largest count (and value) is the last bin which starts at 73.87 and ends at 80.10. In this case, the amounts are actually percentages, and the national orientation percentages tend to be clustered at the higher amounts.
Although it is possible to achieve the same results from within Excel, it is a tedious (and potentially error-prone) process. Once you have both Excel and EZ-R XL open, this procedure should be able to be performed very quickly. (And often, other procedures will be performed, both on the selected range and others - making this a good exploratory procedure).
There is also a very similar procedure available which does not require the installation of software. This procedure can be run directly from the Internet. Details and a help file are available.
Note: the display created is html code (not a png graphic).
You can leave (anonymous) comments, if you wish, at our audit blog.
There is also a web analytics version which can be run directly from the Internet using Excel or other data source (in tab separated value format). View Web Analytics details. All software is provided at no cost.