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