Print


Improving Your Audits with Excel-Based Analysis Techniques

By Richard B. Lanza

Year after year, many audits and reviews are done without much use of data analysis. Use of such techniques can improve engagement efficiency, lower risks in these engagements through improved audit quality and provide the basis for more-quantifiable management letters.

Some of the common reasons these tools are not used is that client data may be difficult to obtain and the tools themselves may require unbillable training time. However, almost all client systems can export their data to Microsoft Excel spreadsheets and, once there, most CPAs have great comfort in using Excel for sorting, filtering and performing other types of analysis.

Excel can provide a superb foundation for learning data analysis. Some example tests include:

  • Recalculating accounts payable aging ledgers using Excel formulas rather than manually.
  • Selecting accounts receivable sample items by sorting ledgers by amount and completing a stratified sample. These items can then be automatically imported into Word document confirmation forms using the Mail Merge feature in Word.
  • Filtering inventory ledgers for unit cost prices that exceed current sales prices for use in lower of cost or market testing.

In looking at some of the recent large-scale frauds, such as WorldCom, management override around the journal entry process was the key contributing factor (according to an Oct. 30, 2002, article in The Wall Street Journal). Data analysis can provide a great defense against override by performing a more extensive search for unusual activity.

As highlighted in the AICPA’s Practice Alert 2003-02, “Journal entries and other adjustments oftentimes exist only in electronic form, which requires extraction of the desired data for any quality analysis. In an IT environment, it may be necessary for the auditor to employ computer-assisted audit techniques (‘CAATs’) (for example, report writers, software or data extraction tools, or other systems based techniques) to identify the journal entries and other adjustments to be tested.”

The Practice Alert goes on to explain various journal-entry tests to complete that would be difficult or impossible for most client engagements without a computer. These tests include filtering to entries that are made at unusual times of day (that is, outside regular business hours); that are made by unusual users, blank or nonsensical user names, senior management, or the IT staff; that are made to unrelated, unusual or seldom-used accounts, or that contain round numbers.

To perform these types of tests, the following steps can be taken in Excel:

  • For unusual times of day, an approach would be to obtain the time-stamp field for analysis. Another popular approach for testing unusual days for posting is to get a date field and then use the WEEKDAY() function. For instance, WEEKDAY(A1) will convert a date field cell A1 into the day of the week (1 is a Monday and 7 is Sunday). Then, by selecting the top of the column containing the WEEKDAY() functions, the Auto Filter feature (under the Data menu item in Excel) can be used to filter all WEEKDAY(Date_Field) values that are equal to 6 or 7.
  • To get a quick sense of the people entering the journal entries, the Subtotal feature (also under the Data menu) can be used to subtotal all account posting amounts based on the user column. This feature simply asks for the column upon which to subtotal (i.e., EMPLOYEE_ID) and then the columns to total (i.e., DEBITS and CREDITS).
  • In order to assess accounts being used for unusual activity, the Subtotal feature can be used to get a total of the amounts per account and also provide transaction counts per account, which can be used to identify lesser-used accounts.
  • For identifying round numbers, the MOD() function can be used. The MOD() function divides the number by a provided divisor and then lists the resulting value that is not divisible by the divisor. For example, say that $10,422 is in cell A1 and the function MOD(A1,1000) is placed in cell B1. The result in B1 would be $422, as this would be the remainder after dividing $10,422 by $1,000. As another example, if cell A2 had $100,000 in it then MOD(A1,1000) would result in a zero value, indicating a round number. Once this MOD() function is used for every amount posted in the journal entry, all zero items can be filtered using the AutoFilter feature noted above. Please note that for round multiples of $10,000, the function would be written as MOD(A2, 10000).

In this engagement season, give data analysis a try by starting with some simple tests in Microsoft Excel, thereby gaining confidence in using these efficient procedures and otherwise improving your services.

Richard B. Lanza, CPA, CFE, PMP, is a member of the Society’s Technology Assurance Committee and is president of Cash Recovery Partners, LLC. He also founded www.AuditSoftware.net, a Web site devoted to giving free advice on using software to improve auditing services.