ACL for Windows ©CAATS 2007

ACL Command – Explanation

FILTER – Show me only records where…… a condition is true

COUNT – How many ? – counts number of records
TOTAL – How much ? – totals one or more numeric fields
STATISTICS – Minimum, Maximum, Average, Standard Deviation, etc.

SEQUENCE – Is file sorted (sequenced) on…
DUPLICATE – Duplicate Records – one or more criteria (key fields)
GAPS – Are there any missing records?

CLASSIFY – Total of one or more numeric fields by any Character field
STRATIFY – Ranges of a Numeric field (0–100, 101–200, 201–300…)
AGE – Number of Days past a Cutoff Date
CROSS TAB – Creates rows and columns for specified fields, totaling a numeric field

SUMMARIZE – Totals by one or more Character fields – can list other fields

SORT – Re-order the data – creates a new data file (faster processing)
INDEX – Re-orders the data – using an index (less space required)

EXTRACT – Create a new file containing only some of the records and/or fields
EXTRACT – APPEND – Add records to the bottom of another file – must have same table layout
MERGE – Combine two sorted files into one sorted file – same table layout

EXPORT – Send the data to another software package (Excel, Word, etc.)

JOIN – Combine two files to create a third file – Matched, Unmatched, Primary, Secondary, Primary Secondary. The Secondary file must be sorted
RELATE – Logically combine up to 18 files – Children must be indexed
BENFORD – Compare actual frequency of first ‘x’ digits distribution to Benford frequency
VERIFY – Checks the integrity of the data – identifies invalid numeric, date or character fields
SIZE – Determines sample size – based on confidence level etc.
SAMPLE – Select sample records – creates new file
EVALUATE – Projects the sample results to the entire population
SCRIPTS – To save commands in a file so you can re-execute them later; to ensure consistency; to speed up analysis
LOG – Automatically records all ACL Commands and the results



Generic Approach to the Application of Data Analysis to Auditing ©CAATS 2007

The first step is to ensure that you understand the goals and objectives of the audit. Then:

  1. Meet with the client and the programmer for the client applications. Identify all available databases both: Internal to the client organization – main application systems; and External to the client organization – including benchmarking and standards

  2. List fields in all available databases and the standard reports that are available.

  3. Based upon the audit objectives, identify the data sources, the key fields or data elements required by the audit team.

  4. Request the required data – trying to ensure that unnecessary fields are excluded for the request. Prepare a formal request for the required data, specifying:
    • the data source(s) and key fields,
    • the timing of the data (for example: as of Sept 31 2002),
    • the data transfer format (floppy, LAN, Internet, CD ROM, tape, etc.),
    • the data format (DBF, Delimited, flat file, ODBC, ASCII print file, etc.),
    • control totals (number of records, key numeric field totals),
    • record layout (field name, start position, length, type, description),
    • a print of the first 100 records

  5. Create or Build the ACL Input File Definition – automatically created by ACL for DBF, ODBC, and Delimited files.

  6. Verify the data integrity:
    – Use Verify Command – to check data integrity,
    – Check ACL totals against control totals,
    – check the timing of the data to ensure proper file has been sent,
    – compare ACL view with Print Out of first 100 records
    – authorization – obtain client agreement on data (source, timing, integrity, etc.).

  7. Understand the Data – use ACL commands COUNT, STATISTICSC, STRATIFY, CLASSIFY, etc to develop an overview of the data

  8. For each objective
    – formulate hypotheses about field and record relationships
    – Use ACL to perform analytical tests for each hypothesis
    – Run tests – the output is your “hit list” – possible problem records
    – Evaluate initial results and refine the tests
    – Re-run and refine test to produce shorter, more meaningful results (repeat steps 5–7 as needed)
    – Evaluate the results – using record analysis, interview, or other techniques – to examine every item on the refined results.
    – Form an audit opinion on every item in your results. For each you should be say that the record is OK – there is a valid explanation; or that it is a probable improper transaction and more review is needed

  9. Quality Assurance and Documentation – exceptions to source; confirm analysis and nature of exceptions; and identify reasons for the exceptions

    10. ∧ Return to top ∧