Maximizing Journal Entry Testing Through Automation

February 2007

Maximizing Journal Entry Testing Through Automation

Automated tests can help organizations identify instances of fraud and material errors in journal entries.

Rich Lanza, CFE, CPA/CITP, PMP
President, Audit Software Professionals

Scott Gilbert
President, SLG Enterprises

Many organizations have spent great effort to document, test, and fine-tune their journal entry processes to meet compliance mandates. Even standard-setting institutions like the American Institute of Certified Public Accountants (AICPA) or International Federation of Accountants (IFAC) have issued guidance to companies on ways to manage journal entry override more effectively. However, sometimes lack of effective internal controls may lead to material errors in journal entries with just a few keystrokes. Organizations that wish to prevent or identify financial fraud and material errors should consider using journal entry automated tests. To maximize these efforts, internal auditors can learn about the different kinds of automated journal entry tests and how to perform them.

THE ADVANTAGES OF AUTOMATED JOURNAL ENTRY TESTS

Automated journal entry testing can help mitigate one of the top risks affecting financial statement audits: the top-side journal entry, a fraudulent practice where accountants manipulate finance reports to close gaps between actual operating results and results reported to the investing public.

What Constitutes a Journal Entry Test?

Journal entry testing is defined by the AICPA's Practice Alert 2003-02 (PDF, 691 KB) as the entries and other adjustments that often exist in electronic form and require the extraction of the desired data for any quality analysis. To test these entries, the internal auditor might have to use computer-assisted audit techniques (CAATs), such as report writers, software, or data extraction tools, and other system-based techniques, to identify the journal entries and other adjustments to be tested.

The practice alert also describes various journal-entry tests that would be difficult or impossible to complete for most client engagements without a computer. For example, CAATs could be used to identify transactions made at unusual times of the day (e.g., outside of regular business hours). Data extraction tools also could be used to detect entries made by unusual users, for example senior management, IT staff, or nonsensical usernames.

While conducting a journal entry test, there's a better chance of detecting fraud by analyzing 100 percent of the underlying data. In addition, these kinds of tests are in line with audit standards, such as the AICPA's Statement of Auditing Standard No. 99 (SAS 99) and its Practice Alert 2003-02 (PDF, 691 KB). Furthermore, this approach automates manual journal entry testing, freeing up the auditor's time to perform other tasks, such as gaining a better understanding of the organization and improving future tests. Finally, automated journal entry tests support audit findings and recommendations with quantitative data rather than sample selections of the data to be audited.

TESTS TO PERFORM

Fraudulent adjustments have unique identifying characteristics. Most of the time, these adjustments take place at the end of the financial reporting period or as a post-closing entry that has little explanation or no description. In addition, fraudulent journal entries may be made to unrelated, unusual, or seldom-used accounts, and might be conducted before or during the preparation of financial statements that don't have account numbers, have round numbers, or consistently end with the same number. Fraudulent adjustments also are made to accounts that:

Contain complex or unusual transactions, significant estimates, or end-of-period adjustments. For example, an employee could record fictitious business events or transactions or change the recognition timing of legitimate transactions, particularly those recorded close to the end of an accounting period.
Are prone to errors, are not reconciled on a timely basis, or contain unreconciled differences. For instance, a company might increase its revenue by not reconciling the quantity of goods shipped to the quantity of goods billed or by not reconciling expense statements on a regular basis to confirm activities charged are reasonable, allocable, and allowable.
Are associated with an identified risk of material misstatement due to fraud. For example, an employee might create fictitious revenues by recording false sales that did not occur to meet revenue targets. Because the accounting transaction created is a credit to sales with an offsetting debit to accounts receivable, its boosts the company's assets and income.

Besides the AICPA's guidance, internal auditors can perform precise computerized journal entry tests such as the ones listed below. These tests are categorized based on the five Ws — who, what, where, when, and why. The five Ws represent the main categories auditors should use to analyze data for completeness. Following is a description of the main tests auditors need to conduct under each category:

Who Entered the Journal

