AUDITING

April 2002

A Customized Spreadsheet for Tests of Controls: Random Sampling

By Michelle McEacharn and Bruce Wampler

Customized Tables for Tests of Controls: A Spreadsheet,” by Bruce Wampler and Michelle McEacharn (The CPA Journal, February 2001), described a spreadsheet that auditors can use in performing tests of controls. This spreadsheet allows the user a great deal of flexibility in defining the input values that determine sample size, far beyond the combinations offered by the AICPA tables. Additionally, the spreadsheet gives auditors enhanced information to use in evaluating the sample results. The authors have modified the spreadsheet to further assist the auditor in testing controls by randomly generating a specific set of sample items. Readers may download the spreadsheet file from www.cpaj.com.

The Next Step

Once an auditor has determined the appropriate sample size, as described in the earlier article, a sample of items from the population must be taken. Methods for selecting sample items include the following:

• Haphazard sampling techniques, where the randomness of the sample is questionable;
  
• Systematic sampling techniques, where patterns in the data may cause misleading results, and
  
• Random number techniques, the best approach from a theoretical standpoint.

Large audit firms may have statistical sampling software programs that offer random number generation features. Random number tables are an alternative, although using them can be tedious and time-consuming. Auditors without access to random number generator programs may not be aware that they exist in typical spreadsheet software.

A number of Microsoft Excel functions can be used to generate random numbers, including RAND, which generates random numbers between 0 and 1, and RANDBETWEEN, which allows the user to generate a random number that falls between two user-specified values. Additionally, “data analysis” (from the Tools menu) offers two options for generating random numbers, “random number generation” and “sampling.” Whichever method is used, there can be unintended results. Unless specific user actions are taken to prevent it, the random numbers will change each time the worksheet is recalculated.

Users familiar with the available spreadsheet functions for generating random numbers will find that these functions are sufficient for selecting a random sample. Nevertheless, the modifications to the tests of controls spreadsheet simplify the process and should be of practical benefit.

Using the Worksheet

Three buttons have been added to the tests of controls worksheet. The “additional help” button takes the user to a page that provides descriptions, instructions, and other helpful information. After entering the required inputs (i.e., tolerable rate, expected population deviation rate, and the acceptable risk of assessing control risk too low), the “calculate sample size” button will calculate the required sample size and critical value for the defined inputs and provide information useful in evaluating the sample results. The “generate random sample” button will generate a set of random numbers based on the required sample size. Using auditor inputs, macros, and Visual Basic for Applications (a programming tool for Microsoft applications), sample items are generated, sorted, and displayed in a printable format. Generating the random sample items entails the following steps.

Once the “generate random sample” button is clicked, the user will be asked three questions. The first question allows the user to request extra sample items. Extra sample items are sometimes needed because an item selected as part of the original sample is not relevant to the test of the control. For example, if a check that is selected was voided because the check-writing machine destroyed it, another check would be needed as a replacement.

Extra sample items may also be needed if the auditor wishes to sample without replacement; the spreadsheet (as well as the AICPA tables upon which it is based) assumes sampling with replacement. When using sampling with replacement, the same items may be selected more than once; if that item is found to represent a deviation, it would count as multiple deviations when evaluating the results of the sample. Auditors who prefer to sample without replacement should generate extra sample items as possible substitutes for any duplicated sample items generated during the initial selection process. If duplicate items are replaced, more audit evidence will be examined than necessary; hence, sampling without replacement will result in more conservative sampling results.

The spreadsheet allows the user to select up to 30 extra sample items. Extra sample items are presented separately and appear with “extra” to the left of the number. To ensure randomness, theory dictates that sample items must be selected in the order generated. The auditor must examine all items selected for the required sample size, so these items are sorted for the auditor’s convenience in pulling the sample. Because the auditor may use only some of the extra items, the order in which they are generated is important. Consequently, extra sample items are not sorted and should be used in the order in which they appear on the spreadsheet.

The next two questions ask the user to input the lowest and highest values from which the sample will be drawn. These values are based on the population being tested and might represent invoice numbers, purchase order numbers, or account numbers. Choosing the highest and lowest values creates a range of relevant items for the audit period.

If the auditor is performing a test of controls for which there are no beginning and ending numbers that can be used, 1 should be entered as the lowest value and the size of the population should be entered as the highest number. The resulting generated random numbers would then represent ordered population items. For example, if the random number “4” is generated, the fourth item in the population, depending upon how the population is ordered, would be selected. Software limitations prohibit the program from accepting any low or high value exceeding 10 digits in length.

Once those three questions have been answered, the spreadsheet will generate the sample and sort the items in ascending order. Any requested extra items will also be generated but not sorted. The sample items will be displayed in a printable format, which includes columns for the auditor to indicate whether a
deviation was found to exist and for any additional auditor comments to be cross-referenced.

Because the spreadsheet assumes sampling with replacement, the actual number of different sample items may be less than the required sample size. Unless the auditor replaces each duplicate sample item with an extra sample item, the results from testing of the duplicated item should be counted proportionately (i.e., the number of times it appears).

The worksheet’s page headings are set through the use of the header formatting and may be changed by the user. The main body of the worksheet, however, is set through the macro and any modifications would require the user to have a thorough understanding of macros and Visual Basic for Applications. The auditor can use the printed worksheet to select the sample items, note deviations found for specific sample items, and evaluate the overall results of the testing process by comparing the number of deviations to the critical value provided by the spreadsheet’s sample size calculations. To calculate another sample size or generate a different random sample, the user should return to the primary calculation screen, enter the new input values, and click on the appropriate button.

©2002 CPA Journal. Legal Notices

Visit the new cpajournal.com.