Double Click
Tools to Manipulate Data
ACL and IDEA are two powerful software tools available to help you work effectively with data sources.
FROM: JAN-FEB 2004 ISSUE | BY SUSAN KING
Now that accounting data is hidden inside computers, accountants rely on the reports generated by accounting software to give us the information we need. This can be frustrating, but the power of computers also presents a huge opportunity for analyzing data.
Introducing ACL and IDEA
Both Audit Control Language (ACL) and CaseWear IDEA (IDEA) were developed as tools for external auditors to interrogate data files. ACL was developed as a teaching tool at the University of British Columbia in the early 1970s and was released commercially in 1987. IDEA was developed by the federal government in 1984, acquired by the Canadian Institute of Chartered Accountants in 1987, and then acquired by CaseWare in 2000.
Both software packages provide auditors with the ability to read client data files and perform computer-assisted audit techniques (CAATS) to verify functions performed by the client's accounting software. Examples include aging accounts receivable balances, and adding up inventory costs. The software will also perform audit functions such as drawing a random sample of items to verify physical inventory results.
But ACL and IDEA have attracted interest beyond that of external auditors. Management accountants and analysts are using ACL and IDEA to analyze financial data to make business improvement decisions and recommendations. Internal auditors use these tools to test for policy compliance, and to do value-for-money audits. Fraud investigators use the software to look for signs of money laundering and to test the validity of payments made to suppliers. And IT departments use it to look for anomalies in system logs and access rights.
Getting Started
Getting started with these packages is pretty easy IDEA comes with a small and concise user manual and a good tutorial. In addition to introducing new users to the screen layout, it also steps through importing a transaction file, and some of the commonly used tests and commands.
ACL Version 8.0, released summer 2003, comes with a Getting Started manual and an ACL in Practice manual containing a comprehensive case study in tutorial format. But be forewarned, neither manual discusses the use of the hardware key, which must be inserted in the computer's parallel port to start the software successfully.
Next Level
Once you have opened a database and understand what the various windows and tool bars do, these products are pretty simple to use at a basic level. I like the way raw data is presented in spreadsheet form, as it allows me to move around easily to become acquainted with the data. I can sort the data quickly without creating a separate file, which is also helpful. Both packages provide statistics that give me a general understanding of the ranges of values and highlight potential areas of focus for subsequent work. I can also drill down on the results of many of the statistics. This is especially useful as I peruse the data to look for things I want to investigate more fully.
The power of these packages begins with understanding the wide range of commands and functions available. Commands such as count, extract, or stratify, quickly and easily manipulate the data. The most commonly used ones are available on toolbar buttons. Both ACL and IDEA have more than 80 arithmetic, date, financial, and other functions similar to those in Excel. And both packages graph data at the click of a mouse.
ACL and IDEA also provide development tools for more advanced users. These tools can be used to automate analysis steps so that similar work can be executed repeatedly. For example, a controller might want to perform monthly inventory analysis, or create an audit routine to be run periodically.
Data Access
Accessing the data to be analyzed is the most critical and also the most difficult part of using these packages. Before beginning a project, you will need to identify the data files required and the fields and records within the file. If the volume of data is large, you may have to have the IT department extract certain parts of the data. You will also need to know where to access the data — from a server, CD, tape, or other source. Locate a written file layout definition before you begin. ACL's Data Access Guide covers these topics and discusses some common data sources.
Once you have the files you require, ACL and IDEA both have Wizard tools to import the data. The wizards create definitions for the software to enable it to read the data properly. The ACL Data Definition Wizard identifies the following file formats: dBASE, delimited text file, SAP, AccPac, Print Image File (Report), Excel, Access, and XML Data. The IDEA Import Assistant identifies ASCII fixed length, ASCII delimited (including Comma Separated Value), EBCDIC fixed length, Excel, Access, dBASE, Lotus 123, and Print Reports files, as well as files from SAP's AIS module.
But neither the ACL nor IDEA Wizard recognized the QuickBooks files I experimented with, and it is likely that other accounting packages might provide similar challenges. The IDEA Web site does have a utility to download to use QuickBooks files with IDEA, and has similar utilities available for Simply Accounting, MYOB, and other packages. The company has announced that Great Plains and Navision utilities will be available soon. These utilities are invaluable to accountants without access to in-house technical support. CGAs looking to buy ACL or IDEA should consider the data access question carefully before purchase. I recommend you talk to the vendors about the best way to access data with your specific accounting system.
The Bottom Line
If you are looking for a single, powerful, analysis tool to work with several data sources, ACL or IDEA would be an excellent investment.
ACL is priced at $2,895 per user and IDEA is $2,750 per user. Both offer network versions and IDEA offers a Small Business Edition for processing up to 100,000 records per file. Visit www.acl.com and www.caseware-idea.com for more information.
[ TOP ]
Susan King, CMC, CGA, is a senior manager in the management solutions practice at Deloitte & Touche. She helps mid-market companies select information technology and implement it effectively in their organizations. E-mail suking@deloitte.ca.
"Micromation" is co-ordinated by John W. Yu, M.Sc., CDP, FCGA, co-founder of an Internet company providing online services to accountants (www.CGAonline.net). He is also executive editor of two online magazines (www.accountantsledger.com and www.itaudit.org). E-mail jyu@cga-canada.org.