This site uses cookies to store information on your computer. Some are essential to make our site work; others help us improve the user experience. By using the site, you consent to the placement of these cookies. Read our privacy policy to learn more.
Worldwide leaders in public and management accounting
AICPA:
Auditors know that journal–entry testing is one of many procedures they can use in addressing client data completeness and, hence, audit risk (see AU–C Section 240, Consideration of Fraud in a Financial Statement Audit). What auditors and other CPAs might not know is how to use Microsoft Excel to test journal entries.
For the many CPAs who use Excel through a Microsoft Office license, the spreadsheet application may prove an appealing choice for use in journal-entry testing. Excel can perform any number of computer-assisted auditing techniques (CAATs) for no additional cost beyond the Office licensing or subscription fee.
Before proceeding, it’s important to note that auditors’ work involves judgment. The procedures described in this article outline just one of many ways journal-entry testing can be completed. In general, auditors must consider multiple factors (e.g., control environment, availability of client’s data enabling the use of CAATs) in performing journal-entry testing during audit engagements.
With those caveats in mind, let’s jump into an example. Imagine an accounting practice called Just4this Example Accounting that provides tax and audit services to small and midsize businesses. The firm’s 10 professionals are not trained in using a specialized audit software to apply CAATs, but the team has access to Microsoft Office and uses Excel extensively.
One of the firm’s clients, NotReal Manufacturing Co. Inc. is undergoing a financial statement audit for 2019. The owners of NotReal Manufacturing are contemplating the sale of the company in the next few years; hence, steady revenue increases are important to them. In the past two years, however, the client’s industry has seen an economic decline along with revenue and profit deterioration.
The Just4this Example Accounting engagement team brainstormed possible approaches to journal-entry testing and judged the client’s accounting system capable of generating appropriate data for the use of CAATs. Specifically, the audit team was able to obtain trial balances as of 12/31/2018 and 12/31/2019, as well as the listing of all journal entries for year 2019 in the Microsoft Excel format.
Note that you can download the initial file received from the client and the final file after the audit team went through journal-entry testing procedures. You may find it beneficial to open both files and follow them while continuing with the article.
Let’s follow the process the Just4This Example team would follow for this engagement. The first step is to ensure that the population of journal entries is complete. To do this, the engagement team recalculates the 2019 trial balance numbers of each account and compares them to the 2019 trial balance provided by the client. If there are no variances, the team can reasonably conclude that the journal-entry data is complete.
To recalculate the 2019 trial balance, the team uses 2018 trial balance information and journal-entry debits and credits for 2019. In the Excel file received from the client, the audit team starts by inserting columns in the trial balance sheet, adding formulas to calculate the total impact of debits and credits from the journal-entry sheet for each trial balance account, and recalculating 2019 trial balance amounts.
To insert three new columns between columns "2018 TB" and "2019 TB," the team selects columns D, E, and F, right-clicks, and selects Insert. This adds three columns (new columns D, E, and F). The team then enters the new column headers "Journal Entry Debits," "Journal Entry Credits," and "Recalculated 2019 TB" in cells D1, E1, and F1, respectively, as shown in the screenshot "Recalculating Trial Balances" below.
The formulas in each column use the following format, which specifically shows the formulas used in row 2:
Column D: =SUMIF(‘JE Data 2019′!A:A,A2,’JE Data 2019’!F:F)
Column E: =SUMIF(‘JE Data 2019′!A:A,A2,’JE Data 2019’!G:G)
Column F: =D2-E2+C2
Then, the team compares the recalculated 2019 trial balance to the original 2019 trial balance provided for the audit. To accomplish this, the team adds a formula in column H (rows 2-45) and adds a header for this column in cell H1 ("2019 TB Check"), as shown in the screenshot "Adding a Variance Column" below.
Column H: =F2-G2
The variance column shows zeros for all accounts, so the team concludes that the journal-entry data is complete. Note that some account differences may show a true zero as a dash while others may show it as "0.00" or "(0.00)." This is likely due to small rounding differences between the recalculation and the provided trial balance, but the final variances are still deemed as zeros.
One more check done by the audit team is the totaling of all debits and credits to verify that they balance. To add totals, the team adds formula =SUM(C2:C45) in cell C47 and then copies the cell formula to cells D47-H47, as shown in the screenshot "Totaling and Balancing Debits and Credits" below.
The total of debits equals the total of credits for the journal-entry data. The 2018 and 2019 trial balances also show a net zero total. Both provide additional evidence that the journal-entry data is complete.
One other test that could be performed is the summarization of debits and credits for each individual journal entry to ensure debits equal credits for each entry. However, as the grand total debits and credits were already determined to be equal, we will skip the test for this example.
Next, the audit team proceeds with selecting individual journal entries for testing. It is important to note that there are multiple ways to do sample selection. While some selections are targeted (e.g., based on dollar amounts), others call for selecting random journal entries for testing (also known as unpredictability in journal-entry testing).
Selecting targeted journal entries can be based on multiple criteria and should address fraud risks identified during the audit. Below is a nonexclusive list of targeted journal-entry tests:
Based on identified fraud risks and available journal-entry data, the audit team decides to perform the selection of journal entries using criteria shown in the first, second, and fifth bullet items above.
To assess the journal-entry (dollar) impact on income, the team needs to calculate the income statement impact of each journal entry. On the journal-entry data sheet, the team adds a new column, "Income Statement Impact," and shows debits or credits for income statement accounts only (i.e., for balance sheet accounts, the calculation returns zeros).
In our example, income statement accounts are numbered 40,000 and above.
Column H: =IF(A2>=40000,F2-G2,0), as shown in the screenshot "Assessing Journal-Entry Impact" below.
Column H shows the income statement impact (debits and credits) for all journal entries. The total of column H represents the net income for 2019, as shown in the screenshot "Income Statement Impact" below.
The team then proceeds with making journal-entry selections.
The audit team decides to review any journal entries with an income statement impact of more than $10,000. A way to see debits or credits to the income statement accounts is to filter them in column H on the journal-entry sheet. However, this will show every line item for each journal entry, which may not be the most efficient approach. Another way is to summarize column H by journal-entry number first. To accomplish this, the audit team creates a PivotTable of the journal-entry data, as follows:
At this point, a new sheet appears with the PivotTable created. Note: This is sheet Test A in the final Excel file available for download with the online version of this article. The engagement team now needs to select journal-entry data to be included in the PivotTable. The created sheet has PivotTable Fields on the right side. The team drags "Journal No." to Rows and "Income Statement Impact" to Values, as shown in the screenshot "Dragging to Rows and Values" below.
This creates a view with a total income statement impact of each journal entry. Right-click in any cell in column B, select Sort, then Sort Smallest to Largest, as shown in the screenshot "Sorting the Sum of Income Statement Impact" below.
The team reviews the result by looking at the top and bottom of the sorted list and notes that three journal entries, No. 48, No. 609, and No. 610, are more than $10,000, as shown in the screenshot "Impacts Over $10,000." These three entries will become part of the selected sample for testing.
To select journal entries with rounded numbers over $10,000, the engagement team needs to take several steps, using the filter function on the journal-entry data sheet:
The team discovers that a number of journal entries meet the selection criteria: No. 15, No. 189, No. 339, No. 495, No. 609, and No. 610. The team also notes that two entries, No. 609 and No. 610, were already identified in the test for significant income statement impact.
The audit team also wants to check if any journal entries were posted on major holidays. Major holidays for year 2019 were as follows: 1/1/2019, 1/21/2019, 2/18/2019, 5/27/2019, 7/4/2019, 9/2/2019, 10/14/2019, 11/11/2019, 11/28/2019, 11/29/2019, and 12/25/2019. To cross-reference those dates with journal entries, the team does the following: labels column M as "Holidays," clicks Ctrl+G and goes to cell M4201, and then lists holidays in date order in cells M4201-M4211, as shown in the screenshot "Listing the Holidays" below.
This selection process gives the audit team a number of journal entries: No. 57, No. 120, No. 121, No. 310, No. 311, No. 312, No. 390, No. 478, No. 479, No. 549, No. 550, No. 609, and No. 610. Some of these entries appear to be routine and may not require any special follow-up (other than an explanation of why they were posted on a holiday); so, the engagement team decides to further investigate only journal entries having an income statement impact: No. 311, No. 390, No. 478, No. 609, and No. 610. The team again notes that entries No. 609 and No. 610 have already been selected for testing in other tests.
Besides targeted journal-entry testing, it is also expected that the audit firm will select random journal entries for testing. Such a selection process can be relatively simple. For example, the team picks the following entries by scrolling through the list of journal entries and haphazardly selecting some for a test sample: No. 41, No. 119, No. 176, No. 305, and No. 480.
Once the audit team has selected journal entries for testing, it takes steps to finalize procedures. The team requests appropriate documentation and explanations from the client. Two journal entries selected for testing have several red flags associated with them: No. 609 and No. 610. The team has received appropriate information and made necessary conclusions related to the journal-entry testing, assessed the impact of these findings, and, if needed, performed additional testing procedures.
In conclusion, as this use case shows, the Just4this Example Accounting professionals were able to successfully apply CAAT to journal-entry testing with the assistance of Microsoft Excel and without the need for specialized software. Using Microsoft Excel offers an alternative for small and medium-size audit firms to consider when they perform journal-entry testing or other CAAT during audits.
About the authors
Inna Voytsekhivska, Ph.D., is an assistant professor of accountancy at Western Michigan University. Igor Voytsekhivskyy, CPA, CIA, is a CFO for a Michigan-based not-for-profit in the health care information space. To comment on this article or to suggest an idea for another article, contact Jeff Drew, a JofA senior editor, at Jeff.Drew@aicpa-cima.com or 919-402-4056.
AICPA RESOURCES
Article
"Using Excel and Benford’s Law to Detect Fraud," JofA, April 2017
Auditing standard
AU-C Section 240, Consideration of Fraud in a Financial Statement Audit
Private Companies Practice Section and Succession Planning Resource Center
The Private Companies Practice Section (PCPS) is a voluntary firm membership section for CPAs that provides member firms with targeted practice management tools and resources, including the Succession Planning Resource Center, as well as a strong, collective voice within the CPA profession. Visit the PCPS Firm Practice Center.
IMTA section and CITP credential
The Information Management and Technology Assurance (IMTA) section supports AICPA members who provide services in the areas of information security and cyber risk, privacy and IT risk management, business intelligence, and emerging technologies. CPAs may also pursue the Certified Information Technology Professional (CITP) credential, which demonstrates an individual has the expertise to advise organizations on how to maximize information technology to manage their business. Access to the IMTA’s tools and resources is included with AICPA membership.
LEARNING RESOURCES
For more information or to make a purchase or register, go to us.aicpa.org/cpe-learning or call the Institute at 888-777-7077.
AICPA/CPA.com Digital CPA Conference 2021
Dec. 5-8, Nashville, Tenn.
Digital CPA is for practitioners curious about technology and its impact on the accounting landscape. Together we’re challenging business models, transforming practice areas, and driving innovation across the profession.
Practitioners Symposium and Tech+ Conference and the AICPA National Advanced Accounting and Auditing Technical Symposium (NAAATS) at AICPA ENGAGE 22
June 6-9, Las Vegas
Learn the latest about the technology and audit trends shaping the accounting profession. The Practitioners Symposium and Tech+ Conference and NAAATS are two of the nine conferences that make up the must-attend accounting conference of the year.
Streamlined Excel Reporting Series
The Streamlined Excel Reporting Series includes four webcasts that will guide you through step-by-step demonstrations on how to create and use PivotTables to increase your productivity and effectiveness as a finance professional.
Due to a national paper shortage, we replaced the November printed copy of the magazine we normally mail with a digital issue that you can access here.
Starting this month, all Association magazines — the Journal of Accountancy, The Tax Adviser, and FM magazine (coming in February) — are completely digital. Read more about the change and get tips on how to access the new flipbook digital issues.
SPONSORED REPORT
Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.
SUBSCRIBE
Be the first to know when the JofA publishes breaking news about tax, financial reporting, auditing, or other topics. Select to receive all alerts or just ones for the topic(s) that interest you most.
NEWS APP
This quick guide walks you through the process of adding the Journal of Accountancy as a favorite news source in the News app from Apple.
© Association of International Certified Professional Accountants. All rights reserved.
Reliable. Resourceful. Respected.
