February 23, 2008 by Mike.Blakley.

Support is now provided for both comma separated value format files (CSV), as well as “print” files through the use of file specifications. Further information is available. Also, there are now a variety of tutorials available in shockwave format to demonstrate the system in action. Information about system limitations is now available.

Posted in Data Extraction, Error identification, Audit sampling, Data Classification, Audit Software, Data Validation and conversion, Data Validation | No Comments »

November 27, 2007 by Mike.Blakley.

The latest version, released 11/24/2007, includes support for CSV files and “print” files and also includes a “help” button on the forms screen. Further, there are several  examples demonstrating its use: 1) interval sampling, 2) testing Benford’s law in MS-Access database, 3) Data Stratification and 4) Date checking. Other examples and articles have been added.

A demo of the system is available for viewing.

Posted in Data Extraction, Audit sampling, Data Classification, Audit Software | No Comments »

October 28, 2007 by Mike.Blakley.

Updates

Version 1.34 adds chart results for certain of the audit commands, such as population statistics, weekday analysis, Benford’s Law, etc. These charts are produced in industry standard GIF format and may optionally be saved or stored on the computer for inclusion in reports or used as the basis for editing.

Version 1.34 also includes two additional audit commands used for running a series of command language statements at once. This facilitates performing a series of analytical procedures in a documented and standard manner. The new audit command is called “bc” for batch command, and executes a series of commands on consecutive rows in a worksheet. There are examples provided in the QuickStart workbook at http://ezrstats.com/online/inno/QS.xls.

Version 1.36 adds three commands: query, graphics and trendline. The purpose of the query command is to import data from databases to a worksheet for further analysis. The trendline command is used to chart a series of points. Also, in version 1.36, the charts have been made a little larger, along with the option to specify a title, using the title= parameter. The graphics command is used to set the color theme of the charts displayed

Software is an Excel addin, which is available for free download.

Posted in Data Extraction, Audit sampling, Audit Software | No Comments »

September 7, 2007 by Mike.Blakley.

Several users have pointed out that the Chi Square statistics obtained using Benford’s law differ from that obtained using the chi square command alone - this has been corrected, details are available. I have also had several requests to “streamline” the entry of commands and provide a logging facility in order to maintain an audit trail of the commands processed. These features have been included in version 1.22 which can be downloaded. Details on the upgrade can be viewed. As always, I welcome all comments, suggestions and ideas for making this a more useful tool.

Posted in Audit sampling, Audit Software | No Comments »

February 20, 2007 by Mike.Blakley.

If you select random samples from Excel, you may be interested in an approach that takes the data directly from Excel, using the “copy” command, and then “pasting” in onto a web form. Currently, two types of sampling are supported: interval sampling (attribute sampling) and cumulative monetary amount sampling. Both procedures return a sample and a sample reconciliation report. These can be selected, copied and pasted back into Excel. Ofcourse it is necessary to determine the sampling parameters required prior to selecting the sample. In the case of interval sampling, a sample size calculator and evaluator is available.

Posted in Audit sampling | No Comments »

January 19, 2007 by Mike.Blakley.

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, this has already been done, and you can 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).

3. 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).

4. 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).

5. 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.

6. 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.

7. Click the column name to be sampled (e.g. SaleAmount).

8. Click the “Run” bottom at the bottom of the dialog.

9. 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”.

10. Document the results in the work-papers, including the data source, sample reconciliation, etc.

See the screen shot of the process… (or view the step-by-step process with results).

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.

Below is the key portion of the sample reconciliation document:

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

Note that the same methodology can also be used for data files, which may contain considerably more rows than the Excel limitation of approximately 65,000.

Posted in Audit sampling | 3 Comments »

November 20, 2006 by Mike.Blakley.

Proc SAMPLE is a command language procedure to provide audit population sampling procedures. Both cumulative dollar amount (cma) which is also known as dollar unit sampling (dus) as well as random and interval sampling are supported. The auditor may provide the random numbers, interval values, R factor and J, etc. Description of algorithm used.

Posted in Audit sampling | No Comments »

November 20, 2006 by Mike.Blakley.

Sample transactions using the cumulative monetary amount (dollar unit sampling) method.  View example screen shots.

Posted in Audit sampling | No Comments »