Critical Thinking Assignment (75 Points)Important! Read First
Complete the Critical Thinking Assignment. Review the rubric to confirm you are meeting the assignment requirements.
Note: P1 below is the abbreviation for Part 1 of the TechWear Case Study Assignment. The same abbreviation pertains for subsequent Modules, too.
TechWear Case Study Parts I and II: Slideshow PresentationAnalytics Tools:
You can perform the analysis coming up for your Assignments in Modules 4, 5, or 6 using either Excel or Tableau. If you do not have Tableau skills, you can start learning with this case study in Modules 4-6. In comparing Tableau with Excel, the disadvantage of Excel is that it is not as easy to replicate for future uses (e.g., other years, other companies). Once developed, a Tableau workbook can be adapted easily to other years. It can also accommodate larger file sizes. It is also easy to update data with live connections. However, learning Tableau can be challenging and might take some time for you to learn on your own.
Therefore, the final choice is yours: Excel or Tableau for the case study you will be preparing for your Assignments in Modules 4, 5, and 6.
As you work on this case study in Modules 4-6, you will find a complete set of “how to” videos that follow along with the case study requirements, which should be extremely helpful. In addition, a Google search will likely yield excellent step-by-step instructions for both tools.
If you haven’t used Tableau before, note that it is freely available to students at the following link: Tableau for AcademicLinks to an external site..
Online Tableau learning videos can be found at the following link: Tableau TrainingLinks to an external site..
The following are helpful videos:
- Getting StartedLinks to an external site. video to show an overview of using Tableau (23 minutes)
- Understanding the Tableau InterfaceLinks to an external site. (4 minutes)
- Getting Started with DataLinks to an external site. (5 minutes)
Data disclosure:
The data was developed for this case and is not based on a real company. As a result, it may not conform to normal expectations (such as expected patterns underlying Benford’s Law).
Review of basic managerial and accounting concepts
As part of your introduction of this case study, it may be helpful to present (or review) the following basic management and accounting concepts:
Order-to-cash cycle: walk through the order-to-cash cycle at a high level (using T accounts, if necessary) and assume a credit sale, as follows:
Order-to-Cash Cycle
Data disclosure
Activity
Debit
Credit
A $100 order is placed and fulfilled by the product being shipped
Accounts receivable $100
Sales $100
Option A: sales return – full credit
Sales return $100 (allowance or contra sales if no allowance is established)
Accounts receivable $100
Option B: payment in full
Cash $100
Accounts receivable $100
Option C: bad debt write-off
Allowance for doubtful accounts $100 (or bad debt expense if no allowance is established)
Accounts receivable $100
How would you determine the period-end accounts receivable balance (i.e., which accounts affect the accounts receivable (AR) account)?
- Answer: beginning AR + sales – sales returns – cash receipts – bad debt write-offs = ending AR
- In this case study, opening AR = 0 (since we are dealing with year one of a start-up company). Also, there haven’t been any sales returns or bad debts from inception through December 31, 2015.
Accounts receivable trial balance: The trial balance is a listing of all outstanding accounts as of a specified point in time (typically month-end, quarter-end or year-end). Describe the key elements of an accounts receivable trial balance and the concept of aggregating it with a summary (in this case, at the customer level). Customer name; amount; open invoice date; and number.
Accounts receivable aging analysis: The nature and purpose of an accounts receivable aging analysis.
- An accounts receivable aging analysis is a managerial tool designed to highlight potential collectability issues. The most common measure on which to base the aging is the date of sale (i.e., invoice date), though with companies that receive multiple partial payments from more than one source (e.g., a hospital), the date of the last payment also may be used.
- Categories usually include 0-30 days (considered “current’), 31-60 days, 61-90 days and 91+ days. However, for some companies with a very long collection cycle (e.g., hospitals), more categories are added (up to 360+ days) and other group increments beyond 30 days are common.
Background to the TechWear Case Study:
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.
You will be preparing extensive data analysis of TechWear’s order-to-cash function starting here in Module 4, and continuing in Modules 5 and 6.
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: Part 1 Data Download Part 1 Data 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.
Part I: TechWear Case Study – 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 these invoices has been captured and that there are no additional invoices or duplicates included in the file.
(Note: you will be unable to answer the Requirements in Part II for this Module without performing the above analyses).
See the following video:
TechWear Case Study – Part 1
TechWear Case Study – Part 1 Audio TranscriptDownload TechWear Case Study – Part 1 Audio Transcript
to make sure you have completed the Part I functions in Excel, correctly, and have the correct output going into Part II below.
Part II: TechWear Case Study
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 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, provide a brief statement regarding your findings.
Select 2 out of the 3 following analyses to prepare and submit as part of your submission for Part II):
- 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).
Four videos are available in the module to assist you with each of these questions:
Note: You can increase the video to full screen and adjust the video quality from the settings within the video player.
TechWear Case Study – Part 2, Question 1TechWear Case Study – Part 2, Question 2TechWear Case Study – Part 2, Question 3TechWear Case Study – Part 2, Question 4
Summary of Your Required Submission for Parts I and II:
Slideshow file submission: Submit a slideshow presentation (PowerPoint or similar software) for your instructor to grade containing the following with your name and Module # on the file.
- For Part I: verify the accounts receivable balance; validate that there are no other customer names and that no customer names are misspelled; and verify that the data for all of the invoices has been captured and that there are no additional invoices or duplicates included in the file. The answers to Part I can be included on the first slide in your slideshow presentation.
- For Part II: after you develop an accounts receivable (AR) trial balance (by customer and by invoice) as of December 31, 2015 (Requirement 1), submit your results for 2 of the 3 items you chose to analyze, including a brief statement on your findings.
- Use the speaker’s notes section on each slide to explain your findings.
Review the week’s Assignment grading rubric for more information on expectations and how you will be graded.
