A very common audit procedure is to sample data stored on an Excel worksheet. Although there are many ways to perform such a sample, the procedures tend to be tedious and time consuming. I have two prototype methods for such sampling, which can be done directly through the Internet. The idea is to copy the data to be sampled from the Excel workbook and then "paste" it onto a web form, etc. There are two examples, one is for interval sampling and the other is for cumulative monetary amount (CMA) sampling. Both procedures provide the sample selected as well as a sample reconciliation report which can then be "pasted" back into Excel.

A brief tutorial on the use of web analytics software to classify data and to identify dates falling on federal holidays. Because the software is run from the web, there is no need to install or maintain the software. Also, this software is available for free use. The general approach is to analyze data contained in Excel sheets by selecting and copying the data, and then "pasting" it into the web analytics form and running the analysis on the internet. The results of the analysis can then be pasted back into Excel, if desired. The tutorial page uses data in Excel format.A description of all the analytic tools is available for review and use (no cost).

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.

Comments are welcome (post comment on blog)....

This example explains how to perform a simple, yet effective, test to determine if a data range in Excel conforms with that expected under Benford's law. The example data used is that provided in the 26th annual report to Congress (link provided). This is an audit test that should take no more than a few minutes, yet provides a good test of fit for the numbers in the range, and potentially an easy test for possible fraud or made-up data. See a step-by-step procedure.

Provide sums and counts for positive, zero and negative amounts, as well as absolute values. Useful for sampling and obtaining control totals. To use the software, highlight and copy the area on a spreadsheet of interest and then "paste" the area into EZ-R Excel for analysis. View screenshot. This menu driven software is available at no charge.

Provide basic statistics such as mean, minimum, maximum, standard deviation. Useful to obtain an overview of the data distribution. To use the software, highlight and copy the area on a spreadsheet of interest and then "paste" the area into EZ-R Excel for analysis. Screen shot.

Group the data by values, providing counts and summaries (if a numeric weight variable has been specified). This is done by opening an Excel workbook, selecting and copying the area of interest, and then "pasting" it into EZ-R Excel for menu drive analysis. Screen Shot.

Group the data by values into "bins" in order to determine the population distribution. Useful for sampling or data classification. First copy and paste the data from an Excel spreadsheet into EZ-R Excel. Next, specify the "bin" values, i.e. starting and ending values. and then click "Run". Screen shot.

A step-by-step tutorial is available, based upon an Excel spreadsheet which can also be downloaded.

Identify the five largest and smallest values, and list the row number they are found on and the number of standard deviations their value has from the population mean. Useful in identifying potential error conditions. Select, copy and paste data from an open Excel spreadsheet into EZ-R Excel for analysis. Screen shot

Provide a histogram of data values and chart it. Useful to visualize how the data is distributed. Select and copy the data from an Excel worksheet and then "paste" it into EZ-R Excel. The system automatically selects the range and bins for the histogram as follows - The minimum and maximum values are set at 10% beyond their values.Once the data has been selected and analyzed, a chart is produced. Screen shot.

Identifies duplicate integer numbers (e.g. Check numbers, purchase orders, vouchers, etc.) Values do not need to be presorted. From the list of column names, click one or more column names of interest. Then click the "Run" button. You may also double click in the "Selected" column in order to select multiple columns. Useful for quickly locating potential duplicate items (numeric only - there is a separate function for locating duplicate text).

Identifies gaps or missing values in series of integers such as check numbers, purchase orders, receiving report numbers, etc. Values do not need to be presorted. First select the range of values to be analyzed in the Excel sheet. Once the range has been selected, it can be copied to the clipboard, pasted into EZ-R XL and then an analysis run.

Determine the extent to which the data conforms with that expected under Benford's Law. From the list of column names, click the column name of interest (or double click multiple columns in the Selected column). Then click the "Run" button. Once the processing is complete, a Benford analysis is performed and a chart displayed. Software is available at no charge. The chart will show the "Kolmogorov-Smirnov D-Statistic" which is a measure of the degree to which the distribution conforms with that which would be expected. The values range from 0 (perfect correlation) to 1 (no correlation). Benford distributions with a D-Statistic of .05 or less generally indicate good conformity with Benford's law.Screen shot

Using the columns selected, performs the statistical KS test and also linear regression to report on relationships or differences between the columns. Useful for analytical review of financial data (e.g. trial balance or account balances) over various years or quarters. There are two components to the analytical review. The first is a linear regression between various columns or rows. The first step is to start with the data on the Excel sheet to be analyzed.Shown below is an example of financial statements for four quarters and also fiscal year end amounts. This example uses actual amounts shown in SEC filings. Highlight the areas shown on the spreadsheet to be analyzed, ensuring that each of the columns of interest has a title name contained in a single cell which clearly denotes the financial data. In the case below, we have selected the five comparative income statements for the various periods. Screen shot.

There is also a step-by-step tutorial available, which uses Excel data which can be downloaded.

This component is another statistical measure ("KS") to identify the line item of greatest interest as contributing the most to any variation or deviation between the two columns (which are typically balance sheets or income statements at different points in time). From the list of column names, left double click the column names of interest. (If desired, an alternative procedure is to select the cell and type an "X" in the cell). Select the column name which contains the name of the account being listed and either right double click the cell or else manually enter a "D" in the cell contents. Once all selections have been made, click the "Run" button.The link shows an example of financial statements for four quarters and also fiscal year end amounts. This example uses actual amounts shown in SEC filings. Highlight the areas shown on the spreadsheet to be analyzed, ensuring that each of the columns of interest has a title name contained in a single cell which clearly denotes the financial data. In the case below, we have selected the five comparative income statements for the various periods. Screen Shot.

Identifies duplicate entries in the range (case sensitive). Data from a range within an Excel sheet is selected and copied, then pasted into EZ-R Excel. This is a test for duplicate text values. screen shot.

Classifies long distance numbers into invalid as well as the top and bottom five by frequency of call. A range of long distance telephone numbers can be analyzed and summarized. The analysis will count the number of invalid telephone numbers (these are ten digit numbers, but either the area code is invalid or unassigned). Source of data is the North American NPA assignments . The valid telephone numbers are classified as to area code, and the both the top5 (i.e. the area codes most frequently called) as well as the bottom 5 (area codes least frequently called) are listed. The telephone number may be in any format with any sorts of separators, i.e. parenthesis, dash, space etc. Screen shot