A state of the art audit software package

By Paul D. Warner

In Brief

Toward a More Effective Audit

The use of computer based auditing packages has evolved over the years. ACL has become the audit software program preferred by a large number of accounting firms.

The author explains how data must be extracted and formatted to be used by ACL, and covers the various windows that are used in performing the work. Also discussed are ACL capabilities with respect to--

* analyzing data

* defining an "if" statement

* the Year 2000 Problem

* commands

Examples of how ACL can be used are provided in the text as well as a sidebar. The program was tested and no problems encountered.

The world of computer-based auditing packages started over a generation ago when Deloitte Haskins & Sells released Auditape. Then, each of the other Big Eight developed its own version. These audit packages reached their pinnacle in programs such as Sterling Software's DYL280 II and DYLAudit. These mainframe programs were given the name generalized audit software (GAS), because they provided auditors with computerized procedures for testing computer-based data files. Prior to their introduction, auditors had to obtain or write customized programs to access computer-based files or perform manual testing on client-produced printouts.

One of the first microcomputer-based audit programs, IDEA (Interactive Data Extraction and Analysis), was developed for the Canadian government and made available to the profession through the Canadian Institute of Chartered Accountants and the AICPA. It exists today as IDEA 5. The subject of this review, ACL (Audit Command Language), also comes to us from Canada. Now in its eleventh year, it has become the audit software program of choice for many accounting firms.

During the early years of microcomputer-based auditing programs, storage capacity severely limited their application. Today, with availability of Gigabyte drives (the computer used in this evaluation has an 11+ Gigabyte drive), this is no longer a problem. Data can be downloaded to the microcomputer, or ACL can be installed on a server. There is also an IBM mainframe version--ACL for MVS.

Early auditing programs, such as Auditape, required users to learn specialized programming languages and, in some cases, general computer programming languages such as COBOL or RPG. ACL's interface is user friendly and does not require knowledge of a programming language. It does require analysis of data file structures, which can be an overwhelming task for all but the most experienced user. Computer literacy isn't enough, and training is essential.

Working With Data

Obtaining Data. The microcomputer version of ACL requires that data be in the form of a flat sequential file. Database files, will generally have to be converted into a flat file. However, with the proper drivers installed, ACL has the capability to read relational databases that utilize open database connectivity (ODBC, a standardized application programming interface). Some examples of ODBC-compliant databases are dBase, Excel, FoxPro, Access, Oracle, Paradox, and text files. Once an ODBC compliant database is opened, you can select the fields and records you need.

ACL has an interesting alternative. It can analyze data in the form of a report spool file (print image file). Text (ASCII) data should be avoided since it slows ACL down.

Smaller data files from personal computers can be obtained via floppy or Zip disks. Larger PC files and files from minicomputers and mainframes can be downloaded or transferred to magnetic tape or cartridges. ACL can read most types of tapes and cartridges.

Data files downloaded or transferred from a tape should preferably be sequential, fixed-length, flat files containing a single record type. Although ACL can process multiple record type files with variable length records, my experience says that they require expertise in data file organization for successful application. Sequential, fixed-length, flat files with one record type are the format of choice. Flat, sequential files contain rows of consecutive data much like the rows of data in a spreadsheet and are therefore easy to visualize and manipulate.

Lastly, remember to request a copy (not the original file) of the data and obtain the file layout. Although ACL never uses the original data file, it is still always safer to use a copy. If something should go wrong, you will not end up with an irate MIS manager.

Input File and Data Definition. ACL must be provided with information on the file source (disk or tape), file type (fixed, variable, etc.), character type (ASCII or EBCDIC), record length, etc. Once this information has been provided, it is then necessary to define the record structure. Each field is defined by providing it with a name, its starting position in the record, length, number of decimal places (if numeric), and data type (e.g., ASCII, numeric, date). ACL allows you to define new fields that do not exist in the file. For example, you may want to compute the total cost of an inventory item by multiplying unit cost by quantity. The result of this computation can be saved in a newly defined field for later use.

Working With ACL

There are four fundamental ACL windows in which most of your work is performed: view, overview, last result, and command log.

The view window consists of columns that can contain data from a given field or a valid expression (a computational field). In essence, the view window displays data in a tabular format resembling a spreadsheet. A view can be copied, displayed, or changed. The user can arrange the format of the data, apply filters (used to search for records that meet specifically defined criteria), add expressions (i.e., generate a field containing the extended value of other fields), and issue commands.

The overview window provides a view of the input file definitions--the views--and indexes much the same way Windows Explorer shows the contents of each drive. Clicking on any of the items in the overview opens that item.

The last result window contains the results from the last command issued. Running a command automatically opens the last result window. There is a print option with this window, thereby providing a hard-copy trail.

The command log is the most complete documentation tool in ACL. It automatically opens a log file as soon as a document is opened. The log file captures every activity, command, and message. ACL also displays the results following each command.

ACL Capabilities

Analyzing Data. The first step after downloading the data is determining if it is complete. The count command can be used to determine the number of records in the file. The count command is executed by selecting it from the analyze section in the menu bar. The result appears in the last result window. Numeric field totals are available by clicking on the total icon or selecting total from the analyze drop down menu. In either case, ACL displays all of the numeric fields in the view. The user selects the ones to be totaled and the results appear in the last result window. Other commands operate in a similar fashion.

