ACL 9

Tutorial 3

COMMANDS:

ANALYZE -> CLASSIFY

ANALYZE -> SUMMARIZE

DATA -> SORT

DATA -> REPORT

ANALYZE -> PROFILE

ANALYZE -> STRATIFY

The tutorials in this folder are supplemental exercises that relate to ACL 9 topics covered in chapters 8, 9, 10, and 12 in the Hall, IT Auditing, 3^rd edition textbook. The tutorials presume that the student has installed ACL from the CD accompanying the text and has worked through the ACLStart.pdf document in the ACL Manual folded. The tutorials are not a substitute for this basic ACL knowledge.

These tutorials use data tables stored in the Sample Project.acl that is automatically installed with ACL. Open the sample project and click on the appropriate table to perform the tutorial. The name of the table is indicated in bold type prior to each section in the tutorial (e.g. AP TRANS).

AP_TRANS

1. CLASSIFY the data in AP_Trans by Vendor_No and accumulate on Invoice_Amount.

How would an auditor use this data in an audit?

2. The SUMMARIZE command generates a record count and accumulates totals of character or date key fields. It is most effective for large input files, because it can report on a limitless number of unique key fields. Summarize is similar to Classify except: (1) it requires a sorted file, (2) you can include additional character or numeric fields that are not accumulated in the summary file, and (3) it can create a comprehensive report.

Summarize AP TRANS on Vendor_No and subtotal on all numeric fields.

Check PRESORT, and save as SUMVEN.

View the types of the fields in SUMVEN. What type are they?

b. Sort the file on Invoice_Amount and save it to a new file called SUMVENsort. Using the FILTER command eliminate vendors with total invoices below a materiality threashold of $2000.

c. EXPORT the resulting file as an Excel spreadsheet; name it AuditSumVen.

 d. Open the AuditSumVen Speadsheet 

REPORT the results in the LOG using the REPORT function. Give the report a heading, and print it to screen (you will use the LOG to retrieve the REPORT when needed).

3. AP_TRANS

STRATIFYING DATA

Before stratifying data, you need to obtain the maximum and minimum limits of the data using the PROFILE command.

(a) Run the PROFILE command on the Invoice_Amount in preparation of running the STRATIFY command.

Analyze -> Statistical ->

Profile -> Profile Fields -> click Invoice_Amount -> OK ->

b. Run the STRATIFY command on the Invoice_Amount. Accumulate on Quantity and Unit_Cost.

Analyze -> Stratify -> [notice that the minimum and maximum amounts were picked up from the command log and Profile data] Stratify On -> double click Invoice_Amount -> OK -> Subtotal Fields -> click Quantity, Click Unit_Cost, then -> to move them to the Selected Fields -> OK ->

Re-run STRATIFY but eliminate the outlier. [HINT: Run STATISTICS first to find value of 2^nd highest invoice amount]

As of: 03/14/2010 16:24:11

Produced with ACL by: Lehigh University

Command: STRATIFY ON Invoice_Amount MAXIMUM 20387 INTERVALS 10 TO SCREEN Table: Ap_Trans

How would you use the 2^nd highest invoice?

e. View the data as a bar chart.

4. INVENTORY

STRATIFYING DATA

Run the PROFILE command on all of the amount fields in preparation of running the STRATIFY command.

Run the STRATIFY command on VALUE. Subtotal on VALUE.

Which of the numbers would be of interest to an auditor?