Question

9) Describe the ACL analysis? How can ACL tools and techniques be used in an audit? Be specific and use examples to support your answer. What did you learn about ACL that you found to be most useful with respect to IT Audit?

Answer 1

Describe the ACL analysis?

Audit Command Language (ACL) Analytics is a data extraction and analysis software used for fraud detection and prevention, and risk management. It samples large data sets to find irregularities or patterns in transactions that could indicate control weaknesses or fraud.

How can ACL tools and techniques be used in an audit?

In general an audit tool is anything auditors use to complete an audit. An audit tool can be software such as ACL, Access or Excel. It can also be a hard-copy audit program or check list.

34 More particularly, in the closure audit for the operational programme ‘Andalusia’, the Commission selected a random sample of 37 projects out of 5 319 in an amount of EUR 870 341 396, or 16.69% of the final expenditure declared, on the basis of monetary unit sampling and with the aid of Audit Command Language (ACL) software, a computer-assisted audit tool.

Annex 3 - List of Background Documents and Supporting Information The audit was conducted using Computer Assisted Audit Tools and Techniques (CAATT). The approach consisted in downloading the transactions from the Bank of Montreal (BMO) web site into our Audit Command Language Software (ACL).

Audit and Evaluation Audit of Acquisition Card Transactions Audit of Acquisition Card Transactions The audit was conducted using Computer Assisted Audit Tools and Techniques (CAATT). The approach consisted in downloading the transactions from the Bank of Montreal (BMO) web site into our Audit Command Language Software (ACL).

◦ The judgemental sampling was based on a selection of 215 high risk transactions, with a total value of $260M, identified using an automated data analysis software package, Audit Command Language (ACL).

Print-friendly Audit of Acquisition Card Transactions The audit was conducted using Computer Assisted Audit Tools and Techniques (CAATT). The approach consisted in downloading the transactions from the Bank of Montreal (BMO) web site into our Audit Command Language Software (ACL).

The data selected were analyzed using Audit Command Language (ACL) software.

The Audit and Inspection Department obtained Audit Command Language (ACL) packages, and has contracted with an external expert to conduct a training session for Department staff in December

The Audit Command Language (ACL) software was used to perform integrity tests to identify transactions with anomalies such as corrupted data, completeness of the file and blank fields.

Audit Command Language (ACL) software was used to download and analyze procurement transactions from CAS, as well as generate a statistically valid, random sample of 273 transactions for review.

Using the ERDF financing in the amount of 951 243 399 German marks (DEM) granted to the operation programme and on the basis of the calculations made by the software Audit Command Language (ACL), the Commission applied an extrapolated financial correction of DEM 25 516 719, which represented a reduction of 2.68% of the ERDF financial assistance to the overall programme.

A data analysis software package, Audit Command Language (ACL) was used in the analysis of approximately 600,000 pay transactions and 156,000 leave transactions.

• obtained the GCIMS database and the GCIMS data dictionary and ran queries using the audit software package Audit Command Language (ACL);

The Audit and Inspection Department obtained Audit Command Language (ACL) packages, and has contracted with an external expert to conduct a training session for Department staff in December 2002.

Audit Methodology The audit was conducted using Computer Assisted Audit Tools and Techniques (CAATT). The approach consisted in downloading the transactions from the Bank of Montreal (BMO) web site into our Audit Command Language Software (ACL).

In addition, as the Internal Audit Office has collective command of three United Nations working languages- Arabic, English and French- some internal auditors were strongly encouraged to acquire proficiency in the fourth working language, Spanish

In addition, as the Internal Audit Office has collective command of three United Nations working languages – Arabic, English and French – some internal auditors were strongly encouraged to acquire proficiency in the fourth working language, Spanish.

Because there were no publications visible during the audit, the Regional Official Languages Officer and Co-ordinator will discuss this issue with the detachment commander to ensure that when publications are made available to the public, they are always displayed in both official languages.

Be specific and use examples to support your answer.

