Journal-entry testing using Excel

In this example walk-through, learn how to use Microsoft’s spreadsheet application to perform several tasks, including sampling.
By Inna Voytsekhivska, Ph.D., and Igor Voytsekhivskyy, CPA

Journal-entry testing using Excel
Image by carloscastilla/iStock

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.

AN EXCEL EXAMPLE

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.

TEST COMPLETENESS OF JOURNAL-ENTRY DATA

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.

voyt-exhibit-recalculating-trial-balances

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.

voyt-exhibit-2-adding-variance-column

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.

voyt-exhibit-3-totaling-balancing-debits-credits

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.

SELECT JOURNAL ENTRIES FOR TESTING

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:

  • Significant income statement dollar impact: Select journal entries with an income statement impact larger than a threshold. The threshold is determined by the audit team and usually has a relationship to audit materiality.
  • Rounded number journal entries: Select journal entries with rounded numbers. Rounded numbers usually indicate a manual journal entry or an estimate.
  • Benford's law analysis: Select (a number of) journal entries that do not comply with the first-digit law (i.e., the law of anomalous numbers).
  • Unusual account combinations: Select journal entries that appear to have an unusual (not typical for this client) combination of accounts.
  • Unusual journal-entry dates: Select journal entries that were prepared (posted) during holidays or weekends, for example.
  • Unusual journal-entry author: Select journal entries that were prepared by an employee who usually does (should) not prepare them.

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.

ASSESS JOURNAL ENTRIES

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.

voyt-exhibit-4-assessing-journal-entry-impact

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.

voyt-exhibit-5-income-statement-impact

The team then proceeds with making journal-entry selections.

JOURNAL ENTRIES WITH SIGNIFICANT INCOME STATEMENT IMPACT

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:

  • Click anywhere in the journal-entry data table on the respective sheet.
  • Select the Insert tab and click the PivotTable icon.
  • In the PivotTable dialog box, keep default settings and click OK.

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.

voyt-exhibit-6-dragging-rows-values

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.

voyt-exhibit-7-sorting-sum-income-statement-impact

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.

voyt-exhibit-8--impacts-over-10000

JOURNAL ENTRIES WITH ROUNDED NUMBERS

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:

  • Add a new formula in column I ("Round Numbers") and copy it to all rows: =IF(F2>10000,IF(F2-ROUND(F2,-3)=0,"True","False"),"False"). This formula evaluates numbers in column F ("Debits") and returns True if there is a round number over $10,000.
  • Select the DATA tab and click the Filter icon. Be sure to have the cursor in the first row of the table; it does not matter which column as long as it has data.
  • The filter icons appear in each column of the first row (header) of the table. The engagement team selects the filter in column I ("Round Numbers"), unselects False, and clicks OK. The resulting filtered list shows only journal entries with round numbers over $10,000, as shown in the screenshot "Items Meeting the Selection Criteria" below.
voyt-exhibit-9-items-meeting-selection-criteria

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.

JOURNAL ENTRIES MADE ON HOLIDAYS

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.

voyt-exhibit-10-listing-holidays
  • Labels column J as "Holiday match" and adds a new formula in cell J2 (=MATCH(D2,M$4201:M$4211,0) and copies it to all rows in the journal-entry table.
  • Selects the filter dropdown in column J on the journal-entry data sheet. In the filter list, unselect #N/A at the bottom of the list, as shown in the screenshot "Sorting the Holiday Matches" below.
voyt-exhibit-11-sorting-holiday-matches

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.

UNPREDICTABILITY IN JOURNAL-ENTRY TESTING

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.

COMPLETE AUDIT PROCEDURES FOR SELECTED JOURNAL ENTRIES

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.

SPONSORED REPORT

Get your clients ready for tax season

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.

100th ANNIVERSARY

Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.