ACL project
alfarhan22
ACLPROJECTGUIDELINE_2020INSTRUCTION.docx|
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)[footnoteRef:1], See pages 5-7 of this guideline [1: Changes made to the textbook questions are in red letters. ] · 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. · Please 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. · Google search on how to do print screen on PC or Mac. |
|
Submission: Comment by Yan Luo: 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 |
Max 2 students/group · Submission to [email protected] , by noon April 14. Late submission will NOT be graded. · Must have a cover page, including student name, red ID and section number for each student · File name= name_redID_ACL · Subject of your email: name_redID_ACL · All pages must be numbered
|
|
How to use ACL |
Please watch the videos for each question (link posted in the blackboard folder of ACL in the section of course document). Students are also encouraged to self-study the ACL software by using any of the following resources · ACL instructions (pages 8-12 of this guideline), adopted from 15th edition of textbook. · ACL-in-practice: http://www.csun.edu/~vcact00f/460/Project_files/ACL9_ACL_in_Practice_Guide_PDF.pdf · Many other useful materials can be found on ACL Academy, Youtube or Google
|
|
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. |
|
Bonus points: |
Two bonus point questions are presented on page 7. 2% bonus point each. 4% in total |
Data Analytics (ACL) Assignment
ACCTG 431/Spring 2020
3
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 Name Count Amount
|
|
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 receivables using 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) Comment by Yan Luo: 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/7If 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
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 receivables using Benford’s Law.
