Monetary-Unit Sampling Using Microsoft Excel

CPA Journal, The, May 2005 by Wampler, Bruce, McEacharn, Michelle

Monetary-unit sampling (MUS) is a method of statistical sampling used to assess the amount of monetary misstatement that may exist in an account balance. The method, also known as dollar-unit sampling or probability-proportional-to-size sampling, has been used for many years and is widely accepted among auditors.

Many auditors apply MUS using essentially the same methods that were used before the invention of personal computers and spreadsheet software. This approach relies on printed tables that offer limited options. Furthermore, MUS requires calculations that introduce the possibility of error if made manually. With current technology, auditors can use a Microsoft Excel spreadsheet that automates and enhances the use of MUS and reduces the chance of errors. Interested readers may download a copy of the authors' spreadsheet from www. cpajoumal.com.

Using MUS involves three key steps:

* Determining the proper sample size;

* Selecting the sample and performing the audit procedures; and

* Evaluating the results and arriving at a conclusion about the recorded population value.

Determining the Sample Size

Although MUS is used for variables sampling, it is actually based on attribute sampling techniques. Attribute sampling is often used for tests of controls and is most appropriate when each sample item can be placed into one of two classifications: "exception" or "no exception." When a monetary balance is the object of interest, however, there are varying degrees of exceptions; for example, the balance of a $5,000 receivable may be overstated by $50, $500, or even $5,000. An auditor is obviously more concerned with larger misstatements. Adjusting for the extent of misstatement by converting the degree or rate of misstatement into a monetary amount will be discussed below.

Because MUS is based on attribute sampling, the sample size may be determined by the same basic procedures as for a statistical sample size for tests of controls. A common approach uses sample size tables published by the AICPA. The Exhibit I spreadsheet is based on the same algorithm used to calculate the values in the AICPA tables; unlike the AICPA tables, however, this spreadsheet may be used for any combination of the required inputs, providing the auditor with more flexibility. Four inputs are required to determine the sample size, but the first two inputs are discussed together because of their close relationship.

Population dollar value and tolerable misstatement. The population dollar value is the amount recorded on the books for the account being audited. Tolerable misstatement is the maximum monetary misstatement in an account balance that can exist, when combined with misstatement in other accounts, without causing the financial statements to be materially misstated. Determining tolerable misstatement (and overall materiality) requires significant auditor judgment and is beyond the scope of this article.

The actual input needed to determine the sample size is the tolerable rate of misstatement (TR), which equals the tolerable misstatement divided by the population dollar value. Because tolerable misstatement is usually expressed as a dollar amount, however, the spreadsheet is designed to accept the input in this form and calculate TR. There is an inverse relationship between TR and required sample size.

Expected population exception rate (EPER). EPER is the exception rate anticipated to exist in the population. For example, if the auditor uses 2% for EPER and the recorded population value is $100,000, the implication is that the auditor expects the recorded value to be misstated by $2,000. If the auditor's estimate of the expected misstatement is a dollar amount, it should be converted to a percentage. EPER is directly related to sample size and must be significantly less than TR. As EPER approaches TR, the sample size will become prohibitively large.

Determining EPER requires auditor judgment. In making this estimate, the auditor might consider prior audit findings, recent changes in client personnel, or other information that might shed light on the likelihood of misstatement. If no misstatement is expected, zero may be used. To allow a margin for error, using some small value for EPER might be prudent even if no misstatement is expected.

Acceptable risk of incorrect acceptance (ARIA). ARIA is the maximum risk the auditor is willing to accept of incorrectly concluding that the population is not materially misstated when, in fact, the true misstatement in the population exceeds tolerable misstatement. Although ARIA should be set at a low level, the exact value used may be affected by several factors, including overall acceptable audit risk and the results of tests of controls and other substantive tests (e.g., analytical procedures) performed on the account. Determining ARIA requires significant auditor judgment and is beyond the scope of this article. There is an inverse relationship between ARIA and required sample size.

To use the spreadsheet to determine sample size, the auditor enters the four inputs in the indicated cells and clicks on the "calculate sample size" button. The required sample size (given this combination of inputs) will then be displayed. The spreadsheet will stop calculating and display an error message if the required sample size exceeds 500. As the next section discusses, the actual number of items examined may be less than the dollar sample size because the same item may be selected more than once.