# A better spreadsheet for tests of controls: leveraging technology for more efficient audits.

From a theoretical standpoint, statistical sampling for tests of controls generally should be based on the hypergeometric distribution. This distribution can be used to calculate exact probabilities when sampling is without replacement, which is appropriate for most audit applications. However, the distribution is very difficult to calculate, and until recently, only the most powerful computer programs were readily capable of making the calculations in realistic situations (e.g., population sizes exceeding a few hundred). Because it depends on four variables, tabulating the distribution is not a practical alternative, since any legitimate attempt to do so would require hundreds (or even thousands) of tables.

[ILLUSTRATION OMITTED]

Due to these constraints, auditors usually use a different distribution--most often the binomial distribution--as an approximation of the hypergeometric distribution. If the population is large and the sample size is small relative to the population, the binomial distribution is a good approximation of the hypergeometric distribution. How large is large enough? Although the answer depends on the exact circumstances, there is almost no difference in required sample sizes when the population exceeds 5,000 items; in many cases, the population must be less than 2,000 items before there is a noticeable effect on sample size. This requirement (i.e., a large population) is often met in auditing applications, so the use of the binomial distribution is acceptable in many circumstances.

Auditors will sometimes, however, face situations where the population size is not large. For example, a control may be applied once daily, which would result in a population of approximately 250 items (assuming a five-day workweek) over a one-year time frame. As another example, an audit client might have 10 bank accounts, each reconciled monthly, for a population of 120 items. In these situations, if the auditor determines the sample size based on the binomial distribution, it will result in a sample size that is larger than necessary (some specific examples are provided below). In fact, if the binomial distribution is inappropriately used for a small population, it is possible to obtain a sample size that exceeds the population size--a nonsensical result.

To remain competitive, auditors must strive to perform the correct amount of audit work--neither too much nor too little--based on the inputs (e.g., acceptable risk levels) that they have specified. For tests of controls, there are two concerns when determining the sample size:

* If the sample size is too small, the result will be an ineffective audit (i.e., audit risk will be higher than desired).

* If the sample size is too large, the result will be an unnecessary increase in audit cost (i.e., the audit will be inefficient).

While the second concern is not as critical as the first, it is nonetheless an important issue, and it is an outcome that could occur if the binomial distribution is used to determine sample size for a small population. Fortunately, it is no longer necessary to tolerate the imprecision associated with the use of the binomial distribution when exact calculations, based on the hypergeometric distribution, can be made using software that is already available to most auditors. The discussion below demonstrates a spreadsheet that can be used to determine the correct sample size and evaluate the results for tests of controls on populations of any size.

AICPA Upper-Limit Method

A common method used for statistical sampling for tests of controls is the AICPA upper-limit method. This method is based on the binomial distribution, which assumes that the population is large--an assumption that is not always appropriate. The authors' method modifies the upper-limit method for use with any size population. It requires me same three inputs necessary for the traditional upper-limit method, plus a fourth input, the population size. The other three required inputs are:

Tolerable exception rate. The maximum exception rate in the population that an auditor is willing to accept without altering the planned assessed level of control risk is the tolerable exception rate (TER). In other words, the TER is the maximum exception rate that the auditor can tolerate without increasing control risk relative to the planned level. As the planned reliance on a control increases (decreases), the TER should be lowered (raised). The hypergeometric calculations actually require the maximum integer number of exceptions in the population that can be tolerated, but this number is calculated by multiplying the TER by the population size. If this calculation results in a value that is a non-integer, it is rounded down to be conservative, because rounding up would result in a TER that is slightly higher than the maximum amount specified by the auditor. The TER is inversely related to sample size.

Expected population exception rate. The exception rate believed to exist in the population is the expected population exception rate (EPER). This estimate may be based on prior audit findings, a pilot sample, or the auditor's perception of the effectiveness of the control. The EPER is directly related to sample size and must be significantly less than the TER if sampling is to be feasible. An EPER of zero may be used; while this value will result in the smallest sample size (all else being equal), the consequence is that finding even one exception in the sample will lead to a conclusion that the control is ineffective. Use of a realistic EPER value will generate a sample size that is large enough to provide the auditor with a reasonable chance of concluding that reliance on the control is appropriate, even if some exceptions are found.

