ACL Case Project

906

ACL Basics, Tutorial, and Cases

ACL is a generalized audit software package widely used by public accounting and internal audit- ing organizations to access, analyze, and manipulate electronic data contained in client systems. The following ACL basics, tutorial, and cases—and the problems within the chapters—are designed to help you learn how to use many of the important features of this tool as well as to provide you with hands-on experience of using the computer to help perform audits. You are likely to use this or a similar program in your professional career. The ACL program is contained on a CD disk that comes with your book. It is recommended that you install ACL on your own computer if it has a Windows operating system. If this is not possible, check with your instructor to see if it is available in a computer lab.

Data Files The data fi les for the ACL tutorial, cases, and problems must be downloaded from the following Internet address: www.cengage.com/accounting/rittenberg under “Student:Companion Site.” If you install ACL on your own computer, it is recommended that you download the data fi les to your hard drive. If you use ACL on a college network, download the fi les to whatever input/out- put device can be used on the lab’s computers. The fi les created by ACL will be stored on the same device with the data fi les.

Getting Started 1. Familiarize yourself with some of the basic features of ACL by reading the “ACL Basics” section.

2. Work the tutorial.

ACL Basics This section contains descriptions of some of the basic features of ACL. Following this section is a tutorial to help you apply many of these features to other cases and problems.

Start the ACL program. Note the menu options on the standard toolbar and the options under each item. Move your cursor along the icons and note what each one stands for.

The fi rst step in using ACL is to (1) create a new project or (2) open an existing project.

(1) Create a New Project To start a new project (such as a case), select FILE | NEW | PROJECT and give the project a name. Or click the Create a New Project icon at the left of the toolbar. Be sure to save the project

ACL Appendix

63722_app_p906-p932 pp3.indd 90663722_app_p906-p932 pp3.indd 906 5/11/09 4:10:44 PM5/11/09 4:10:44 PM

A C L B a s i c s 907

in the directory with the data fi les. The fi les that are created when using ACL are saved on the same drive with the data fi les.

You will then need to import one or more fi les (called tables in ACL) to work on.

Import a Table (fi le) Click on FILE | NEW | TABLE and follow the on-screen instructions.

(2) Open an Existing Project To open an existing project, select FILE | OPEN PROJECT and click on the fi le name in the directory in which it was saved. Or click the Open an Existing Project icon. You can then con- tinue working on the project and can import additional fi les.

Basic Activities Following are some basic activities that can be performed on a fi le.

Add a Column You can add one or more columns with new information you create based on the data in the table. For example, you could calculate the diff erence between two fi elds. Either click the Add Column icon on the toolbar or right-click the mouse, and select Add Columns. Click the Expr button and build the expression needed to calculate the data for the new column (such as Amount–Confi rmed). Type a column name in the Save as box (such as Diff erence). Click OK and OK again. You may need to move the screen to the right to see the new column.

Age Select ANALYZE | AGE or click the Age icon on the toolbar. Select the fi eld on which to age (such as INVDATE). Click the button next to the Cutoff date window and select the appropri- ate date. You can accept the default aging categories or change them. Choose the fi eld to subtotal by aging category (such as AMOUNT) in the right window. Click the Output tab at the top and select where you want the output. If the output is to a fi le, give it a name. Click on that category (such as >45) to get a list of items in a specifi c aging category.

Classify Used to count and aggregate the number and percentage of records related to each unique value of a character fi eld and to subtotal specifi ed numeric fi elds for each of these unique values. Click ANALYZE | CLASSIFY | select the fi eld to classify on and the fi eld(s) to subtotal. This can be output to the screen, a new fi le, graph, or the printer.

Cross-tabulate Used to count and aggregate records simultaneously using two character fi elds and a related numeric fi eld. Results appear in a row/column grid form. Select ANALYZE | CROSS-TABULATE. Se- lect one character fi eld in the Rows box. Select the second character fi eld for the Columns box. Click the name(s) of the fi elds to be subtotaled. You can choose to include the record count for each cell.

Duplicates/Gaps/Sequence Used to identify duplicate items, gaps, and items out of sequence. Access through ANALYZE | Look for gaps, look for duplicates, or examine sequence | then select the fi eld to analyze.

Export Select DATA | EXPORT TO OTHER APPLICATION. Select the fields to be exported. Choose to export it as a Word, Excel, Word Perfect, or text fi le. Give the new fi le a name. The exported fi le will be stored on the same storage medium as the project. This can be used, for example, to insert a sample of customer names, addresses, and balances into accounts receivable confi rmation letters.

Extract Select DATA | EXTRACT DATA | IF. You can then create an expression to select the items to extract. For example, if you want to extract all unpaid invoices over a certain age based on the fi eld INVDATE, the expression would be INVDATE < (click on DATE and select the appropriate date). Fields can be entered into the expression either by typing or by double-clicking on the fi eld in the Available fi elds window. If you want to extract all amounts over $100,000, the expression

Auditors will often create an additional column to test the accuracy of computations, such as inventory price times quantity, or will use it to create new information, such as inventory turnover. The new column can then be manipulated to perform further analysis.

P R A C T I C A L P O I N T

Internal auditors will often use the Duplicates feature to identify overpayments or potentially fraudulent payments.

P R A C T I C A L P O I N T

63722_app_p906-p932 pp3.indd 90763722_app_p906-p932 pp3.indd 907 5/11/09 4:10:46 PM5/11/09 4:10:46 PM

908 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

would be AMOUNTS > 100000. Click OK when the expression is complete. Type a name for the extracted fi le in the To window.

Expressions for extracting data can include specifi c text. For example, if there is a column la- beled COMMENT with explanations of confi rmation exceptions including “Confi rmed − OK” and you want to extract all of those that do not have that explanation, the expression would be COMMENT <> “Confi rmed − OK”. The exact words must be in quotes.