Summarize journal entries based on who entered the journal (i.e., the person listed as the one who typed in the journal entry) to determine if he or she is authorized to do so. Identifying who entered the data can become a bit complicated if data entry clerks are inputting the information rather than an authorized manager. This is because the auditor will have to determine who ordered the data clerk to perform the entry. Other factors that might affect the amount of time it takes auditors to summarize journal entries include the company's size — the larger the company, the longer the process can take to be completed - and whether the journal entry process is manual rather than automated.

What Was Entered

Summarize journal entries by account and repetitive extracts (e.g., more than 50 instances) and unique account sequences used in the journal entry based on the first five debit and credit postings.
Extract nonstandard or manual journal entries for further analysis rather than extracting an entry from a created system, such as an accounts payable ledger posting.
Stratify the size of journal entries based on the journal entry amount, using the debit side of the transaction.
Summarize general ledger activity on the amount field based on the absolute value of the debit or credit to identify top occurring amounts.
Create a scatter-graph of the general ledger account that includes the numbers of all transactions and debit and credit amounts separately.

When the Journal Was Entered

Extract journal entries posted on weekends and holidays.
Extract journal entries that were made immediately following the end of the fiscal-year.
Summarize journal entry credits and debits processed by day, month, and year.

Where the Journal Was Entered

Extract journal entries made to suspense accounts and summarize them based on the person entering the journal entry and their corresponding account numbers.
Extract journal entries to general ledger accounts that are problematic or complex based on past issues at the company or the industry in general (e.g., accounting journal errors subsequently corrected by accounting staff or auditors) by reviewing previous audits or by asking management to determine past issues.
Extract debits in revenue and summarize them by their corresponding general ledger accounts.

Why the Journal Was Entered

Extract all general ledger transaction amounts, such as debits or credits, that exceed the average amounts for the general ledger account by a specified percentage — five times the average is the default.
Extract journal entries that equate to round multiples of 10,000, 100,000, and 1,000,000.
Extract journal entries using key texts, such as "plug" and "net-to-zero," anywhere in the record.
Extract journal entries that are made just below set accounting department approval limits, especially multiple entries of amounts below such limits.
Extract journal entries where there is a credit to an expense account and no corresponding debit to another expense account (i.e., illustrating reclassification of expenses).
Extract journal entries where there is a debit to the revenue account and no corresponding credit to another revenue account (i.e., illustrating reclassification of expenses).
Extract journal entries with other major classification changes in the area of assets, liabilities, net worth, and unbalanced fund transfers.
Extract other major classification changes in the area of assets, liabilities, net worth, and unbalanced fund transfers.

Besides identifying fraud, internal auditors can use the five Ws when determining the accuracy of financial statements.

HOW TO PERFORM THE TESTS

The auditor can perform many of the tests described earlier by using existing data analysis tools. Standard tools available in the market include Microsoft Excel and Access. In addition, database tools also are available with functions that enable auditors to explore and analyze targeted data. Below are some of the ways to perform these tests by categorizing, analyzing, and summarizing data, regardless of the product being used.

