Sampling Using Microsoft Excel
Bruce Wampler and Michelle McEacharn
- 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
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/down.htm.
MUS involves three key steps:
Determining the proper sample size;
Selecting the sample and performing the audit procedures;
Evaluating the results and arriving at a conclusion about
the recorded population value.
the Sample Size
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.
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.
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.
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.
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
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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
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
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%).
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
- (Under-) Statement
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.
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%
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.
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.
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.
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.
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.
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.
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
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.
Wampler, DBA, CPA, is an associate professor of accounting
in the department of accounting and business law, college
of business administration, Louisiana State University in
Michelle McEacharn, DBA, CPA, CIA, is a professor
and the head of the department of accounting, college of business
administration, University of Louisiana at Monroe.