Chapter 9 Quiz

Note: The material contained within these pages is taken directly from your textbook, Computerized Auditing Using ACL Data Analytics. Alterations were made only for formatting and the omission of specific questions.

Reference

Arens, A. A., Elder, R. J., & Borsum, C. J. (2013). Computerized Auditing Using ACL Data Analytics. (3^rd ed.). Okemos, MI: Armond Dalton Publishers, Inc.

Assignment #1

Simpson Company: Using ACL in the Audit of Sales and Collection Cycle ? Part 3

Complete the following requirements using ACL:

Q 1. You should have summarized the sales transaction file into a file of outstanding balances in Part 2. Prepare and print a list of the accounts receivable balances at 12/31/14 sorted from largest to smallest balance. The list should include only the customer name and balance outstanding.

Q 2. Determine which customer name had the most transactions, including all types, for 2014. Prepare and print a list of all of these transactions that includes from left to right the customer name, customer number, billing date, type of transaction , document number, and amount of the transaction. Sort the list by transaction type, then transaction date.

Q 3. The master file table of accounts receivable is considered a reliable source of valid customers. Determine if all customers in the accounts receivable balance table are also included in the master file table of accounts receivable.

Q 4. Simpson has a general policy of providing a credit limit of $15,000 except for customers with excellent credit ratings and a long history of doing a large amount of business with Simpson annually. Approved credit limits for each customer are included in the accounts receivable master file. List the customer number, customer name, credit limit, and outstanding accounts receivable balance for any customer with a credit limit greater than $15,000.

Q 5. Generate a list of customers with a receivable balance greater than $10,000 and that exceeds 75% of their credit limit. Print this list, including customer number, customer name, balance, and credit limit. What is the likely purpose of this procedure?

Q 6. The balance for one of these customers exceeds the credit limit. Evaluate whether this customer is a significant credit risk.

Q 7. Accounts receivable balances greater than $50,000 are key items and will be tested separately. Determine the sample size and sampling interval for a monetary unit sample of the remaining positive balances assuming a 90% confidence level, tolerable misstatement of $50,000, and no expected errors.

Sample size:

Interval:

Q 8. Select a random sample of accounts under $50,000 for testing using the sample size and interval calculated in the previous question. Use a seed of 3212. Include the accounts to be confirmed, including the key items, in a new table called AR_Confirmations that includes the customer name, balance due, and the complete address includingcity, state and zip.

Q 10. The client has provided you with a sales cutoff file, which is a summary of shipments immediately before and after year-end. You will observe in that file that shipping personnel do not consistently issue shipping documents in the order they were shipped. Perform sales cut-off tests for the year ended 12/31/2014 to determine that all shipping documents before year-end are properly included as sales transactions for 2014 and shipments after year end are excluded from 2014 sales transactions. Indicate your findings.

Assignment #2

Banlee Wholesale Company: Using ACL in the Audit of Inventory

Answer all parts of these requirements on this page through 9-19. It is more convenient to complete the assignment if you remove pages 9-13 through 9-19 now. For each requirement, include the command you used, where applicable, and the correct answer or exception(s) found. For requirements that require a printout include your name and the assignment number on the printout you hand in to the instructor.

You are assigned to audit inventory at Banlee Wholesale Company for the year ended December 31, 2014. Inventory is included in five inventory locations with the policy that every inventory item must be stored in at least two locations, but no more than three. The physical inventory count was done on December 30 and 31 at each location by a team of Banlee employees. One staff person from your CPA firm was present at each of three locations selected by the audit in-charge on the Banlee audit. All three staff members who observed the count stated the counting personnel followed the company's written counting procedures, which had been approved by your firm. Each staff person concluded that the counts were reasonably stated. Each staff person also made several counts of inventory and included the count information on a working paper that was sent to you for subsequent follow-up. Each staff person also accounted for all tag numbers used as follows:

Miami 21001 - 21034

Ocala 23001 - 23022

Orlando24001 - 24098

The warehouse supervisor for each location sent a list of tag numbers used to your CPA firm's Orlando office. They were as follows:

Miami 21001 - 21034

Naples22001 - 22113

Ocala 23001 - 23022

Orlando 24001 - 24098

Tampa25001 - 25080

Each office sent all tags to the home office in Orlando to accumulate inventory quantities and compile total cost of all inventory. Information from the tags was entered into an Excel worksheet, called the inventory summary, by a clerk who also entered the unit cost from the inventory master file. He then extended each inventory item and totaled the extended amounts and turned the worksheet over to the controller who had an assistant recheck the calculations before he adjusted year-end inventory for the amount on the worksheet.