To extract nonstandard or manual journal entries, the auditor can find the field on the journal entry file that indicates a manual journal entry. Once the auditor obtains the client's journal entry file, manual journal entries need to be isolated using this identifier. The auditor can then summarize the debit and credit amounts for all journal entries by their general ledger account number and account description.
To scan and summarize the data quickly, auditors should determine the number of journals falling into specific numeric intervals. For example, the auditor can set up specific cut-off points (i.e., intervals) by which he or she can categorize the data (i.e., separating journals into various dollar categories). The auditor can then stratify journal entry amounts to count the number of records that fall into a specific number of even intervals, providing totals summarized by stratum (i.e., a collection of sampled units defined by a specific characteristic). This will help the auditor better understand how the data is structured. The auditor can start at a specific point and set as many intervals as needed.
To summarize general ledger activity on the amount field, such as absolute debit or credit values that identify the top occurring amounts, auditors should begin by summarizing debit and credit totals by the journal entry amount, using this field to base the summary. To do this, the auditor will want to create a new field that contains the debit or credit entry. This will enable the auditor to summarize the journal entries on one amount field. The auditor should then review the summarized file and isolate the journal entry amounts that occur 25 times or more, save these records in a separate file, and join this file back to the journal entry detail to retrieve the general ledger's account number and account description. Finally, the auditors should summarize these journal entries by the journal entry amount, general ledger account number, and general ledger account description to obtain a final file showing the summarized activity for the top 25 used accounts.
To obtain a more visual picture of the data, the auditor may want to graph results. For instance, the auditor can create a scatter graph illustrating general ledger debit and credit amounts and the number of transactions for each. To create the graph, auditors can summarize the debit and credit amounts by placing the general ledger account number and account description into separate files. Second, the auditor should join both files together by the general ledger's account number and account description to produce a comprehensive summarized file. Third, the auditor can export the results into Excel using the "Chart" option in the "Insert" drop-down menu to create an X-Y scatter graph that compares the debit and credit values.
To isolate journal entries posted on weekends and holidays, the auditor can set up a separate file containing the holidays for the year and create a flag if the journal entry date matches those holiday dates. In addition, the auditor can use functions to identify journal entry dates that occur during a weekend. Auditors can use Excel to analyze the time-stamp field or to obtain a date field by using the WEEKDAY() function - from the "Insert" drop-down menu, select "Function," and search for WEEKDAY within the "Insert Function Window." For instance, WEEKDAY(A1) will convert date field cell A1 into the day of the week, using 1 for Monday, 2 for Tuesday, and so on. By selecting the top of the column containing the WEEKDAY() functions, the "Auto Filter" feature located under the "Data" menu item in Excel, can be used to filter all WEEKDAY(Date_Field) values that are equal to the program's default values of 6 or 7.
To extract journal entries that equate to round multiples of 10,000, 100,000, and 1,000,000, use the MOD() function, which provides the remainder after the auditor divides a number by a 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, because this would be the remainder of dividing $10,422 by $1,000. Or, if cell A2 had $100,000 in it, then MOD(A1,1000) would result in a zero value, which would indicate a round number. Once the auditor uses the MOD() function for every amount posted in the journal entry, he or she can filter all zero items using the "AutoFilter" feature. Note: the function would be written as MOD(A2, 10000) for round multiples of $10,000.

LOOKING FORWARD

When looking at some of the recent large-scale frauds, such as WorldCom, management override around the journal entry process was the key contributing factor. This is to be expected, because the easiest route to changing the books and records is for executive management to post a top-side journal entry. Though it's always possible to make the adjustments in the sub-ledgers (e.g., fixed assets and sales journals), this requires more collusion with other organizational departments. Therefore, the top-side entry is still the best way to commit the financial statement fraud, and the automated test is one of the best known detection agents.

Oftentimes, data analysis is considered an IT audit initiative while journal entry testing is viewed as a financial audit activity. In reality, data analysis and journal entry testing should be combined to develop an integrated audit approach that enables companies to analyze data from a business perspective. By using this collective approach to test journal entries, companies can help identify fraudulent transactions or material errors. In addition, automated journal entry tests support audit findings and recommendations with quantitative data rather than sample selections and can analyze 100 percent of the underlying data, thus detecting fraud in journal entry processes and freeing up auditors for other tasks.

Rich Lanza, CFE, CPA/CITP, PMP, president of Audit Software Professionals, has more than a decade of experience in the audit and assurance technology field. Lanza is experienced in providing personalized coaching expertise in automating report systems. He frequently speaks at industry events, serves as a columnist for several professional journals, and has authored 13 publications.

Scott Gilbert, president of SLG Enterprises, possesses 15 years of business and data analysis experience in solving business problems that involve complex data issues using various software tools like SAS, ACL, IDEA, ActiveData, Access, Excel, SQL, and Visual Basic. Gilbert is a former manager within Ernst & Young's Technology and Security Risk Services group.

Internal Auditor is pleased to provide you an opportunity to share your thoughts about the articles posted on this site. Some comments may be reprinted elsewhere, online, or offline. We encourage lively, open discussion and only ask that you refrain from personal comments and remarks that are off topic. Internal Auditor reserves the right to edit/remove comments.

To make something bold:
<strong>Text to bold</strong>

To make something italic:
<em>Text to italicize</em>

To make a hyperlink:
<a href="URL">Text to link</a>