An Introduction to the Use of Data Analysis in Auditing

1
An Introduction to the Use of Data Analysis in
Auditing

• Presented by Alan Swiatlowski, CISA

2
Introduction

• What are some of the advantages of using data
  analysis in audits?
• In this day and age, almost every business
  function includes a computer system to support
  it.
• Data Analysis can eliminate manual,
  hit-and-miss sampling of information.
• In many cases, 100 verification of the systems
  data can be achieved.

3
Introduction (Contd)

• Therefore, varying degrees of data analysis
  could be applied to literally almost any type of
  audit
• to acquire financial or operational information
  about the audit client as part of the audit
  planning process
• to confirm up to 100 of the completeness and
  accuracy of the organisations information. (Do
  away with hit-and-miss manual sampling of
  client files.)
• to confirm audit findings that resulted from
  reviewing various documentation
• to analyse specific segments of the data
• to serve as a detective tool
• to detect and/or test for suspected control
  weaknesses
• to assess the adequacy of existing controls
• to support the credibility of your audit findings
  and recommendations.

4
Course Overview

• We will cover the following discussion items
• What is data?
• How is data stored?
• How do you get at the data?
• What are some implications of using data analysis
  in audits?
• IS terminology as it relates to data extraction
  and analysis.
• We will relate these concepts to a point-of-sale
  (POS) audit that required extensive use of data
  analysis. (The benefits of using data analysis in
  audits will hopefully become self-evident).
• We will apply Computer-Assisted Audit Tools and
  Techniques (CAATTS) in our analysis of the POS
  data.

5
What is data?

• Bits and Bytes
• Characters
• ASCII and EBCDIC Characters
• Fields/Data Elements Records Files/Tables/Datase
  ts
• Fixed-Length Records vs. Variable-Length Records
• Data is Information

6
File Types

• In what format would you find the data stored?
• Hardcopy
• Microfiche
• Flat Files (i.e. text files, log files,
  spreadsheet files, delimited files)
• Indexed Files (i.e. VSAM/ISAM, IMS)
• Relational Tables/Databases (i.e. DB2,
• MS Access)

7
File Types (Contd)

• Examples of data file extensions
• .fil, .txt, .dat, .csv, .wks, .xls, .doc, .wpd,
  .dbf, .mdb, datasets (mainframe), .db2
  (mainframe)
• Note some programs, such as MS Excel and MS
  Access, offer some of their own tools to analyse
  data, although they are sometimes limited or
  technical in their ability.

8
File Types (Contd)

• Data files that may not be very effective with
  computerised data analysis
• Word Processing files
• Print-image files (i.e. electronic journals)
• Where the effort to get the data is greater than
  the anticipated benefits
• Where the size of the data is relatively small
  and manual review is practical
• Keep it simple

9
File Types (Contd)Example of an Electronic
Journal File

• Electronic Journal Report
• 0200
• AEJK 10 SVR 010
• N.S.
• CASHIER1 CSHR 001
• 8123 0713 MAY.26'03 REG W/S02 P1
• 0292
• AEJK 10 SVR 010
• 89000
• 1 MD PREM COFFEE 1.45
• SUBTOTAL 1.45
• GST TAX 0.10
• TAX TOTL 0.10
• TOTL 1.55
• CASH 1.55

10
Impacts of using Data Analysis in Your Audits

• Budgeting
• Audit Terms of Reference
• Audit Objectives
• Technical Resources
• Developing Audit Procedures
• Accessing the Data
• Having Adequate Audit/Analysis Tools

11
Budgeting

• Budgeting as it relates to data analysis
• 50 - Planning, including identifying and
  acquiring the data that you need
• 15 - Data preparation
• 35 - Analysis
• Possible data processing and other IS costs.

12
Budgeting (Contd)

• Budgeting for Contract IS Auditors
• It is often difficult to know, up front, how much
  time is required to complete the data analysis.
  Initially, there may be a number of unknowns that
  make budgeting a challenge.
• allow for some flexibility, if possible, and be
  able to provide the contract auditor with
  technical information about the relevant computer
  and application environment in which the data
  resides, and information about the data
  environment and the resources available to
  him/her.
• Require the contractor to come back shortly after
  engagement with their assessment and proposed
  strategy to access and prepare the data for
  analysis. If possible, update the budget,
  accordingly.
• Use past experiences with data analysis projects
  as a measurement tool.

13
Audit Terms of Reference

• Identify the Problem and/or Risk
• The Security Department advised us of
  potentially fraudulent scenarios where money
  could be diverted for someones own personal
  benefit.
• Use flexible wording
• We will also consider the use of ACL (Audit
  Command Language) to perform data analysis, where
  appropriate.

14
Audit Objectives

• Understand the audits objectives
• What is the problem and what data/information
  is available to you to achieve those objectives?
  The audit objectives should be able to assist you
  in determining the main data elements that you
  will need for the audit in order to achieve the
  desired outcomes.

15
Audit Objectives (Contd)

• The review was directed at
• Identifying risks/exposures to the collection and
  recording of revenues (including any possible
  losses arising from employee theft or
  manipulation of the POS systems)
• Identifying existing risk mitigation strategies
  (including the role of RAPS in the detection and
  prevention of POS fraud)
• Prioritising any residual risk, and
• Making recommendations to minimise risk of
  revenue loss through
• strengthening controls at the point of sale,
  including any environmental controls, and
