Option 2 Analytics Mindset auditing theory PDF

Preview

Summary

Option 2 Analytics Mindset auditing theory Option 2 Analytics Mindset auditing theory Option 2 Analytics Mindset auditing theory Option 2 Analytics Mindset auditing theory Option 2 Analytics Mindset auditing theory...

Description

1

RUNNING HEAD: ANALYTICS MINDSET- TECHWEAR

Option #2: Analytics Mindset – TechWear ACT 580 – Capstone Accounting Colorado State University- Global Campus Student Name Date

Analytics Mindset – TechWear

2

Analytics Mindset – TechWear Part I 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.

To verify the account receivable balance, I used the sum function under the amount column. As you can see, the ending balance is $684,491.19 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

Answer: to verify this information, I used filter function to verify the total approved customer and to validate that the customer names are not misspelled. A also did pivot table for customer name which shows 15 customers with correct spelling of customer.

3

Analytics Mindset – TechWear

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.

I filtered the data by Sales Type (Column A) and it shows total of 230 sales. (See excel file). As you can see below, there is no duplicate transaction for same customer with duplicate amount or same transaction date. I used conditional formatting function to find the duplicate value. There is no additional invoice or duplicate in this file.

4

Analytics Mindset – TechWear PART II 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 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. 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. By looking at the chart below, we can say that the December had the most sales and cash receipt compare to all other month. The chart is also showing upward trend, which means March is the month with lowest sale and cash receipt compare to all other months. By looking at the chart, we can also say that the cash receipt and sales are growing each every month significantly. Business is going in the right direction.

5

Analytics Mindset – TechWear $3,000,000.00 $2,500,000.00 $2,000,000.00 $1,500,000.00 $1,000,000.00

CashReceipt Sales

$500,000.00 $0.00

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)

Answer: Based on the visualization, we can see that DSO Ratio is increasing towards the Month to month and highest day’s sales outstanding is in November. It also means that the company is having the credit problem (deficiency in its collection activity) as the sales goes higher.

6

Analytics Mindset – TechWear

DSO 120 100 80 60 40 20 0 ar M

ril Ap

ch

ay M

ne Ju

ly Ju

st gu u A S

m te ep

r be

r be to c O

m ve No

r be

m ce De

r be

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

2%

9%

34%

55%

Percentage of AR Balance by Catego

0 -3 0

3 1 -6 0

6 1 -9 0

>9 0

Based on the chart, we can see that almost half of the ale I due more than a month. It also means that company sell product on credit a lot and customer do not pay for the product immediately. High balance of account receivable is not good for company. There is high chance that company won’t receive this money, especially one with greater than 60 days of age.

7

Analytics Mindset – TechWear Reference...