Analytics mindset TechWear 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.

Complete the following using Excel

1. You are focusing on accounts receivables and cash receipts. On the worksheet named "Part2Q1" list at least 3 questions you might want to resolve with your analysis

2. Develop an accounts receivable (AR) trial balance (by customer and by invoice) as of December 31 2015. Show your work on the worksheet labeled "Part202" Recall that beginning ARsales - 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 collectability 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, write a brief statement providing a conclusion from your findings Display the year-to-date trend in sales and cash receipts by month for 2015. Use a visualization (e.g chart) to best highlight any concerns about potential collection issues. Show your work on the worksheet labeled "Part2Q3"

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 (with DSO as the x-axis and months as the y-axis), to best highlight any concerns about potential collection issues. Show your work on the worksheet labeled "Part2Q4"

4. DSO running total of ending AR balance for each month /running total sales for each month running total of the number of days for each month All of the running totals are year to date*

5. Develop an aging analysis by customer and invoice using 30-day increments (0-30 days, 31-60 days, 61-90 days and > 90 days). Aging is based on the difference between Period End (in this case 12/31/2015) and the invoice date. Display this at the customer level. Provide a visualization of the percentage of accounts receivable in each aging category at the company level using a column chart (with percentage as the x-axis and aging category as the y-axis).

Show your work on the worksheet labeled "Part2Q5" Revenue Cycle Case - TechWare

Objective:

Develop an analytics mindset To perform excel techniques in a revenue cycle audit context

To explore revenue cycles risks

Overview:

This case is based on the TechWare case developed by the Ernst & Young Foundation through the EY Academic Resource Center. It is a five-part case (we will only do parts I-IV) where you will be asked to assume the role of an auditor and perform various audit procedures on the order-to-cash business cycle (accounts receivable and sales) for TechWear, a start-up company that manufactures and sells upper end, high-tech sportswear Throughout the case, you will develop your analytics mindset by learning how to: Ask the right business questions Extract, transform and load relevant data (i.e., the ETL process) Apply appropriate data analytic techniques Interpret and share results with stakeholders The following is a brief overview of each part of the case. Detailed instructions will follow.

Part I: You will become familiar with the background of TechWear. You will be involved in the audit of the order-to-cash cycle (i.e., revenue cycle) and will start with an initial risk assessment. You will become familiar with the initial data file from year one (2015) of TechWear’s operations and will perform some basic ETL procedures on this file to ensure that you have captured all of the data.

Part II: Using the data from Part I, you will be asked to develop an accounts receivable trial balance and perform some analyses to inform their assessment of the collectibility risk on the December 31, 2015, accounts receivable balance.