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). ShipDate: this is the date the goods were shipped. Required Become familiar with your data file. Make certain that your data is complete and accurate before performing any analysis. Complete the following using Excel. Save your work for each requirement as a separate sheet. Where necessary, add notes near the top of the relevant sheet to explain your analysis and your conclusions from each step 1. You've been told that the accounts receivable balance on the general ledger at December 31, 2015, is $684,491.19. You also know that as a start-up company, the beginning accounts receivable balance is zero. You are also told that there are no returns or write-offs in 2015. Verify this balance. 2. You've also been told that TechWear only conducts business with the following 15 approved customers. Validate that there are no other customer names and that no customer names are misspelled. Bigmart Cool Threads Corner Runner Cross Country Mart Family Fit Fit N Fun Goodway Neighborhood Athletic Supply Northern Lites Runner's Market Southeast Regional Southern Runners Super Runners Mark Urban Runner ValueChoice Analytics mindset case studies - TechWear 2 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US3. The sales transaction log shows that 230 sales were transacted this year. beginning with transaction 1001. Verify that the data for all Of these invoices has been captured and that there are no additional invoices or duplicates included in the le. Part II: Required: Now that you have your data you need to perform appropriate analytics techniques to inform your rislt assessment for the order-tocash cycle for TechWear. 1. Develop an accounts receivable {APE} trial balance (one by customer and one by invoice) as of December 31, 2015. Recall that beginning AR + sales sales retums cash receipts bad debt wnte-o s = ending AR. As mentioned in Part I_. the beginning accounts receivable balanoe is zero and there are no returns or wmecs in 2015. Perform the following analyses relating to collectibility risk (which is the risk the company won't collect money for its sales} on the Deoember 31. 2015. accounts receivable balance. For each procedure. provide a brief statement regarding your ndings. 2. Display the year-toasts trend in sales and cash receipts by month for 2015 (with dollars on the xaxls and months on the y-axis). Use a visualization to best highlight any concerns about potential collection issues. Hint: you will be analyzing year-todate amounts for each month. 3. Compute the year-tocate dayssalescutstandlng (DEC) ratio for each month. Show the results numerically and with a visualization. Forthe latter. use a column chart. also called a vertical bar chart. to best highlight any concerns about potential collection issues. oso = ending AR balance for the period r total sales for the period {ynrtcedatet} * number of days in the period {year-todate} 4. Develop an aging analysis by customer and invoice using 30-day increments (030 days. 3150 days. 5190 days and > 90 days}. Provide a visualization of the percentage of accounts receivable in each aging category at the company level using a column chart. Ernst & Young FOUND ATION" Case studies Case #3 103 points Part 1: Background: TechWear is a privately owned business that began operations in March 2015. 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 rate Perspiration rate Calories burned Exercise 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. TechWear recently retained your firm as its auditors, largely because of your commitment to conduct a highly efficient, technology-enabled audit. Data You are first responsible for performing a risk assessment of TechWear related to its order-to-cash function. Therefore, you know that your focus needs to be on sales and cash transactions. Your first task is to acquire the data for these transactions. You work with TechWear's IT group to gain access to its sales and cash receipts data for its start-up period of operations, March through December 2015. You have been provided with an Excel file with this data (Analytics_mindset_case_studies_Techwear_P1.xls) so you can begin your analysis. The data file includes the following fields: 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. Analytics mindset case studies - TechWear 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US