The 21st Minute


Conducting Attribute Testing with Data Analysis Software

By Don Sparks, CIA, CISA, ARM

Q: As an internal auditor, we are approached on a regular basis by software providers offering products that do not appear to have real differentiating features. What do you find is a unique feature of some software that is not readily available in other products?

A: That is a really good question. A more highly touted distinction of purpose built auditing data analysis tools is to protect the client’s data after a copy is imported into the software. This feature in itself is a significant differentiating feature from generalized spreadsheet and database programs.

Here is one of my more frequent responses and the one that I provided the person at the chapter meeting. Some data analysis products can do so much more.

Once you narrow (sample, filter, extraction, etc.) your data to a test set, you can actually create a working paper by appending (inserting) data fields for auditor input. This allows space for the auditor to document the completion of audit program steps without the need to export to another utility. And by staying within the software program, all of the other features are readily available.

Editable fields are valuable for:

  • Creating a tickmark box [using your standard tickmark explanations]
  • Entering comments/data [working paper documentation]
  • Amending fields, such as when evaluating monetary unit samples
  • Performing new calculations and ratios derived from protected fields in the database

Editable fields are modifiable fields that can be added through Field Manipulation, Direct Extraction or Import Assistant (with Delimited, Fixed length, EBCIDIC, and dBASE files).

In order to help you distinguish Editable fields from protected fields, by default, our solution displays editable data in a blue font. You may change the display color for data in an Editable field through View > Options > Grid Settings.

Also, by default, our software records any entries/changes you make to the Editable fields data in the History. To stop the history file from recording these, check the Do not record field changes made through editable fields check box in the View > Options menu.

If you make any changes to the data in an Editable field, any index that is based on the Editable field becomes invalid. To validate the index, re-index the database.

Here is an example of how this would work in the field. Through observation and inquiry, an internal auditor learns that in one situation a salesperson did not adhere to the under 90 day return of a credit balance on cancelled business. The refund was almost one year old when the auditor found this transaction through other tests. This finding sparked the auditor's curiosity. If one credit balance exists, could there be more?

The auditor decided to find out and basically took the following steps:

1. The auditor decided to find out who in the organization is responsible for monitoring return of balances on canceled business. If someone was responsible, they may have a standard report distributed by information services from which the auditor could satisfy their questions.

The auditor was surprised to learn that since it was a credit balance, no one had been appointed responsibility. Also, levels of approval had not been established, i.e., a fieldman had complete authority to approve a credit balance return for any amount. This was consistent with the transaction but very disturbing for an auditor.

2. The auditor completed a data request form and obtained the authorization of the CEO, to whom the auditor had an administrative reporting relationship. With the assistance of IT, the auditor was able to import and join two data files, the open receivable file and the account status from the account manual file. The auditor filtered on only canceled account balances with a credit balance. Further analysis disclosed hundreds of open credit balances on canceled business some as old as 4 and 5 years.

3. The auditor did not stop here, though it appeared to the auditor a serious lapse in internal controls did in fact exist. The auditor decided to sample some of the transactions and pull the support files to see what types of patterns could be detected. Before putting this issue on the table for discussion, the auditor did not want to overlook the potential for a valid business reason to justify holding credit balances.

4. The auditor prepared the following working paper using the data analysis software tool.

(a) This is part of the data imported from the client's open accounts receivable file. The auditor can read this data only, that is the data cannot be changed by the auditor.

(b) The auditor determined the age of the transaction by appending a new data field called AGE_DAYS and used this formula so the software could do the calculation automatically [@age(CANCEL_DATE,"20110622")].

(c) The auditor wanted to document the testing performed so a tick mark field was appended, a multistate field with the name VALID_CREDIT. The auditor set the tick marks a "check mark" would mean that the test step was completed without exception, an X would represent that the test was completed with a valid exception and a question mark would represent the auditor was unable to complete the test step.

(d) Finally, the auditor decided to insert a comments field to hold a brief explanation unique to the transaction being examined. The auditor used the data manipulation feature to append an editable field using the name AUDITOR_COMMENTS and set the field length to 200 characters.

Best Practices , CaseWare IDEA

Posted By


Related Posts
Uncovering Fraud Using Fraud Data Analytics
May 15 The days of exploring data, hoping to stumble across a fraud scheme have ended. In fact, auditors are now expected to integrate fraud detection into the audit p...
Tech Tip: Transforming Your Data
May 15 We are inundated with data on a daily basis. Here are a few time-saving tips for transforming your raw or jumbled data into useful information.   Adding...
Task Automation Using IDEA
Apr 16 Audit scenarios rarely require an entirely unique process. Having a preferred set of tests ready to go is a great time saver, but that can be further improved i...

This website has been designed for modern browsers. Please update. Update my browser now