TechWear Business Analytics Mindset Case Studies Memo Assignment

User Generated

pufurel50

Business Finance

Description

i have a complete assignment. i just need two paragraph written memo that how part 4 & 5 was done. in first file there is instruction and second and third file is a complete assignment

Unformatted Attachment Preview

Analytics mindset TechWear Part I: Background: 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. 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_studies_Techwear_P1.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. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 ► 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: 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 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. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 2 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. 1. 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. 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. 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 percentage as the x-axis and aging category as the y-axis). Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 Analytics mindset TechWear Part III: Background: One year has passed and it is now time to begin the 2016 audit. TechWear’s business has continued to expand. Our initial analysis of 2016 data indicates a much greater risk in accounts receivable (compared with 2015). When the audit team discussed this with company management, we were reminded that last year’s accounts receivable proved to be fully collectible and we were assured that this year accounts receivable would also prove to be fully collectible as well. Management indicated that, similar to 2015, there have been no product returns or accounts written off in 2016. The senior has asked you to assist with planning the 2016 audit, beginning with the order-to-cash cycle, by focusing on accounts receivable and sales. In particular, she has provided you with the following key audit assertions that need to be addressed: Accounts receivable – account balance Sales – class of transactions Existence Occurrence Completeness Completeness Valuation and allocation Accuracy Classification and understandability Cutoff Classification She has asked that, for each of these assertions, you describe the following at a high level: ► Audit objectives ► Things that could go wrong in TechWear’s sales and accounts receivable system ► Mitigating controls designed to prevent or detect such occurrences ► Audit tests that use data analysis (including graphical views) to obtain evidence ► Data requirements needed to run the tests Your senior has already interviewed a number of key personnel (Director of Marketing and Sales, Shipping Supervisor, Business Office Director (responsible for billing and collections), Accounting Supervisor and the IT Director) and taken notes during these interviews, as follows: Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 General notes TechWear has some very aggressive growth goals (targeting $38 million in global sales for calendar year 2016). The excitement on social media has been very encouraging, resulting in very positive trends in the number and size of orders from existing customers, as well as some prominent new customers. Sales personnel are under a significant amount of pressure to meet their monthly targets. The production department is barely able to keep up with the orders. Some have expressed concern that product quality may be impacted in the future unless changes are made to improve the manufacturing infrastructure. Management is planning a major renovation in early 2017 that will greatly expand its production capacity. One individual involved with R&D expressed concern that the product may not function as designed in some less technologically developed environments with sporadic connectivity and slower transmission speeds. Like many start-up companies, TechWear has operated with a very limited staff, resulting in an inadequate segregation of duties. At least two key employees are related, including the Director of IT and a top sales representative. Director of Marketing and Sales The Director oversees a small team of salespeople that market TechWear’s products to distribution outlets. An order cannot be entered into the system unless the customer has been set up in the customer master file, which reflects data such as the customer’s name, identification number (assigned by TechWear), billing information and credit limits. Historically, the process to set up a new customer involved running an extensive background and credit check, which could take up to 30 days. However, due to complaints from sales personnel, management decided to create a “provisional status” that would enable immediate order fulfillment while the credit check is in process. TechWear uses an order-entry system that enables sales personnel to generate an order. Once an order is entered, the system automatically generates a shipping order that appears on the shipping department’s order fulfillment log. Shipping Supervisor TechWear’s goal is to fulfill an order within 24 hours of its receipt. Orders entered near the end of the day (after the FedEx pickup time) are marked as “pending” and cleared out the following business day. At the time of shipment, TechWear’s shipping clerk enters the shipping number into the system and the FedEx identification number (all shipments for the same day have the same FedEx identification number), which then releases the order, resulting in the sales transaction being posted (with a transaction number that matches the shipping number) and an invoice being generated. The invoice is sent either electronically or via mail to the customer. At the same time, the cost of inventory relieved for items sold is automatically generated and recorded based on the shipping number. Typically, the cost of sales ranges between 35% and 45%. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 2 The ERP system automatically logs the date of shipment. A listing of unfilled (pending) orders can be generated from the system based on orders entered that do not list a shipping number, FedEx identification number and shipping weight. One is rarely produced because the company historically has not had a problem with orders being unfulfilled beyond 24 hours. The system allows an order entered late one day (noted as pending) to be overridden and released by the Director of IT as if the order had been fulfilled. The ERP system leaves no trail when this occurs, and simply removes the pending flag. TechWear strongly believes in its products and has an unlimited 90-day return policy that allows a customer to return any product for any reason within 90 days of purchase. Additionally, it warrants its products against manufacturing defects for two years. If a product is returned (which has not happened yet) the Shipping Department issues a credit memo to the customer, which results in a reversal of the sale. Business Office Director (responsible for billing and collections) Cash is collected either via checks received through the mail or a bank lockbox (which is the preferred method, given the size of its customers), whereby payments are posted by the bank directly into TechWear’s depository account. Customer billing disputes, which management says have been rare, are resolved by the Business Office. Thus far, there have been no bad debt write-offs, which management credits to the quality of its products and its extensive background checks. On a quarterly basis, the Business Office reviews an “Aged AR” listing of old accounts warranting further attention. If necessary, an allowance for “bad debts” would be recorded in the general ledger. Actual bad debts would be charged against the allowance. Accounting Supervisor The Accounting Office is responsible for making the bank deposit. It also reconciles the depository bank account at month-end, ensuring that all cash receipts have been accounted for. The main responsibility of the Accounting Office is to monitor daily sales and produce action reports for management. These are system-generated reports that are reviewed by an accounting analyst who flags adverse sales trends. The focus is on meeting company targets. Director of IT The IT Department (consisting of two individuals) is responsible for ensuring that TechWear’s website is running efficiently and is protected from internal and external threats. The Director has “super user” rights and can access any system. His assistant has full read-only rights and can only make changes to test files. The assistant has been charged with monitoring sales activity and ensuring continuity and security. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 3 Required: Complete the audit data analysis planning template on the following page. Note the following: ► This template has been partially completed for you to provide examples about how to complete the template. ► The scope of this case study is limited to internal data (i.e., only internal data is provided and no external data). External data provides a higher form of evidence. Examples of external data include data from a bank, a vendor (like FedEx) or others. Additionally, some internal data supplied externally (such as sales tax filings) often is better than data only used internally. ► Note that testing of journal entries involving the general ledger, IT general and application controls, and controls over the financial statement close process are beyond the scope of this case study. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 4 Audit data analysis planning template Assertion What could go wrong? Auditing objectives Mitigating controls Data analysis auditing procedures Data requirements Accounts receivable – account balance Existence Amounts reported in the financial statements represent valid receivables. An order is placed and a receivable is recorded without any product being shipped. Debits to accounts receivable are only generated from the order entry system. ► Compare debits to accounts receivable against recorded sales. ► Review and test subsequent receipts. ► Evaluate proper segregation of duties by examining postings by source and preparer (e.g., sales only entered by authorized salesclerk). ► Sales amounts ► Cash receipt amounts ► Customer names ► Transaction dates ► Transaction numbers ► General ledger postings by preparer and source (e.g., order entry) A shipping number and FedEx identification number are entered into the system as evidence of order fulfillment and discrepancies are investigated. ► Verify that every sales transaction has a shipping number and FedEx identification number. ► Analyze gross margin percentage trends by month and by customer. ► Customer names ► Transaction numbers ► Shipping numbers ► FedEx ID numbers ► Sales amounts ► Cost of goods sold amounts ► Transaction dates Completeness Valuation and allocation Classification and understandability Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 Assertion What could go wrong? Auditing objectives Mitigating controls Data analysis auditing procedures Data requirements Sales – class of transactions Occurrence Amounts reported in the financial statements represent valid sales. An order is placed and a sale is recorded without any product being shipped. A shipping number and FedEx identification number are entered into the system as evidence of order fulfillment and discrepancies are investigated. Verify that every sales transaction has a shipping number and FedEx identification number. ► Customer names ► Transaction numbers ► Shipping numbers ► FedEx ID numbers ► Sales amounts Completeness Accuracy Cutoff Classification Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 2 Analytics mindset TechWear Part IV: Background: It is now February 6, 2017, and you are ready to begin the year-end audit procedures for the 2016 audit. The client has provided the 2016 data that you requested (Analytics_mindset_case_studies_Techwear_P4.xls) so you can begin your work. Procedures have already been performed by your team to ensure that the data you received is complete and accurate. The data file includes data on two tabs — 2016 AR data and 2016 inventory relief data. 2016 AR data tab The data fields are the same as what you received for 2015, with the exclusion of the ship date. Additionally, the “Type” field includes transaction information for the opening balance (Opening Balance), which reconciles with the 2015 ending balance of $684,491.19 and the unapplied cash receipts (Unapplied Receipts). 2016 inventory relief data tab This data includes the following fields: ► 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. The December 31, 2016, working trial balance shows the following: Accounts receivable $18,114,802.50 (no allowances have been recorded) Sales $37,333,890.86 Cost of sales $14,269,387.17 Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 Required: ► You have been asked to perform each of the following select work steps that are based on the auditing procedures documented in the audit data analysis template from Part III. Perform your analysis in Tableau. ► As you perform each work step, document your findings in detail and propose any audit adjustment using the summary of auditing findings document on the following page. On this document, add or remove “audit findings” rows as needed based on your work. Before you perform the next work step, make certain to remove the corresponding data from your analysis so you can gain the best insights from the work step. ► Prepare a brief, one-page summary of your overall findings for discussion with the audit committee that includes your summary table and any relevant visualizations. Audit work steps 1. Verify that every sales transaction has a shipping number and FedEx identification number. Identify and quantify any sales that have not been shipped (including names of customers and transaction numbers). Propose any necessary audit adjustment. 2. Verify that every shipping number has a recorded sales transaction. Identify and quantify the cost of sales for products shipped without a sale being recorded (including names of customers and transaction numbers). Propose any necessary audit adjustment. 3. Identify shipments that occurred in 2017 for 2016 sales. Identify and quantify any sales and the cost of sales for amounts recorded in the improper period (including names of customers and transaction numbers). Propose any necessary audit adjustment. 4. Analyze gross margin percentages by month and by customer, reporting results in a tabular and graphical form, after considering your previous findings. Identify any percentages that are outside the range of expectations. 5. Develop a trial balance of accounts receivable at December 31, 2016, after reflecting about any proposed audit adjustments. Display this at the customer level with the ability to drill down to the transaction (invoice) level. 6. Develop an aging analysis of accounts receivable at December 31, 2016, after reflecting about any proposed audit adjustments. Use the following aging categories (0–30 days, 31–60 days, 61–90 days, > 90 days and unapplied cash). Display this at the customer level with the ability to drill down to the transaction (invoice) level. Provide a visualization of the amount of accounts receivable in each aging category. Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 2 Summary of audit findings As of December 31, 2016 Sales Cost of sales Accounts receivable $37,333,890.86 $14,269,387.17 $18,114,802.50 Other Audit finding Audit finding Audit finding Adjusted as of December 31, 2016 Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 3 Analytics mindset TechWear Part V: Background: As a result of your preliminary findings, TechWear’s audit committee has asked that you postpone the issuance of your audit report until at least 80% of the accounts receivable balance outstanding at December 31, 2016, has been resolved (e.g., collected, written off or returned). As of May 31, 2017, approximately 80% of the accounts receivable balance has been settled, so it is time to perform your procedures. The client has provided you with the data that you requested (Analytics_mindset_case_studies_Techwear_P5.xls). Procedures already have been performed by your team to ensure that the data you received is complete and accurate. The data file includes two tabs — 2017 AR data and 2016 Unapplied cash. 2017 AR data tab This data includes January through May 2017 cash received, write-offs and returns for 2016 account balances. The data fields are the same as what you received for 2016, with the exclusion of the invoice date. Additionally, the “Type” field includes transaction information for write offs (Write-off) and returns (Returns). 2016 Unapplied cash tab This data includes the same fields as what you received for the 2016 accounts receivable data. This data represents the unapplied cash at the end of 2016 updated now to reflect a “type” of “CashReceipt” and also includes the transaction number, applied to transaction number and invoice date information. Required: 1. Prepare a December 31, 2016, runoff analysis as of May 31, 2017 (demonstrating how the December 31, 2016, balance has “run off,” meaning explain what happened to the beginning balance to get to the ending balance, between January 1 and May 31, 2017). Display this at the customer level with the ability to drill down to the transaction (invoice) level. Document your findings by completing the following table: Adjusted accounts receivable as of December 31, 2016 $15,118,973.57 Runoff: Net returns Net bad debt write-offs Net cash receipts Remaining accounts receivable as of May 31, 2017 Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 1 2. Calculate the 2017 cash receipts on the December 31, 2016, balance by month and provide a visualization of the trend. 3. Determine what amount of the December 31, 2016, accounts receivable balance you believe is collectible. Prepare an oral presentation for the audit committee that includes your audit findings and audit adjustments (continue using the previous summary of audit findings document from question #1), which you will present in class. It should be succinct, requiring no more than 10 minutes to present. Continue to use the previous template to present your audit adjustments for the accounts receivable balance, only as provided below (note that you can add or delete items from the table below if you have more/fewer findings and adjustments than listed). Also, use the visualizations that you have prepared to support your presentation. Summary of audit findings Accounts receivable As of December 31, 2016 $18,114,802.50 Audit finding/adjustment Audit finding/adjustment Audit finding/adjustment Audit-adjusted balance as of December 31, 2016 Analytics mindset case studies – TechWear © 2016 Ernst & Young Foundation (US). All Rights Reserved. SCORE No. 02315-161US 2 Transaction Cust Transaction Cust Name Number Num Date 200460 201190 200323 200175 201651 200796 202235 200566 201238 200348 201702 200216 200878 200650 201271 200248 200945 200125 200671 200381 200698 200973 201744 201284 200716 201345 200718 201844 202021 201053 201930 202315 201062 202005 202380 201531 201127 202049 202426 201571 202133 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 1914 First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place First Place 11/1/2016 11/2/2016 11/4/2016 11/5/2016 11/5/2016 11/7/2016 11/8/2016 11/9/2016 11/10/2016 11/11/2016 11/12/2016 11/14/2016 11/14/2016 11/16/2016 11/17/2016 11/19/2016 11/21/2016 11/22/2016 11/22/2016 11/25/2016 11/26/2016 11/26/2016 11/26/2016 11/28/2016 11/29/2016 11/30/2016 12/1/2016 12/3/2016 12/3/2016 12/10/2016 12/13/2016 12/16/2016 12/17/2016 12/17/2016 12/20/2016 12/21/2016 12/24/2016 12/24/2016 12/27/2016 12/28/2016 12/31/2016 Amount 27,784.00 29,633.46 27,784.00 27,784.00 32,433.64 27,784.00 28,963.30 27,784.00 29,934.05 27,784.00 32,754.89 27,784.00 27,584.00 27,784.00 30,236.63 27,784.00 27,870.84 55,168.00 24,990.00 27,784.00 1,404.40 28,159.48 28,963.30 30,543.67 27,584.00 30,852.52 2,794.00 68,963.30 68,450.41 68,743.14 68,963.30 68,080.30 69,037.66 68,963.30 68,080.30 67,784.00 69,334.66 68,963.30 68,080.30 71,794.28 108,963.30 1,751,911.73 Invoice Date 11/1/2016 11/2/2016 11/4/2016 11/5/2016 11/5/2016 11/7/2016 11/8/2016 11/9/2016 11/10/2016 11/11/2016 11/12/2016 11/14/2016 11/14/2016 11/16/2016 11/17/2016 11/19/2016 11/21/2016 11/22/2016 11/22/2016 11/25/2016 11/26/2016 11/26/2016 11/26/2016 11/28/2016 11/29/2016 11/30/2016 12/1/2016 12/3/2016 12/3/2016 12/10/2016 12/13/2016 12/16/2016 12/17/2016 12/17/2016 12/20/2016 12/21/2016 12/24/2016 12/24/2016 12/27/2016 12/28/2016 12/31/2016 Transaction Number 102406 202405 202407 102425 102426 102427 201128 201129 201130 201150 202068 202070 102430 201169 201170 201570 201572 201576 201579 201587 201901 202088 202090 202091 202113 202114 202115 102448 102449 102450 202093 202095 202135 202425 202428 202448 202451 Cust Num 1843 1909 1911 1862 1864 1863 1901 1900 1903 1905 1903 1900 1168 1907 1908 1913 1912 1911 1905 1909 1906 1905 1902 1905 1909 1910 1911 1814 1837 1838 1906 1908 1912 1912 1900 1902 1905 Cust Name Southeast Regional Oakland Athletics Awesome Threads Corner Runner Cross Country Mart Fit N Fun Boston Marathon California Clothes Hot Sox Finishers' World Hot Sox California Clothes Bigmart Cool Runnings On Your Mark Great Run Best Time Awesome Threads Finishers' World Oakland Athletics Track Time Finishers' World Fast N Fit Finishers' World Oakland Athletics Millennial Mile Awesome Threads ValueChoice Cool Threads Urban Runner Track Time On Your Mark Best Time Best Time California Clothes Fast N Fit Finishers' World Transaction Date 12/26/2016 12/26/2016 12/26/2016 12/27/2016 12/27/2016 12/27/2016 12/28/2016 12/28/2016 12/28/2016 12/29/2016 12/27/2016 12/27/2016 12/30/2016 12/30/2016 12/30/2016 12/28/2016 12/28/2016 12/28/2016 12/28/2016 12/28/2016 12/30/2016 12/28/2016 12/28/2016 12/28/2016 12/30/2016 12/30/2016 12/30/2016 12/31/2016 12/31/2016 12/31/2016 12/29/2016 12/29/2016 12/31/2016 12/27/2016 12/30/2016 12/31/2016 12/31/2016 Amount 19,290.38 14,757.50 13,082.94 23,576.60 28,040.30 17,396.28 24,687.87 39,490.76 42,276.35 32,005.30 52,437.36 46,255.30 26,764.14 47,608.70 7,649.00 17,142.35 21,509.28 18,034.61 34,297.36 20,493.30 53,239.48 39,518.00 13,592.80 9,736.00 15,524.50 19,287.29 13,929.00 12,982.86 20,614.58 38,905.70 60,169.40 15,966.40 64,188.90 23,576.60 45,643.00 52,982.86 197,264.25 1,243,917.30 Invoice Ship Num Fed Ex ID Date 12/26/2016 102406 42750 12/26/2016 202405 42750 12/26/2016 202407 42750 12/27/2016 102425 42751 12/27/2016 102426 42751 12/27/2016 102427 42751 12/28/2016 201128 42750 12/28/2016 201129 42750 12/28/2016 201130 42750 12/29/2016 201150 42749 12/27/2016 202068 42751 12/27/2016 202070 42751 12/30/2016 102430 42754 12/30/2016 201169 42754 12/30/2016 201170 42754 12/28/2016 201570 42756 12/28/2016 201572 42756 12/28/2016 201576 42756 12/28/2016 201579 42756 12/28/2016 201587 42756 12/30/2016 201901 42754 12/28/2016 202088 42756 12/28/2016 202090 42756 12/28/2016 202091 42756 12/30/2016 202113 42754 12/30/2016 202114 42754 12/30/2016 202115 42754 12/31/2016 102448 42755 12/31/2016 102449 42755 12/31/2016 102450 42755 12/29/2016 202093 42757 12/29/2016 202095 42757 12/31/2016 202135 42755 12/27/2016 202425 42759 12/30/2016 202428 42734 12/31/2016 202448 42755 12/31/2016 202451 42755 Ship Inv Cost Date Relief Amount 1/5/2017 7,909.05 1/5/2017 6,050.58 1/5/2017 5,102.35 1/6/2017 9,312.76 1/6/2017 11,636.72 1/6/2017 7,132.48 1/6/2017 9,751.71 1/6/2017 15,361.90 1/6/2017 16,445.50 1/6/2017 12,802.12 1/6/2017 20,398.13 1/6/2017 18,502.12 1/9/2017 10,571.84 1/9/2017 19,519.57 1/9/2017 3,059.60 1/9/2017 7,114.07 1/9/2017 8,496.17 1/9/2017 7,394.19 1/9/2017 14,233.40 1/9/2017 8,402.25 1/9/2017 20,710.16 1/9/2017 16,202.38 1/9/2017 5,369.16 1/9/2017 4,040.44 1/9/2017 6,442.67 1/9/2017 7,618.48 1/9/2017 5,780.54 1/10/2017 5,322.97 1/10/2017 8,451.98 1/10/2017 15,173.22 1/10/2017 23,466.07 1/10/2017 6,546.22 1/10/2017 25,675.56 1/10/2017 9,430.64 1/10/2017 18,257.20 1/10/2017 21,722.97 1/10/2017 81,864.66 501,271.83 Month Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Grand Total Amount Inv Cost Relief Amount Gross Margin % 787,842.60 316,643.51 471,199.09 2.21% 1,262,096.40 507,940.74 754,155.66 3.54% 1,496,049.80 600,989.27 895,060.53 4.20% 1,746,238.40 702642.82 1,043,595.58 4.90% 1,949,392.05 782,714.11 1,166,677.94 5.47% 2,211,534.73 890,065.89 1,321,468.84 6.20% 2,454,227.03 986,693.86 1,467,533.17 6.89% 3,068,371.81 1,234,631.81 1,833,740.00 8.60% 3,205,816.23 1,288,445.74 1,917,370.49 9.00% 3,899,118.83 1,567,966.27 2,331,152.56 10.94% 6,287,355.82 2,528,760.34 3,758,595.48 17.64% 7,213,935.43 2,861,892.81 4,352,042.62 20.42% 35,581,979.13 14,269,387.17 21,312,591.96 100.00% Nov, 17.64% Gross Margin percentage By month Dec, 20.42% Jan, 2.21%Feb, 3.54% Mar, 4.20% Apr, 4.90% May, 5.47% Jun, 6.20% , 17.64% Jul, 6.89% Aug, 8.60% Oct, 10.94% Sep, 9.00% Cust Name Sales Amount Cost of Sales Gross Margin Percentage Atletico Madrid SA $153,246.89 $61,076.48 $92,170.41 0.43% Awesome Threads $619,826.95 $248,538.80 $371,288.15 1.74% Best Time $869,846.81 $347,175.94 $522,670.87 2.45% Bigmart $1,108,246.92 $445,590.25 $662,656.67 3.11% Boston Marathon $1,114,491.38 $445,911.73 $668,579.65 3.14% California Clothes $1,847,445.51 $740,565.98 $1,106,879.53 5.19% Cool Runnings $1,841,756.64 $738,180.48 $1,103,576.16 5.18% Cool Threads $1,218,132.42 $487,779.29 $730,353.13 3.43% Corner Runner $829,846.81 $332,429.99 $497,416.82 2.33% Cross Country Mart $1,157,143.73 $466,493.22 $690,650.51 3.24% Family Fit $605,897.95 $242,707.38 $363,190.57 1.70% Fast N Fit $947,634.18 $377,835.61 $569,798.57 2.67% Finishers' World $1,903,919.45 $768,288.27 $1,135,631.18 5.33% Fit N Fun $640,859.82 $257,315.45 $383,544.37 1.80% Goodway $1,714,251.11 $689,001.75 $1,025,249.36 4.81% Great Run $645,068.61 $250,289.97 $394,778.64 1.85% Hot Sox $3,769,633.46 $1,512,741.21 $2,256,892.25 10.59% Millennial Mile $943,220.13 $378,392.79 $564,827.34 2.65% Neighborhood Athletic Supply $462,535.80 $185,482.21 $277,053.59 1.30% Northern Lites $694,841.88 $277,697.76 $417,144.12 1.96% Oakland Athletics $706,905.88 $283,309.19 $423,596.69 1.99% On Your Mark $471,251.80 $189,415.41 $281,836.39 1.32% Runner's Market $1,848,391.73 $740,741.48 $1,107,650.25 5.20% Southeast Regional $914,597.73 $366,891.95 $547,705.78 2.57% Southern Runners $2,118,984.90 $855,511.25 $1,263,473.65 5.93% Super Runners Mark $1,788,308.84 $718,543.47 $1,069,765.37 5.02% Track Time $2,168,804.90 $868,449.99 $1,300,354.91 6.10% Urban Runner $1,586,305.20 $635,104.26 $951,200.94 4.46% ValueChoice $890,581.70 $357,925.61 $532,656.09 2.50% Grand Total $35,581,979.13 $14,269,387.17 $21,312,591.96 100.00% Corner Runner 0.00% 1.85% 2.57% ValueChoice Urban Runner 1.99% 1.96% 1.32% 1.30% Track Time 5.20% Super Runners Mark 8.00% Southern Runners 2.65% Southeast Regional 4.81% Runner's Market On Your Mark Oakland Athletics Northern Lites Neighborhood Athletic… Millennial Mile 12.00% Hot Sox 5.33% Goodway 1.80% Great Run 3.43% 3.24% 2.67% 2.33% 1.70% Fit N Fun 5.19% 5.18% Finishers' World Fast N Fit Family Fit 3.14% 3.11% 2.45% 1.74% 2.00% 0.43% Cross Country Mart 4.00% Cool Threads Cool Runnings 6.00% California Clothes Boston Marathon Bigmart Best Time Awesome Threads Atletico Madrid SA Gross margin percentage by Customer 10.59% 10.00% 5.93% 6.10% 5.02% 4.46% 2.50% ValueChoice 4.46% 2.50% Account Receivable Trial Balance CustName TransactionDate Atletico Madrid SA Total Awesome Threads Total Best Time Total Bigmart Total Boston Marathont California Clothes Cool Runnings Cool Threads Corner Runner Cross Country Mart Family Fit Fast N Fit Finishers' World Fit N Fun Goodway Great Run Hot Sox Millennial Mile Neighborhood Athletic Supply Northern Lites Oakland Athletics On Your Mark Runner's Market Southeast Regional Southern Runners Super Runners Mark Track Time Urban Runner ValueChoice Grand Total Amount 153,246.89 $505,856.55 $698,010.81 $ 274,487.87 $854,635.38 $1,345,657.11 $1,266,426.84 $280,354.77 $68,662.48 $178,288.64 $ 39,317.29 $700,756.58 $1,440,519.45 $ 168,993.07 $ 331,784.25 $528,527.01 $2,891,712.26 $691,437.73 $48,286.00 $71,896.79 $520,821.88 $321,415.80 $251,283.76 $138,289.83 $451,270.06 $245,789.76 $1,519,384.90 $ 312,174.56 $63,602.55 $16,362,890.87 $ Cust Name 0-30 Days 31-60 Days 61-90 Days >90 Days Atletico Madrid SA $0.00 $153,246.89 $0.00 $0.00 Awesome Threads $213,384.73 $245,645.42 $113,430.40 $47,366.40 Best Time $350,720.81 $326,876.00 $172,966.00 $19,284.00 Bigmart $235,364.96 $667,818.08 $91,940.75 $177,687.44 Boston Marathon $366,148.74 $421,952.64 $179,116.00 $147,274.00 California Clothes $570,101.60 $886,783.91 $351,216.00 $39,344.00 Cool Runnings $532,385.68 $682,537.64 $581,833.48 $44,999.84 Cool Threads $327,972.99 $555,013.91 $151,139.13 $205,119.52 Corner Runner $214,940.48 $571,456.83 $21,940.22 $21,509.28 Cross Country Mart $207,708.81 $827,226.88 $21,275.25 $122,208.04 Family Fit $110,638.96 $445,601.96 $32,511.50 $46,780.51 Fast N Fit Total $334,761.26 $343,097.12 $224,663.40 $45,112.40 Finishers' World Total $1,006,599.77 $575,319.68 $322,000.00 $0.00 First Place Total $1,006,995.55 $744,916.18 $0.00 $0.00 Fit N Fun Total $116,987.49 $407,005.27 $18,319.28 $111,334.05 Goodway Total $380,135.64 $1,003,415.89 $173,274.72 $203,945.08 Great Run Total $277,857.32 $206,258.29 $101,842.20 $59,110.80 Hot Sox Total $1,240,614.00 $1,549,916.46 $570,310.20 $408,792.80 Millennial Mile Total $292,740.49 $352,294.37 $251,325.07 $46,860.20 Neighborhood Athletic Supply Total$69,953.80 $339,891.00 $27,701.70 $48,286.00 Northern Lites Total $119,458.46 $533,768.63 $59,606.13 $120,884.42 Oakland Athletics $234,671.67 $295,614.21 $158,516.00 $18,104.00 On Your Mark $169,153.00 $167,754.80 $134,344.00 $0.00 Runner's Market $536,018.77 $1,132,844.47 $188,246.91 $42,698.29 Southeast Regional $227,323.37 $587,562.20 $29,771.40 $139,647.14 Southern Runners $319,726.50 $1,183,499.48 $308,482.04 $326,211.72 Super Runners Mark $431,960.34 $915,530.60 $16,509.78 $432,545.90 Track Time $763,677.50 $955,527.40 $449,600.00 $0.00 Urban Runner $394,353.74 $875,522.00 $178,902.99 $187,804.43 ValueChoice $203,715.37 $615,796.50 $45,933.94 $58,794.19 Total $11,256,071.80 $18,569,694.71 $4,976,718.49 $3,121,704.45 Category 0-30 Days 31-60 Days 61-90 Days >90 Days Amount $11,256,071.80 $18,569,694.71 $4,976,718.49 $3,121,704.45 aging analysis of accounts receivable $20,000,000.00 $18,000,000.00 $16,000,000.00 $14,000,000.00 $12,000,000.00 $10,000,000.00 $8,000,000.00 $6,000,000.00 $4,000,000.00 $2,000,000.00 $0.00 Amount 0-30 Days 31-60 Days 61-90 Days >90 Days Type TransactionNumber Write-off CashReceipt Returns CashReceipt Tota Returns CashReceipt Write-off CashReceipt Write-off CashReceipt Write-off CashReceipt AppliedToTransactionNumber Returns Write offs Cash receipts CustNum CustName Awesome Threads Best Time Best Time Cool Runnings Cool Runnings Finishers' World Finishers' World Hot Sox Hot Sox Track Time Track Time ValueChoice TransactionDate Amount ($505.856,55) ($474.578,06) ($64.188,90) ($2.485.589,07) ($48.221,72) ($1.897.728,29) ($31.377,36) ($2.734.310,84) ($294.945,82) ($2.957.644,87) ($117.268,92) ($184.242,97) ($112.410,62) ($949.448,65) ($8.836.365,81) Type TransactionNumber AppliedToTransactionNumber CustNum CustName TransactionDate CashReceipt 24828 101273 1167 Goodway 11-Nov-16 CashReceipt 26064 101273 1167 Goodway 16-Dec-16 CashReceipt 27307 101273 1167 Goodway 27-Dec-16 CashReceipt 24829 101128 1168 Bigmart 11-Nov-16 CashReceipt 26065 101128 1168 Bigmart 16-Dec-16 CashReceipt 26245 101128 1168 Bigmart 17-Dec-16 CashReceipt 27166 101128 1168 Bigmart 26-Dec-16 CashReceipt 24833 101150 1838 Urban Runner 11-Nov-16 CashReceipt 26069 101150 1838 Urban Runner 16-Dec-16 CashReceipt 26474 101150 1838 Urban Runner 17-Dec-16 CashReceipt 24839 101266 1861 Family Fit 11-Nov-16 CashReceipt 26075 101266 1861 Family Fit 16-Dec-16 CashReceipt 26792 101266 1861 Family Fit 20-Dec-16 CashReceipt 24841 101191 1863 Fit N Fun 11-Nov-16 CashReceipt 26077 101191 1863 Fit N Fun 30-Dec-16 Net cash receipts $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ Amount InvoiceDate (4.494,89) 22-Jun-16 (2.312,79) 22-Jun-16 (2.282,15) 22-Jun-16 (4.054,94) 1-Jul-16 (4.106,46) 1-Jul-16 (1.368,82) 1-Jul-16 (1.338,21) 1-Jul-16 (3.673,80) 2-Jul-16 (3.951,80) 2-Jul-16 (2.462,70) 2-Jul-16 (2.750,83) 18-Jul-16 (1.392,90) 18-Jul-16 (696,45) 18-Jul-16 (52,27) 5-Jul-16 (3.663,84) 5-Jul-16 (38.602,85) $ (8.874.968,66)
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

Check that file have finished. If you will have any question just ask.

1
Running head: MeMo

Memo

Student’s Name

Institutional Affiliation

2
Memo
TechWear which is a manufacturer of high-end gear and it is quite critical for the
company to be able to find the relationship between the different partners it does business
with as related to the orders that the company gets, the sales and the need to have a clear
foothold of the transactions that were done. The analysis was done one on the data collected
and it was as due to the need to understand the relationship that exists on the order to cash
function. Verification of the balance for the year 2015 can be seen from the done calculations
which are attached with this memo. Develo...


Anonymous
Super useful! Studypool never disappoints.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags