TechWear AssignmentBackgroundTechWear is a privately owned busines. Its sole business is the manufacture and sale of upper-end, high-tech sportswear. It only sells to large distribution outlets. Its primary product is a line of lightweight exercise clothes that contain a new, long-range RFID chip that captures the following information about the user based on personal data (age, weight, etc.) entered by the user: Heart ratePerspiration rateCalories burnedExercise efficiency (percent of capacity) The chip is able to continuously send this information to a host device as far away as 15 miles. The clothes are also GPS enabled and able to track routes, distances and elevations. Management prides itself on being on the cutting edge. The company expects to conduct an IPO within a year or two. As an accountant working for TechWear, you’ve been asked to perform procedures to verify sales have been recorded properly. To complete these tasks you’ve been provided a file that is both complete and accurate. The data file includes data on two tabs — 2016 AR data and 2016 inventory relief data. Descriptions of the variables included on each tab are provided below. 2016 AR Data Tab Type: this is the type of transaction, which is either a sale (Sales) or a cash receipt (CashReceipt).TransactionNumber: this is the transaction number (beginning with 1001). AppliedToTransaction Number: this is the sales transaction number to which a cash receipt is applied.CustNum: this is a unique customer number used to identify each customer.CustName: this is the customer’s name.TransactionDate: this is the date of the sale or cash receipt.Amount: this is the amount of the sale or cash receipt. Cash receipts will show a negative amount.InvoiceDate: this is the date the sale was invoiced (billed).2016 Inventory Relief Data Tab 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.ProcedurePerform the following procedures to analyze sales transactions. Each task should be performed on a separate worksheet in Tableau. When you are done, save the assignment as a Tableau Workbook and upload through Canvas. Please note that I have provided videos in Canvas to assist you with the harder procedures.
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).
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).
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).
4. Join the two tabs from the Excel file in a way that ensures you only have the sales for which inventory cost is available (I understand doing this will affect the other worksheets). Calculate gross margin (the difference between the sale amount and cost) and gross margin percentage for each customer. Show the results in a table that has sales, cost of sales, gross margin, and gross margin percentage summarized by customer.