Auditing Oracle ERP Using ACL:
An example of the Sales and Collection Cycle

Abstract
This project focused on the sales and collection cycle, and data from the Oracle
database were adopted as the auditing subject. Five audit objects were developed
before this project proceeded to the stage of ACL auditing. Related audit procedures
were developed according to these objects. The aim of this project was to determine
weaknesses in internal control by using ACL auditing software and to provide
suggestions for additional improvements.
Keywords: Audit Command Language (ACL), internal control, sales and
Collection Cycle

Foreword
Computer-assisted audit tools (CAATs) are designed specifically for system auditing.
CAATs are compatible with various file types and data formats. CAATs demonstrate
sampling, file compilation, date calculation, and data conversion features, and they
assist auditors in enhancing productivity, detecting fraud, continuous monitoring,
law and regulation compliance, data security assurance, and joint audit practices.
Audit Command Language (ACL) is one of the most prevalent auditing software. For
more than two decades, ACL is renowned worldwide for providing auditing, control
test, and compliance technology solutions for people with financial and accounting
backgrounds.
The ultimate goal for all business activities is to maximize profit, and sale is the
principal measure of profit. Sales is the main source of revenue for most companies,
and the sales process involves most of the departments in a company, including
operations department, production department, accounting department, and sales
logistics department. Specifically, sales activity is a complex process. Apart from sales, cash collection is another routine business activity. With high transaction
frequency, fraudulent behaviors such as embezzlement and theft are likely to occur
during the process of cash collection and bank deposit. Therefore, the sales and
collection cycle is a crucial yet highly risky segment in the daily operations of
companies.
This project focused on the sales and collection cycle, and data from the Oracle
database were adopted as the auditing subject. The aim of this project was to
determine weaknesses in internal control by using ACL auditing software and to
provide suggestions for additional improvements.
Introduction to the Sales and Collection Cycle
In a conventional company, the sales and collection cycle involves multiple steps,
such as processing orders, managing credit, delivering goods or providing services,
issuing sales invoices, recording sales and accounts receivable, providing sales
discounts, allowances, and sales returns, and recording cash receipts.
The main purpose of auditing the sales and collection cycle is to examine whether
the account balance involved in the cycle is appropriately maintained according to
the generally accepted accounting principles. Five transaction types are involved in
the sales and collection cycle: sales (cash sales and sales on account), cash receipts,
sales returns and allowances, write off uncollectible accounts, and bad debt expense.
The sales and collection cycle involves the process of determining goods and
services transfer, a process that begins from the customer’s order placement and
ends with the transfer of goods and services into cash or accounts receivable. In this
process, the company uses documents and records to communicate the transaction
process and implement internal control. Table 1 illustrates the business functions
and documents of sales and collection cycle. When the functions of such documents
are understood, the audit process can be facilitated.

Using ACL Software to Conduct Audit Procedure Analysis
1. Establishing Audit Objectives and Procedures
ACL can be used to audit computer files according to the following procedures:
 Establish audit goals
 Learn about computer application systems and file structures
 Organize the contents to be audited
 Transfer files from the host computer to a personal computer or disc
 Define file fields and formats in ACL
 Verify whether the data defined are accurate and whether they conform to the
account records
 Execute the audit analysis
 Generate reports of the audit results
Five audit objects were developed before this project proceeded to the stage of ACL
auditing. Related audit procedures were developed according to these objects.

2. Defining data fields

3. Carrying out audit procedures
Auditing Objective 1
Audit whether cash basis accounts receivable write-offs matches the amount of
cash received from accounts receivable.
Auditing procedures：
Compare the AMOUNT in the AR_CASH_RECEIPTS_ALL Table with the
AMOUNT APPLIED in the AR_RECEIVABLE_APPLICATIONS_ALL Table.

