PROBLEM 14-36

COMPREHENSIVE CASE: MT. HOOD FURNITURE
USING IDEA 2004 SOFTWARE FOR THE AUDIT OF VARIOUS ASPECTS OF ACCOUNTS RECEIVABLE

Part I

You have been assigned to the audit of accounts receivable for Mt. Hood furniture. In preparation for the audit you have extracted the following information from the client’s database in excel format.
|Information |File Name |
|Annual Sales Information |Mt Hood Sales Adj File.xls |
|Annual Cash Receipts Information |Mt Hood Cash Receipts File.xls |
|Annual Sales Adjustments Information |Mt Hood Sales File.xls |
|Customer Master File |Customer Master File.xls |

The first thing you should do is go to the file menu, and go to “set working folder.” Set the working folder on the a: drive, d: drive, or any place that you want to save your files.

|Note: If you are going to work in the SBA computer lab, you should put these files on a floppy disk where you can store these files and other files |
|that Idea will create. If you are going to use Idea on your own computer, create a subdirectory for these files and other files that Idea will |
|create. |

Further, Julia Anderson has prepared the following summary of accounts receivable transaction over the last two years. As a first step you have agreed the summary amounts to the general ledger without exception and the 12/31/x2 and 12/31/x1balances agree to the prior auditor’s working papers. The 12/31x2 transactions also tie out to the prior auditor’s working papers. Further, you can assume that you have performed tests of controls and you have assessed control risk as low for the following transaction level assertions. • Existence and Occurrence • Completeness • Valuation and Allocation • Presentation and Disclosure (Classification)
This information is also included in an MS Excel file “AR Control Totals” which represents a draft working paper.

[pic]

Importing the Excel Files with Client Data into Idea

The first thing you will need to do in order to audit Mt. Hood’s data, you need to import the MS Excel files with the client’s data (see above) into Idea.

To accomplish this task you should first set the working folder. You should have the excel files that you have downloaded from the WebCT site for Accounting 492 in this folder. When you are working in the SBA computer lab you should set the working folder as the A: drive, where you will have the data files and other Idea files on a floppy disk.

Once you start the Idea software, go to the “File” menu, and select “Import Assistant.” Check the box that says “predefined formats” and highlight “Microsoft Excel.” When you click on “finish” a select file option dialog box will come up. Now find the excel files in your working folder. When you open the file Idea give you a preview of the file. Before you click on OK, check the box that reads “First Row is Field Names.” Now click OK and you have opened the first file. Do this for the Sales File, the Cash Receipts File, and the Sale Adjustments File.

If you have problems developing control totals, hit F1 to obtain the Idea help menu. Type in “Import Data into Idea”, bring up the help menu for joining databases, and then click on the step-by-step icon. This should assist you in the process.

Control Totals:
You need to check the control totals provided by the client to the underlying data that you have obtained from Mt. Hood Furniture’s data files. If you have problems developing control totals, hit F1 to obtain the Idea help menu. Type in “control totals”, bring up the help menu for joining databases, and then click on the step-by-step icon. This should assist you in the process. Set the control totals in the data files that you have imported into Idea and check the accuracy of the data provided by Julia Anderson above.

You might also choose to explore the field statistics portion of Idea. Again, consider using the help menu for “Field Statistics.”

|Requirement for Part I: Develop working paper documentation that you tested the control totals that appear on page 1 of the assignment above. |

Part II

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

Idea works primarily 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. This will build a history of each transaction. 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.

If you have problems joining the databases, hit F1 to obtain the Idea help menu. Type in join databases, bring up the help menu for joining databases, and then click on the step-by-step icon. This should assist you in the process.

Open the sales file and then click on File, Join Databases. When the dialog box comes up it should identify the sale file as the primary database. Once you see that the sales file is the primary database, then select all fields by clicking on the fields button, making sure all the fields are highlighted, and then indicate OK.

Now move on to selecting the secondary database. Select the “cash receipts file” as the secondary database. When you select fields, select only the CR_NO, CR_DATE, and CR_AMOUNT fields. This will make for a cleaner joined files with no repetitive data columns.

You will want to name the joined file in the space for file name”. Call it “Sales and Cash Receipts.”

