Question

As part of the engagement team for the audit of JA Tire Manufacturing for the year ended December 31, 2019, you are responsible for auditing the sales and collection cycle. Visit the textbook website to download the data file “JATireSales.xls” provided to your audit firm by the company. The manager has instructed you to read the JA Tire Manufacturing system description provided on the first tab of the Excel file before attempting this assignment to familiarize yourself with the sales process and the relevant worksheets and terminology. This file contains sales transaction information for the year ended December 31, 2019. You will use this data file to perform the following audit procedures.

Required

• Open the data file and review the contents for each of the worksheets (Sales Order, Bills of Lading, Invoice, Cash Receipts, Customer Master, and Product Master). You will work with the Invoice, Sales Order, Bill of Lading, and Customer Master worksheets for this problem. What columns are included in both the Invoice and Sales Order worksheets? What columns are different between these two worksheets?
• One of the audit objectives you are testing is the occurrence audit objective for sales. Use functions in Excel to determine whether any of the following are present, and if present, identify which observations:
  • Duplicate invoice numbers in the Invoice worksheet
  • Duplicate customer purchase order numbers in the Sales Order worksheet
  • Bills of lading and invoices with repeated sales order numbers in the Bill of Lading and Invoice worksheet, respectively
  • Sales orders in the Sales Order worksheet without a customer purchase order, but that were shipped (as evidenced by a bill of lading in the Bill of Lading worksheet)
  • Invoice numbers in the Invoice worksheet with a voided sales order number in the Sales Order worksheet
  • Sales orders in the Sales Order worksheet with a customer number that is not on the approved customer list
• Summarize your findings. What is your overall conclusion about the occurrence audit objective?

Answer 1

Answer:-

a.

Columns included in both worksheets: sales order number, customer number, product number, price, order quantity, order amount.

Columns included in Invoice worksheet only: invoice number, shipment date, invoice date, payment due date, bill of lading number.

Columns included in Sales Order worksheet only: customer purchase order number, sales order date, ship to address number and street, ship to address city, ship to address state, ship to address zip, sales division, voided.

b.

Since this is the first problem using the JA Tires dataset, we provide more detail here when we first use a command. Less detail for each step is provided when we reuse a command in this problem or in later JA Tires problems. As Excel often provides updates, the steps or names of the commands may change as compared to what we have included here. In that case, an instructor may need to refer to Excel help to understand the new steps for each process.

Part b. Searches using JA Tires dataset and commands used to identify the issues

Issues identified

i. Duplicate invoice numbers on invoice worksheet To find duplicate invoice numbers, select the Invoice column by clicking on the top of the column, go to Conditional Formatting in the Home tab under Styles, select Highlight Cells Rules, and select Duplicate Values, then click OK. This highlights any duplicate values within the column. To see all of the highlighted cells together, sort based on cell color. First, highlight the entire dataset by navigating to the top left cell in the dataset (A1), then hold Ctrl and Shift down together and then hit the right arrow key followed by the down arrow key. Then go to the Data tab and select Filter. The filter arrows should appear at the top of each column. Go to the Invoices column and select the filter arrow at the top of the column. Select Filter by Color, and then only the highlighted cells will appear. To unfilter the data, you can click on the Filter option within the Data tab again. These duplicates may arise from corrections to original invoices, as described in the Sales System Description tab in the JA Tires Excel workbook.

135943 136267 136462

Part b. Searches using JA Tires dataset and commands used to identify the issues	Issues identified
ii. Duplicate customer purchase order numbers in the Sales Order worksheet To find duplicate customer purchase order numbers, repeat the steps used above to identify duplicate invoice numbers. Purchase order numbers are entered by the customer when the order is placed, as described in the Sales System Description tab.	AS168953264 2864523698350 AS1689538208 956315859 AS16895336132 9563125585
iii. Bills of lading and invoices with repeated sales order numbers in the Bill of Lading and Invoice worksheet, respectively: Repeated sales order numbers in the Bill of Lading worksheet: Repeated sales order numbers in the Invoices worksheet: Repeated sales order numbers may be associated with the duplicate invoices identified above, or may be associated with a customer order that is split up into separate shipments (as identified by different bill of lading numbers for the same sales order)	700000635 700001239 700001583 700001863 700002071 700003593 700000495 700000635 700000817 700001011 700001239 700001583 700001863 700002071 700003593
iv. Sales orders in the Sales Order worksheet without a customer purchase order, but that were shipped (as evidenced by a bill of lading the Bill of Lading worksheet) Navigate to the Sales Order worksheet, and first identify sales without a customer purchase order. Select the entire dataset, and select Filter within the Data tab. At the top of the Customer Purchase Order Number column, click on the Filter arrow. Within the search section, deselect “Select All”, and then choose “(Blanks)” at the bottom of the list, and click OK. This will filter to only those observations without a purchase order number. Verify that these sales order numbers have a bill of lading in the Bill of Lading worksheet. A lack of a customer purchase order might indicate an “expedited order” as described in the Sales System Description tab.	700000438 700001412 700001847 700002172 700002357 700002945 700002961 700004194

Part b. Searches using JA Tires dataset and commands used to identify the issues	Issues identified
v. Invoice numbers in the Invoice worksheet with a voided sales order number in the Sales Order worksheet The easiest way to identify these is using a VLOOKUP command. Navigate to the Invoices worksheet. Start a new column in L and give it a heading such as “voided”. In cell L2, type =VLOOKUP(K2,Sales_Order!$A$2:$O$4007,15,FALSE). This command searches for the Sales Order Number in cell K2 of the Invoice worksheet within the entire Sales Order worksheet, and if an exact match is identified, it returns the value in the 15th column of the Sales Order worksheet, which is an indicator for voided sales orders. It will return a zero if the cell is blank. Copy this formula down the entire column L. To identify the voided sales orders, add filters to the entire worksheet (highlight worksheet, and select Filter in the Data tab), and then filter column L to identify the “x” values.	136769 139002 139495
vi. Sales orders in the Sales Order worksheet with a customer number that is not on the approved customer list The approach here is similar to that above, using a VLOOKUP command. Navigate to the Sales Order worksheet and start a new column and give it a heading (e.g. CustNo). Type =VLOOKUP(B2,Customer_Master!$A$2:$M$18,1,FALSE). This will return the value of Customer Number from the 1st column of the Customer Master worksheet if the customer number matches across the two worksheets. Filter the new CustNo column to identify values of “#N/A” which indicates there was no match on customer number. As indicated on the Sales System Description tab, orders to customers that are not on the approved customer list requires approval from the Regional Manager.	700000415 700000681 700001405 700002456 700003143 700003387 700004096

c.

Summary of findings: The audit procedures above identified several instances of duplicate invoice numbers, duplicate customer purchase order numbers, and repeated sales order numbers. In addition, the procedures identified sales orders without customer purchase orders, invoices for sales that were voided, and sales to customers not on the approved customer list. These transactions could violate the occurrence assertion, or as noted above, these instances may be related to exceptions to the “standard” process as noted in the Sales System Description tab. The auditor would need to understand and test the internal controls surrounding these exceptions, and test the specific items identified through these procedures to validate the occurrence of the transaction or verify that voided sales orders were not included as part of sales revenue.