Monetary unit sampling excel template

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

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

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

1. 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: Summary of Creating a Project in IDEA6 Create IDEA Project

1. Open IDEA.

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

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

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

5. Open IDEA. From the Home tab, in the Import group, click Desktop. The Import Assistant dialog box appears.

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

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

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

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

10. 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).

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

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