Note: When records are extracted from a fi le, you must create a new fi le and you should save that fi le. ACL describes this as creating a fi lter because we have fi ltered the data to create a new fi le. To get back to the original fi le, click on the original fi le name in the left window.

File Statistics There are two ways to get statistics about the data in the fi le.

1. Click on the icon with the % sign. Choose the fi eld(s) on which you want statistics. Click on the Output tab and choose where you want the output (screen, fi le, or print).

2. Select ANALYZE | STATISTICAL and either STATISTICS or PROFILE. If you choose STATISTICS, you have the same choices as in Step 1 above. The statistics provided with STATISTICS are numbers, totals, and averages for positive, negative, zero items, overall totals, and highest and lowest values for the fi eld(s) selected. The statistics that are provided with PROFILE are total, absolute, minimum, and maximum values for the fi eld(s) selected.

Filters Filters may be used to query the data in a table that has been imported without adding a new fi eld or creating a new fi le. For example, a fi lter can identify customer unpaid invoices over $50,000 as follows. Click on the EDIT VIEW FILTER button (the icon immediately on the left side of the fi lter window with the symbol fx ).

This brings up the Edit view fi lter box:

1. Enter the expression AMOUNT > 50000 in the expression window. This is done by double-clicking fi rst on the AMOUNT fi eld in the Available Fields window, then clicking on the > expression, and then entering 50000.

2. Click OK. The screen now shows those invoices greater than $50,000. This fi ltered table can be printed and/or manipulated in the same ways as any table. These fi lter screens, however, cannot be saved as a separate fi le. If you want to save this information as a new fi le that you can use later, you need to extract the data as described above under “Extract.”

3. To close this fi lter and get back to the original table, click on the REMOVE FILTER icon with the red X.

63722_app_p906-p932 pp3.indd 90863722_app_p906-p932 pp3.indd 908 5/11/09 4:10:46 PM5/11/09 4:10:46 PM

A C L B a s i c s 909

Join Files Important note: To join two tables (fi les), be sure the fi elds on which the fi les are to be matched (such as invoice numbers) are in the same format, such as ASCII. To change the format, select EDIT | TABLE LAYOUT or click the Edit Table Layout icon on the toolbar. Double-click on the fi eld to be reformatted, and select the new format from the drop-down menu. Click on the green check mark in the left margin to accept the change, and then OK.

Select one of the fi les as the primary fi le by making it active on the screen. If the active fi le is not to be the primary fi le, click on the fi le you want to be the primary fi le in the left window of the screen. Click the Join icon that looks like an upside-down organization chart or select DATA | JOIN TABLES. Click on the secondary fi le you want to join with the primary fi le. Click on the fi eld name for the primary and secondary fi le keys (fi elds on which the fi les will be matched, such as invoice numbers). These fi elds must be in ASCII format. Click on the primary and second- ary fi elds you want in the combined fi le. To select more than one fi eld in a window, hold down the Ctrl key. Type a name for the combined fi les next to the To button, and then OK.

Join Options Click on the More tab to choose the join option(s) you want:

1. Match Primary Records — Only the records in the secondary fi le that match the primary records will be joined. This is the default option.

2. Match Primary Records: a. Include all primary records — All primary records will be included in the joined fi le even if there is no

matching record in the secondary fi le. For example, if there is a payroll check for an employee that is not in the master fi le, it will be included in the joined fi le where it can be identifi ed for further investigation.

b. Include all secondary records — All secondary records will be included in the joined fi le even if there is no matching record in the primary fi le. For example, this could be used to identify an employee in the payroll master fi le who did not get paid.

c. Both options (a) and (b) can be selected for the joined fi le.

Move a Column/Change Column Widths Columns can be rearranged by left-clicking on the column heading, holding the mouse button down, and dragging the column to where you want it.

Column widths can be changed by placing the cursor to the right side of the column heading and moving it in either direction.

Prepare and Print Reports You can tailor a report of the information on the active screen and get column totals. Select Data | Report or click the Report icon on the toolbar. Type any information you want in the header and/or footer of the report, such as the client’s name or your name, a date, and the nature of the information. Choose where you want the output (screen or fi le). The report can be previewed by choosing File | Print Preview or clicking the Print Preview icon. You can change the page lay- out from portrait to landscape by clicking the Setup button. The report will display and print totals for all numeric fi elds. You may need to adjust the column widths to see entire column headings.

If you do not want to add headers or footers to the report, you can simply click the Print icon or select File | Print on the menu. All numeric fi elds will be automatically totaled.

Relating Files Two or more fi les can be related to enable the use of the information from all the fi les as if they had been joined. With the desired primary table active, choose DATA | RELATE TABLES | Add Table |, choose the table to be related, and drag between the common fi eld.

Sampling—Size, Selection, Evaluation There are two basic ways to use ACL for sampling: monetary sampling (PPS sampling) and record sampling (random items such as for attribute sampling).

Monetary (PPS) Sampling Sample Size Select SAMPLING | Calculate sample size | Monetary. Enter the confi - dence level (the complement of risk, such as 90 for a 10% risk), the dollar value of the population, materiality (tolerable misstatement), and expected total errors. Click CALCULATE. This calcu- lates the sample size, sampling interval, and maximum tainting percent. Click the Output tab at the top and select Screen. You can then print the calculated results.

63722_app_p906-p932 pp3.indd 90963722_app_p906-p932 pp3.indd 909 5/11/09 4:10:47 PM5/11/09 4:10:47 PM

