Techwear Data Analytics Case

Part IV:

Background:

It is now February 6, 2017, and you are ready to begin the year-end audit procedures for the 2016 audit. The client has provided the 2016 data that you requested (Analytics_mindset_case_studies_Techwear_P4.xls) so you can begin your work. Procedures have already been performed by your team to ensure that the data you received is complete and accurate.

The data file includes data on two tabs 2016 AR data and 2016 inventory relief data.

2016 AR data tab

The data fields are the same as what you received for 2015, with the exclusion of the ship date. Additionally, the Type field includes transaction information for the opening balance (Opening Balance), which reconciles with the 2015 ending balance of $684,491.19 and the unapplied cash receipts (Unapplied Receipts).

2016 inventory relief data tab

This data includes the following fields:

• ShipNum: this is the shipping number. This number becomes the sales transaction number when the invoice is created, which is the transaction number field on the 2016 AR data tab.
• FedExID: this is the FedEx identification number. All items shipped on a given day will have the same number.
• CustNum: this is a unique customer number to identify the customer (same field that is on the 2016 AR data tab).
• CustName: this is the customer name (same field that is on the 2016 AR data tab).
• InvoiceDate: this is the date the sale was invoiced (billed) (same field that is on the 2016 AR data tab).
• ShipDate: this is the date the goods were shipped.
• InvCostReliefAmount: this is the inventory cost relief amount, or the cost of sales.

The December 31, 2016, working trial balance shows the following:

Accounts receivable $18,114,802.50 (no allowances have been recorded)

Sales $37,333,890.86

Cost of sales $14,269,387.17

Required:

• You have been asked to perform each of the following select work steps that are based on the auditing procedures documented in the audit data analysis template from Part III. Perform your analysis in Tableau[1].
• As you perform each work step, document your findings in detail and propose any audit adjustment using the summary of auditing findings document on the following page. On this document, add or remove audit findings rows as needed based on your work. Before you perform the next work step, make certain to remove the corresponding data from your analysis so you can gain the best insights from the work step.
• Prepare a brief, one-page summary of your overall findings for discussion with the audit committee that includes your summary table and any relevant visualizations.

Audit work steps

1. Verify that every sales transaction has a shipping number and FedEx identification number. Identify and quantify any sales that have not been shipped (including names of customers and transaction numbers). Propose any necessary audit adjustment.
2. Verify that every shipping number has a recorded sales transaction. Identify and quantify the cost of sales for products shipped without a sale being recorded (including names of customers and transaction numbers). Propose any necessary audit adjustment.
3. Identify shipments that occurred in 2017 for 2016 sales. Identify and quantify any sales and the cost of sales for amounts recorded in the improper period (including names of customers and transaction numbers). Propose any necessary audit adjustment.
4. Analyze gross margin percentages by month and by customer, reporting results in a tabular and graphical form, after considering your previous findings. Identify any percentages that are outside the range of expectations.
5. Develop a trial balance of accounts receivable at December 31, 2016, after reflecting about any proposed audit adjustments. Display this at the customer level with the ability to drill down to the transaction (invoice) level.
6. Develop an aging analysis of accounts receivable at December 31, 2016, after reflecting about any proposed audit adjustments. Use the following aging categories (030 days, 3160 days, 6190 days, > 90 days and unapplied cash). Display this at the customer level with the ability to drill down to the transaction (invoice) level. Provide a visualization of the amount of accounts receivable in each aging category.

Summary of audit findings
	Sales	Cost of sales	Accounts receivable	Other
As of December 31, 2016	$37,333,890.86	$14,269,387.17	$18,114,802.50
Audit finding
Audit finding
Audit finding
Adjusted as of December 31, 2016