Task:

Perform Data Analytics analysis using ACL to solve the following questions from your textbook:

7-39 (Payroll), 8-41 (Inventory), 10-37 (Purchase), 12-36 (Purchase), 16-38 (Sales)[1], See pages 5-7 of this guideline

Summarize your answer by using the template on page 2-4 in the upfront of your submission.

Step-by-step print screen (with time and date) is required for each question. No credit will be given for any answers without step-by-step print screen, or print screens without time/date.

oPlease refer to page 13 of this document for examples of Step-by-step print screens (red boxes are optional). This is just for demonstration purpose. Make sure that your screen is large enough to be readable.

oGoogle search on how to do print screen on PC or Mac.

Data Files:

Data files used in this assignment are built in the ACL assignment. Specific data file is specified ied in each question's requirement. In each video, I will also talk about which data file to be used.

Summary of answers

Q7-39

a)

Number of payroll transactions =

b)

Largest:$Smallest:$

c)

$

d)

Number of different pay periods:

e)

Number of exceptions:

f)

Number of gaps:

If you were to identify gap in the sequence, what would be your concern?

Q8-41

a)

Total amount of invoices generated during the year =

_______ invoice has a negative value

b)

Total amount of invoices still outstanding = $

c)

Q10-37

a)

The total of the Amount column for all Pcard purchases is $

b)

Number of Pcard transactions that are >$1,000 each:

c)

Vendor NameCountAmount

d)

How many transactions are exceeded $1,000?

e)

How many transactions are between $990.00 and $999.99?

Q12-36

a)

Total amount of all purchases = $

b)

The stratum accounts for the greatest number of purchases is

c)

Number of duplicate in purchase order number:

Number of gaps:

Number of missing purchase order numbers:

Possible explanation:

d)

_____________ purchase orders do not have a requisition number.

The total dollar amount of purchases made without a requisition = $

Concerns:

e)

_________ vendor accounts for more than 5% of total purchases.

Q16-38

a)

Number of outstanding invoices =

Total dollar amount of the outstanding invoices = $

b)

Total outstanding amount is

The customer number _______________has the largest balance due.

c)

The smallest account balance outstanding = $

The largest account balance outstanding = $

d)

_________invoices are more than 90 days outstanding.

How will the auditors use the aging information in the audit of accounts receivable?

e)

Bonus point question

Join the sales_order, invoice and shipment then create a three-way-match form. What's the total sales order amount of 2014?

f)

Bonus point question

Analyze the data of account receivable outstanding and identify any suspicious outstanding receivablesusing Benford's Law.

Present your chart comparing Expect% and Actual% below

Chapter 7 Question 7-39 Payroll (P. 219)

How many different pay periods are there?

How many payroll transactions have a net pay that is not equal to the amount in the file?

Chapter 8 Question 8-41 Inventory Valuation (P. 260)

Hint: Try another "Output" method in Summarize.

Chapter 10 Question 10-37 Purchase (P. 334)

b. Pcard purchases over $1,000 are required to be reviewed by a supervisor. How

many purchases are over $1,000?

d. For the vendor with the largest total amount identified in part c., identify the

number of transactions that exceeded $1,000 for subsequent follow-up. (Filter and

Quick Sort)

How many transactions are between $990.00 and $999.99?

5,898,195.69

Chapter 12 Question 12-36 Purchase (P. 405)

Hint: Try different output method and click under Subtotal

Chapter 16 Question 16-38 Sales and Collection Cycle (P. 563)

Bonus point questions (2% extra/each question)[YL2]

e. Join the sales_order, invoice and shipment then create a three-way-match form. What's the total sales order amount of 2014?

f. Analyze the data of account receivable outstanding and identify any suspicious outstanding receivablesusing Benford's Law.

[1] Changes made to the textbook questions are in red letters.

[YL1]Deduct 10% for each failure in complying with any of these submission requirements.

For example if you got 6/7 on this ACL project, however you forgot to put your redID. You will lose 10%*6=0.6 on this project and will only earn

6-0.6=5.4/7 on this project.t

[YL2]For Example, if you are able to solve e correctly and you already earned 7/7 on this ACL project, your final score with the bonus point will be 9/7

If you are able to solve both e and f,and your earned 7/7 on this ACL project,your final score with the bonus point will be 11/7