910 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Sample Selection Select SAMPLING | Sample Records. Choose the fi eld to be sam- pled from the Sample on window (such as AMOUNT), click MUS under Sample type, and enter the sampling interval. Under Sample parameters, click Fixed interval, type in the sampling interval and a random start to serve as a random number seed (they can be any num- bers) or leave the start blank and let ACL generate a random start. For cutoff , type the sampling interval or leave it blank. If you leave it blank, ACL will automatically use the interval for this value. Type in a fi le name next to the To button. The next screen will be the sample. This new fi le can then be printed out and/or used later.

Sample Evaluation Once the audited amounts have been determined through the audit proce- dures, select SAMPLING | Evaluate error, click Monetary, and enter the confi dence level and sampling interval. In the Errors window, carefully enter the book value of the fi rst item in error followed by a comma and the amount of the error (such as 23451.22, 250.33). Note: Enter the amount of the error, not the audited value. Press the Enter key and proceed to enter the next book value in error and amount of error, etc. When fi nished entering the errors, click the Output tab and select Screen or File (if File, give it a name). In either case, you can print the results.

Random Dollar (MUS but not PPS) Sampling Random dollar sampling can be used to select a monetary (but non-PPS) sample. It can be used to select all items over a cutoff amount (top stratum) and a predetermined interval of other items in the population. The predetermined interval is calculated by subtracting the dollar value of the top stratum items from the population book value and dividing the result by the desired sample size from the lower stratum.

Random Record Sampling Random record sampling can be used in a variety of ways. It can be used for attribute sampling as described in the sampling chapter. The concepts follow the nature of attributes and are used for Sarbanes 404 work for a number of companies. The auditor needs to set the confi dence level, the number of items in the population, the upper error limit, and the expected error limit. ACL com- putes the sample size. ACL can then be used to select and evaluate the sample in a manner similar to that described above.

Saving Files Files created by ACL are automatically stored on the same storage device with the data fi les. There- fore, you can end an ACL session and come back later. The fi les will still be there until you delete them. Caution: If you created a fi lter for a fi le, a separate fi le of the fi ltered data is not created.

Sort Right-click on the column heading you want sorted and choose whether you want it sorted in ascending or descending order. All other columns will be included in the sort.

Stratify Select ANALYZE | STRATIFY or click on the Stratify icon. Choose the fi eld on which to stratify in the Stratify On window (such as AMOUNT). Choose the fi eld to subtotal (such as AMOUNT). Type in the minimum and maximum values for the intervals (such as 0 and 100000 ) and the number of intervals (such as 10). Click the Output tab and choose where you want the output.

Summarize Records can be summarized based on some key (such as customer number to get customer bal- ances from an unpaid invoice fi le). The fi eld to be summarized, such as CUSTOMER NO, must be in Character (ASCII), Date, or EBCDIC format, not Numeric format. To change the format from numeric to ASCII, select EDIT | TABLE LAYOUT, or click the Edit Table Layout icon on the toolbar. Double-click on the fi eld to be reformatted, and select ASCII from the drop-down menu. Click on the green check mark in the left margin to accept the change, and then OK.

Select ANALYZE | SUMMARIZE or click on the Summarize icon. Choose the fi eld on which to summarize in the Summarize On window (such as CUSTNUM). Choose the fi eld to subtotal in the Subtotal Fields window (such as AMOUNT). Choose the other fi elds you want in the output fi le in the Other Fields window. Click on the Output tab at the top and select Screen or File.

Stratification is an important tool to provide the auditor with a better “feel” for a large population.

P R A C T I C A L P O I N T

63722_app_p906-p932 pp3.indd 91063722_app_p906-p932 pp3.indd 910 5/11/09 4:10:47 PM5/11/09 4:10:47 PM

A C L Tu t o r i a l 911

Verify Used to check for data validity errors and ensure the data in the table conforms to the table format layout (date, numeric, character). This should be used before doing other procedures on the fi le.

Views This is used to prepare reports when there are too many fi elds in the fi le. Right-click on the fi le name in the Project Navigator window, select Properties, and select the view tab. Choose to add a view. Give the new view a name. Choose which fi elds you want in the new view by double-clicking on the fi eld names in the order you want them. Click OK twice to set the new view. The names of the views of this fi le appear at the bottom of the window. You can click on the view name to activate the view.

Delete Files Files created in the current project can be deleted by right-clicking on the fi le name in the left window, then choosing Delete.

Close Projects To close the project (it will be saved in the directory with the data fi les), either select FILE | CLOSE PROJECT or click the Close the Open Project icon on the toolbar.

To delete fi les you created with ACL, go into the directory with the data fi les and delete the fi les created by ACL (those with the .fi l extension).

ACL Tutorial Start Up It will be most convenient for you to install ACL on your own Windows-based computer. If you cannot do this, contact your instructor to fi nd out if and where ACL may be installed in a lab on campus.

The data fi les may be downloaded from www.cengage.com/accounting/rittenberg under “Student Resources.” Download them to your own hard drive if you are using your own com- puter. Otherwise, download them to a storage device that can be used in a lab environment.

Husky Tutorial Case You are auditing the accounts receivable records of Husky Corp. as of December 31, 2009. The general ledger control account shows a balance of $4,263,919.52. You will use ACL to help per- form some audit procedures.

There are four related data fi les you need to download for this tutorial:

Husky Unpaid Invoices 2009 • contains the unpaid invoices as of 12/31/09.

Husky Shipping File 2009 • contains the shipment numbers and shipment dates for those invoices. You have verifi ed that the last shipment number used in 2009 is 62050.

Husky Credit Limit 2009 • shows the credit limit for each customer.

Husky Confi rmations 2009 • shows the confi rmation results.

Audit Procedures This tutorial will show you how to perform the following audit procedures using ACL:

1. Foot the unpaid invoice fi le.

2. Identify any unpaid invoices older than 45 days.

