University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Tutorial 4
Tutorial Objectives
By the end of this tutorial you should;
- Be able to identify preliminary patterns using basic analysis
-
Exercise 1 – Identifying Expense Patterns
In this tutorial, because we are working with data from Metaphor Corporation, we will assume the typical
scenario and examine patterns in their employees’ expenditures. By examining and understanding patterns, we
can help the organization make better decisions.
Exercise 1.1 – Total Expenses per Category
Suppose Metaphor Corporation would like to know the total expenses, by its’ employees, for each
category.
Exercise 1.1.1 – Using “SUMMARIZE”
SUMMARIZE is a powerful built-in command that helps us easily create summaries based on our
required parameters and more.
1. Open Trans_April table -> select Analyze -> Summarize
2. In the Summarize On, select CODES -> in the Subtotal Fields, select AMOUNT -> in the Other
Fields, select DESCRIPTION -> in the Output Tab, select File and name it: Trans_by_Code -> click
OK
Exercise 1.2 – Isolate Transactions
Suppose Metaphor Corporation would like to look deeper at the expenses of the categories of airfare,
accommodation and rental cars – which have the codes between 3000-3750. Hence, we would like filter these
categories and total their expenses.
Exercise 1.2.1 – Using “BETWEEN”
The build-in BETWEEN command retrieves any record that has values in the selected field falling in
between the two values provided.
1. Open Trans_April table -> and set the filter using the BETWEEN command
*Syntax: BETWEEN (field, min, max)
ALLTRIM (field/string)
2. Enter the following command BETWEEN (ALLTRIM (CODES),”3000”,”3750”) -> verify & execute
the filter.
3. With the current filtered view -> count the records and then total the field AMOUNT
4. Export this view as a table to enable us to further analyze it later -> select Data -> Extract Data ->
ensure Record is selected -> enter Trans_April_Travel in the TO field -> click OK.
Exercise 1.2.2 – Using “MATCH”
The built-in MATCH command is simply another way of applying multiple filters to a table (instead of
using OR). Now we want to examine expenses related to the categories of gulf and drinking establishments –
which have the codes of 7992 and 5813, respectively. Hence, we would like filter these categories and total
their expenses.
1. Open Trans_April table -> and set the filter using the MATCH command
*Syntax: MATCH (field, test1, test2, … , testn)
2. Enter the following command MATCH (ALLTRIM (CODES),”7992”,”5813”) -> verify and execute
the filter. Repeat step 3 from the previous exercise.
1
Prepared By: Ahmed Ramadan
University Of Bahrain
College of Information Technology
Department of Information Systems
IT Auditing
IT IS 444 / IT BIS 445
Exercise 1.3 – Total Expenses per Credit Card
Suppose Metaphor Corporation would like to know the total expenses incurred by each credit card
holder.
Exercise 1.3.1 – Using “CLASSIFY”
CLASSIFY is another powerful ACL command like SUMMARIZE, however, this does is different from
summarize as you can only select one field to classify records by, it is similar to grouping records according to
the set criteria (GROUP BY – in SQL).
1. Open Trans_April table -> select Analyze -> Classify.
2. In the Classify On, select CARDNUM -> in the Subtotal Fields, select AMOUNT -> in the Output
Tab, select File and name it: Trans_April_by_CCN -> click OK and count the number of records in
the new table you just created
3. Delete the columns Percent of Count and Percent of Field
4. Save your work.
2
Prepared By: Ahmed Ramadan