Computer Assisted Auditing Using IDEA - With Carolyn Newman
Below is the transcript of the workshop held January 18, 2000 and presented by Carolyn Newman on computer assisted audit techniques. Please review the transcripts, and share them with anyone else in your firm responsible for CAATs.
Scott Cytron: Good afternoon. Welcome! We are happy you could be here with us this afternoon. I’m Scott Cytron and I will be facilitating the workshop today. We want to encourage you to take advantage of the interactive nature of the workshop. I’ll make a note of any questions that arise and will make sure we have time at the end of the workshop to get back to specific issues. This way, our workshop leader will have plenty of time to get through the session.
Let me introduce our workshop leader, first. Carolyn J. Newman, CPA, CISA has a management consulting and accounting practice in Houston, Texas, established in 1990. In 1992, she founded Audimation Services, Inc., to distribute IDEA and other AICPA-sponsored software to internal auditors and government. She is a former Regional Technical Director of EDP Auditing for an international accounting firm, where her duties included developing EDP Auditing training courses. She is a frequent speaker on the subject of computer assisted audit techniques.
She holds a Bachelor of Arts degree from Texas Lutheran College, and a Master of Accountancy from the University of Houston.
Did I cover everything Carolyn? If so, let's get started!
Carolyn Newman: Thank you, Scott. I hope to be able to provide useful information for all the participants.
This actually happened: Auditors obtained a 400-page printout of their client’s accounts receivable report, which showed a total of $30,000,000. It footed to less than $15,000,000. How long did it take the auditors to foot the report? Fifteen minutes. They used computer assisted audit techniques. How can a computer printout have a wrong total? Fraud. The Controller knew how to spool the report to a file and edit the file prior to printing out the report.
How can computer assisted audit techniques be used to improve audit effectiveness and help detect fraud? Computer assisted audit techniques (CAATs) involves using the computer to perform functions that would take much longer or be impossible to perform manually.
The main benefits are as follows:
What tools are used for CAATs? Spreadsheets, Database programs, and generalized audit software. We could do a whole workshop on the features and uses of each product, but today we will focus on a generalized audit software package called IDEA.
IDEA stands for Interactive Data Extraction and Analysis. It is a PC based File Interrogation Tool for use by auditors, accountants, investigators and IT staff. It analyzes data in many ways and allows extraction, sampling and field manipulation of data in order to identify errors, problems, specific issues and trends.
It can be used in the following main areas:
Exception testing can be used to identify unusual or strange items. These may be simply large items or where the relationship between two pieces of information on an item do not correlate (e.g. rate of pay and pay grade). Many fields of information can also be checked for allowable values (e.g. standard fees).
IDEA can help with the preparation of figures for an analytical review. In particular, it can generate analyses which would not otherwise be available. The File Stratification function will give a profile of the population in value bands, groups of codes or dates. This is particularly useful when auditing assets such as accounts receivable, inventories, loans or for a breakdown of transactions. Additionally, the information can be summarized to determine trends. If graphical analysis is required by particular codes or sub-codes then Charting can be used. Figures can also be compared against previous years using the file compare feature to determine the differences by item or even groups of items.
IDEA is excellent for proving calculations. To total (or provide other statistical information such as averages, maximum or minimum value), view the auto generated Field Statistics for the database. Calculations can be checked or new calculations created by appending a calculated field to the database using the Field Manipulation option. The miscalculations can then be copied to a new database using the Extraction option. The accuracy of any management reports may involve a wider use of functions to achieve the result of reproducing the report. You may need to use to Join Databases function to bring two or more databases together into one new database. You may need to use the Aging function to analyze receivables or inventory.
Cross Matching Data Between Systems
One of the common ways to test the validity of an item is to crosscheck it against some other information. An example would be checking addresses or bank details of employees against those on Accounts Payable files to see if any employees were also purporting to be suppliers. These tests are carried out by importing both files and then using the Join Databases function. Another effective test for completeness is to cross-check between a master file and a transaction file to see if there are any items on the transaction file for which a master file record has not been recorded.
Testing for Gaps and Duplicates
IDEA can be used to test for completeness including testing for gaps or missing items in a numeric sequence using the Gap Detection function. A quick way to identify breaks in series of check numbers or inventory tag numbers. IDEA can search for duplicate records in a database based on one or up to eight fields using the Duplicate function. This can be very effective in certain circumstances such as testing for duplicate payments to suppliers. You can also identify double input of ticket numbers during inventory counts or duplicate names or addresses on insurance or benefits claims file.
IDEA offers 4 types of sampling:
Systematic - which should be used for samples to evenly cover a population range.
Random - which should be used where statistical projections are required on various attributes of the population without bias.
Stratified Random Sampling - which should be used either where the population consists of different groups (items have a different risk) which need separate evaluating, or if Classical Variables Sampling (Variables Estimation Sampling) is to be used.
Monetary Unit Sampling - which should be used where a financial assessment of error is required with items that can be partially wrong.
I recognize some names in the workshop. Would any IDEA users like to add their experience?
Bruce Roff: I've used IDEA extensively in auditing large manufacturing entities in areas of inventory and accounts receivable. I've found the old version cumbersome in retrieving and importing information. Has the new version addressed different types of files?
Carolyn Newman: What version are you on?
Bruce Roff: I've received the new version 3.0, but have not used it yet; I'm working with the older version, 2.?
Carolyn Newman: Version 2 introduced the import assistant, but did not allow the direct import of Excel and Access files. Version 3 does.
Bruce Roff: That will be very helpful, thank you.
Carolyn Newman: Version 3 is a 32-bit application, so the ODBC links and performance in NT are much improved.
I met someone who was still using the DOS version 4 yesterday. It amazes me how some people can learn something and keep on trucking!
Bruce Roff: I've used the dos version so many years ago. I know people that continue to use it. I don't understand why.
Fred Lyons: We used to analyze two separate systems. One at a county level & one at a city level. We compared deed transfer files at the county level to transfer tax files at the city level matching lot & block numbers. The result was that the city was not collecting all the tax they should. A lot of money was involved. We worked with them to improve their controls to minimize this problem going forward
Bruce Roff: Fred did you find it difficult to identify which fields represent which attributes you were testing?
Carolyn Newman: Yea, Fred! We have a number of customers who have put IDEA in the IS department to speed up the ad hoc reporting
Fred Lyons: There is always work involved in this area. This was with IBM mainframe files, so we discussed the record layouts with the systems people.
Jim Kaplan: Fred, what was the technical level of the auditors that interfaced with the systems people?
Fred Lyons: Jim, this job was the first time they used IDEA. I was the expert in CAATS but they caught on fast.
Bruce Roff: I've used it on files in excess of 2 gigs. The record lengths were quite extensive. With out the IT support it would have been impossible.
Carolyn Newman: Successful use of CAATS always requires planning. You MUST understand the data before you begin.
Bruce Roff: I would agree.
Carolyn Newman: There's a debate among firms, I think, that relates to whether the auditor in the field should be the IDEA user, or whether a specialist should come out and perform the analysis. Comments?
Bruce Roff: The most important planning warning label I would put on this software is cost/benefit?
Carolyn Newman: Bruce, do you have a "for instance"?
Jim Kaplan: IMO all auditors on staff should be able to apply CAATs to audit projects. In my previous positions there was always a separation between IT auditors and non-IT auditors.
Bruce Roff: The auditor should weigh the cost of initial set up time and the time it takes to obtain the data vs. traditional auditing procedures
Chris Curran: As far as using a specialist, or the auditor in the field, it would depend on the size of the firm/audit group and the skill level of the auditors.
Fred Lyons: I agree with Jim. IDEA is easy to use, it works like excel. The difficult part can be getting the data into IDEA. I think one computer specialist can support a number of CAAT auditors.
Chris Curran: Using specialists may be more efficient, however, all auditors should be able to use CAAT tools to some extent.
Scott Cytron: Do you agree Carolyn?
Jim Kaplan: Chris I agree. But the nature of auditing is changing so fast that I think all auditors should be developing IT skills and be able to apply them to all their audit projects.
Bruce Roff: the learning cost can be substantial in the set up, but going forward it will be prove to be more efficient.
Fred Lyons: I agree with Bruce in that IDEA will take a little time to use on an audit. It should be applied to material issues
Scott Cytron: What do you recommend?
Carolyn Newman: We have seen the initial cost of defining files go down because so many systems now have the ability to product print reports and/or export data to Excel or ASCII formats.
Bruce Roff: Carolyn, I have found the set up of printed reports more time consuming than raw data.
Carolyn Newman: Scott, I do agree that all auditors should be able to use CAAT tools. The auditors in the field are the ones making the decisions.
Mike Yankunas: We purchased IDEA one year ago. Training was held for 5-6 key users. An info session was held for all other A&A staff to alert them to the benefits and capabilities. Now one year later we still have yet to utilize it to any great extent. Any suggestions on how to get started? What has worked? What hasn't?
Carolyn Newman: Bruce, you're talking about DataImport for IDEA, which is part of the Import Assistant, and allows you to define a mask so that only the data in a report is brought into IDEA. It does take some practice. We can send you a PowerPoint show that educates you a little on the best way to set up a mask.
Bruce Roff: I've recently implemented it at my firm. I have many years of experience using the software. I think that it is a great tool and adds to the firms marketing capabilities, not to mention the staff’s response with getting into something cutting edge.
Carolyn Newman: There absolutely HAS to be partner support for use of CAATs to succeed. It seems like you went about the training in the right way, but maybe you needed someone to be a coach or champion. Actually, that's one of the reasons we set up our toll free help desk.
Bruce Roff: Has anyone used this software in fraud area?
Mike Yankunas: What is the number?
Carolyn Newman: Help desk for IDEA: 1-888-641-2800. By email -- email@example.com. We started Saturday hours last week.
Carolyn Newman: One of our users reported using it on a healthcare fraud investigation.
Chris Curran: Mike, identify key audit areas that you would like to use IDEA for, like A/R or Inventory, and develop standard procedures for these areas. You can find suggestions in the IDEA help.
Carolyn Newman: We also have an application sheet that suggests fraud testing areas.
Mike Lewis: Mike our firm was in a similar situation. We just had to continually train and educate employees now we are using it on most of our engagements. Our goal is to use it on all of our engagements. We keep a database that describes how we are using it on clients for others in the firm to read up on. It has been quite a successful way to implement it firmwide.
Fred Lyons: Mike you could pick one test, such as recalculating extended cost on an inventory report. Make this IDEA test mandatory for all audits with inventory over x dollars. (a one page inventory report isn't worth it)
Carolyn Newman: Another idea to ensure implementation is to make the "mandatory" usage measurable in some way. You know the adage -- what gets measured gets done. I believe Grant Thornton is revising their audit program set-up to include IDEA tests if the approach will be automated.
Bruce Roff: I would suggest that during the planning key audit areas consider the use of IDEA and how it will add to audit efficiency. Considering CAATS. I have a success story to share with the users Carolyn if I may?
Carolyn Newman: My favorite efficiency approach comes the second year, when you compare this year's inventory with last year. Not detail price testing!
Carolyn Newman: Bruce, please go ahead
Bruce Roff: I've used IDEA in price testing a large manufacturing inventory, we found in performing our price test a large variance of what would be considered a material amount when extrapolated. We were able through the use of the software to identify what the problem was in front of the officers of the company. It proved to be a great client relations tool and has put our image up a few clicks.
Scott Cytron: That's great Bruce!
Carolyn Newman: Clients really like it when you help them identify duplicate payments they can recover, too.
Bruce Roff: I haven't done that kind of work, but I can see how easy it would be.
Carolyn Newman: The trick is to go out before the audit, so that value-added work can be value-billed.
Bruce Roff: I will be implementing that this year.
Carolyn Newman: Speaking of manufacturing clients, one of our trainers was a controller and heavy IDEA user who speeded up month end closings and identified a number of inventory pricing problems with IDEA.
Bruce Roff: I think it is vital to use IDEA in that industry.
Carolyn Newman: Another user found a way to perform bank account reconciliations in less than an hour that had previously taken a clerk all day.
Chris Curran: Carolyn, you spoke about efficiency. What about improving efficiencies through IDEA scripts?
Carolyn Newman: I was hoping that topic would come up. Scripts are macros that can be written, or recorded and played back to automate and standardize a series of tests.
Bruce Roff: Do you offer training in writing scripts?
Carolyn Newman: We have a number of scripts available, and you can download some from the KnowledgeBase.
Scott Cytron: Carolyn, I know I learned a lot from you. Thanks for presenting today. Are there any other questions as we start to wrap up the session?
Bruce Roff: I want to say thank you Carolyn for your time. I have always been IDEAs biggest cheerleader.
Fred Lyons: BTW I have found the knowledgebase to be a great way to get help with IDEA
Mike Yankunas: This is the first time I have utilized one of these on-line training sessions. It was very informative and useful. Thanks.
Carolyn Newman: Thank you. This was fun! And I learned some things from you all.
Bruce Roff: I've spoken to Wanda and John in the past. They have been very helpful in setting up training and in technical support.
Scott Cytron: Thanks for signing on and have a great and successful week! Look for the transcript to be posted later.
Carolyn Newman: Thanks again.