Data Analytics Assignment

Data Analytics (ACL) Assignment (12%) ACCTG 431 (Fall 2016)

1

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 2-4 of this guideline

Submission: Submission must be in hard copy. In the header please specify “ACCTG431, Fall 2016, Section, Name and RedID).” All pages must be numbered.

Due Date: ACL skill assignments is due by 3pm on December 16, 2016 (SSE-2415). Early submission (fifty percent bonus point) is accepted in the classes before December 6.

How to use ACL

Self-study Students are strongly encouraged to self-study the ACL software by any of the following

• ACL instructions (after page 7 of this guideline), adopted from 15th edition of textbook. • ACL-in-practice: http://highered.mheducation.com/sites/0078025613/student_view0/acl_student_.html • Other useful materials can be found on ACL Academy, Youtube or Google

ACL workshop • A 60-minutes ACL workshop is scheduled in the class time on December 13, 2016. Yan Luo will demonstrate the key data

analytics functions using ACL. Data Files2: The data files for ACL can be downloaded from:

• https://drive.google.com/file/d/0BxrPOaBhayzBcWRhSXctaVRoMjA/view?usp=sharing

• Click Download on the upper right hand corner ACL-Lab For students need to access ACL software installed on Windows

• Gene Whittenburg Accountancy Lab o Location: EBA 113, Five desktops o Limited time slots; Sign-up for the lab hours required. Required sign-up 48 hours in advance by using the following link: o https://docs.google.com/spreadsheets/d/1viftFqlNiYc5EiHQXiULFcky1NltvmnN0FiKwgDANKg/edit?usp=sharing o ACCTG 431 GA will stay with you during your usage of the lab.

• Wells Fargo Financial Markets Lab o Love Library (Room LL 261), Computer #10,18,19 o Available time slots can be found below

https://cbaweb.sdsu.edu/news/stories/2012/10/wells_fargo_financial_markets_lab o NO GA stay with you during your usage of the lab

Help needed? 3 Questions about 1) ACL installation; 2) import data for your analysis; 3) clarification of assignment questions, please contact [email protected]

1 Changes made to the textbook questions are in red letters. 2 Normally your ACL installation will load the data files for your automatically. In that case, you do NOT need to download the data files again. 3 GA will NOT reply specific questions on how to use ACL function or how to solve assignment questions.

Data Analytics (ACL) Assignment (12%) ACCTG 431 (Fall 2016)

2

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)

Data Analytics (ACL) Assignment (12%) ACCTG 431 (Fall 2016)

3

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?

Data Analytics (ACL) Assignment (12%) ACCTG 431 (Fall 2016)

4

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

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

APPENDIX

ACL Instructions

Finding Companies, Tables, and Using Commands

830 APPENDIX

Click Sample_Project to open the tables under Sample_Project � Click the plus sign next to the yellow tables folder � Click the plus sign next to the yellow accf'unts payable subfolder.

Observe that there are now two blue lined boxes under the accounts payable subfolder. Each of those represents a table.

Double click AP_Trans and observe that a typical ACL table labeled AP_Trans for accounts payable transactions opens. On the bottom row of that screen, the

number of records in the table is listed (102). The table includes columns with titles and data in each column. That is the information that auditors verify using various commands.

Click the top of the Invoice Amount column on the table ( make sure the column is now all dark) � Go to the Windows drop-down options at the top of the screen and select ''Analyze" (on the Menu bar)� Under ''Analyze," select "Total Fields" (on the drop-down list).

The amount shown on the screen should be 278,641.33. You just used an ACL command (Total Fields) to calculate the total of the invoice amounts in the table. That sure beats using an adding machine or reentering the data in Excel. (Note: Commands can be accessed through the Menu bar or using the icons at the top of the screen. In this text we use the Menu bar.)

Click the small x below the large X at the top right hand side of your screen to close that window and return to the AP_Trans table.

� Click the small x below the large X to close the AP _Trans table. T his will return you to the "Welcome to ACL" screen; "Sample Project" will be open on the left side of the screen.

� Click the large X to exit ACL.

You will use the remainder of this material to help you answer the assigned questions in the text. The first three functions (Quick Sort, Filters, and Computed Fields) are not considered ACL commands. The remaining functions included here are all ACL commands. You will use these commands in ACL homework problems that are included in selected chapters. The descriptions below provide a brief overview of the functions that will be used to complete those problems. Within each ACL homework problem in the text, you will see the commands needed to answer the problem listed within parentheses in that problem.

Quick Sort Used to sort data in any field; either from lowest to highest or vice versa.

1. Click on the column heading of the field you want to sort. 2. Right click using your mouse to find the � Quick Sort Ascending or Quick Sort

Descending. (You can right click Quick Sort Off to undo the sort.)

Filters Used to ask questions of data in a table without adding a new field.

1. Click the Edit View Filter button tiJ in an open table to enter the Edit view filter window.

2. Use the Expression box to build an expression. 3. The Expression box is where you build filters using the available fields in the

current table, as well as the operators(=,< >, AND, etc.). There are three com­ ponents to a filter: (1) field, (2) operator, and (3) a numeric value, character value, or date.

4. In the Available Fields portion of the window, double-click the name of the field for which you are building a filter. Notice that the field name is inserted in the Expression box.

5. Use the operator buttons (=, +, <, >, etc.) and the numeric keypad on the key­ board to build the filter. Note: If you are performing a recalculation to determine if the original calculation already in the data table is correct, use <> as the operator with no spaces between the less than and greater than signs.

6. Enter an appropriate string or value after the operator. Use the following guidelines:

• Numeric values-enter as a number with no commas or dollar signs. For example, to enter $1,000, type 1000.

• Character values-enclose with one or two quotations. For example, to enter department DlO, type "DlO" or 'DlO'. Use the same case as is used in the data field.

APPENDIX 831