Risk of assessing control risk too low. The maximum risk the auditor is willing to accept of incorrectly concluding that the population exception rate is less than or equal to the TER when, in reality, it exceeds the TER, is called the risk of assessing control risk too low (RACRTL). This risk also may be referred to as the risk of overreliance or, using statistical terminology, [beta] risk (a type of sampling risk). It is important to monitor this risk because, if the auditor inappropriately relies on a control, the effectiveness of the audit will be compromised and overall audit risk will be higher than desired. The exact value used for the RACRTL depends on the importance of the control and the auditor's risk preferences, but it should be set at a relatively low level. The RACRTL is inversely related to sample size.

A second type of sampling risk is the risk of assessing control risk too high (RACRTH). RACRTH is the maximum risk the auditor is willing to accept of incorrectly concluding that the population exception rate is greater than a specified rate, when in reality, it is less than or equal to that specified rate. This risk also may be referred to as the risk of underreliance, or a risk. If this type of error occurs, the auditor will choose not to rely on an effective control and will therefore perform unnecessary substantive tests; the consequence will be an inefficient--though effective--audit. Ideally, both types of sampling risk would be measured and controlled. However, the result of assessing control risk too low (i.e., possibly issuing an unqualified opinion on financial statements that are materially misstated) is much more serious than the result of assessing control risk too high. For this reason, the upper-limit method does not control for RACRTH; because the authors' spreadsheet is an adaptation of that method, it does not control for RACRTH either.

The spreadsheet program developed by the authors is designed for use with Microsoft Excel 2007 and newer versions of Excel, but should not be used with versions prior to Excel 2007, because calculation limitations may prevent it from functioning properly. It is a user-friendly program, requiring only the four inputs discussed above; all other cells on the SampleCalc worksheet are protected to prevent inadvertent alteration by the user. As an additional precaution, the authors recommend that, after generating the information needed, the user close the original file without saving it. The spreadsheet may be downloaded from The CPA Journal website (www.cpaj.com/down.htm).

As a security precaution, Excel 2007 disables macros each time a file is opened. The spreadsheet will not work unless the auditor enables macros. Most users will see a security warning near the top of the page indicating that macros have been disabled; before proceeding, a user should click on the "Options" button, select "Enable this content" from the popup window, then click "OK." Users who do not see this security warning should ensure that macros are enabled before proceeding. (Excel's help menu will indicate how to enable macros.)

Some users may encounter a "circular reference" error when they first use the spreadsheet. If this occurs, it can be easily remedied by following these steps:

* Open the spreadsheet in Excel, and enable macros as discussed in the previous paragraph.

* An error message about the circular reference will appear; click on "Cancel."

* Click on the big round button in the top left comer of the screen when using Office 2007 (or the File button in the top left corner of the screen when using Office 2010).

* At the bottom of the window that pops up, click on the "Excel Options" button (the "Options" button in Office 2010).

* On the next window, click on the "Formulas" tab on the upper left side.

* In the "Calculation options" section, at the top of the screen, check the "Manual" radio button to select manual calculation. Make sure that a checkmark appears to the left of "Recalculate workbook before saving."

* Click on the "Enable iterative calculation" box; a checkmark will appear.

* In the "Maximum Iterations" box, enter 299.

* In the "Maximum Change" box, enter 1.

* Click "OK" in the bottom right-hand corner of the window.

Once these changes are made, save the file and use it as the starling point for future work. By completing these steps, the changes to the Excel options settings should attach to the file and override the default settings on the user's computer, preventing the need to repeat these steps in the future.

To use the spreadsheet, an auditor enters values for population size, EPER, TER, and RACRTL. The auditor then clicks on the "Calculate Sample Size" button, which invokes a macro that returns the numbers shown in bold in the table in Exhibit 1. The worksheet should be printed and included in the working papers to document the input values used, sample size, and so forth.

[ILLUSTRATION OMITTED]

To illustrate, assume that the size of the population being tested is 500, and the auditor uses values of 2% for EPER, 6% for TER, and 5% for RACRTL. An example of the spreadsheet output is shown in Exhibit 1, which indicates a required sample size of 96. At 19.2% of the population, this sample size may seem somewhat large; however, had the auditor (inappropriately) used the traditional upper-limit method, the resulting sample size would have been 127. In this scenario, use of the proper distribution (hypergeometric rather than binomial) enables an auditor to avoid examining 31 unnecessary items, or approximately 32% of the correct sample size.

It should be noted that the difference between the hypergeometric and binomial sample sizes will not always be this significant. In fact, if the population size is relatively large, there may be little or no difference between the two methods. Furthermore, some auditors using the binomial distribution may use a "finite population correction factor," which reduces the binomial sample size in an attempt to compensate for a small population. Had this adjustment been made to the example in the previous paragraph, the result would have been a sample size of only 110, instead of the unadjusted sample size of 127.

For illustrative purposes, Exhibit 2 shows the sample sizes that result from using the hypergeometric distribution as compared to the binomial distribution (without adjustment for the finite population correction factor) for assumed combinations of inputs. As mentioned above, the difference between sample sizes is negligible for large populations. But for smaller populations, the difference in sample size is unpredictable, and it depends upon the exact combination of inputs. Nonetheless, while the binomial distribution may sometimes yield results that are correct (or at least "close enough"), there is simply no reason for auditors to continue using it for tests of controls. The hypergeometric distribution is the conceptually correct distribution, and by using a spreadsheet like the authors', auditors can obtain, at no cost, the exact information needed for any test of controls. Auditors who use the hypergeometric distribution can take comfort in the fact that they are utilizing proper statistical theory for tests of controls. As an additional benefit, auditors who have been using the binomial distribution for small populations may reduce "overauditing," which could result in significant cost savings over time.
```EXHIBIT 2

Sample Sizes: Hypergeometric Versus Binomial Distribution

Sample Size

Population Size  EPER  TER   RACRTL  Hypergeometric  Binomial

100              2.5%  7.0%   10.0%         59           75

200              1.5%  4.0%    7.5%        113          177

300              1.0%  5.0%    5.0%         83           93

400              3.0%  6.5%    6.0%        122          155

600              2.8%  6.7%    9.2%         96          100

800              2.1%  5.5%    4.0%        137          171

1,000            2.5%  5.0%   10.0%        152          158

2,500            0.5%  4.0%    5.0%        115          117

5,000            3.5%  8.0%   10.0%         82           82

Population Size  Difference  Percentage Difference

100                  16              27.1%

200                  64              56.6%

300                  10              12.0%

400                  33              27.0%

600                   4               4.2%

800                  34              24.8%

1,000                 6               3.9%

2,500                 2               1.7%

5,000                 0                 0%
```

The spreadsheet provides several items that may be used to evaluate the results of the test. The critical value indicates the maximum number of exceptions that may be found without modifying planned reliance on the control; in this example, the auditor would not be justified in relying on the control as planned if more than two exceptions are found. The auditor who performs the actual test work should be aware of the critical value before testing begins, since it likely would be prudent to abandon the test immediately if a third error is found. One would not want to spend time examining any remaining items in the sample if it has already been determined that the control cannot be relied upon.

The RACRTL column indicates the exact RACRTL that exists if the auditor relies on the control, based on the number of exceptions actually found in the sample. For example, if exactly two exceptions are found, the auditor should rely on the control as planned, with a RACRTL of 4.95% (just under the specified allowable limit of 5%). If only one exception is found, the auditor should feel even more confident in the decision to rely on the control, since the RACRTL of 1.17% is much less than 5%. If three exceptions are found, an auditor should not rely on the control as planned; however, if one decides to rely on the control anyway, an auditor-should be aware that the RACRTL is 13.75%, nearly three times the risk that was originally specified as acceptable.

The column labeled "Upper Limit" shows the maximum population exception rate for a particular number of exceptions in the sample, based on the specified RACRTL (5% in this example). Remember that the auditor' had specified an acceptable 5% risk that the population exception rate exceeds the TER of 6%. If only one exception is found, the upper limit indicates that there is a 5% risk that the population exception rate exceeds 4.6% (i.e., the control is stronger than required for the planned level of reliance). On the other hand, if three exceptions are found, there is a 5% risk that the population exception rate exceeds 7.6%, which would not support the planned level of reliance. Information regarding the upper limit may be useful if the number of exceptions found in the sample is greater than (less than) the critical value, and an auditor wishes to rely on the control to a lesser (greater) extent than originally planned.

The spreadsheet offers auditors considerable flexibility over the input values used, as well as the ability to determine quickly how changes in input values affect the sample size. Nevertheless, there are some limits on the four inputs. The amount used for the population size cannot exceed 100,000; if the population size is unknown or greater than 100,000, a value of 100,000 should be used. Using this value will generate a sample size equal to the one that would result from the traditional upper-limit method based on the binomial distribution. Inputs for EPER must be between zero and 25% (inclusive), while the values for TER and RACRTL must be greater than zero but not greater than 25%. Finally, the TER must be greater than the EPER. The system-defined maximum limits for the EPER, TER, and RACRTL are used because it is very unlikely that a control would be tested if any of these values exceeded 25%.

The spreadsheet output has some limits as well. Values for the RACRTL and the upper limit are shown for each exception value from zero up through two more than the critical value, with a maximum of 20 exceptions allowed in the sample. Values for the upper limit are rounded up to the nearest 1/10 of 1%, and they are not calculated if they exceed 25%. If the necessary sample size exceeds 300, a message will be shown stating that testing the control is not cost-effective; no sample size will be displayed in such an instance.

Selecting the Sample Items

Once an auditor has determined the required sample size, the next step is to select the actual sample items. If the conclusions drawn from statistical sampling are to be valid, a probabilistic sample selection method must be used--that is, judgmental or haphazard sample selection is not permitted. The theoretically preferred sample selection method for tests of controls is simple random sampling, whereby each item in the population has an equal probability of being selected.

The spreadsheet includes a module that automates and simplifies the sample selection process. After calculating the sample size, the auditor should click on the "Generate Random Sample" button, which will invoke the sample generation macro. The program will ask the auditor if any extra sample items (up to 30) are desired; it is usually a good idea to generate some extra items in case an item selected as part of the original sample cannot be used (e.g., a voided check). The auditor also will be asked to enter the lowest and highest item numbers (e.g., check numbers or invoice numbers) in the population; this step enables the generation of sample item numbers that correspond to the item numbers within the population. If the population items are not sequentially numbered, a value of I should be entered as the lowest value and the number of items in the population should be entered as the highest value. The random numbers that result represent ordered population items; for example, if the sample includes item number 17, the 17th item in the ordered population would be selected as part of the sample.

After these questions are answered, the program will generate a random sample of the appropriate size, in addition to the number of extra items that were requested (see Exhibit 3). Because the sampling methodology is based on sampling without replacement, the program ensures that no items are duplicated in the sample or the extra items. All items in the primary sample are sorted in ascending order to facilitate the physical gathering of the sample. Extra items are not sorted; if it is necessary to replace an item in the primary sample, extra items should be chosen in the order listed, from top to bottom. The sample items are shown in a printable format, which includes one column that may be used to indicate if an exception was noted and a second column to cross-reference any necessary explanations.

[ILLUSTRATION OMITTED]

A More Efficient Method

Auditors often use tables based on the binomial distribution when testing controls. If the population is small, however, use of the binomial distribution will result in sample sizes that are larger than necessary, which will increase audit costs. Furthermore, auditors who use tables have very limited choices for the input values; for example, tables may be available only for RACRTL values of 5% or 10%.

By using the spreadsheet accompanying this article, auditors can remedy both of these problems. Because it is based on the hypergeometric distribution, the spreadsheet generates the correct (i.e., minimum) sample size, regardless of the size of the population. In addition, the spreadsheet offers the auditor significant flexibility when specifying input values; for example, a RACRTL value of 6.3% may be used if desired. Both of these features improve the efficiency of an audit by enabling the auditor to perform the proper amount of testing. Audit firms that continue to do things the way they have always been done may be bearing unnecessary costs that decrease audit efficiency. With today's technology, and the availability of free pro grams such as this one, it is no longer necessary to tolerate this imprecision.

Bruce Wampler, DBA, CPA, is an associate professor in the department of accounting at the college of business, the University of Tennessee at Chattanooga. Michelle McEacharn, DBA, CPA, CIA, is a professor and associate dean for academics at the college of business administration, the University of Louisiana at Monroe.
COPYRIGHT 2011 New York State Society of Certified Public Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.