Used ACL command: Summarize, Join Tables, and Local Filters.
ACL Auditing step：
Step 1. Select Analyze > Summarize. Use the Summarize command to summarize
the AMOUNT_ APPLIED in the AR_RECEIVABLE_APPLICATIONS_ALL table
by CASH_RECEIPT_ID. This process produced new table and named as
SUMVEN_APPLICATIONS_AMOUNT.
Step 2. Select Data>Join Tables. Use the Join command to join
SUMVEN_APPLICATIONS_AMOUNT table and AR_CASH_RECEIPTS_ALL
table. This process produced new aggregate table, and was saved as MATCHED
AMOUNT.
Step 3. Use Local Filters to view whether the amount of AMOUNT _APPLIED and
AMOUNT are the same. Select Analyze > count records. In the Expression box
enter AMOUNT _APPLIED <>AMOUNT, save as difference amount.
The audit result showed in figure 1. Of the 19,634 entries, the amount in 85 entries
of AMOUNT _APPLIED in AR_RECEIVABLE_APPLICATIONS_ALL Table
differed with AMOUNT that of AR_CASH_RECEIPTS_ALL Table.

Auditing Objective 2
Audit whether the date of collection matches the date of deposit.
Auditing procedures：
Compare the date of RECEIPT_DATE with the date of DEPOSIT_DATE in the
AR_CASH_RECEIPTS_ALL table.

Auditing step use ACL：
Step 1. Select Analyze > count records.
Step 2. In the Expression box enter RECEIPT_DATE<>DEPOSIT_DATE, save as
difference date. Click OK.
The audit result showed that of the 19,682 transaction entries, 60 entries had dates
where the data of collection did not match the date of deposit.
Auditing Objective 3
Audit whether the calculation of the sales revenue is accurate and normal.
Auditing procedures：
Examine whether the sales revenue contains any negative number; review the sales
revenue number by multiplying the ordered quantity by the per unit sales price in the
transaction detail file.

Used ACL command: Local Filters
ACL Audit step：
Step 1. Select Analyze > count records. In the Expression box enter
UNIT_SELLING_PRICE<0, save as Negprice. Click OK.
The audit result showed that of the 298,074 transaction entries, 17 entries had
negative numbers in per unit sales price.
Step 3. Select Analyze > count records.
Step 4. In the Expression box enter
UNIT_SELLING_PRICE*QUANTITY_ORDERED<> REVENUE_AMOUNT, save
as difference revenue amounts.
The audit result showed that of the 298,074 transaction entries, 1,946 entries showed
disparate numbers in the result obtained by multiplying the per unit sales price by
the ordered quantity and the collected revenue.

Auditing Objective 4
Audit whether goods sold to clients without sales order.
Auditing procedures：
Compare the sales order with the identification of selling to customer.

Used ACL command: Join Tables, Local Filters.
Auditing step use ACL：
Step 1. Select Data >Join Tables. Use the Join command to join
RA_CUSTOMER_TRX_ALL table and RA_CUSTOMER_TRX_LINES_ALL
table. This process produced new aggregate table, and save as MATCH_ID.
Step 2. Select Analyze > count records. In the Expression box enter
SALES_ORDER=””, save as UNEXIST. Click OK.
The audit result showed that of the 43,394 entries, 14,073 entries represented goods
sold to clients without sales order.
Auditing Objective 5
Audit if the system involves any transaction whose date of transaction precedes the
date of sale order.
Auditing procedures：
Compare the date of transaction with the date of sale order

Used ACL command: Relate Table, Local Filters
ACL Audit step：
Step 1. Open the RA_CUSTOMER_TRX_ALL (primary table) and select Data >
Relate Tables. In the Relations dialog box, click Add Table and select
RA_CUSTOMER_TRX_LINES_ALL table (secondary tables).
Step 2. Drag the CUSTOMER_TRX_ID (key field) from the
RA_CUSTOMER_TRX_ALL table to the CUSTOMER_TRX_ID in the

