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:

  • Tolerable rate. The maximum population deviation rate the auditor is willing to accept without altering the planned assessed level of control risk. For example, a low (3–7%) rate might be used if substantial reliance on the control is planned. The tolerable rate is inversely related to sample size.
  • Risk of assessing control risk too low (RACRTL). The maximum risk the auditor is willing to accept of incorrectly concluding that the population deviation rate is less than or equal to the tolerable rate when, in fact, the population deviation rate exceeds the tolerable rate. It is important to minimize this risk because such an error will result in an overreliance on controls and an unjustified reduction in substantive tests. Consequently, the effectiveness of the audit will be compromised and audit risk will be greater than desired. Establishing the RACRTL will depend on the importance of the control and the auditor’s tolerance for risk, but it should be set at a low level. The RACRTL is inversely related to sample size.
  • Expected population deviation rate. The deviation rate believed to exist in the population. Auditor judgment, considering such factors as the overall control environment and prior audit findings, would be used in estimating this rate. The expected deviation rate is directly related to sample size and must be lower than the tolerable rate if the control is to be relied upon.

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

    Bruce Wampler, DBA, CPA, and Michelle McEacharn, DBA, CIA, CPA, associate professors of accounting at the University of Louisiana at Monroe, can be reached at and

    Robert H. Colson, PhD, CPA

    The CPA Journal

    This Month | About Us | Archives | Advertise| NYSSCPA

    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

  • Visit the new