Analytics mindset case studies TechWear

Ernst & Young Foundation (US)
Case Studies
Analytics mindset 
TechWear 

TechWear

Part III:

Background:

It is now February 6, 2020, and you are ready to begin the year-end audit procedures for the 2019 audit.

The client has provided the 2019 data that you requested

(Analytics_mindset_case_studies_Techwear_2019P3.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. 

2019 AR Data Tab:

The data fields are the same as what you received for 2018, with the exclusion of the ship date. Additionally, the “Type” field includes transaction information for the opening balance, which reconciles with the 2018 ending balance of $684,491.19 and the unapplied cash receipts.

2019 Inventory Relief 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 on the 2019 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 is on the 2019 AR data tab)

► CustName: this is the customer name (same field that is on the 2019 AR data tab)

► InvoiceDate: this is the date that the sale was invoiced (billed) (same field that is on the 2019 AR data tab).

► ShipDate: this is the date the goods were shipped.

► InvCostReliefAmount: this is the inventory relief amount, or the cost of sales.

The December 31, 2019, 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

Analytics mindset case studies – TechWear 7

© 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US

Required:

► You have been asked to perform each of the following select work steps in Access. You will first need to load your data into Access.

► As you perform each work step, document your findings in detail and propose any audit adjustment. Using the table at the end of 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 results from your analysis so you can gain the best insights from the work step.

► Prepare a brief, one-page summary of your overall findings that includes your summary table (below).

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. Indicate your final query in your memo.
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. Indicate your final query in your memo.
3. Identify shipments that occurred in 2020 for 2019 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. Indicate your final query in your memo.
4. Analyze gross margin percentages by customer, after considering your previous findings. Identify any percentages that are outside the range of expectations. Indicate your final query in your memo.

Check figures: Audited Sales - $34,338,061.83 Audited Inventory Relief (COGS) - $13,768,115.34 

5. Develop a trial balance of accounts receivable at December 31, 2019, after reflecting about any proposed audit adjustments. Indicate your final query in your memo.