** February 2001 **

**Customized Tables for Tests of Controls: A Spreadsheet**

*By Bruce Wampler
and Michelle McEacharn*

Auditors performing tests of controls often use tables published by the AICPA (or practice guides based on these tables) to calculate statistical sample sizes and evaluate sample results. To determine the sample size, the auditor must specify three percentages:

Although there are unlimited combinations of these three factors, which may each produce a different sample size, an auditor using tables is limited to a relatively small choice of input values (e.g., a RACRTL of 5% or 10%). How should an auditor respond if the desired RACRTL equals 8%? In the absence of nonlinear interpolation, the auditor may have to use the 5% table as a conservative alternative. Consequently, the sample size will be larger than required, and the auditor will perform unnecessary work.

Tables are also available to assist the auditor in evaluating the results of tests of controls. However, like the sample size tables, the small number of tabled values limits their usefulness.

**A Worksheet Solution **

A worksheet that automates the sample size determination and the test evaluation would improve audit efficiency. The auditor enters values for the expected population deviation rate, the tolerable rate, and the acceptable RACRTL and a macro generates the appropriate sample size. The worksheet also calculates the maximum number of deviations allowable without modifying planned reliance, the exact RACRTL that exists if the auditor relies on the control, and the upper limit (discussed later). The Exhibit shows how the spreadsheet output would appear.

The auditor enters the italicized values shown in the Exhibit, while the numbers
in bold are returned by the worksheet’s calculations. To illustrate, an
expected population deviation rate of 4%, a tolerable rate of 9%, and an
acceptable RACRTL of 5% yields a sample size of 100. If exactly four deviations
(the critical value) are found in the sample, the auditor can rely on the
control as planned, yielding an exact RACRTL of 4.74%. The worksheet also
provides additional information for other deviation results. In this example,
fewer than four deviations results in a RACRTL much less than 5%, giving
the auditor even greater assurance, while only one “extra” deviation would
result in a RACRTL of 10.45%, more than double the planned degree of risk.
For a complete discussion of issues related to audit the limitations of
audit sampling, see Chapter 3 of the AICPA Auditing Practice Release, *Audit
Sampling* (1999).

Information regarding the upper limit, which is the maximum population deviation rate associated with the specified RACRTL, could lead the auditor to consider modifying the reliance on the control. For example, if only two deviations are found, the auditor can be 95% certain (100% – 5% RACRTL) that the population rate does not exceed 6.2%, which may justify increased reliance on the control. If more than four deviations are found, the test results would not support the planned level of reliance. However, the upper limit would assist in deciding if some lesser level of reliance might be appropriate.

Several easily modified assumptions limit this version of the spreadsheet. Values for the upper limit are rounded up to the nearest one-tenth of a percent and are not calculated if they exceed 25%, because no reliance would likely be justified under such circumstances. Inputs, which can be easily changed to determine their effect on sample size, are restricted to values between zero and 25%. If the calculated value for sample size exceeds 300, no sample size will be displayed, and a message will appear to the effect that tests of controls are probably not cost effective. Finally, the spreadsheet is designed to display the RACRTL and the upper limit for all deviation values from zero through two more than the critical value, with an absolute maximum of 20 deviations.

**Efficiency and Effectiveness **

The audit worksheet, which may be downloaded in Excel 97 format from www.cpaj.com, offers a number of benefits. The auditor is not limited to input values found within standardized tables but is free to select values consistent with individual expectations and preferences. Furthermore, the auditor can easily evaluate the differences in required sample sizes and critical values at various deviation rates and risk levels. The worksheet provides the auditor with easy working paper documentation of the assumptions made and the resulting sample size, as well as information to evaluate the results and support the actual level of reliance on the control. Finally, even if the auditor does not already have access to the necessary software, it can be acquired relatively inexpensively. Ultimately, the efficiency and effectiveness of the audit is improved with little cost in terms of time, knowledge, or financial resources.

**Editor:
Robert H. Colson, PhD, CPA**

The CPA Journal is broadly recognized as an outstanding, technical-refereed publication aimed at public practitioners, management, educators, and other accounting professionals. It is edited by CPAs for CPAs. Our goal is to provide CPAs and other accounting professionals with the information and news to enable them to be successful accountants, managers, and executives in today's practice environments.

©2009 The New York State Society of CPAs. Legal Notices