• the ongoing monitoring and use of analytical
  tools to minimise exposure.

16
Scope Creep

• Be cognisant of the fact that, sometimes, the
  data analysis can end up becoming an audit unto
  itself.

17
Technical Resources

• Identify key Information Systems or Business
  Systems personnel who the IS Auditor can liaise
  with and, if necessary, obtain the necessary
  approvals to use their services. If necessary,
  obtain access through the data owner and Systems
  Security or IS Department to view the data
  (written request).
• Maintain a good rapport with your Systems
  Department and your IS contacts. Ensure that
  they have a general understanding of your audit
  requirements, relevant to data requirements.

18
Technical Resources(The Endless Loop)

• Loop
• (IS Dept )
• What kind of information are you looking
  for?
• (Auditor )
• What kind of information is available?
• End-Loop

19
Developing Audit Procedures

• POS Audit Procedures (High Level)
• We will follow a staged approach to our review
• 1) We will familiarize ourselves with current POS
  training manuals and operational procedures for
  the two major revenue streams and look to
  identify initial control weaknesses and
  vulnerabilities in the application and
  environmental areas of these two systems.
• 2) Create scenarios (including questionable cash
  transactions) in a test environment to attempt to
  exploit control weaknesses and vulnerabilities
  identified in 1, above.
• 3) Gain familiarisation with the Revenue system
  and run test transactions (from 2, above)
  through the Revenue system and analyse the
  results.
• 4) Review recent work done by the Security
  Department in their identification of suspicious
  transactions/activity, to complement Internal
  Audits preliminary observations and findings.
• 5) Stratify in order of significance all risks
  identified through the previous stages, identify
  existing mitigation strategies and summarise the
  residual risks and their likely impacts.
• We will also consider the use of ACL (Audit
  Command Language) to perform data analysis, where
  appropriate.

20
Developing Audit Procedures(Contd)

• Sometimes its a case of not knowing what youve
  got until youve got it.

21
Some Sources of Audit Information(For the
purposes of data analysis)

• Pre-defined reports (i.e. electronic FMRs) may
  already exist that suffice for the
  purposes/objectives of the audit
• Some business applications have functionality
  that allows you to export the data (i.e.
  PeopleSoft and Oracle Financials). If this is
  the case, you can review the data to confirm what
  is available to you and whether or not it meets
  your audit objectives
• Data Warehouses
• Spreadsheets, databases
• System log files
• An applications audit file
• Use of query tools with possible assistance from
  the IS Department
• Data Extraction and analysis tools

22
How to Access the Data

• Assess where the data resides. For each of the
  following examples, some technical understanding
  of the computer environments may be necessary
• Mainframe Use data extract utilities (i.e.
  JCL/SYNCSORT) to access the data and download it
  in an ASCII-compatible format for further
  analysis on your PC.
• Oracle or other relational databases from
  mid-range computers Run a query (SQL) to
  extract the relevant data and copy it down to an
  ASCII-compatible format. Or, some database
  applications, such as Oracle and PeopleSoft, have
  data extract or reporting utilities that you can
  run without the need to ask for assistance from
  your IS Department (as long as appropriate levels
  of access have been granted).
• Data Warehouses/LANs/Microcomputers Certain
  ASCII data files may already be readily
  available, or queries may be run to obtain the
  data.
• FTP (File Transfer Protocol) A utility used for
  transferring data from a source system to your
  local system/environment.

23
Sample SQL Statement (Script) to Extract the
Appropriate Transactions

• SELECT
• es.LOCATOR_ID,
• es.EMP_ID,
• es.RAPS_WATCH_CODE,
• th.SALE_CENTRE_CODE,
• th.SELLING_DEVICE_ID,
• th.RECEIPT_NUMBER,
• th.TICKET_TS,
• th.TX_TYPE_CODE,
• th.TICK_AMOUNT,
• sg.GS_TYPE_CODE,
• sg.SALES_GS_LINE_NUMBER,
• sg.PLU,
• sg.QUANTITY,
• sg.UNIT_PRICE,
• sg.SELL_PRICE,
• FROM employee_session es, ticket_header th,
  sales_gs sg
• WHERE
• es.SESSION_SEQ th.SESSION_SEQ

24
How to Access the DataQuestions and Comments

• What is the current format of the data?
• - ASCII or EBCDIC based?
• - Text File? Spreadsheet? Comma Delimited?,
  etc?
• ACL for Windows, for example, uses ASCII-based
  text.
• flat files are probably the easiest file
  structure to work with.
• Have a place to store the data (ie. LAN Drive or
  CD).

25
Understand the Data

• The use of data dictionaries
• Definition
• The descriptions of the names of data elements
  (fields), their position in the record (file) and
  their valid values.

26
A Practical Application of Data Analysis Using a
POS Audit

• Initial data considerations
• Understanding the data
• Working with the data
• Have a place to store the data
• Keep a documented log of the data files that you
  create

27
Summary of Some of the CAATTS Used for the POS
Audit

• Defining data files
• Verifying the data
• Sorting, Grouping, Batching, Summarizing,
  Joining, Exporting
• Re-creating certain transaction sequences
• Reporting

28
Demonstration

Point-of-Sale Revenue Audit
29
An Introduction to the Use of Data Analysis in
Auditing

• Thank you
• Questions ?
• E-Mail ASwiatlowski_at_Shaw.ca