TechWear Case Study Parts III and IV This Module's assignment is a continuation of the extensive data analysis of TechWear's order-to-cash function which you started in Module 4. Part III: TechWear Ca

TechWear Case Study Parts III and IV

This Module's assignment is a continuation of the extensive data analysis of TechWear's order-to-cash function which you started in Module 4.

Part III: TechWear Case Study

Download the Word file TechWear Case Study - Background and Requirements - Part 3 Download TechWear Case Study - Background and Requirements - Part 3in the module for more details on Part III of the case study. Complete the audit data analysis planning template (2-pages) in the Word file and submit this template to your Instructor for grading and feedback. 

Part IV: TechWear Case Study

Note: this part of the case study will take an extensive amount of time, so you should get started on this early in the week. Seek the help of the Instructor as you work on the Requirements for Part IV. Your instructor has solutions for both Excel and Tableau for Part IV.

Seven videos are available in the module to help you with the requirements for Part IV: 

Note: You can adjust the size and quality of the videos by selecting the settings icon in the video player.

TechWear Case Study - Part 4, Question 1TechWear Case Study - Part 4, Question 2TechWear Case Study - Part 4, Question 3TechWear Case Study - Part 4, Question 4TechWear Case Study - Part 4, Question 5 and Question 5 DataTechWear Case Study - Part 4, Question 6

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 (in the module: ACT558_Module_05_P4_Data.xls Download ACT558_Module_05_P4_Data.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:

Submit a Word document with your answers to each Requirement below.

• You have been asked to perform each of the following selected audit work steps that are based on the auditing procedures documented in the audit data analysis template from Part III.
• 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 chart. On this chart, 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 (below) 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 (0-30 days, 31-60 days, 61-90 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 SalesCost of salesAccounts receivableOther

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

Review the week's CT Assignment grading rubric for more information on expectations and how you will be graded.