3. Identify customer balances greater than their credit limit or for which there are no credit limits.

4. Perform a sales cutoff test.

5. Select a PPS (Monetary) sample of unpaid invoices, confi rm them, and evaluate the results.

Data fi les and typed input are shown in italics. ACL icons, commands, and equations are shown in bold. FIELD NAMES are in FULL CAPS.

63722_app_p906-p932 pp3.indd 91163722_app_p906-p932 pp3.indd 911 5/11/09 4:10:47 PM5/11/09 4:10:47 PM

912 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Step 1—Start a new project. Select File | New | Project on the menu bar. Select the location with the data fi les in the Save New Project As window; enter Husky AR as the name for the project. Click Save.

Step 2—Import a table (fi le). To import a fi le (ACL refers to them as tables), click Next on the Select Data Source screen. Locate the Husky Unpaid Invoices 2009 fi le in the Select File to Defi ne window and double- click on it. Click Next three times.

Give the fi le a new name—Husky _Unpaid. Click Save | Finish, and OK. The fi le is now im- ported and shown on the screen.

63722_app_p906-p932 pp3.indd 91263722_app_p906-p932 pp3.indd 912 5/11/09 4:10:47 PM5/11/09 4:10:47 PM

A C L Tu t o r i a l 913

Step 3—Foot the fi le and agree to the general ledger. With the Husky_Unpaid table in the active window, select ANALYZE | STATISTICAL | STATISTICS on the menu bar and click on AMOUNT to foot the fi le. Click OK.

The next screen shows several things. In the fi rst matrix, it shows the total value, which agrees with the general ledger balance ($4,263,919.52), and the number and amount of positive and negative values. The second matrix shows on the fi rst line the value of the largest ($155,198.43) and smallest amount ($−22,659.74) of the unpaid invoices. Print the statistics by clicking on the Print icon or selecting File | Print on the menu bar.

63722_app_p906-p932 pp3.indd 91363722_app_p906-p932 pp3.indd 913 5/11/09 4:10:48 PM5/11/09 4:10:48 PM

914 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Step 4—Identify any unpaid invoices older than 45 days. Click the Husky_Unpaid tab above the statistics to make the table active in the main window, then select ANALYZE | AGE on the menu bar. Accept the default Age On INVDATE. Set the cutoff date to December 31, 2009. Change the aging periods to 0 and 45. Click AMOUNT under Subto- tal Fields to subtotal. Click OK.

There are four invoices amounting to $79,017.13 that are over 45 days old.

