Chapter 7: Audit Data Analytics

Problem Statement

This case, Red Cedar Furniture Company, introduces students to IDEA and requires them to use a variety of IDEA features to analyze and evaluate the adequacy of the allowance for doubtful accounts.

Students begin the case by importing the data files into IDEA. Students then reconcile the underlying data with the general ledger. Next, students join various data files to create accounts receivable file and reconcile accounts receivable to the general ledger. Students then use IDEA to create accounts receivable aging. For problem customers, students export a transactions file back to MS Excel, determine the difference between invoice date and the date the invoice is paid, and analyze how the customer's payment history changes over time. Students then use this information to evaluate the allowance for doubtful accounts. Students complete the case by evaluating internal controls over authorizing credit and whether credit is granted to customers in excess of their credit limits.

Part I

You have been assigned to the audit of accounts receivable for Red Cedar Furniture Company (RCFC). In preparation for the audit, you have extracted the following information from the client's database in MS Excel format.

Information*

File Name

Annual Sales Information

RCFC Sales File.xls

Annual Cash Receipts Information

RCFC Cash Receipts File.xls

Annual Sales Adjustments Information

RCFC Sales File.xls

Customer Master File

Customer Master File.xls

* Customer numbers for furniture dealers are currently between 1 and 200. Customer numbers in the 500 series represent discount chains. Customer numbers in the 600 series represent custom cabinet customers.

Further, Julia Anderson, Red Cedar's CFO has prepared the following summary of accounts receivable transactions over the past two years. As a first step, you have agreed to the summary amounts to the general ledger without exception, and the 12/31/2022 and 12/31/2021 balances agree to the prior auditor's working papers. The 12/31/2022 transactions also tie out to the prior auditor's working papers. Further, you can assume that you have performed tests of controls; you have assessed control risk as low for the following transaction level assertions.

Existence and Occurrence

Completeness

Valuation and Allocation

Presentation and Disclosure (Classification)

Summary of Accounts Receivable Transaction for the Two Years Ended December 31, 2023 and 2022

Importing the Excel Files with Client Data into IDEA

The first thing you will need to do in order to audit RCFC's data is to import the MS Excel files with the client's data (see above) into IDEA. Second, you should check the control totals provided by the client to the underlying data you have obtained from the client, so that you know that the data is complete. You should also evaluate the completeness of the data and determine if data needs to be "cleaned."

Requirement for Part I: Develop working paper documentation that indicates you tested the control totals for Sales Transactions, Cash Receipts Transactions, and Sales Adjustment Transactions for the year ended December 31, 2023, and draw a conclusion about the completeness of the data. Also, prepare working paper documentation about the relevance and reliability of the data using the format in Illustration 7.5.

Part II

Joining Data Files and Creating a Transactions File for All Revenue Process Transactions

IDEA works with data files extracted from a client's database. You now have three IDEA files: one for sales, one for sales adjustments, and one for cash receipts. The next step is to join these files. Your goal is to build a history of each transaction for each customer. Note that each file has a record of the underlying invoice number, in addition to the customer number. This will allow the auditor to build a history of each transaction throughout the year. You will want to join the sales file, the cash receipts file, and the sale adjustments file into one database for 2023 transactions with all revenue process transactions in one data file.

Creating New Fields, Extracting Data Files and Creating an Accounts Receivable File

The next step is to create a file with sales invoices with outstanding amounts. First, you will want to calculate the amount outstanding for an invoice. The next step is to create a file with only the outstanding invoices. Note that you want to eliminate the fully paid invoices, but you do not necessarily want only invoices with outstanding amounts greater than zero. If a customer has overpaid an invoice and has a credit balance, you want to keep these transactions.

Requirement for Part II: Develop working paper documentation that indicates you tested the control totals for accounts receivable at December 31, 2023.

Part III

Auditing Valuation and Allocation of Accounts Receivable

For the next aspect of this case, you must draw a conclusion about the adequacy of the allowance for doubtful accounts. Develop a set of working papers that support your logical conclusion about the adequacy of the allowance for doubtful accounts; note that there is not a "right" answer with respect to the adequacy of the allowance for doubtful accounts. Instead, support your conclusion with the best evidence possible.

If you believe that the allowance for doubtful accounts should be adjusted, you should propose a possible adjustment in journal entry form as part of your conclusion.

Discussion with Julia Anderson indicates that the company has aggressively pushed to increase sales in the last few years. In addition to the data files, your instructor will provide you with a file containing the results of a discussion with Julia Anderson about specific customers.

Aging

You might consider the following uses of IDEA as you prepare your analysis of the adequacy of the allowance for uncollected accounts. IDEA has an aging function that you should be able to use on the Accounts Receivable file. Once you have completed the aging, you can double-click on an aging classification and IDEA will show you the specific invoices and customers that are in that aging category. It might be helpful to create a file with the full transaction history of companies that have receivables that are over 90 days outstanding or another break point that represents your expectation of an older, outstanding accounts receivable.

Some analyses can be done in MS Excel instead of IDEA. Consider the following. First, open a database of customers with older outstanding accounts receivable that you have created in IDEA and export this file to MS Excel. You can select fields that you want to export, but you might export all the data. Ultimately, you want to create a file in MS Excel that has the annual history of 2023 transaction for the customers with old outstanding receivables.

Now do a Data Sort in Excel. Sort the data by this size of receivables (Accounts Receivable at December 31, 2023) in descending order. If no cash has been received, IDEA will leave the cash receipts date blank. Change the date on the cash receipts where no cash has been received to year-end (12/31/2023). Now create a new key piece of data. Calculate the number of days between the invoice date and the date cash is received. You might title this column Collection Days. Now resort the data, sorting the data first by the "customer number" and then by "collection days" in ascending order. You now have a database that shows each customer's history in terms of how long receivables are outstanding for each customer. This can be very helpful in evaluating the customer history for customers that might be included in the allowance for doubtful accounts.

Requirements of Part III: Evaluate the adequacy of the allowance for doubtful accounts for RCFC. Use the logic outlined in Illustration 7.6. Are there any companies that you believe have business reasons for falling outside of the auditor's initial expectations? Based on your analysis, do you need to propose an audit adjustment? If so, draft the proposed adjustment.

Part IV

Now that you have been working with IDEA, here is a challenge to test your creativity. You have also obtained the customer master file from RCFC. The customer master file has information on customer credit limits.

Requirements of Part IV: Evaluate RCFC's internal controls that ensure that customers do not purchase more than their authorized credit limits. If you believe internal controls need to be improved, draft a management letter comment with your findings and recommendations.