A common audit procedure is to select a random sample using the cumulative monetary amount (also known as dollar unit sampling) in order to audit transactions from a population. Often, that data may reside within an Excel spreadsheet.
The underlying process is determine the materiality and the confidence level required. The purpose of this audit exercise is not to explain the methodology for the process, but instead to lay out the procedural steps needed to perform a CMA sample from data contained on an Excel spreadsheet. (Note that the same technique can be applied to other data sources as well, provided it is in the tab separated value (TSV) format. There is a white paper on converting data to tab separated value format.
For purposes of this exercise, we have taken data published by the World Bank and used this data as the basis for the sample. The data consists of both debit and credit transactions (although credits are not sampled). We have placed the data into an Excel workbook which can be downloaded, as well as the formatted data. In order to follow this exercise, the following steps will need to be performed:
1. Download the Excel workbook and select the first sheet, which is named WB.
2. There is some "clean-up" work which needs to be done, as some data is missing or else improperly formatted. (However, we have already done this, so you may want to just download the formatted data). This includes ensuring that all the columns have headers which consist of a single word (i.e. no embedded blanks).
2. After clean-up, select the data range to be sampled, and copy it to the clipboard (Ctrl-C or Edit|Copy).(Skip this step if you have downloaded the formatted data WB.tab).
3. Open Word Pad and "paste" the data into it.Save this file as "WB.tab" and remember the location where it was saved. (You can skip this step if you have downloaded the formatted data).
4. Open EZ-R Stats for Windows, and select the menu item Sampling | CMA Sample. If you do not have EZ-R Stats for Windows, it can be downloaded and installed without cost.
5. In EZ-R Stats, specify the input file (WB.tab), the output file where the sample extract is to be written (e.g. cma.tab), the "R" factor (e.g. 2), the J factor (e.g. 5000) and a random start (e.g.347). The random start must be between 0 and J / R.
6. Click the column name to be sampled (e.g. SaleAmount).
7. Click the "Run" bottom at the bottom of the dialog.
8. Once the sample is run, the sample selections will be written to the file specified, and a sample reconciliation will be written to the same file name, but with the file suffix of ".rec".
9. Document the results in the work-papers, including the data source, sample reconciliation, etc.
Below is the end result that you should have obtained...
The example sample reconciliation can be viewed here.
In this instance, there were 13 transactions in excess of J, (so they were selected) and 55 under J.
population debits 325884.40
population credits -9.10
value of samples under J 33688.20
value of samples over J 55414.90
Number of samples under J 55
Number of samples over J 13
Number of samples under J is 55 times J of 5000.00 gives 275000.00
Add starting RS of 347.00
Subtract ending RS of -4877.50
Add amount of samples over J 55414.90
population debits 325884.40
Difference (s/b 0) is 0.00
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 that this same methodlogy can also be used for data files, which may contain considerably more rows than the Excel limitation of approximately 65,000.
You can leave 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.