Please help me. The questions for the data are below
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-161US 3. 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 file. Part II: Required: Now that you have your data, you need to perform appropriate analytics techniques to inform your risk assessment for the order-to-cash cycle for TechWear. 1. Develop an accounts receivable (AR) trial balance (one by customer and one by invoice) as of December 31, 2015. - Recall that beginning AR + sales - sales returns - cash receipts - bad debt write-offs = ending AR. As mentioned in Part I, the beginning accounts receivable balance is zero and there are no returns or write-offs 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 December 31,2015 , accounts receivable balance. For each procedure, provide a brief statement regarding your findings. 2. Display the year-to-date trend in sales and cash receipts by month for 2015 (with dollars on the x-axis and months on the y-axis). Use a visualization to best highlight any concerns about potential collection issues. Hint: you will be analyzing year-to-date amounts for each month. 3. Compute the year-to-date days-sales-outstanding (DSO) ratio for each month. Show the results numerically and with a visualization. For the latter, use a column chart, also called a vertical bar chart, to best highlight any concerns about potential collection issues. - DSO= ending AR balance for the period / total sales for the period (year-to-date)) * number of days in the period (year-to-date) 4. Develop an aging analysis by customer and invoice using 30-day increments (0-30 days, 31-60 days, 61-90 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. 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-161US 3. 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 file. Part II: Required: Now that you have your data, you need to perform appropriate analytics techniques to inform your risk assessment for the order-to-cash cycle for TechWear. 1. Develop an accounts receivable (AR) trial balance (one by customer and one by invoice) as of December 31, 2015. - Recall that beginning AR + sales - sales returns - cash receipts - bad debt write-offs = ending AR. As mentioned in Part I, the beginning accounts receivable balance is zero and there are no returns or write-offs 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 December 31,2015 , accounts receivable balance. For each procedure, provide a brief statement regarding your findings. 2. Display the year-to-date trend in sales and cash receipts by month for 2015 (with dollars on the x-axis and months on the y-axis). Use a visualization to best highlight any concerns about potential collection issues. Hint: you will be analyzing year-to-date amounts for each month. 3. Compute the year-to-date days-sales-outstanding (DSO) ratio for each month. Show the results numerically and with a visualization. For the latter, use a column chart, also called a vertical bar chart, to best highlight any concerns about potential collection issues. - DSO= ending AR balance for the period / total sales for the period (year-to-date)) * number of days in the period (year-to-date) 4. Develop an aging analysis by customer and invoice using 30-day increments (0-30 days, 31-60 days, 61-90 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