RA_CUSTOMER_TRX_LINES_ALL Table. Click Finish to exit the Relations
dialog box and save as RELATION. Then these two tables are related.
Step 3. Add SALES_ORDER_DATE and TRX_DATE fields to the RELATION table,
do the following: Right-click the RELATION table view and select Add Columns.
Select RA_CUSTOMER_TRX_ALL Table and
RA_CUSTOMER_TRX_LINES_ALL Table from the From Table drop-down list
and select SALES_ORDER_DATE and TRX_DATE fields to add to the
RELATION table view. Click OK. The SALES_ORDER_DATE and TRX_DATE
fields are added to the RELATION table view.
Step 4. Select Analyze > count records. In the Expression box enter
SALES_ORDRE_DATE>TRX_DATE, save as Negdate. Click OK.
The audit result showed that of the 47,161 transaction entries, 214 entries had a
transaction date that precedes the order placement date.
Analysis of Audit Results and Suggestions for improvement
In this project, the audit process was conducted according to five objectives. The
auditors adopted auditing programs specifically designed for these objectives,
compiled the results by using ACL auditing software, and proposed suggestions of
improvement regarding the results.
(1) The company should determine if cash had been embezzled because some
account funds were collected but not deposited.
(2) If the collected funds were not deposited immediately, then the risks are that the
funds could be embezzled, stolen, or lost. Therefore, the auditors suggest that the
company should deposit cash into the bank on the same day of collection to
lower the risk of missing cash.
(3) The company should investigate why certain sales prices were of negative values
to clarify if those were key-in mistakes or were associated with other causes. In
addition, a recalculation also indicated instances of mismatched sales records.
Therefore, the company should ask related staff to recalculate and verify the data
to ensure the quality of the data.
(4) The company should confirm if false sales transactions existed. If confirmed, the
company may have flaws in its internal control system, indicating that goods
could be sold to clients without bill of sales. The company should review the
flaws in its internal control and require its employees to follow related
regulations.
In an ordinary transaction process, the clients would have to order goods first before
the company delivered the goods them. Thus, the scenario in which the date of transaction preceded the date of order placement could have happened because of
flaws in the internal control of the company or because the employees did not follow
the internal control policies of the company. Therefore, the auditors propose that the
company should review its flaws in internal control and require its employees to
follow related regulations.

What did you learn about ACL that you found to be most useful with respect to IT Audit?

Reduced frequency and impact of avoidable risk events by 50%. With better and timelier access to key process data using the ACL Platform, the organization can quickly identify when a task or procedure is non-compliant, enabling managers to identify and deal with issues before they happen. As a result, ACL software has helped reduce the frequency or duration of avoidable airport or terminal shut-down events by 50 percent.
Improved corporate performance and revenue generation. Additional profits of more than US$650,000 (risk-adjusted, net present value over three years) can be attributed to improved decision making due to access to fresh, reliable and comprehensive data reports and dashboards. Corporate leaders, able to make data-informed decisions, can more quickly take advantage of strategic opportunities and manage risks, lowering costs and increasing revenue.
Increased audit efficiency and reduced audit cost, with 33% more audits completed. Since audit teams using ACL software can easily configure access to data sources and can conduct both planned and ad hoc reviews quickly and accurately, the organization saw a 33-percent improvement in the number of audits completed (increased to 48 from 32) and a 50-percent reduction in the amount of time it takes to complete some audits (decreased audit cycle from four weeks to two).
Improved human resource allocation. With improvements in processes and efficiency, the team was able to reallocate two audit professionals to other under-resourced departments (25-percent reduction in required staffing resources) saving the organization nearly $550,000 (risk-adjusted, net present value over three years).

Since 1987, ACL technology has helped organizations reduce risk, detect fraud, enhance profitability, and improve business performance. ACL delivers its solutions to 14,700 organizations in over 150 countries through a global network of ACL offices and channel partners

ACL delivers technology solutions that are transforming audit and risk management. Through a combination of software and expert content, ACL enables powerful internal controls that identify and mitigate risk, protect profits, and accelerate performance.