Analytics_mindset_case_studies_TechWear_HodoM20.pdf

Analytics mindset case studies – TechWear 1
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02315-161US

Analytics mindset
TechWear

Background:

TechWear is a privately-owned business that began operations in March 2018. 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
This task emphasizes the second bullet of developing an analytics mindset — Extract, transform and
load relevant data (ETL). Before using data for any analysis, it is imperative to understand the data. For
this case, you have the following three data files:

Analytics_mindset_case_studies_Techwear2018_P1.xls – 2018 AR Data

Analytics_mindset_case_studies_Techwear2019_P3.xls – 2019 AR Data & 2019 Inventory Relief
Data

The data files generally contain the following information (review each file carefully):

AR Data

► 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 2
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02315-161US

► 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.

Inventory Relief Data

► 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.

Deliverables:

► Two Excel workbooks answering Parts I-III

► One Memo addressed to the Audit Committee of TechWear that explains the methods used to
answer each questions and any audit findings from Part III that requires adjustments to the financial
statements.

Analytics mindset case studies – TechWear 3
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02315-161US

Part I:

You are first responsible for performing a risk assessment of TechWear related to its -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 2018. You
have been provided with an Excel file with this data
(Analytics_mindset_case_studies_Techwear2018_P1.xls) so you can begin your analysis.

Required (15 points)

Become familiar with your data file. Make certain that your data is complete and accurate before
performing any analysis. Complete the following using Excel:

1. You’ve been told that the accounts receivable balance on the general ledger at December 31, 2018,
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 2018. 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

Analytics mindset case studies – TechWear 4
© 2016 Ernst & Young Foundation (US). All Rights Reserved.
SCORE No. 02315-161US

– Super Runners Mark

– Urban Runner

– ValueChoice

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.

6

Part II:

Now that you have your data, you need to perform appropriate analytics techniques to inform your risk
assessment for the -to-cash cycle for TechWear.

Required: (26 points)

1. Develop an accounts receivable (AR) trial balance (by customer and by invoice) as of December 31,
2018.

– 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 2018.

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, 2018, 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 2018 (with months on the x-
axis and dollars on the y-axis). Use a visualization to best highlight any concerns about potential
collection issues.

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.

– 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). Display this at the customer level with the ability to drill down to the
transaction (invoice) level. Provide a visualization of the percentage of accounts receivable in each
aging category at the company level using a column chart (with aging category as the x-axis and
percentage as the y-axis).