Click on >45 under the column headed Days; ACL will retrieve those four invoices from the unpaid fi le. Print the page showing the details for these four invoices by clicking on the Print icon or selecting File | Print on the menu bar. Notice the total of the AMOUNT column is printed. These old accounts should be investigated to determine their collectibility. (Note: Page 2 of the printout shows the report history. To suppress printing this page in subsequent printings, click on Tools, Options, Print, and remove the check mark next to “Include Report History with Reports.”

ACL is designed to support audit procedures. Thus, the auditor has chosen 45 days past due as a point where accounts need to be further analyzed for collectibility.

P R A C T I C A L P O I N T

63722_app_p906-p932 pp3.indd 91463722_app_p906-p932 pp3.indd 914 5/11/09 4:10:48 PM5/11/09 4:10:48 PM

A C L Tu t o r i a l 915

Note: If you want to save this information as a fi le to retrieve later, make the Husky_ Unpaid table active and select DATA | EXTRACT DATA on the menu and click on IF. Enter an expression by double-clicking on INVDATE, click <, select DATE and scroll to November 16, 2009 on the date selector, and click OK. Click OK again. Enter a fi le name such as Over 45 Days next to the TO button. Click OK. The screen will look like the one above but will not have anything showing in the Filter window.

Step 5—Identify customer balances greater than their credit limit or for which there are no credit limits. Click on the Remove Filter icon (with the red check mark) to make the entire Husky_Unpaid table active.

To determine each customer’s balance, change the fi eld type of CUSTNUM from NUMERIC to ASCII. To change the fi eld type, select Edit | Table Layout on the menu bar and double- click on CUSTNUM. Click the down arrow in the Type window, locate and click on ASCII in the window (it is located above NUMERIC). Click the green arrow at the left of the screen, and then click on the red X in the upper-right corner to exit this screen.

63722_app_p906-p932 pp3.indd 91563722_app_p906-p932 pp3.indd 915 5/11/09 4:10:49 PM5/11/09 4:10:49 PM

916 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Select Analyze | Summarize on the menu bar. Click to summarize on CUSTNUM and sub- total on AMOUNT. If CUSTNUM does not appear in the Summarize On window, you need to change the fi eld type to ASCII. Click the Output tab and choose FILE.

Name the new fi le Customer_Balances. Click OK.

The window will show each customer’s balance and the number (count) of unpaid invoices.

The auditor wants to summarize this data to get a total for each customer or, in some cases, will want a total for related entities for an overall credit evaluation. This would have been useful in the Lincoln Savings & Loan case.

P R A C T I C A L P O I N T

63722_app_p906-p932 pp3.indd 91663722_app_p906-p932 pp3.indd 916 5/11/09 4:10:50 PM5/11/09 4:10:50 PM

A C L Tu t o r i a l 917

Import the Husky_Credit Limit 2009 fi le by selecting File | New | Table on the menu bar. Click Next. Double-click on Husky_Credit Limit 2009. Click Next three times. Save the imported table as Husky_Credit. Click Finish | OK. Change CUSTNUM from numeric format to ASCII by select- ing Edit | Table Layout on the menu. Double-click on CUSTNUM and change the format to ASCII (as described at the beginning of this Step 5) so that the fi les can be matched on this fi eld.

Make Customer_Balances the active table by double-clicking on that name in the Overview tab of the Project Navigator (left window). Select Data | Join Tables on the menu bar or click on the Join icon. Choose Husky_Credit as the secondary table. Click CUSTNUM in both the Pri- mary Keys and Secondary Keys windows to match the two tables based on CUSTNUM. Click CUSTNUM and AMOUNT under Primary Fields using the Ctrl key on the keyboard to select more than one fi eld in the window. Click CRLIMIT under Secondary Fields to print. Select Presort Secondary Table. Type the name Balances and Credit Limit in the To box. Click OK.

The joined fi les now show the balances and credit limits for each customer.

63722_app_p906-p932 pp3.indd 91763722_app_p906-p932 pp3.indd 917 5/11/09 4:10:51 PM5/11/09 4:10:51 PM

918 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Create a fi lter to show those customers with balances that exceed their credit limit. Click on the Edit View Filter icon next to the Filter window. Enter the expression AMOUNT > CRLIMIT. Click OK.

The results show that there are fi ve customers with balances that have exceeded their credit limit and one for which there is no credit limit. These should be investigated to determine collectibility.

Print this table by clicking on the Print icon or selecting File | Print on the menu bar. Notice the total of each numeric column is printed.

63722_app_p906-p932 pp3.indd 91863722_app_p906-p932 pp3.indd 918 5/11/09 4:10:53 PM5/11/09 4:10:53 PM

A C L Tu t o r i a l 919

Step 6—Perform a sales cutoff test. Import Husky_Shipping File 2009 by selecting File | New | Table on the menu bar. Click Next. Double-click on Husky_Shipping File 2009. Click Next three times. Save the table with the name Husky_Shipping. Click Save, Finish | OK. Change INVNUM to ASCII format.

Make Husky_Unpaid the active table. Change INVNUM to ASCII format. Join the Husky_ Shipping table with the Husky_Unpaid fi le. Click INVNUM as primary key and secondary key to join on this fi eld. Select the fi elds to show in the new table by selecting all of the fi elds in the Primary Fields window (remember to hold the Ctrl key down) and select the SHIPNUM and DATESHIP fi elds under Secondary Fields. Click to presort the secondary fi le. Name the new table Unpaid with ship number. Click OK.

A new table is created that shows the results of combining these two tables ( adjust column widths to see all columns):

63722_app_p906-p932 pp3.indd 91963722_app_p906-p932 pp3.indd 919 5/11/09 4:10:53 PM5/11/09 4:10:53 PM

920 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

The last shipping number used in 2009 as confi rmed by you was 62050. Create a fi lter with the expression SHIPNUM > 62050 to see if there are any shipments after year end. Click OK.

The results show three shipments after year end. You should follow up on these to determine whether the accounts should be corrected for this apparent cutoff error. Print this table by clicking on the Print icon or selecting File | Print on the menu. Notice the total of each numeric column is printed.

Step 7—Select a PPS (MUS) sample of unpaid invoices and confi rm them. Note: This step will make more sense after you have studied Chapter 8, Tools to Gather Audit Evidence. For now, simply follow the instructions.

Make the Husky_Unpaid table active by clicking on that name in the left window. Select Sam- pling | Calculate Sample Size on the menu. Enter the confi dence level of 95 (95% = TD risk of 5%), the population book value of 4263919.52, materiality of 200000, and expected total errors of 10000. Click the Calculate button. Click OK.

63722_app_p906-p932 pp3.indd 92063722_app_p906-p932 pp3.indd 920 5/11/09 4:10:54 PM5/11/09 4:10:54 PM

A C L Tu t o r i a l 921

Print the next window by clicking the Print icon or selecting File | Print on the menu. The sample size is 70 and the interval is 60,833.33.

Make the Husky_Unpaid table active by clicking on the top tab labeled Husky_Unpaid. Select Sam- pling | Sample Records on the menu. Do not change the defaults of MUS and Fixed Interval. Enter the interval (60833.33), enter the number 12345 in the Start window, and leave the Cutoff window blank. Entering a specifi c number in the Start window will generate the same sample every time. By leaving it blank, ACL will supply a random start. Type Sample in the To window. Click OK.

The next window shows your sample.

Assume you have sent positive confi rmations to each of these customers. The confi rmation results are documented in the Husky_Confi rmations 2009 fi le. Import this fi le by selecting File | New | Table on the menu. Click Next. Double-click on Husky Confi rmations 2009. Click Next three times. Save the fi le using the name Husky Confi rm. Click Save, Finish, and OK. Change INVNUM to ASCII format.

Make the Sample table active by double-clicking on that name in the left window.

63722_app_p906-p932 pp3.indd 92163722_app_p906-p932 pp3.indd 921 5/11/09 4:10:56 PM5/11/09 4:10:56 PM

922 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Join the Husky_Confi rm fi le with the Sample fi le. Select Data | Join Tables on the menu or click the Join icon. Select Husky_Confi rm as the Secondary Table. Click INVNUM in the Pri- mary Keys and Secondary Keys windows. Holding down the Ctrl key on your keyboard, click all of the primary fi elds and the CONFIRMED and COMMENT secondary fi elds to print out. Click to presort the secondary table. Name the new fi le Confi rm Results in the To box. Click OK.

Adjust the column widths so that you can see all of the fi elds on the screen. You can hide the left window by clicking the < button at the top of the window. Print this table for your audit docu- mentation by clicking on the Print icon or selecting File | Print on the menu.

Create a fi lter to show all comments other than “Confi rmed – OK” using the expression Comment <> “Confi rmed – OK”. Note the use of quotation marks around the exact wording and spaces used in the Comment column. Click OK.

63722_app_p906-p932 pp3.indd 92263722_app_p906-p932 pp3.indd 922 5/11/09 4:10:58 PM5/11/09 4:10:58 PM

A C L Tu t o r i a l 923

The fi lter will provide the following items for which the comments were other than “Confi rmed – OK.”

Analyze the comments to determine if there are any apparent misstatements that need to be pro- jected to the population. In this sample, there are four misstatements: INVNUM 168609 that the client claims is an isolated event; INVNUM 169173, for which there is a wrong price or quantity; INVNUM 169394, for which the client returned some merchandise but did not get credit on a timely basis, and INVNUM 169982, for which goods were shipped after year end. The other comments in this illustration are not misstatements but are simply timing diff erences or alternative audit procedures that indicated the amount is correct.

Add a new column that will show the amount of diff erences. Right-click anywhere in the window and click on Add Columns. Click on Expr. Enter the expression AMOUNT – CONFIRMED. In the Save As window, give the new column the title DIFFERENCE. Click OK. Click OK again.

Move the screen to the right so you can see this new column. Move this column next to the CON- FIRMED fi eld by moving the cursor to the new column heading, DIFFERENCE. Hold down the left mouse button and move the column to the left next to the CONFIRMED column and release.

63722_app_p906-p932 pp3.indd 92363722_app_p906-p932 pp3.indd 923 5/11/09 4:10:59 PM5/11/09 4:10:59 PM

924 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Print this fi le by clicking the Print icon or selecting File | Print on the menu.

Select Sampling | Evaluate Error on the menu to evaluate these sample results. Enter the confi - dence level (95) and interval (60833.33). There are four misstatements. For each misstatement (error), enter the book value (AMOUNT) followed by a comma and the Diff erence. Press the Enter key after each error and enter the AMOUNT and Diff erence for each additional error. Click OK.

Print the evaluation. In this illustration, the results indicate a most likely misstatement of $71,170.69 and an upper error limit of $304,871.49. The upper limit is greater than the tolerable misstatement of $200,000. The alternative follow-up procedures available to the auditor are discussed in Chapter 8. Close the project by clicking Close the Open Project icon or choose File, Close Project on the menu.

63722_app_p906-p932 pp3.indd 92463722_app_p906-p932 pp3.indd 924 5/11/09 4:11:00 PM5/11/09 4:11:00 PM

A C L Tu t o r i a l 925

Nonstatistical Sampling Option Rather than using PPS sampling, you could choose a nonstatistical sampling approach and, for example, select all invoices over $50,000 and 20 that are under $50,000. Make Husky Unpaid active by double-clicking on Husky Unpaid in the left window. To determine the book value of the items over $50,000 and those that are less than or equal to $50,000, choose ANALYZE | STRATIFY | stratify and subtotal on AMOUNT. Choose “Free” and enter 0 and 50000. Click OK.

The total of the 17 items over $50,000 (top stratum) is $1,726,938.78. Ignore the negative invoice. Other audit procedures will be applied to negative balances. The book value of the items between $0.00 and $50,000.00 (the lower stratum) is $2,559,640.48. With a calculator, divide the lower stratum by the desired sample size of 20, giving an interval of $127,982.

Make HUSKY UNPAIDS active.

63722_app_p906-p932 pp3.indd 92563722_app_p906-p932 pp3.indd 925 5/11/09 4:11:01 PM5/11/09 4:11:01 PM

926 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Filter out the negative invoice (Amount > 0). Select SAMPLING | SAMPLE RECORDS and leave the defaults of Amount, Fixed Interval, and MUS. Enter the interval of 127982 (do not use a comma), use a random start of 9876, cutoff of 50000, and save as a new fi le called OPTION SAMPLE. By entering 50000 as the cutoff , the resulting sample will include all items with a book value greater than 50,000.

Sorting the sample in descending order shows that the sample includes the 17 top stratum items (>$50,000) and 20 lower stratum items.

63722_app_p906-p932 pp3.indd 92663722_app_p906-p932 pp3.indd 926 5/11/09 4:11:02 PM5/11/09 4:11:02 PM

A C L Tu t o r i a l 927

Proceed as described for PPS sampling above to join the confi rmation fi le with the sample fi le and identify misstatements. Name the joined fi le as Sample with confi rms.

The book value of the sample of the lower stratum will be needed. Stratify the sample using Analyze | Stratify, Stratify on Amount, subtotal Amount, and choose Free, enter 0 and 50000. Click OK.

The book value of the sample items in the lower stratum is $341,660.47 (see above). The popula- tion book value of the lower stratum is $2,559,640.48 (see earlier stratifi cation report).

After fi ltering out the “Confi rmed – OK”, the results are shown next.

63722_app_p906-p932 pp3.indd 92763722_app_p906-p932 pp3.indd 927 5/11/09 4:11:03 PM5/11/09 4:11:03 PM

928 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Since this is not a PPS sample, it must be evaluated using a nonstatistical approach. There are three misstatements, all in the lower stratum. They are $213.81, $722.18, and $14,315.54, for a total of $15,251.53. Project the misstatements in the lower stratum by multiplying the total misstate- ment detected in the lower stratum by the ratio of the book value of the entire lower stratum to the book value of the lower stratum items in the sample. Therefore, the projected misstatement is $114,260.90 [($2,559,640.46/$341,660.47) * $15,251.53].

Tolerable misstatement was set at $200,000. Research studies suggest that if the projected mis- statement in a nonstatistical sample is less than 1/3 of tolerable misstatement, the results indicate there is not a material misstatement in the population. However, in this case, the projected mis- statement is well over half of tolerable misstatement, thus leaving too little room for sampling error. Therefore, the sample tells the auditor that more evidence needs to be obtained.

ACL Case 1—Fraud You are auditing Pell grants provided to students at six state universities. The Pell grant program is a federal fi nancial aid program for college students. The maximum grant a student can receive dur- ing a school year is $3,125 with a maximum of $1,041.67 per semester and summer session. The amount of a grant depends on fi nancial need (need) and the number of credits taken (status). Stu- dents cannot receive a grant at two diff erent schools during the same school term. Download the fi le labeled pella from the web site www.cengage.com/accounting/rittenberg under “Student- Companion Site.” The fi le contains the following information:

SSN Social Security Number Last Student’s last name First Student’s fi rst name Middle Student’s middle name or initial School School—coded 1 to 6 Term Coded 1 to 3: 1—Fall Semester 2—Spring Semester 3—Summer Semester Need* Financial need—coded 1 to 5: 1—100% of allowable grant 2—75% 3—50% 4—25% 5—0% Status* Credits taken—coded 1 to 4: 1—12 or more credits: 100% 2—9 to 11 credits: 75% 3—6 to 8 credits: 50% 4—3 to 5 credits: 25% Amount Amount of grant for the term • Computation of grant: $3,125/3 * Need * Status For a full-time student with maximum need: $3,125 / 3 * 100% * 100% = $1,041.67 For a student with a code 3 need taking 9 credits: $3,125 / 3 * 50% * 75% = $390.63

* Hint: To convert the NEED codes to the proper decimal value, use the expression (.75 * (NEED − 1) − NEED + 2). The same conversion can be used for STATUS codes.

Required 1. Develop an audit program to identify potential fraud using ACL.

2. Use ACL to perform the steps in your audit program. Turn in the following:

a. Your audit program referenced to the ACL printouts supporting each audit step.

b. A report on your fi ndings including additional steps you would take to determine if fraud actually occurred.

c. Appropriate ACL printouts properly indexed with comments written on the printouts to explain the printout and its implications. Do not print out the entire grant fi le. Extract only the items of signifi cance.

63722_app_p906-p932 pp3.indd 92863722_app_p906-p932 pp3.indd 928 5/11/09 4:11:04 PM5/11/09 4:11:04 PM

A C L C a s e 2 — B e n f o r d ’s L a w C a s e 929

ACL Case 2—Benford’s Law Case Dr. Frank Benford, a physicist at General Electric in the 1920s, found that the fi rst and second dig- its of many populations of numbers occur with a fairly consistent frequency. This has been found true, for example, of census numbers and certain accounting populations, such as accounts payable. Benford developed a model that predicted the frequency of each digit occurring in a particular location depending on the length of a number. For example, he found that the digit #1 occurs as the fi rst digit in about 30% of all populations, while the digit #2 occurs in about 17.5% of all populations. On the other hand, the digit #9 occurs as the fi rst digit only about 4.5% of the time. Thus, digits such as 990 do not occur as often as digits such as 124. Many other researchers have empirically verifi ed the Benford predictions.

Auditors have found that as individuals commit fraud or make up fraudulent transactions, their intuition in developing numbers for the fake documents often does not follow Benford’s Law. Therefore, auditors have come to use Benford’s Law to identify a wide variety of unusual transac- tions, including fraud, double payments, and other fi ctitious accounts. Audit software, such as ACL, comes with modules that allow auditors to apply Benford’s Law to search for unusual patterns in populations by identifying numbering patterns that diff er signifi cantly from that predicted by Benford’s Law.

Using ACL to Perform Benford Analysis Benford Analysis can be found by clicking Analysis | Perform Benford Analysis. You will be instructed to select a fi eld on which to perform the analysis. You can then make a choice to perform an analysis on the leading digit only, or you can perform an analysis on the two leading digits.

You can choose the type of output you want for the analysis by clicking the Output tab at the top of the window. The GRAPH option will provide a bar graph with the predicted and actual frequencies of each leading digit or the two leading digits. The SCREEN and FILE options will create a report containing the following:

The actual count of the leading digit (or two leading digits)•

The expected count of the digit(s)•

A Zstat statistic•

The Zstat statistic is derived from the probability of the deviation between the actual count and the expected count of the digit. The signifi cance of the Zstat statistic is determined by comparing it with the Z statistic used to describe normal distributions in most statistical textbooks. For ex- ample, there is a 95% chance that most samples from a distribution would fall within 1.96 standard deviations from the mean, thus creating a Zstat of 1.96 for a 5% tail end of a distribution and 2.58 for a 1% tail. Any Zstat statistic greater than 2.58 would indicate a very rare occurrence.

The Case To illustrate the power of Benford’s Law in an auditing context, assume that you are the internal auditor for Knot Manufacturing Company and are auditing the travel, entertainment, and meal reimbursements for 2009. Company policy requires receipts and management’s approval of all re- imbursements over $5,000. Download the fi le Expense Reimbursements from www.cengage.com/ accounting/rittenberg under “Student - Companion Site.”

Required Analyze expense reimbursements using Benford’s Law and ACL. Import the Expense Reimbursements fi le, which contains the reimbursement document numbers, employee numbers, and the amount of each reimbursement. Click Analyze | Perform Benford Analysis for the AMOUNT of the reimbursement.

1. Analyze on the leading digit. Choose Output to Screen and again to Graph. Print both outputs.

2. Analyze on the leading two digits. Choose Output to Screen and again to Graph. Print both outputs.

3. Summarize the number and amount of reimbursements between $4,900 and $5,000 by employee number. Print the results.

4. Analyze the results and provide possible explanations for the results. Identify reimbursements for certain employees that may need further investigation.

63722_app_p906-p932 pp3.indd 92963722_app_p906-p932 pp3.indd 929 5/11/09 4:11:04 PM5/11/09 4:11:04 PM

930 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Introduction to ACL Cases 3 and 4— Accounts Receivable and Inventory The following accounts receivable and inventory cases relate to the audit of NSG Manufactur- ing Company (NSG) for the year ended 12/31/2009. NSG manufactures and sells slippers, casual and athletic shoes, and skis for children, women, and men. It has two manufacturing plants, one in Knoxville, Tennessee (USA) and the other in China (INT’L). Following is information from the prior-year audit and the client’s records for the current year.

12/31/2009 12/31/2008

Sales $321,557,486 $298,514, 657

Sales Returns 6,004,359 4,658,119

Cost of Goods Sold 168,153,229 156,103,359

Net Income 10,514,967 9,761,464

Accounts Receivable 21,567,378 19,841,978

Inventory 14,959,739 13,567,115

Total Assets $144,817,500 132,508,016

ACL Case 3—Accounts Receivable Sales are shipped FOB shipping point with credit terms n/45. You have verifi ed that the last ship- ping number used in 2009 was 261,336 and that numbers were used in numerical order. Tolerable materiality is set at $500,000 for accounts receivable. You have asked for and received the following fi les related to accounts receivable, along with a fi le containing confi rmation results.

File Name and Description Data Elements

NSG Unpaid Customer No.

(All unpaid invoices at 12/31/2009) Invoice Number

Invoice Date

Amount

Ship Date

NSG Shipping Invoice Number

(Shipping number for each unpaid invoice) Ship No.

NSG Credit Customer No.

(Credit limit for each customer.) Credit Limit

NSG Confirmation Invoice Number

(Results of the confirmation process. Confirmed (Amount confirmed)

There are four different confirmation Comment (Explanation of the results

files—A, B, C, and D.) of the confirmation)

Required 1. Use ACL to perform, or help perform, the procedures in the following audit program.

2. Develop and turn in audit documents in the following order (properly index each document for cross- referencing purposes):

a. The audit program with your initials and references fi lled in for each procedure.

b. A memo summarizing the results of the procedures (including the dollar impact of actual or potential misstatements) and identifying any additional procedures the results indicate should be performed. Be sure to provide cross-references to supporting printout(s) and/or other documents.

c. Appropriate printouts that contain handwritten narrative explanations of each printout and its signifi cance. Be sure to develop dollar information appropriate to each procedure to help identify the signifi cance of any fi ndings. Do not print out the entire Accounts Receivable or any related fi le. Extract only the items of signifi cance.

63722_app_p906-p932 pp3.indd 93063722_app_p906-p932 pp3.indd 930 5/11/09 4:11:04 PM5/11/09 4:11:04 PM

A C L C a s e 4 — I n v e n t o r y 931

Audit Program Procedure Done By W/P Ref.

1. Foot the file and agree to the general ledger. ______ ______

2. Check for duplicate invoice numbers in the unpaid

invoices and shipping files. ______ ______

3. Age the unpaid invoices and identify any unpaid

invoices older than 45 days. ______ ______

4. Identify any customer balances that exceed the

credit limit or for which there is no credit limit.

(Customer number must be in ASCII format) ______ ______

5. Perform a sales cutoff test. ______ ______

6. Determine if any customers have a negative

balance and consider the need to reclassify

those as liabilities. ______ ______

7. Select a PPS (MUS) sample of unpaid invoices.

Use tolerable misstatement (materiality) of $500,000,

expected misstatement of $75,000, and a TD risk

of 10%. There are four different files of confirmation

results: A, B, C, and D. Your instructor will assign

files to the class members. Combine the confirmation

file with the sample file to determine the results of the

confirmation process. Determine any misstatements

and project the misstatements to the population. ______ ______

8. Summarize the results of the above procedures and

identify additional procedures those results indicate

should be performed. ______ ______

ACL Case 4—Inventory You have asked for and received the inventory fi le NSG Inventory as of 12/31/2009. This fi le con- tains the following information:

LINE Two codes—USA and INT’L Product line codes: C—Children’s shoes W—Women’s products M—Men’s products

For women and men’s products, the second (and third) letter(s) specifi es the product:

S—Slippers C—Casual shoes A—Athletic shoes SP—Skis Premium SS—Skis Standard SNUMB Stock number LASTSALE Date of last sale NUMSOLD Number sold year-to-date RETURNED Number returned year-to-date DEFECTIVE Number returned that were defective INVQTY Quantity on hand UNITCOST Unit cost EXTCOST Unit cost times quantity on hand SELPRICE Current selling price

Salespersons receive a 10% commission based on selling price.

63722_app_p906-p932 pp3.indd 93163722_app_p906-p932 pp3.indd 931 5/11/09 4:11:04 PM5/11/09 4:11:04 PM

932 ACL APPENDIX A C L B a s i c s , Tu t o r i a l , a n d C a s e s

Required 1. Develop an audit program that focuses on the inventory data fi le provided by the client. The program steps

must be able to be performed using ACL. Other program steps, such as observation, should not be a part of this program. Your project grade will be based on your creativity in designing useful audit procedures. Make sure your program includes the audit objective to be achieved by each audit procedure.

2. Use ACL to perform, or help perform, the procedures in your audit program.

3. Develop and turn in audit documents in the following order (properly index each working paper for cross- referencing purposes):

a. The audit program with your initials and document references fi lled in for each procedure.

b. A memo summarizing the results of the procedures and identifying any additional procedures the results indicate should be performed. Be sure to provide cross-references to supporting printout(s) and/or other documents.

c. Appropriate printouts that contain handwritten narrative explanations of each printout and its signifi cance. Be sure to develop dollar information appropriate to each procedure to help identify the signifi cance of any fi ndings. Print only the items of signifi cance.

63722_app_p906-p932 pp3.indd 93263722_app_p906-p932 pp3.indd 932 5/11/09 4:11:04 PM5/11/09 4:11:04 PM