Data stratification for data in Excel ranges
the quick and dirty dozen
For the selected range data can be stratified into "buckets". Each bucket represents a numeric range, and the ranges are all adjacent to each other. Data stratification then allows the auditor to plot both the counts and transaction amounts, depending upon which "bucket" each transaction falls into. The advantage of data stratification is that can assist the auditor in identifying unusual values, in addition to planning a random sample for further testing.
To perform a data stratification, eight steps are used as shown in the screen shots below:
Step 1 - Identify the area within an Excel sheet to be analyzed.
Step 2 - Select the area and copy it to the clipboard.
Step 3 - Paste the clipboard data onto the form.
Step 4- Select the stratify command from the drop-down list.
Step 5 - Enter the data range (in this case column 1 identified as "C1". Also specify the stratification values. These are numbers in ascending order, separated by spaces.
Step 6 - Click the "Run" button to obtain the stratification report.
Step 7 - Review the stratification report under the "Report" tab.
Step 8 - Click the "Copy" button and then "paste" the results into an empty area on an Excel worksheet. These results can be used for for further review and to document the work performed.
The results are also available as a chart.
Notice that in the above case, much of the data lies above the strata specified. ordinarily, the auditor would revise the strata by typing in new (or revised) strata and then re-run the test by clicking the "OK" button. This would replace the existing report.
Also note that a variety of statistics as well as sub-total information may be obtained for each variable as well.
The User Guide is available for download in PDF format. There is no license cost or restriction on use. The software handles Excel Workbooks (2003 and 2007), Access databases (2002 and 2007) as well as text format files (tab separated values, comma separated values). The system requires either Windows XP, Vista or Windows 7 to operate. Approximately 50 MB of disk storage is recommended with a minimum of 500 MB of memory.