Now click on the “Match” button to identify the fields to match. You should match INVOICE_NO(N) in the primary fields with INVOICE_NO(N) in the secondary fields, in ascending order. Click OK when this is set up and then make sure identify that you want to include “all records in primary file” before you say OK to join the two databases. You should now have a Sales and Cash Receipts file as a subset of the Sales file with 1,067 records. You have dropped cash receipts for sales made in the prior year. Don’t worry. You are auditing 2003.

Open the Sales and Cash Receipts file and lets start the process one more time to join the sales adjustments with this file. Once the Sales and Cash Receipts file is open, again select File, Join Databases from the file menu. This should show the Sales and Cash Receipts files as the primary database. Now select the fields from the primary database. When you click on ”fields”, all the field should be highlighted.

Now move on to selecting the secondary database. Select the “sales adj file” as the secondary database. When you select fields, select all the fields except the INVOICE_NO field and the CUSTOMER_NO field. This will make for a cleaner joined file with no repetitive data columns.

You will want to name the joined file in the space for file name”. Call it “2003 Transaction File.”

Now click on the “Match” button to identify the fields to match. You should match INVOICE_NO(N) in the primary fields with INVOICE_NO(N) in the secondary fields, in ascending order. Click OK when this is set up and then make sure identify that you want to include “all records in PRIMARY files” before you say OK to join the two databases. You should now have a 2003 Transaction file as a subset of the Sales file with 1,067 records. You now have one file with all the revenue cycle transactions, neatly saved in one place.

Creating New Fields, Extracting Data Files and Creating an Accounts Receivable File
Now you want to create a file with sales invoices with outstanding amounts.

First, you will want to calculate the amount outstanding for an invoice. Note: if you have problems go to the help menu and find the file for “Append Virtual Field Step by Step Instructions.”

From the menus select Data, Field Manipulation. When the “Filed Manipulation” dialog box comes up click on append. This will allow you to create new field. Name the new field “AR_DEC_31.” Tab over to ”type” and select “virtual numeric.” Idea will set the field length and set the decimal at “0.” When you click on the parameter field an equation editor will come up. Follow these steps when the “equation editor” box comes up. 1. Click on “Insert Database Field.” 2. Double click on the NET_SALES field. 3. Click on the “-“ sign. 4. Double click on the CR_AMOUNT field. 5. Click on the “-“ sign. 6. Double click on the SALES_ADJ field. 7. Your equation should now read “NET_SALES - CR_AMOUNT - SALES_ADJ” 8. Click on the “A+” Icon. If you have written he equation correctly it should respond with a message that you have a valid equation. 9. Click on the “√” Icon. You equation should now appear in the parameter column. 10. Click OK. You should now have a new column in your 2003 transaction file database labeled AR_DEC_31.

|Requirement for Part II: Develop working paper documentation that you tested the control totals for accounts receivable that appear on page 1 of the |
|assignment above. |

Creating an Accounts Receivable File
The next step is to create a file with only the outstanding invoices. Note, you want to eliminate the fully paid invoices, but you do not necessarily want invoices with amounts greater than zero. If a customer has overpaid and invoice, and has a credit balance, you want to keep these transactions.

From the menu, select Data, Extractions, Direct Extractions. If you need help you should select “Data Extract” in the help menu. You should select “all” for records to extract. For the file name you should name the file
“AR Dec 31”

Now click on the equation editor button to edit the selection criteria. Your selection criteria should read
“AR_DEC_31 0 “ Now when you click on OK Idea should create a file with your receivables as of 12/31/03.

As a next step you should select the control total to ensure that the data accurately reflects the amount of accounts receivable in the general ledger as of 12/31/03. (Note: Rounding difference might result in being with plus or minus $1)

Note: 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.

Part III

Auditing Valuation and Allocation of Accounts Receivable

Note: As part of this exercise this problem does not ask you to perform tests of existence of accounts receivable. Audit sampling, which is a key aspect of selecting items for confirmation of accounts receivable is covered in Accounting 493. You will return to this if you continue on and take Advanced Auditing.

