Monetary-Unit Sampling Using Microsoft Excel

By Bruce Wampler and Michelle McEacharn

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.cpajournal.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 1 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.

If the sample size is larger than the auditor desires, then the auditor may use one (or some combination) of the following options to reduce the sample size:

• Increase tolerable misstatement;
• Increase ARIA; or
• Reduce EPER.

Practically speaking, the only good option is the first one, assuming that tolerable misstatement allocated to some other accounts can be reduced. In general, ARIA should not be modified at this stage, because the desired ARIA determines the sample size, not vice versa.

Furthermore, the EPER should be the auditor’s best estimate of the exception rate in the population, which should not be influenced by the resulting sample size. Thus, if the tolerable misstatement cannot be changed, the auditor must perform audit procedures on the indicated sample size (assuming it does not exceed 500) in order to gather sufficient evidence to support the opinion on the financial statements.

Selecting the Sample

Using the spreadsheet in Exhibit 1 to illustrate the sample selection process, assume that the recorded balance in accounts receivable is $300,000, consisting of 300 customer accounts. Based on the inputs shown, the sample will consist of 100 numbers between 1 and 300,000, each representing one of the $300,000 in the population.

To select the specific sample items, the auditor must match each of the $300,000 in the population with one of the 300 customer accounts. The exact mapping is unique for each population and is typically prepared by calculating the specific dollars associated with each account, as well as a cumulative running total. The process is illustrated below for the first four accounts. Dollars 1 through 460 would be associated with account 1, dollars 461 through 2,086 would be associated with account 2, and so on.

Although the sample items may be chosen randomly, systematic sample selection is often used in conjunction with MUS. The first step of systematic sample selection is to calculate the sampling interval (I), which is the population value divided by the desired sample size. In the example, the sampling interval is $300,000/100, or $3,000. The first dollar is selected by choosing one random number (R) between 1 and 3,000. The second dollar would equal R + I, the third dollar would equal R + 2I, and so on. For example, if a random starting point of 521 is selected, the first item in the sample would be account 2. The second item would be dollar 3,521 (521 + 3,000), corresponding to account 4.

This pattern continues until a total of 100 dollars are identified. When using systematic sample selection, any item larger than the sampling interval must be selected at least once, and very large items may be selected more than once. Consequently, the number of items selected may be less than the sample size. If an exception is noted for an item that is selected multiple times, it is treated as an independent observation for each time selected.

The spreadsheet user can generate the sample dollars by clicking on the “generate sample” button. Because a random starting point is chosen each time, the exact dollars identified will probably change each time this function is used, even if the underlying parameters (population value and sample size) do not change. Using this output, the auditor will identify the actual sample items using the above procedure. Finally, the auditor would apply the appropriate audit procedures (e.g., confirmation) to each sample item. Information on the exceptions found (if any) will be used in the final phase to form a conclusion about the recorded population value.

Evaluating the Results

Although based on attribute sampling principles, the objective of MUS (estimating the monetary misstatement in the population) is quite different from the objective of “traditional” attribute sampling (estimating the population misstatement rate). Specifically, MUS requires consideration of the following two factors:

• The type of exception (overstatement or understatement) must be considered because it has important implications for the estimate of monetary misstatement in the population.
• The extent of the exception must be measured and considered in estimating the misstatement. For example, an item with a $1,000 recorded value that is overstated by $900 (90%) should be given more weight than a $1,000 item that is overstated by $10 (1%).

The following illustration of how the results are evaluated when using MUS uses the sample results depicted in Exhibit 1. Assume that the auditor detected the following four exceptions in the sample of 100 and that each item was selected only once:

To evaluate the results, overstatements should first be segregated from understatements (the spreadsheet does this automatically). Once the items are segregated, however, the process used to estimate the monetary misstatement is the same for both types of exceptions. The starting point for an auditor performing this process (without the benefit of the spreadsheet) would be tables like the one in Exhibit 2. Given the sample size of 100 and three overstatement exceptions, the table indicates a 5% chance that the true exception rate in the population exceeds 7.6%; however, concluding that the recorded value is overstated by $22,800 (7.6% of $300,000) would overstate the amount of misstatement.

