Take My Manual Audit, Please

It's time to audit through (not around) the computer.

Executive Summary
  • IN TODAYS BUSINESS WORLD, where data increasingly are in electronic format, manual audits may not be good enough. Powerful software applications that audit through rather than around the computer can examine raw accounting data faster than is possible manually and in as much detail as desired.
  • SUCH SOFTWARE REQUIRES minimal training, does not require special data translation and has no file size limitations.
  • THESE PROGRAMS help reduce the cost of audit engagements and open the door to new consulting opportunities.
  • ALTHOUGH AUDIT SOFTWARE can be applied to every phase of the engagement, manual audit activities should be the first to be replacedthey are, after all, the proverbial low-hanging fruit.
  • SINCE AUDIT SOFTWARE lends itself to 100% testing, not just sampling, of client data, other benefits are available:
    • Auditors develop a better understanding of clients businesses, equipping them to add value on client engagements.
    • Efforts to extrapolate information from errorsa common effort when manually auditing databecome unnecessary.
RICHARD B. LANZA, CPA, is a vice-president of audit technology at AuditWatch, Inc., Falls Church, Virginia. He trains and consults for auditors in both ACL and IDEA software. He is the author of 101 ACL Applications: A Toolkit for Todays Auditors, published by GAP Publications. His e-mail address is rlanza@earthlink.net .

When auditors tackle an engagement in which most, if not all, of a clients data are in electronic format, they generally audit around the computerthat is, they print out the pertinent information and then scrutinize the printed page. Such a manual procedure is okay as far as it goes, but in todays business world, where data increasingly are in electronic format, manual examinations may not be good enough.

However, there is another option: powerful software applications that audit through rather than around the computer. Such software can examine raw accounting data faster than is possible manually and in as much detail as desired. This article outlines the benefits of using auditing software and shows how relatively user-friendly the applications are.

The two leading auditing software packages are ACL Software and Interactive Data Extraction & Analysis (IDEA) . For information about these products, see How to Contact the Vendors, below.

How to Contact the Vendors

ACL Software
575 Richards Street
Vancouver, British Columbia,
Canada V6B2Z5
Phone: 888-669-4225
Fax: 604-669-3562
Web page: www.acl.com

Interactive Data Extraction & Analysis (IDEA)
Audimation Services, Inc.
16151 Cairnway, Suite 100
Houston, Texas 77084
Phone: 888-641-2800
Fax: 281-345-2399
Web page: www.cica.ca

The American Institute of CPAs sells a scaled-down version of IDEA (product no. 016546JA). For more information, call 888-777-7077.

Both programs require minimal training, do not require any special data translation and have no file size limitations. They provide detailed audit logs for use as workpaper documentation and allow the auditor to create a toolbox of reports for later use on other data. In addition, the programs reduce the cost of audit engagements.

Although the software can be applied to every phase of the audit, manual audit activities should be the first to be replacedthey are, after all, the proverbial low-hanging fruit and thus will provide immediate savings. For example, the initial targets should be handling footing of ledgers, selecting statistical samples, generating confirmations and identifying unusual transactions. Those activities alone will probably provide enough cost-saving efficiencies to pay for the software and training costs. But thats not all: Since audit software lends itself to conducting 100% testingnot just samplingof client data, the following other benefits are available:

  • Auditors develop a better understanding of clients businesses because they are able to effectively inspect large volumes of datathus getting a broader view of the enterprise. This prepares them to provide other value-added services.
  • Extrapolating information from errors (a common effort when auditors manually audit data) becomes unnecessary. Although statistical sampling is an option of audit software, by viewing all the data filesand not just samplesauditors can reach precise conclusions; as a result, they do not need to extrapolate that information, which is inherently less accurate.

In the following examples of the types of jobs audit software can perform, I used ACL Software to illustrate key steps; however, using that package does not imply an endorsement of the product. For ease in reference, all computer commands are printed in capital letters.

Stratification of Business Data
Range of
Inventory Balances
of Parts
Percentage of Total
Inventory Balances
Total Inventory
Up to $999 320 0.12% $18,681.29
$1,000 to $9,999 1,700 2.78% $466,570.39
$10,000 to $99,999 1,010 20.88% $3,505,380.96
$100,000 to $1,000,000 70 76.22% $12,793,770.76
Total 3,100 100.00% $16,784,404.40

One of the strongest tools of audit software is the STRATIFY command, which generates an eagles-eye view of large chunks of data. It can be graphed if necessary for inclusion in audit reports. An example of the stratification of a businesss inventory data is shown above.

