IT audit training for AUDIT TOOLS. Session 5. Computer Assisted Audit Techniques S5/ 1. October 1997

training

for

AUDIT TOOLS

training

for

Session Objectives

F

Highlight the importance of proving file

integrity

F

Cover the stages of planning a CAAT

F

Introduce available file downloading tools

F

Introduce popular file interrogation tools

F

Introduce SQL

training

for

Ensuring Data Integrity

F

Need to prove the completeness and correctness of a

file prior to interrogation/sampling.

F

The CAATS specialist must :

V

Prove the file is complete

V

Prove the data carries no corruptions

V

Check the period to which the data refers

F

The auditor can ensure completeness via

:-V

Totalling

V

Reconciliation to General Ledger Hard Copy or

V

Proving the file nets off to Zero

training

for

Define the objectives of this CAAT?

Identify the most appropriate file

Agree the best file format

Develop downloading and

interrogation procedures

Develop procedures to ensure data integrity

CAAT Planning

training

for

CAATs Software and Audit Tools

F

File downloading tools

F

File interrogation/audit tools

V

Sampling, Totalling, Analysis

V

Databases, Spreadsheets

F

Report writing tools

F

System/Security review tools

F

Planning tools

F

Programming tools for specific tasks

training

for

File Downloading Tools

F

Tape Management tools

V

Depot - Overland Data downloading utility

V

Tarsus - Memory Technology PLC File

downloading utility

V

Looktape (Bespoke Produced by UKNAO)

V

Tapeutil - Flagstaff Engineering Tape

scanning/Downloading

V

Fdump - Overland data file Display utility

F

Text Editors

V

Type

V

Brief

V

Hexed

training

for

20 20 31 30 39 34 32 20 20 20 20 20 20 20 20 20

₁₀₉₄₂

20 20 4d 45 44 49 43 41 4c 20 45 58 50 45 4e 53 MEDICAL EXPENSES

45 53 20 20 20 20 20 20 20 20 20 20 20 20 20 20

20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20

20 20 20 20 20 20 20 20 32 37 32 32 39 2e 32 31 27229.21

This is a Hexadecimal view of an ASCII file using the FDUMP

facility.

training

for

You have scanned a tape using Looktape and have the

following information.

File 0

3 blocks read

Block size - maximum 80 bytes minimum 80 bytes

***********

File 1

100 blocks read

Block size - maximum 9100 bytes minimum 182 bytes

***********

File 2

2 blocks read

Block size - maximum 80 bytes minimum 80 bytes

The layout of a tape

training

for

F

You can now calculate the number of

records in the file.

Records = 91 bytes = 100 records per block

99 X 100 = 9900

182 / 91 = 2

Total records in File 1(data blocks) = 9902

training

for

A Hex display is split into 2 parts

Hex Display

Text Display

41 42 43 44 45 45 31 32 33 ABCDEF123 (ASCII )

c1 c2 c3 c4 c5 c6 f1 f2 f3 ABCDEF123 (EBCDIC)

HEX TEXT

A common Hex display to show the end of a record is

0D 0A (carriage return line feed decimal 13 10)

training

for

Idea5

Sampling

Totalling

Indexing

Stratification

File Compare

Multi Format

Export

Multi Format

Import

Magnetic

tape Access

Lotus,Dbase,Excel,Word,

Csv,ASCII,EBCDIC

Binary,Packed,Zoned

training

for

Idea for

Windows

Sampling

Totalling

Indexing

Stratification

File Compare

Multi Format

Export

Multi Format

Import

ODBMS/OLE

Lotus,Dbase,Excel,Word,

Csv,ASCII,EBCDIC

Binary,Packed,Zoned

File Interrogation

IDEA for windows

training

training

for

ACL

Sampling

Totalling

Indexing

Stratification

File Compare

Multi Format

Export

Multi Format

Import

Magnetic

tape Access

Lotus,Dbase,Excel,Word,

Csv,ASCII,EBCDIC

Binary,Packed,Zoned

training

for

APPLAUD

Sampling

Totalling

Indexing

Stratification

File Compare

Multi Format

Export

Multi Format

Import

Dbase,ASCII,EBCDIC

Binary,Packed,Zoned

training

for

Multi Format

Export

Multi Format

Import

Magnetic

tape Access

Lotus,Dbase,Excel,Word,

Csv,ASCII,EBCDIC

Binary,Packed,Zoned

PROSPECTOR

Exception

Reporting

Windows

Interface

Data/Field Type

Recognition

training

for

SAGE

STERLING

Sampling

Totalling

Indexing

Journal

Amendments

Accounts

Production

Multi Format

Export

Multi Format

Import

Lotus,Dbase,Excel,Word,

Csv

training

for

CA-PANAUDIT

PLUS

Sampling

Totalling

Indexing

File

Comparison

Stratification

Mainframe or PC

Functionality

ASCII/EBCDIC

File Interrogation

CA-Panaudit Plus

training

for

Fdump

Tapeutl

Depot

Tarsus

Idea5

_ACL

File conversion/downloading

training

for

If the audited body are unable to provide data for interrogation in

a format that can be easily read by the auditors software, a report

file can be produced which can be manipulated in several ways

:-Monarch

AutoImport

ACL

Idea

Link and Load

Windows Packages

Bespoke Software

training

for

All of the software packages/tools used for file manipulation are

unique stand alone packages. There is one audit tool that is

incorporated into Windows Packages, can be stand alone in either

DOS or Windows.

training

for

What is SQL?

What can it do ?

F

SQL - Structured Query Language

F

Developed to extract data from relational

databases

F

SQL interface in most PC based database

management systems

V

Microsoft Query

V

Microsoft ACCESS

F

SQL can

V

Calculate (Total, Count, Average...)

V

Query (From, Where, Having, Between, Like)

V

Manipulate files (Order, Index, Delete, Create,)

training

for

The user is able to select the fields required in the query, add

calculations and or mathematical functions.

The Standard database Query

Builder in Access

training

for

The SQL code is automatically generated by the ACCESS query

builder.

The standard Database Query

Builder in Access