William V. Allen
The purpose of this paper is to assist audit partners (owners) in understanding how data extraction and analysis software allows you to be a more proficient auditor. This paper will not show you how to use the software, but it will show you various uses of the software in financial statement audits. This paper will show you where you can use data analysis in place of current auditing methods and not only get quicker results, but get more comprehensive results. It will also show you how to use data analysis to enhance some of the tests that you are currently performing.
What is Data Extraction and Analysis Software?
It is software that you load on your computer, generally a laptop, and take into the field with you on an audit.
Extraction: You can then extract your client’s data and import it into the software as a database.
Analysis: The software will allow you to analyze the data. For instance, if you want to examine journal entries you can extract all the journal entries by writing a simple formula and within minutes you will have another database with all the journal entries for the period specified. This is probably the best, and maybe the only way that you can be assured that you have the entire population of journal entries.
The IDEA® – Data Analysis Software will allow you to extract limitless files and all the analyses can be performed using dropdown menus.
In using IDEA original data cannot be added to, deleted from, or modified because you are using a copy of the client data versus original data, and therefore you are unable to corrupt the client’s data.
Appendix A to this paper lists many of the various analyses that the software will allow you to perform.
Do current audit standards require the use of data extraction and analysis? The simple answer is no. However, if one reads between the lines it certainly appears that it will be difficult to meet the standards without using data extraction and analysis or Computer Assisted Audit Techniques (CAATs). For instance, AU Sec 316 and 316A mention the use of CAATs 14 times. This infers that it would be very difficult to meet the fraud audit standard without using CAATs.
When is the use of IDEA appropriate on an audit engagement?
Three factors influence the auditors’ decision to use IDEA on an audit engagement:
IDEA is an excellent tool for meeting audit objectives associated with management’s assertions relating to completeness, existence and valuation. IDEA permits the auditor to expand the scope of audit tests while simultaneously reducing the time required to complete them.
IDEA is very useful for substantive testing when there is a need to determine if items are in error or to identify and quantify items meeting certain criteria, IDEA can bring enormous efficiencies to the audit team.
IDEA is also very useful for compliance testing when checking computer based controls where it is desirable to re-perform edit checks, matching and other computer-based procedures.
IDEA is most valuable when the audit client’s records consist of a large volume of transactions. Typically, IDEA will significantly affect the audit at the following volume levels:
Ease of Download
The client’s data has to be transferred from the "host" (source) computer to the PC running IDEA or onto a network location accessible by the auditor's PC. This may be quite simple and only take a few minutes or it may be more complex and involve writing a specific query or report.
IDEA reads data in virtually any format, including ASCII, dBASE, and EBDIC. Regardless of the format, the auditor will need to know the record layout and what specific data is in what fields of the records. The cost of obtaining this data must be weighed against the benefits of using IDEA once the data is available.
Audimation Services, Inc., the U.S. distributor of IDEA, provides specific training in importing data to IDEA and for smaller audit firms. Audimation will also assist in importing and formatting data.
How can IDEA be used to improve the audit and reduce the time necessary to complete the engagement?
IDEA can be used to assist the auditor in becoming more proficient. A proficient auditor is both effective and efficient. Effective auditors audit the right things and efficient auditors audit them right. These are important concepts; which are reviewed in more detail below.
Effectiveness starts in the brainstorming meetings when the auditor determines which account balances and transaction cycles are significant and material. These are the ―right‖ accounts and transaction cycles to audit. Accounts that are neither significant nor material should not be audited. IDEA will not necessarily assist you in this phase of your audit, although IDEA is very useful in planning analytics and can be used to determine that you did not miss anything in this phase.
Once the ―right‖ account balances and transaction cycles are determined then the auditor must efficiently audit these. IDEA gives you options that you do not currently have. IDEA is faster than any human could ever be. The scope of analysis can be expanded, increasing audit coverage, without any additional human effort. Because all the data in the file is read-only format, it’s impossible for the auditor to inadvertently add or delete items to the database.
How do you identify opportunities to use IDEA on our audits?
That is the primary purpose of this paper. Appendix A is a listing of many audit procedures that can be performed using IDEA. The list is not all inclusive, but it is a very good starting point. Review this list and during the planning phase of every audit, ask the specific question, "How can we use IDEA to better conduct this client’s audit?"
You might consider developing different lists for different industries. For example you could have a general procedures list of things that you want to use IDEA for on every audit. Then a list of procedures for various industries such as: Construction, ERISA, Governments, Manufacturing, Not-for-Profits, etc. I would be more than happy to work with you on creating lists for industries that you serve.
Can I perform/review test work performed with IDEA without learning how to use the application?
This section is for audit partners, owners, and managers who have elected not to become proficient in the software itself. And this will work well.
The first thing to remember is that anything done with IDEA could have been done by hand. It just would not be practical since it would take so long that it would destroy planned realization and jeopardize the audit team’s ability to meet the client’s deadline. Still, it’s the right mindset for you to have.
What you would verify if the test had been done by hand is exactly what you verify when IDEA is used. The important thing is not to learn the software, but to know the audit procedures to perform in the given situation.
When data is imported into IDEA, a database is created. Once a database is created, the original data cannot be deleted or modified. This database is a file with an ―.imd‖ (IDEA managed database) extension. When that file is opened in IDEA, there are various navigation tabs on each page. These tabs look very similar to the worksheet tabs in MS Excel. The tabs and what they contain are:
When an extraction is done in the database, another database is created. IDEA has its own file view, which looks like Windows Explorer, on the left of the screen. The database created from the extraction is called a "child" created from the "parent". The "child" will also have history and field statistics. Here, too, the data cannot be deleted or modified. An extraction performed on a "child" database creates a "grandchild", and so on.
As a reviewer, you can take great comfort in the fact that no human error can result in a modification of any of the data in the database. Control totals are available for comparison to trial balance and financial statement amounts.
How do I perform tests?
You can use Audimation or another outside entity to perform the data extraction and run the tests. All you have to do is be the auditor and tell them what tests to run. They can also probably give you some good advice on how to test certain areas. So the auditor does not have to be proficient in the software, just a proficient auditor.
How do I review?
The best and easiest way to review is to open the database files and review the results of the auditors’ tests. Because of your experience and knowledge of the client’s business, you may gain additional insights from the data analysis that lead to valuable management letter comments or counsel to your client. You may decide that additional extractions and analysis need to be done either by you or by a member of your staff proficient in using the application.
Opening the database and reviewing the IDEA files does not require you to have knowledge of how to use the application. The audit partner points and clicks on the databases in the explorer view, the tabs on the right side of each database (database, history and field statistics) and uses the scroll bars to peruse the results. The mechanics of the review are no more difficult than turning pages in a three ring binder.
In addition to the database, you should also review the history (so you know what the staff did) and the field statistics (so you can see the control totals). Both of these pages can be easily copied and pasted from IDEA to MS Word.
Various analyses that IDEA can perform (this is by no means a complete list of analysis that IDEA can perform). One of the things to keep in mind is that with IDEA you can generally examine 100% of the items in a few minutes versus testing 25 to 60 items in several hours. Keep in mind that IDEA is only limited by your imagination.
Plant, Property and Equipment
And remember that this is only a partial list of possibilities
Here are some further analysis that IDEA can perform (this is by no means a complete list of analysis that IDEA can perform) when you are testing for fraud.
Successful Fraud Tests
Phantom Vendor Schemes
Kickback or Conflict-of-Interest Schemes
Dormant Account Schemes
Money Laundering Schemes
WILLIAM V. ALLEN, Jr., CPA
(The Audit Wizard)
Bill Allen was born and grew up in Massachusetts. He graduated from Boston College in 1965.
Bill is currently President of Making Auditors Proficient, inc (MAP). MAP provides training, technical assistance, monitoring, inspections and other services to local CPA firms throughout the United States. Their specialty and what gives them a unique approach to the various services is their proficiency in logic-based auditing resulting in effectiveness and efficiency in audits.
ill is a recognized expert in Audits of Governmental and Not-For-Profit Entities as well as Single Audits. He was the Audit Partner for the City of Los Angeles audit from 1985 to 1991. He was also a Partner in a Local Firm in northern California with responsibility for over 50 small and medium sized audit engagements.
Bill is also a speaker at many State and National Conferences and is currently serving a third term as a member of the Florida Institute of CPA’s Peer Review Committee. He is also a former member of the AICPA Government Accounting and Auditing Committee.
He is also the Chair of the Audit Committee for the Hospice and Hospice Foundation of Lake/ Sumter County, FL
Bill now practices out of The Villages, FL.
You can reach him at: E-mail: [email protected] Voice: (352) 750-9636 Web Site: www.billallen.com