Using IDEA for Audit Sampling, Page 1
IDEA Audit Sampling Guide–Data Analytics Using IDEA
The starting point is to download IDEA onto your computer.
IDEA may be used for a variety of sampling approaches. Here we present guidance for using
it with attributes sampling and monetary unit sampling (MUS).1
Using IDEA for Attributes Sampling
We illustrate the use of IDEA for attributes sampling both to (1) plan sample size and (2)
evaluate results using an example similar to that in the text (in Detailed Illustration of
Attributes Sampling) but with an upper error limit of 9 percent and an expected error
rate of 2 percent. The text’s population is composed of 3,653 vouchers. The confidence level
is set at 95 percent.
With IDEA open, click the Analysis tab (top of screen) and, from that tab, in the Sample
group, select Attribute (Analysis→ Sample → Attribute).
Calculate the Attributes Sample Size in IDEA
After clicking on Attribute in the sample group, if it is not already selected, select
Planning (Beta Risk Control).” This approach (“one-tailed testing”) is the approach
used in the text (both for calculating sample size and evaluating results.)2
Input
• Population size = 3653 (not 3,653—no comma).
• % Tolerable deviation rate = 9.00.
• % Expected deviation rate = 2.00
• Confidence level to control beta risk = 95 (1 – 5% risk of assessing control risk too
low).
1 More detailed guidance is available in three documents published by IDEA and available with our IDEA materials
(IDEA Audit Sampling Module):
• Attribute Sampling Explained (by Kurt B. Johnson).
• Monetary Unit Sampling Explained (by Kurt B. Johnson).
• Classical Variables Sampling.
2
Information on 2-tailed testing is available in Attribute Sampling Explained by Kurt B. Johnson—see footnote 1
above.
Using IDEA for Audit Sampling, Page 2
Next, click Compute to obtain the sample size directly under the inputs:
The required sample size is 68, with a maximum of 2 deviations allowable (under the
input and in the Conclusion). Details beyond the text presentation are included in the
table below, but the shaded row indicates that if 2 deviations are identified the auditor
may conclude with a bit over 95% confidence (a bit under 5% risk of incorrect acceptance)
that the population deviation rate in the population is greater than 9%.
Evaluate the Attributes Sampling Results in IDEA
Example a: Assume you find no deviations:
Using IDEA for Audit Sampling, Page 3
- Approach 1: Use the text’s first approach of simply determining whether two or less
deviations are identified (in which case a conclusion may be made that the deviation rate
does not exceed the tolerable rate at the specified risk). Here, since no deviations were
identified, we can accept this population.
“The achieved upper deviation rate does not exceed the tolerable deviation rate of 9
percent.” The planned assessed level of control risk is achieved. Absent other
considerations (e.g., discovery of other related types of deviations, irregularities, etc.)
the assessed level of control risk is at or below the planned assessed level.” - Approach 2: Using the text’s second approach, we evaluate the actual results. If you still
are on the previous screen, click on the Sample Evaluation Tab (above Number of
deviations in sample). If you are no longer on that screen, go to Sample Evaluation
(Analysis→Attribute Sampling → Sample Evaluation) and key in:
Record=3653
Confidence = 95.00
Sample size = 68
Number of errors = 0
Click compute and IDEA provides the following:
The 1-Sided Upper Limit is equal to 4.24, well below the 9.00% tolerable deviation rate.
Using IDEA for Audit Sampling, Page 4
Example b: Assume you find 3 deviations - Approach 1: Because we identified more than 2 deviations, we have not met our
audit objective. Accordingly, we conclude that the achieved upper deviation rate is
greater than 9 percent and the achieved upper deviation rate is higher than 9
percent.” Therefore, the planned assessed level of control risk is not achieved. We
need to consider increasing the assessed level of control risk above the planned
assessed level.3 - Approach 2:
Use IDEA’s evaluation function (Analysis→Attribute Sampling → Sample
Evaluation)
Record=3653
Confidence = 95.00%
Sample size = 68
Number of errors = 3
Click compute. Using this second approach, the achieved 1-sided upper deviation
rate is 10.92, which is above the tolerable deviation rate.
Because the upper limit (10.92%) exceeds the tolerable rate (9%) we should consider
increasing the assessed level of control risk and increasing the scope of substantive
procedures. Notice that IDEA also provides “2-tailed” results which control both the risk of
3 Note that, given the second previous “Attribute Sampling” form on page 2, we may be more precise and state that
the 3 deviations result in us being 87.40% confident that the deviation rate is less than 9%. Yet, our sampling plan
required 95.00% confidence.
Using IDEA for Audit Sampling, Page 5
assessing control risk too low and too high (this is the .96% to 12.27% interval in the conclusion)—
see footnote 2.
Using IDEA for Monetary Unit Sampling (MUS)
While IDEA’s MUS software can calculate required sample size without an open project or
source file (that is, a data file such as an Excel file of sales invoices), a project with a source
file is required to evaluate sample results.4 Since we will do both, we will begin by creating
a project and importing a source file.
Because of limitations in the academic version of IDEA, we introduce a population of 3,500
sales invoices to illustrate the application of MUS with IDEA (Chapter 9_Sales Invoices
in our IDEA Source Files in the IDEA General Materials Module). The total of the 3,500
sales invoices is $5,700,000.00. Note that this population is supplemental to the text
presentation.
Create an IDEA project for the Excel source file Chapter 9_Sales Invoices. The
Appendix to this document is probably adequate for you to create the project. Very detailed
guidance is provided in section 2 (particularly pages 33+) of the IDEA Data Analysis
Workbook.
Access and open IDEA with the project you created above open.
Calculate the MUS Sample Size in IDEA
Click the Analysis tab (top of screen) and, from that tab, in the Sample group, select
Monetary Unit and Plan (Analysis → Sample → Monetary Unit → Plan).
Using the file of sales invoices imported into IDEA (created above) calculate the required
sample size and sampling interval, using the following:
• Use values from database field: Amount.
Note: Always check this because IDEA sometimes selects another field (e.g., Sales
Invoice #).
• Total Value = $5,700,000
• Confidence level = .95 (1 – Risk of Incorrect Acceptance). Note: This is another one to
always check as IDEA ordinarily places .90 in the box.
• Tolerable Error (Tolerable Misstatement) = $600,000. Note: Assume that this and
expected error are from the engagement partner.
• Expected Error (Expected Misstatement) = $50,000
4 Users of ACL with this text will find that evaluating MUS results for classroom purposes using IDEA is more
involved than is ACL. With IDEA one extracts a sample (which requires an underlying population), determines
which accounts in the sample are misstated, and then uses IDEA to evaluate results. When ACL is used, you may
simply assume existence of a population, create desired misstatements, and evaluate results without having the
actual population or sample.
Using IDEA for Audit Sampling, Page 6
Enter the above numbers (Total Value may already be inserted). Your screen should appear
as:
Click Estimate to obtain the following, showing sample size of 32, and a sampling interval
of $178,125.00.
Using IDEA for Audit Sampling, Page 7
Then click Accept.
Extract the MUS Sample
After clicking Accept, the screen should indicate the following, although you will have a
different random starting point:
As discussed in detail in Monetary Unit Sampling Explained by Kurt Johnson (see our
footnote 1) there are numerous approaches to MUS. We will use
• Extraction type: Fixed interval.
• High value handling: High values in database. Note: This results in two tables of
sampled items, one with values less than the sampling interval and one with values equal
to or higher than the sampling interval. While similar to the approach used in the text
(although the text doesn’t explicitly use two files), this approach produces results that
differ somewhat from the AICPA approach illustrated in the text.
Using IDEA for Audit Sampling, Page 8
• Random starting point: We suggest that you change this to 80,000.00 (this should allow
you to obtain the exact same sample as we use if the file is still in sales invoice number
order); use a significantly different number (or the one generated by IDEA) if you wish
to obtain a different sample.
• Click OK.
Notice in the File Explorer at the left the two generated sample files (under the Chapter
9_Sales Invoices file you have been working with):
High values are those with AMOUNT equal to or greater than the sampling interval, and
Monetary Sample includes the smaller accounts. Following are the two tables:
Using IDEA for Audit Sampling, Page 9
In an actual setting, the auditors would then audit the items in the sample to obtain audited
values (note that IDEA sets up an audited values AUDIT_AMT column, populated with book
values (AMOUNT). When auditors identify a misstated AMOUNT, they change the
AUDIT_AMT to the audited value. To simplify matters, for your sample assume the
following:
• Monetary Sample: The first three items in the small accounts file (Monetary Sample)
are overstated by $150, 100, and $50, respectively. (In an actual audit situation, one
would audit each of the accounts to identify any errors.) If you used a different random
starting point and any of the items in your sample have a book value less than the
overstatement amount, assume the account’s AUDIT_AMT is 0 (e.g., if your first account
has a $65 book value, simply change the value to 0).
• High Values (other created file): Sales invoice 113976, with a book value of $186,234.33
has an audited value of $180,000.
• No other misstatements were identified.
For each of the above accounts with different audited value vs. book value, over-write the
amount in the AUDIT_AMT with the proper audited amount.
Evaluate MUS Sampling Results in IDEA
A few methods of evaluating results in MUS have been used in practice. In the text we
present the method used in the AICPA Audit Sampling Guide, which in essence is a version
of the “Stringer Bound.” Accordingly, we use the Stringer Bound method in this illustration.
Nonetheless, the way IDEA calculates results does differ somewhat from the text
presentation. Also, if you used a different random starting point than ours (80,000.00)
during data extraction, your results are likely to differ somewhat from ours.
Using IDEA for Audit Sampling, Page 10
To evaluate results
• Make certain that the Monetary Sample file is the open file—that is, the data file with
the accounts smaller than the sampling interval.
• Evaluate using the Stringer Bound (Analysis → Monetary Unit → Evaluate →
Stringer Bound).
• Since we are testing at 95% reliability level, if necessary, change Confidence level to
reflect that level. Remember—IDEA has a tendency to establish this at 90.00%,which
we don’t want in this example.
Click OK.
Using IDEA for Audit Sampling, Page 11
As per Chapter 9 (Appendix 9A), the auditors may accept the population as being materially
correct because the net upper error limit for overstatements ($572.397.44) is less than the
tolerable misstatement ($600,000). This total upper error limit is the result of in essence
treating the overall population of sales invoices as two subpopulations—one composed of
the two items over the sampling interval ($178,125.00) and one with the smaller items.
Then, for each of the 2 populations, the net most likely error and the “total precision” are
calculated. The “net most likely error” corresponds precisely to the text’s projected
misstatement. The “total precision” includes both the text’s basic precision and incremental
allowance and differs in amount somewhat primarily due to treating the two populations
completely separately. Using the approach outlined in the text, we do not use the
Understatements column.5
5
In the text we do not directly address understatements. When using IDEA an understatement is addressed the same
way as an overstatement—simply key in the AUDIT_AMT. Guidance on that is presented by Johnson—see footnote 1
of this paper.
Using IDEA for Audit Sampling, Page 12
Appendix: Homework help – Summary of Creating a Project in IDEA6
Create IDEA Project - Open IDEA.
- From the IDEA Ribbon (near the top), select the Home tab; in the Projects group,
click Create.
• Select Managed Project7 and provide a name (here, Sales Invoice Project is a
possibility).
• Click OK.
• If asked, click Individual usage (although this is not critical).
• Although you do not see it on the screen, IDEA has established a folder with the
name Sales Invoice Project with a number of sub-folders. - From the Home tab, in the Projects group, click Properties. Input a Report
name (e.g., Sales Invoice Project), and, if you wish, a Report period. Click OK.
Load the Excel file Chapter 9_Sales Invoices into IDEA - Minimize IDEA and copy the Excel file Chapter 9_Sales Invoices (in IDEA
General Materials Module) into the subfolder titled Source Files.ILB within your
project. That subfolder is ordinarily placed within My IDEA Documents → IDEA
Projects→ Sales Invoice Project→Source Files.ILB. If not, perform a search
for the project and file. After copying Chapter 9_Sales Invoices go back to IDEA.
Import the Excel file Chapter 9_Sales Invoices into IDEA format. - Open IDEA. From the Home tab, in the Import group, click Desktop. The Import
Assistant dialog box appears. - Select Microsoft Excel. In the File Name, go to the highlighted address and you should
see Chapter 9_Sales Invoices file (you should have placed it in Source Files.ILB
in step 4 above)—highlight it and click Open. - Back in the Import Assistant, ignore other information (Option File and Edit
template). Click next.
6
For details go to IDEA Workbook Section 2 and follow directions.
7 The difference between External and Managed projects is their location in the computer. When one creates a
managed project, it is placed in the IDEA projects folder (by default, My IDEA Documents → IDEA Projects).
External projects can be created anywhere that a user has appropriate permissions. This can be on a local computer
or on a shared network drive. We recommend Managed because of its simplicity and because using that approach
for all assignments keeps your projects in one area.
Using IDEA for Audit Sampling, Page 13 - On the Microsoft Excel screen place a check mark in “First row is field names”—do
not miss that! On left, make certain that “Sales Invoices” (the name we gave the
schedule itself) is checked. Click OK - From the Properties window, click Control Total. In the Select Control Total
dialog box, click the Amount field and click OK. The total should be $5,700,000.00.
If it varies from this, it is probably best to start the process again from the beginning. - You should now have the data visible on your screen. If not, in the Properties window
(far right on screen, illustrated below) click Data to go back to the file data itself.
Remember Properties as it is particularly helpful (e.g., you may be on a screen you
want to leave, simply click on Data to view data again). - Go to any point within the data screen, within the table, and double click to open the
Field Manipulation box. Alternatively, go to Data → Fields → Click on Field
Manipulation (the tiny bottom right corner of the Define Action Field button).
Review data “type” to make certain that it has been categorized properly (particularly
date fields). If your data has a date column(s) make certain a date mask is included in
the parameter field (IDEA ordinarily uses YYYYMMDD as the mask). Note: The Sales
Invoices file does not include a date column. - You should now be able to perform IDEA functions on the imported table, including
sampling. If you are having problems, we recommend Section 2 of the IDEA Date
Analysis Workbook.
