ACL Data Analysis Project

ACL is the most popular data analysis software according to surveys of external and internal auditors. The objective of this project is to give you a hands-on introduction to ACL software.

The ACL program in located on the COBAE network. YOU NEED TO COPY THE DATA FILES BELOW TO YOUR LOCAL MEDIA (e.g., floppy disk or USB drive). Click on each link below, select Save on the dialog box that pops up, and indicate the drive where you want the file saved.

Sample Project.ACL -- the ACL project that is included with the ACL software that is used with the ACL Introductory Tutorial

Metaphor Corporation files (w/all other ACL tutorial files) -- the ACL project that is included with the ACL software that is used with ACL Practice Guide

In addition to the data files, do need to copy the following instruction files:

ACL Introductory Tutorial -- this 15-page tutorial provides an overview of ACL functionality.

ACL_in_Practice_Guide_PDF -- this 92-page tutorial is published by ACL

Introduction to ACL Data Analysis Steps

Collecting and analyzing client data is a critical part of a financial audit. In general, clients will not give auditors direct access to the their computer systems. The fear is that the auditors might accidentally corrupt the data or will run queries that significantly reduce the speed of transaction processing systems--maybe, even crash these transaction processing systems. As such, the auditors request that the client personnel prepare a copy of the requested data for them. The copy will not be a complete copy of everything in the client's accounting system. Instead, the auditors will request a sample of the accounting records. Even if the auditor requests all records (e.g., the journal entries for the year being audited), the auditor typically will not request all the fields or data elements associated with each record.

The client could give the auditors data in a wide variety of forms, such as, simple ASCII flat files, database tables, Excel spreadsheets, etc. The first task for the auditor is to import/convert these files into an ACL "project." The task is to validate the tables in the project. For example, if the client provided the accounts receivable file, the AR balance of the ACL project should equal the same number that the client included on its trial balance. Frequently, before some of these validations can be performs, additional calculated columns have to added to the table. The client's relational databases do not store calculated numbers, so to determine outstanding AR balances, the auditor may have to add a new column in the ACL project that includes the product of unit price * number of units sold.

After the auditor validates the project and has created additional columns, the auditor is then ready to analyze the data. The exact analysis that is performed varies by client and audit objectives. The following table from ACL shows some of the tests that might be performed.

Assignment

1. Do the exercises on the ACL Introductory Tutorial to familiarize yourself with ACL
2. Using ACL_in_Practice_Guide_PDF as your guide, conduct some analysis of selected tables in Sample Project.ACL that goes beyond what was performed in the ACL Introductory Tutorial. Use the chart above as a guide. [Note that the ACL tutorial is based on the Metaphor Corporation files and you are being asked to perform parallel analysis of the Sample Project.ACL project.] Specifically, do the following:
   1. The Sample Project.ACL includes tables from 4 accounting applications: accounts payable, accounts receivable, inventory, and payroll. Select one of those applications, select and run 5 ACL tests on that application that seem appropriate for that application, and summarize your findings in a memo to the audit manager. Your memo should include a discussion of why you selected those specific tests, the results of the test, what the results might mean in terms of potential problems/risks, and what you suggest doing next.
   2. Extra credit: repeat the above for one of the remaining applications.