Use of Data Extraction & Analysis Software in a Financial Statement Audit

William V. Allen

 

Purpose

 

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.

 

Audit Standards

 

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:

• Audit objectives
• Volume and depth of information held on computer
• Ease of downloading data

 

Audit Objectives

 

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.

 

Volume

 

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:

• Accounts receivable: more than 200 balances and/or 1,000 transactions
• Accounts payable: more than 200 balances and/or 1,000 transactions
• Fixed assets: more than 1,000 items
• Inventories: more than 1,000 stock lines
• Purchases: more than 2,500 transactions

 

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

 

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.

 

Efficiency

 

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:

• Database — The data that was imported, in the format that was specified
• History — Everything the auditor did with this data
• Field statistics — Totals and other characteristics of the data in the database

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.

 

APPENDIX A

 

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.

 

General Ledger

 

• Test the general ledger to see if it balances
• Extract all journal entries
• Summarize or reperform summarization of accounts
• Summarize or reperform summarization of transaction
• Compare balances to prior period(s)
• Test for duplicate postings

 

Accounts Receivable

 

• Compare balance in accounts receivable in total and by customer to prior period(s)
• Summarize or re-summarize sales by customer
• Age all accounts
• Separate receivables by debit and credit balances
• Scan receivables for large and/or unusual items
• Extract related party receivables
• Determine number and accounts to sample for confirmation
• Match subsequent collections to period end balances
• Perform sales cutoff

 

Inventory

 

• Compare balances in inventory in total and by product to prior period(s)
• Summarize or re-summarize inventory by product
• Age all products in inventory
• Scan inventory for large and/or unusual items
• Determine number and items to test count
• Match test counts to final inventory
• Reperform all extensions and footings
• Perform inventory price test

 

Plant, Property and Equipment

 

• Compare balances in PP&E to prior period(s)
• Recalculate depreciation
• Extract all new assets

 

Accounts Payable

 

• Compare balance in accounts payable in total and by customer to prior period(s)
• Summarize or re-summarize purchases by customer
• Separate payables by debit and credit balances
• Scan payables for large and/or unusual items
• Extract related party payables
• Determine number and accounts to sample for confirmation
• Summarize invoices by supplier
• Look for bid splitting to avoid procurement system
• Compare invoices to master approved vendor list
• Stratify vendor balances
• Match subsequent period payments against balances to identify any unmatched and possible unrecorded liabilities
• Test for duplicate payments
• Perform purchases cutoff

 

Revenue

 

• Test pricing and discount calculations
• Analyze sales by area, salesperson, month, account, and compare to prior year(s)
• Summarize sales by customer
• Test for missing invoices
• Test for duplicate invoices

 

Expenses

 

• Prepare analysis of expenses by account and by date for current and past year(s)
• Extract expenses over XXX
• Prepare a Benford analysis
• Test for duplicate invoices

 

Payroll

 

• Prepare analysis of payroll by account, department and by date for current and past year(s).
• Show total payroll by gross, net, deductions, and any other value field(s)
• Summarize payroll by personnel
• Compare payroll to master payroll file
• Search to see if more than one check is going into the same bank account
• Recalculate gross and net pay
• Compare hire/fire dates to any pension eligibility requirements
• Extract salaries over XXX

And remember that this is only a partial list of possibilities.

 

APPENDIX B

 

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

 

• Identify employee accounts at financial institutions that have excess numbers of credit memos. Excess credit memos can indicate diversion of funds into employee accounts.
• Identify employee accounts at financial institutions that have large deposits. This can be a leading indicator of kiting schemes.
• Compare employee home addresses, social security numbers, telephone numbers, and bank routing and account numbers to those of vendors from vendor master file. This test can reveal bogus or improperly selected vendor accounts.
• At financial institutions, compare employee social security numbers against those of deposit and loan accounts to identify those not properly coded as employee accounts.
• Analyze all debits to income accounts particularly debit memos. These are often used to offset fraudulently prepared checks.
• Monitor the activity in suspense accounts including the age of suspense items and the rolling of similar amounts. This is another type of account that is often used to hide fraudulent accounting entries. At a minimum, this can reveal operating problems that may be developing.
• Analyze claims paid by a corporate medical insurance provider. Look for duplicate payments using dates, payees, and employee names.
• Obtain a social security number listing from an outside vendor to test for valid SSNs and determine whether holder is alive or deceased. Cross-reference that SSN listing against pension disbursements to look for payments made to deceased persons. Cross-reference that SSN listing against payroll records to find any errors in your payroll records. At financial institutions, you can cross-reference that SSN listing against all depository and loan accounts to identify fraudulent SSNs provided by customers.

 

Questionable Invoices

 

• Identify invoices without a valid purchase order
• Look for invoices from vendors not in approved vendor file
• Find invoices for more than one purchase order authorization
• Identify multiple invoices with the same item description
• Extract vendors with duplicate invoice numbers
• Look for multiple invoices for the same amount on the same date
• Find invoice payments issued on non-business days (Saturdays and Sundays)
• Identify multiple invoices at or just under approval cut-off levels

 

Phantom Vendor Schemes

 

• Match names, addresses, phone numbers, and social security numbers between employee and vendor files
• Check vendor addresses against mail drop address lists

 

Kickback or Conflict-of-Interest Schemes

 

• Look for vendor prices greater than standard
• Identify price increases greater than acceptable percentages
• Check for continued purchases in spite of high rates of returns, rejects, or credits
• Look for high volume purchases from one vendor

 

Dormant Account Schemes

 

• Check that all applicable accounts have been flagged as dormant
• Identify dormant accounts with activity
• Check for transfers from dormant accounts to employee accounts
• Check changes of addresses on dormant accounts. Cross check new addresses to employee addresses.

 

Money Laundering Schemes

 

• Identify accounts with large average value of transactions. It is common for there to be a small number of high value transactions through an account being used for money laundering.
• Identify matched debit and credit transactions on the same account within a short time period
• Search for large rounded transaction values Identify multiple accounts for particular individuals
• Identify large cash deposits
• Test customer identification procedures by searching for missing data in fields such as Date of Birth, Social Security Number, Driver’s License Number, etc.
• Cross check customer addresses against mail drop address lists

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 Website: billallen.com

Audit , Best Practices , CaseWare IDEA , Data Analytics