|Requirements of Part III. In Part III of this case you must draw a conclusion about the adequacy of the Allowance for Doubtful Accounts. Use your |
|intelligence to develop a set of working papers that support your conclusion about the adequacy of the allowance for doubtful accounts. It is also |
|important for you to understand that your logic is more important than your answer. There is not a “right” answer with respect to the adequacy of the|
|allowance for doubtful accounts. Look at the evidence and support your conclusion with the best evidence you can. |
| |
|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 indicate that the company has aggressively pushed to increase sales in the last few years. An important aspect of recent sale growth has been the addition of two discount chains as customers. The company’s profit margins have been reduced by selling to these customers, but they have been faithful about paying on a consistent basis. Julia also made the following comments about specific dealers. • Dealers No. 29 and No. 49 are notoriously slow pay, but the company has not had to write-off receivables from these dealers in the past. If the economy slows down, that could be a different story. • Dealer No. 10 is having financial difficulty and the Company may have to write-off its receivable. • Dealer No. 122 has been turned over to a collection agency. • The two discount store customers, No 501 and 502 usually take about 90 days before payment is received on their receivables, but they are very reliable in paying amounts owed. • Customer cabinet customer No. 604 is relatively new to the company and has been very slow to pay.

You might consider the following uses of Idea as you prepare you analysis of the adequacy of the allowance for uncollectible accounts.

Aging

You can use the aging function on the file that you have named “AR Dec 31.” You can learn more about the aging function by typing “Aging” into the help menu.

With the AR Dec 31 file open select “Analysis” and then “Aging” from the menus across the top. When the Aging Dialog box comes up: • Select an aging date of “2003/12/31.” • The aging field to use is “DATE” • The Amount field to total is “AR_DEC_31.”
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.

If you want to create one group with receivables over 90 days, when the Aging Dialog box comes up: • Select an aging date of “2003/12/31.” • The aging field to use is “DATE” • The Amount field to total is “AR_DEC_31.” • Put zeros in the 120, 150, and 180 Fields

It might be helpful to create a file with the full transaction history of companies that have receivables that are over 90 outstanding. Using the aging identify all of the customers with receivables over 90 days outstanding.
Now go back the 2003 Transactions database. Here you want to do a data extraction. Name the new file “90 Day Customers. “ Using the equation editor, write and equation as follows:
“CUSTOMER_NO= 1.OR.CUSTOMER_NO=2.OR.CUSTOMER_NO=3” if you want to extract the data form customers 1, 2 and 3. You will probably need a longer string that this, but this is the logic to extract the data.

Some analyses are done better in MS Excel than in Idea. Consider the following. First, open the “90 Day Customers” database that you have created in Idea. From the menus select “File” and the “Export.” Select “all” to export all records. You will need to select the type of file to “Export As” which should be “Microsoft Excel 97- 2002.” Then name the worksheet. I would recommend “90 Day Customer Transactions.” You can select fields that you want to export, but you might export all the data. Choose the folder you want to export to and hit OK. You should now have the annual history of 2003 transaction for the customers with receivables outstanding for over 90 days file in MS Excel format.

Now do a Data Sort in Excel. Sort the data by this size of receivables (AR Dec 31) in descending order.
Idea exported cash receipts date as the invoice date is blank. Change the date on the cash receipts where no cash has been received to year-end (12/31/2003). Now create a new key piece of data. Set up a new column titled Collection Days. Calculate “Collection Days” as CR_DATE minus DATE (the invoice date). Now resort the data, sorting the data first by CUSTOMER_NO in ascending order and then by DATE in ascending order. You now have a database that shows each customer’s history in terms of how long the customer usually takes to pay their payables. This can be very helpful in evaluating the customer history for customers that might be included in the allowance for doubtful accounts.

You should be good at merging and managing files by now so have fun.

Part IV

Now that you have been working with IDEA for awhile, here is a challenge to test your creativity. You have also obtained the customer master file from Mt. Hood Furniture. The customer master file has information on customer credit limits. How good are Mt. Hood’s internal controls that ensure that a customer does not purchase more than their authorized credit limits?

|Requirement for Part IV: Evaluate the internal controls and cite any evidence of any customers that have exceeded their credit limits (e.g., are |
|internal controls effective or does a significant deficiency exist). If you conclude that a significant deficiency exists prepare a management letter|
|comment for the client (if relevant). |