Banlee keeps an inventory master file for each location, primarily to have a record of the quantity of inventory at all times. Management's philosophy is to always have enough inventory on hand to satisfy customers? needs and order in large quantities to reduce costs, but not to overstock to avoid obsolescence and minimize storage and other carrying costs. Traditionally the inventory master file is not as accurate as management would like, but it is still sufficient for its intended use.

This is the fifth year your firm has audited Banlee, and although there have often been some errors in the records, they have typically been fairly small and usually caused by clerical errors. Based on your firm's experience on the audit, the evaluation of the risks of misappropriation of assets and fraudulent financial reporting, and especially management's outstanding reputation for integrity, the audit partner has concluded that the risk of fraud is low.

This project contains three files that have already been accessed into ACL as tables. The tables are as follows, and include the following data as received from the controller.

Banlee_Inv_Summary (345 records; $1,843,523.11 total cost)

Banlee_Inv_Master (350 records; $8,241.25 unit selling price)

Banlee_Test_Counts (41 records; 25,177 units)

Complete the following requirements using ACL except requirement 13:

Q 1. Determine if the information in the three tables is consistent with the information provided by the controller.

Q 2. Use the Statistics command for all three tables and list any items that you believe require follow-up after considering materiality.

Q 5. Determine if the tag numbers included in the audit work papers and the ones provided by the warehouse supervisors were all included on the inventory summary. Also determine that only those tag numbers were included and there were no duplicates.

Q 6. Determine if all inventory items included on the inventory summary are included in the inventory master file and vice versa.

Q 8. Determine if the inventory items included in the inventory locations are consistent with company policy.

Q 9. Determine if unit cost times quantity of inventory on hand is correctly calculated for each inventory item and the total for each inventory location is correct. The general ledger balance for each location is:

Miami$210,379.12

Naples 572,874.39

Ocala 80,117.42

Orlando 477,164.53

Tampa 502,987.65

Q 10. The partner on the audit wants you to calculate the total selling price of all inventory divided by the total cost for each location to evaluate the lower of cost or market. The partner also asks that you list the individual items with a selling price below cost. Indicate the results of these procedures. Compare the nature of the information provided by the two procedures.

Q 11. Historically, the quantities on the inventory master file have not been as accurate as management would like, but they are at least useful to assess the accuracy of the inventory count. Calculate the dollar and percent difference in total cost of each inventory item using the units on the inventory master file compared to the amount on the inventory summary. Print a list of any inventory item where the percentage difference exceeds 5% and the dollar amount exceeds $5,000. These items will be recounted to determine if there was a likely inventory counting error.

Q 13. List any other audit procedures not involving ACL in the audit of inventory that you believe should be performed.

Assignment #3

Veltronics Supply Company: Using ACL in the Audit of Accounts Payable

Answer all parts of these requirements on this page through 9-27. It is more convenient to complete the assignment if you remove pages 9-23 through 9-27 now. For each requirement, include the command you used , where applicable, and the correct answer or exception(s) found. For requirements that require a printout include your name and the assignment number on the printout you hand in to the instructor.

You are assigned to the audit of Veltronics Supply Company and are auditing accounts payable balances for the year ended 3/31/2015. Veltronics specializes in selling medical supplies and equipment to hospitals, universities, research centers, and other similar businesses. Veltronics purchases these supplies and equipment from vendors throughout the United States.

It is Veltronics' long standing policy to pay all invoices within the 30-day period that cash discounts are granted, in part to maximize the amount of discounts and in part to maintain good relationships with vendors. In a conversation with the Veltronics' president he informs you that he has received two phone calls in the past week from long standing vendors informing him that they may begin requiring cash on delivery payment because of payment delinquencies. He states that he would like to discuss this further with you when the

Chapter 9 Quiz Note: The material contained within these pages is taken directly from your textbook, Computerized Auditing Using ACL Data Analytics. Alterations were made only for formatting and the omission of specific questions. Reference Arens, A. A., Elder, R. J., & Borsum, C. J. (2013). Computerized Auditing Using ACL Data Analytics. (3rd ed.). Okemos, MI: Armond Dalton Publishers, Inc. Assignment #1 Simpson Company: Using ACL in the Audit of Sales and Collection Cycle - Part 3 Complete the following requirements using ACL: Q 1. You should have summarized the sales transaction file into a file of outstanding balances in Part 2. Prepare and print a list of the accounts receivable balances at 12/31/14 sorted from largest to smallest balance. The list should include only the customer name and balance outstanding. Q 2. Determine which customer name had the most transactions, including all types, for 2014. Prepare and print a list of all of these transactions that includes from left to right the customer name, customer number, billing date, type of transaction , document number, and amount of the transaction. Sort the list by transaction type, then transaction date. Q 3. The master file table of accounts receivable is considered a reliable source of valid customers. Determine if all customers in the accounts receivable balance table are also included in the master file table of accounts receivable. Q 4. Simpson has a general policy of providing a credit limit of $15,000 except for customers with excellent credit ratings and a long history of doing a large amount of business with Simpson annually. Approved credit limits for each customer are included in the accounts receivable master file. List the customer number, customer name, credit limit, and outstanding accounts receivable balance for any customer with a credit limit greater than $15,000. Q 5. Generate a list of customers with a receivable balance greater than $10,000 and that exceeds 75% of their credit limit. Print this list, including customer number, customer name, balance, and credit limit. What is the likely purpose of this procedure? Q 6. Q 7. The balance for one of these customers exceeds the credit limit. Evaluate whether this customer is a significant credit risk. Accounts receivable balances greater than $50,000 are key items and will be tested separately. Determine the sample size and sampling interval for a monetary unit sample of the remaining positive balances assuming a 90% confidence level, tolerable misstatement of $50,000, and no expected errors. Sample size: Interval: Q 8. Select a random sample of accounts under $50,000 for testing using the sample size and interval calculated in the previous question. Use a seed of 3212. Include the accounts to be confirmed, including the key items, in a new table called AR_Confirmations that includes the customer name, balance due, and the complete address including city, state and zip. Q 10. The client has provided you with a sales cutoff file, which is a summary of shipments immediately before and after year-end. You will observe in that file that shipping personnel do not consistently issue shipping documents in the order they were shipped. Perform sales cut-off tests for the year ended 12/31/2014 to determine that all shipping documents before year-end are properly included as sales transactions for 2014 and shipments after year end are excluded from 2014 sales transactions. Indicate your findings. Assignment #2 Banlee Wholesale Company: Using ACL in the Audit of Inventory Answer all parts of these requirements on this page through 9-19. It is more convenient to complete the assignment if you remove pages 9-13 through 9-19 now. For each requirement, include the command you used, where applicable, and the correct answer or exception(s) found. For requirements that require a printout include your name and the assignment number on the printout you hand in to the instructor. You are assigned to audit inventory at Banlee Wholesale Company for the year ended December 31, 2014. Inventory is included in five inventory locations with the policy that every inventory item must be stored in at least two locations, but no more than three. The physical inventory count was done on December 30 and 31 at each location by a team of Banlee employees. One staff person from your CPA firm was present at each of three locations selected by the audit in-charge on the Banlee audit. All three staff members who observed the count stated the counting personnel followed the company's written counting procedures, which had been approved by your firm. Each staff person concluded that the counts were reasonably stated. Each staff person also made several counts of inventory and included the count information on a working paper that was sent to you for subsequent follow-up. Each staff person also accounted for all tag numbers used as follows: Miami Ocala Orlando 21001 - 21034 23001 - 23022 24001 - 24098 The warehouse supervisor for each location sent a list of tag numbers used to your CPA firm's Orlando office. They were as follows: Miami Naples Ocala Orlando Tampa 21001 - 21034 22001 - 22113 23001 - 23022 24001 - 24098 25001 - 25080 Each office sent all tags to the home office in Orlando to accumulate inventory quantities and compile total cost of all inventory. Information from the tags was entered into an Excel worksheet, called the inventory summary, by a clerk who also entered the unit cost from the inventory master file. He then extended each inventory item and totaled the extended amounts and turned the worksheet over to the controller who had an assistant recheck the calculations before he adjusted year-end inventory for the amount on the worksheet. Banlee keeps an inventory master file for each location, primarily to have a record of the quantity of inventory at all times. Management's philosophy is to always have enough inventory on hand to satisfy customers' needs and order in large quantities to reduce costs, but not to overstock to avoid obsolescence and minimize storage and other carrying costs. Traditionally the inventory master file is not as accurate as management would like, but it is still sufficient for its intended use. This is the fifth year your firm has audited Banlee, and although there have often been some errors in the records, they have typically been fairly small and usually caused by clerical errors. Based on your firm's experience on the audit, the evaluation of the risks of misappropriation of assets and fraudulent financial reporting, and especially management's outstanding reputation for integrity, the audit partner has concluded that the risk of fraud is low. This project contains three files that have already been accessed into ACL as tables. The tables are as follows, and include the following data as received from the controller. Banlee_Inv_Summary (345 records; $1,843,523.11 total cost) Banlee_Inv_Master (350 records; $8,241.25 unit selling price) Banlee_Test_Counts (41 records; 25,177 units) Complete the following requirements using ACL except requirement 13: Q 1. Determine if the information in the three tables is consistent with the information provided by the controller. Q 2. Use the Statistics command for all three tables and list any items that you believe require followup after considering materiality. Q 5. Determine if the tag numbers included in the audit work papers and the ones provided by the warehouse supervisors were all included on the inventory summary. Also determine that only those tag numbers were included and there were no duplicates. Q 6. Determine if all inventory items included on the inventory summary are included in the inventory master file and vice versa. Q 8. Determine if the inventory items included in the inventory locations are consistent with company policy. Q 9. Determine if unit cost times quantity of inventory on hand is correctly calculated for each inventory item and the total for each inventory location is correct. The general ledger balance for each location is: Miami Naples Ocala Orlando Tampa Q 10. $210,379.12 572,874.39 80,117.42 477,164.53 502,987.65 The partner on the audit wants you to calculate the total selling price of all inventory divided by the total cost for each location to evaluate the lower of cost or market. The partner also asks that you list the individual items with a selling price below cost. Indicate the results of these procedures. Compare the nature of the information provided by the two procedures. Q 11. Historically, the quantities on the inventory master file have not been as accurate as management would like, but they are at least useful to assess the accuracy of the inventory count. Calculate the dollar and percent difference in total cost of each inventory item using the units on the inventory master file compared to the amount on the inventory summary. Print a list of any inventory item where the percentage difference exceeds 5% and the dollar amount exceeds $5,000. These items will be recounted to determine if there was a likely inventory counting error. Q 13. List any other audit procedures not involving ACL in the audit of inventory that you believe should be performed. Assignment #3 Veltronics Supply Company: Using ACL in the Audit of Accounts Payable Answer all parts of these requirements on this page through 9-27. It is more convenient to complete the assignment if you remove pages 9-23 through 9-27 now. For each requirement, include the command you used , where applicable, and the correct answer or exception(s) found. For requirements that require a printout include your name and the assignment number on the printout you hand in to the instructor. You are assigned to the audit of Veltronics Supply Company and are auditing accounts payable balances for the year ended 3/31/2015. Veltronics specializes in selling medical supplies and equipment to hospitals, universities, research centers, and other similar businesses. Veltronics purchases these supplies and equipment from vendors throughout the United States. It is Veltronics' long standing policy to pay all invoices within the 30-day period that cash discounts are granted, in part to maximize the amount of discounts and in part to maintain good relationships with vendors. In a conversation with the Veltronics' president he informs you that he has received two phone calls in the past week from long standing vendors informing him that they may begin requiring cash on delivery payment because of payment delinquencies. He states that he would like to discuss this further with you when the audit is completed. There are two accounts payable tables that have already been accessed by ACL in the project Veltronics: Veltronics_Unpaid_Invoices (136 records; invoice amount totals $453,382.55) Veltronics Master File (50 records) Complete the following requirements using ACL: Q 1. Determine that the information in the tables is consistent with the information provided by the IT Department. Q 2. Use the Statistics command and evaluate whether any unpaid vendors invoices should be further investigated. List the vendor number for those you believe need to be investigated further, the amount of the unpaid invoice, and a brief explanation for the reason for listing each one. Q 4. Prepare and print a schedule of aged accounts payable. The list should include only the following ages: 0 to 30 days, 31 to 60 days, 61 to 90 days, and over 90 days. (Hint: The aging will be based on the unpaid invoice listing, rather than the list of vendor balances. First create a computed field for the days outstanding for each invoice. Then create computed fields for each aging category. The amount of the computed field will be equal to the invoice amount, conditional on the age of the invoice). For example, after computing a field for days outstanding (Days_OS), the field for 31-60 days would be computed as follows: f(x) = Invoice_Amount If: Days_OS >30 AND Days_OS