Defining an "If" Statement. An "if" statement is an application of an expression. (An expression is used to perform calculations, specify conditions for a test, or to create values that do not exist in the data.) If the user does not want to process all the records in a file, an "if" statement can be designed to focus on a portion of the file (e.g., all inventory items at a specific warehouse) or a specific type of transaction (e.g., receivables with credit balances).

For example, the total command referred to previously could be made into an "if" statement by clicking on an "if" button. The expression builder is displayed in the view window and shows all of the available fields and variables, a series of Boolean operators, and an extensive list of functions similar to those in Excel. The expression builder can be used to develop standardized audit routines that can be used in various applications and audits.

Commands. ACL uses a command language structure to eliminate the need to write custom programs to manipulate data. The command language, which is accessed either via drop-down menus or icons on the customizable menu bar, provides many capabilities. The following is a partial list of available commands:

* Age--produces aged summaries of data using the standard 30, 60, 90 structure or any other periods desired. The output may be in text or graphic format.

* Calculate--computes and displays the value of an expression.

* Classify--counts the number of records falling into each unique class and accumulates totals. Can be used to sort and summarize transactions (e.g., to generate a trial balance from unsorted data).

* Count--counts the number of records in the current input file, or only those that meet a specified criteria (e.g., a specific warehouse). Can be used to verify that the data is complete.

* Duplicates--determines if key fields in the current file are in sequential order or detects and reports gaps, duplicates, or missing numbers in the sequence. A very useful command for determining if the completeness assertion has been met. Great for testing an insurance company's data files for missing policies.

* Evaluate--determines the effect of errors detected in sampling (attribute and PPS)

* Export--creates files for use by other programs. For example, data exported can be used by a word processor to generate confirmation letters by means of the mail/merge function.

* Histogram--generates a 3-D graphical representation of stratified data

* List--displays the contents of a file in columnar form

* Profile--provides values for numeric fields for total, absolute, minimum, and maximum

* Random--generates random numbers for use by programs other than ACL

* Report--creates formatted reports

* Sampling--selects samples and makes error evaluations

* Statistics--calculates various descriptive statistics on specified numeric fields

* Stratify--separates a numeric field or expression into a specified number of equal intervals and accumulates data on designated fields

* Verify--ensures that data in a file conform to the input file definitions and reports any errors encountered. Usually one of the first commands used.

The Year 2000 Problem. ACL includes the start of century preference to set the start of the century for applications that use two digits for the year. The program accepts any two digits between 00 and 78 as the start. For example, if you choose 60 as the start of century, then ACL will treat the years 60 to 99 as 1960 to 1999 and years 00 to 59 as 2000 to 2059.

Applications

Here are some examples of tasks that can be accomplished using ACL. Ultimately, the only limit is the user's imagination. (See sidebar for additional examples.)

Stock and Inventory Control

* Selecting items from perpetual stock for test reconciliation

* Reconciling selected physical counts to computed amounts

* Comparing the value of physical counts to general ledger totals

* Reporting on stock and high value balances using any selection

* Testing clerical accuracy of totals and extensions

* Summarizing and stratifying turnover by stock item in any order

* Testing for duplicate parts, item numbers, prices, or descriptions

* Segregating variances from standard pricing in dollar amount order

* Identifying obsolete inventory by sorted turnover analysis

* Identifying items with yearly volume under on-hand quantity

Fraud Detection. ACL can be used in fraud detection due to its ability to rapidly analyze large amounts of data to identify suspicious transactions. For example, the forensic accountant can use ACL to develop a fraud profile early in the investigation to focus on higher risk areas. A fraud profile summarizes the data characteristics that are expected in a given type of fraudulent transaction. Some examples of such applications follow:

1) Identification of potential phantom vendors by--

* matching names between employee and paid vendor files

* searching for employees and paid vendors with same address or phone number

* finding unusual vendor addresses (e.g., mail drops)

2) Identification of potential kickback or conflict of interest schemes by searching for--

* vendor prices higher than
standard

* vendor price increases greater than acceptable percentages

* vendors with an unusually high rate of returns, rejects, and credits

Testing ACL

ACL comes with a complete set of documentation including a user's guide, command reference, and workbook with an extensive number of lessons. No problems were encountered during the installation of ACL for Windows. ACL comes on four high-density floppies. The installation took a relatively short period of time, and slightly more than 4MB of disk space was needed.

Programs typically provide users with sample files that can be used when learning how the application works (some developers only supply sample files to reviewers). I began testing ACL by using the sample data files provided to all purchasers of ACL. I also tested ACL on data files downloaded to my microcomputer. I did not test ACL on files on a server because of licensing restrictions; ACL requires an access key to be attached to the parallel port in order to operate. Testing files did not cause any problems after the downloading and file defining process.

I did not encounter any compatibility problems while running ACL. Office 97 ran simultaneously, along with AS/2 and DayTimers. It is a powerful program that can turn your computer into a super audit tool. *