Instead, MUS requires the auditor to partition the 7.6% total upper exception limit into layers. The first layer uses the 3.0% upper limit that would apply if no exceptions had been found. Even if no exceptions were found in a sample size of 100, the auditor must still conclude that there is a 5% risk that more than 3.0% of the dollars in the population contain exceptions. The first layer represents a basic allowance for sampling risk that will decrease as the sample size increases. Because nothing is known about these potential misstatements, the conservative approach is to assume a worst-case scenario (100% misstatement), which translates into a $9,000 estimated misstatement for this layer (3.0% of $300,000).

The upper limit associated with the second layer is the value for one exception (4.7%) minus the value for no exceptions (3.0%). Because this first exception was found, the auditor now estimates that an additional 1.7% of the dollars in the population contain exceptions. The upper limit for additional layers is calculated in a similar manner and becomes progressively smaller with each additional layer (due to rounding, the same value may occasionally be used for adjacent layers). The spreadsheet performs these calculations automatically.

To determine the dollar value for each layer, the audit evidence is used to estimate the extent of misstatement. The only decision necessary is to assign each overstatement to a specific layer. MUS uses a conservative approach by ranking the exceptions in descending order based on their percentage misstatement. Because the upper limit becomes progressively smaller with each additional layer, ranking the misstatements in this manner results in the largest misstatement possible for the actual exceptions noted. The spreadsheet calculates the percentage misstatement and sorts the exceptions in the proper order with no additional user input.

Based on this approach, Exhibit 1 indicates that the initial population overstatement estimate is $17,252.10. Recognition should be given to any detected understatements, because they would offset some of the estimated overstatement. In the example, one 10% understatement was found; or, stated differently, a total of $.10 (10% of $1) of understatement was found in a sample totaling $100. Extrapolating this amount to the population results in a point estimate of understatement of $300 ($0.10/$100 x $300,000). This value is subtracted from the initial overstatement estimate, resulting in an adjusted overstatement estimate of $16,952.10.

The same process is applied to any understatements noted in the sample. The auditor again assigns the first layer (zero exceptions) a percentage misstatement of 100%; however, because an item may be understated by more than 100%, it is possible for additional layers to have a percentage misstatement larger than 100%. Because MUS is not designed to test for understatement, an auditor noting such large understatements should consider additional audit procedures. Exhibit 1 indicates an adjusted understatement estimate of $4,293.00.

The auditor now has the information necessary to make a decision regarding the population value using the following decision rule. If both the adjusted overstatement and understatement estimates are less than tolerable misstatement, conclude that the recorded population value is not materially misstated. Otherwise, conclude that the recorded value is materially misstated. In the example above, the auditor would accept the recorded value, because neither misstatement estimate exceeds the $27,000 tolerable misstatement.

To evaluate the results using the spreadsheet, the auditor should click on the “enter data and compute results” button and enter the information for each exception. When the “compute results” button is clicked, the spreadsheet makes the remaining calculations and the proper conclusion is shown at the bottom of the worksheet. This page may be printed for workpaper documentation.

Accuracy and Flexibility

Using the spreadsheet reduces the chance of auditor error as compared to manual computations. A more significant advantage is that the spreadsheet allows considerable flexibility in specifying the inputs used to determine sample size and generates precise information to be used in evaluating the results.

Even if an auditor using tables decides to use one of the few available input combinations when determining sample size, the resulting sample size is unlikely to appear in the tables used to evaluate results. Values for the next-smaller sample size may be used to project the population misstatement, but this increases the likelihood of incorrectly rejecting a balance that is not materially misstated. The spreadsheet generates the correct upper limits based on the exact sample size used, thereby providing more accurate results.

Close