Categories
Study Questions

Analytics Mindset – TechWear

 

You will need Microsoft Excel to prepare this assignment.  You have two weeks to complete this Assignment, which is due at the end of Module 6.

  • Read below (and/or download the attached Word file TechWear) with the instructions for this assignment:
  • Download the following one data set in MS Excel—CSV files.
    • Analytics_mindset_case_study_Techwear_Part1_datafile.xls
  • Download the following five MP4 Files for Parts I and II. The first file will help you with Part I of the case requirements; the last four files will help you with questions 1 through 4 of Part 2 of the case requirements.
    • Part I file:
      • Analytics_mindset_case_study_Techwear_Part1.mp4 (video transcript)Preview the document
    • Part II files:
      • Analytics_mindset_case_study_Techwear_Part2_Question_1.mp4 (video transcript)Preview the document
      • Analytics_mindset_case_study_Techwear_Part2_Question_2.mp4  (video transcript)Preview the document
      • Analytics_mindset_case_study_Techwear_Part2_Question_3.mp4 (video transcript)Preview the document
      • Analytics_mindset_case_study_TechWear_Part2_Question_ 4.mp4 (video transcript)Preview the document

Background:

This is a two-part case where students are 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, students will be developing their analytics mindset in an audit context 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

A brief overview of TechWear:

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 and weight, for example) 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.

Case and Assignment Requirements:

    Part I

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_study_Techwear_Part1_data file.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.
  • 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:

  • 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.
  • 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
  • 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 I (additional guidance):

This section allows students to become familiar with the background company and the data for the order-to-cash business process (sales and cash receipts covering the start-up period of operations, which runs from March 5 through December 31, 2015).

Students are asked to perform some select steps in the ETL process to make certain of the integrity of the data they will be using for their analysis (note that there are more possible steps that could be done, but the requirements are limited for purposes of this case). This work is done in Excel. Students are using some basic Excel skills, including (covering questions 1 through 3):

  • View tab—freeze top row
  • Home tab—summation
  • Data tab—remove duplicates
  • Data tab—data validation

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.

  • Develop an accounts receivable (AR) trial balance (by customer and 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.
    • 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.
    • 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)
  • 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 percentage as the x-axis and aging category as the y-axis).

Part II (additional guidance):

This section asks students to develop an accounts receivable trial balance for December 31, 2015, and perform an initial risk assessment of the collectibility of that balance. This work is done in Excel. Students are using some moderate-level Excel skills, including: (Note that there are MP4 files here to provide you some guidance.)

  • Question 1:
    • Data tab—consolidate
    • VLOOKUP function
    • INDEX MATCH function
    • IFERROR function
    • Insert tab—pivot table
  • Question 2:
    • Date format by month
    • ABS function
    • Insert tab—pivot table—custom calculation—running total
    • Insert tab—column chart
    • Column chart
  • Question 3:
    • Insert tab—column chart
  • Question 4:
    • DATE function
    • IF function
    • Insert tab—pivot table—manual sort
    • Insert tab—column chart

Case Requirements:

You are to submit a Word document with your answers to the three Part I Questions.

You are to submit Excel document, using the Tab function in Excel, to answer the four Part II Questions.

  • .

This page is having a slideshow that uses Javascript. Your browser either doesn't support Javascript or you have it turned off. To see this page as it is meant to appear please use a Javascript enabled browser.

Jessy
Jessy
Just a bit off from the syllabus rubric, but I can add to the slides on my own. Thank you anyway.
Alvin
Alvin
To the writer, THANK YOU, a million times over, this is very impressive! Thank you to the support team for making a hiccup not turn into a disaster!
Milly.P
Milly.P
There was consistent communication from the start, and it was obvious the writer thoroughly reviewed the information provided. Thank you.
Ivy.G
Ivy.G
Excellent work all the time! love my writer also. Don't know if I'm supposed to do this but I just need to say this writer does excellent work!
Emily.s
Emily.s
The writer followed my instruction thoroughly and did a great job. The quality of the paper is more than I expected. Thank you.
Justus N
Justus N
You people have been really patient with me as I created mechanical engineering content. It’s not an easy topic to handle, yet I can say that I worked with a skilled specialist. My writer took time to understand my ideas but it was mostly my fault. Just share more details.
Kelly H
Kelly H
When I received a paper revision from my new college professor, I was desperate as I didn’t know what to do. If not for Academized, I would be doomed. They know how to fix things for you and explain what was wrong as they fix it.
Paul D
Paul D
You're the best, I'll be using your services again soon! Thank you Thank you Thank you!
Mark P.
Mark P.
I asked my writer to help me compose a personal essay about charity work that I do at the local church. It is not easy for me to express myself in words, which is why I needed some privacy and a good person who would appreciate my thoughts and ideas.

error: Content is protected !!
Open chat
1
Do You Need Assignment Help? Lets Chat!
StudyPoo Inc.
Get Help Instantly direct from an Expert.

Talk through Live Chat right now!