After reviewing these results, the auditor may logically proceed to extract the 70 inventory parts for testing (highlighted in yellow) that are $100,000 and above (representing 76.22% of all inventory parts). The software can perform such an analysis in seconds. To illustrate the ease in running this analysis, the softwares dialog box that must be filled out to execute the command is shown on the left.

The auditor also can perform stratifications on many other kinds of data, such as accounts payable, accounts receivable and fixed assets. Performing a stratification of accounts payable checks may lead an auditor to suggest implementing a new process to pay checks.

For an example of a control test, assume an organization has a policy stipulating that the CFO approve all purchase orders (POs) over $10,000. Since most accounts payable systems record both the issuer and the approver of POs, the auditor can determine any lapse in authorization controls by commanding the program to EXTRACT all POs over $10,000 where the CFO didnt record approval. The dialog box for such a command is illustrated below.

Another benefit of audit software is its ability to pick up errors accidentally keyed into accounting software. Such errors are devilishly hard to track down manually because they tend to be so subtle. For example, if a clerk initially keys an inventory purchase quantity into the wrong category, the resulting calculations, of course, will be inaccurate. Although finding that error manually would be tedious, using the EXTRACT command would pick up the errors easily.

Below are four popular electronic audit procedures involving 100% testing. Once the tests setups are created, the software allows the user to save them, permitting a retest of the data or a testing of new, similarly formatted data in the next audit period.

  • Recalculation. One of the simplest yet most time-consuming manual activities is to total printed reports, such as a trial balance of accounts receivable. Using audit software, an auditor can accomplish this with just a few keystrokes. Another common automated tactic is to create calculated fields for purposes such as depreciation expense calculations and inventory extensions for comparison with clients records.
  • Aging. Testing a clients aging of accounts receivable or payables takes less than a few seconds.
  • Exception analysis. Its very difficult to scan printed reports manually for unusual transactions, but by using EXTRACT and a few other commands, the auditor can test for countless exceptions, such as
    • Inventory not used in over three years.
    • Fixed assets with high salvage values as compared with asset values.
    • Customer invoices older than 120 days and over $10,000.
  • Joining. By joining two separate data filessay, the inventory cost and inventory sales filesthe audit software can analyze instantly what, if any, products have been sold below cost. The panel at right shows a typical view of such a report.
  • Fraud detection. One of the most common ways an employee defrauds his or her company is by inventing a fictitious vendor and arranging for all payments to that vendor to be sent to the employees home address. With a few keystrokes, audit software can compare the addresses of employees in the human resources file with that of the vendor addresses.

The next focus of the auditor should be creating value for the client. Following are some other possibilities:

  • Performing operational audits, which may include
    • Merging check registers among locations to assist the purchasing department in maximizing volume discounts and vendor terms.
    • Extracting customers that take discounts after the allowed time frame, giving the collections department an opportunity to follow up.
    • Tagging invoices that are sent out late and then calculating the lost interest opportunity.
    • Analyzing phone bill charges for errors and misstatements.
  • Performing duplicate payment audits that look for such payments in the check register based on fields generally not considered by the accounting softwares edit checks. For instance, the accounting software may flag an item as a duplicate if the same vendor, invoice number and amount are entered, but sometimes the invoice number is entered incorrectly on the duplicate entry; as a result, the edit check will not detect the duplicate. However, an audit that arranges in sequence all payments to the vendor of the same amount with the same invoice date would.

The decision to use audit software clearly requires a significant mind-set shift by CPAs who have performed audits manually all their professional careers. In my experience, as someone who teaches accountants to use audit software, the hardest step is the first one: making the decision to try an audit program. Once CPAs make that decision, learning how to use the application is relatively easy. From then on, the learning curve is swift as the auditor finds shortcuts for even more efficiency and discovers ways to make the audit process even more effective.


Keeping client information safe in an age of scams and security threats

A look at the Dirty Dozen tax scams and ways to protect taxpayer information.


How to create maps in Excel 2016

Microsoft Excel 2016 has two new mapping capabilities. J. Carlton Collins, CPA, demonstrates how to make masterful 2D and 3D maps in Excel 2016.


News quiz: IRS enforcement, a hot job, and audit value

The IRS’s 2016 Data Book, a “hot job” of particular interest at this time of year, and insight into how executive and audit committees view the insights from financial statement audits received attention recently. See how much you know with this short quiz.