ACCT20081 Financial Data Analytics Practical Assessment Weight: 20% Student and Assessment Information Student Information Student Given Name: Click here to enter text. Student Family Name: Click here...

Accounting


ACCT20081 Financial Data Analytics Practical Assessment Weight: 20% Student and Assessment Information Student Information Student Given Name: Click here to enter text. Student Family Name: Click here to enter text. Student Number: Click here to enter text. Campus: Assessment Information This is a Practical Assessment. In this assessment you are required to perform a series of data analytics tasks in SAS Studio. What to submit 1. Complete and submit this document to Moodle. 2. Submit all SAS results as PDFs to Moodle. 3. In total you will be submitting four (4) files only to Moodle. The tasks to be completed are listed below. Before you attempt to start this project, you need to have read, understood and completed all SAS Workshop Activities. PLEASE READ THESE INSTRUCTIONS VERY CAREFULLY BEFORE YOU START. File Formatting and Naming Conventions for this project 1. This file MUST be named using the following convention submit this file to Moodle familyname_student_id_practical_assessment (For example SMITH_s1234567_practical_assessment.docx) 2. SAS programs MUST be named as follows do not submit these files. Paste your answers in the spaces provided after each question below. These files are for your reference only. · Task 1: familyname_student_id_task1.sas · Task 2: familyname_student_id_task2.sas · Task 3: familyname_student_id_task3.sas 3. SAS results MUST be named as follows submit these three files to Moodle · Task 1: familyname_student_id_task1.pdf · Task 2: familyname_student_id_task2.pdf · Task 3: familyname_student_id_task3.pdf Failure to follow the above formatting and naming convention will result in marks being deducted. Assessment Tasks 1. Use the ORION library and write a SAS program that performs the following tasks: [6 marks] The orion.order_fact table contains information about orders that were placed by customers. Create a report that lists the retail customers whose average retail price exceeds the company average retail sales. a) Write a query that displays the average of Total_Retail_Price for all retail prices in the table. · Use AVG(Total_Retail_Price) to calculate the average. · Subset the rows so that only the retail sales are included (Order_Type=1). b) Write a query that displays Customer_ID and AVG(Total_Retail_Price) for those customers whose average retail price exceeds the company average retail price. The query should do the following: · Display the values for Customer_ID and AVG(Total_Retail_Price). Name the second column MeanPrice. · Subset the rows so only the retail sales are included (Order_Type=1). · Group the data by Customer_ID. · Include only groups where the employee’s average retail price exceeds the company average. Use the query from part a) above as a subquery in the HAVING clause. · Add a title to the report c) Include appropriate comments/ documentation so that it is clear to the reader what your code does. d) SAVE your program as familyname_student_id_task1.sas e) SAVE your results as familyname_student_id_task1.pdf Note: Use the Download Results as a PDF File button on the Results tab. Paste your completed SAS program in the space below: 2. Use the ORION library and write a SAS program that performs the following tasks: [10 marks] Produce a report of Orion Star sales force employees’ aggregate sales in 2011. a) Select the Country, First_Name, Last_Name, Value_Sold, Orders, and Avg_Order columns by joining the orion.order_fact and orion.sales tables on Employee_ID. Group the report by Country, First_Name, and Last_Name. Include only employees having an aggregate Value_Sold of $200.00 or more. Order the results by Country, Value_Sold (descending), and Orders (descending). · Calculate Value_Sold by summing Total_Retail_Price. · Calculate Orders by using the count(distinctOrder_ID) function to count the number of unique rows for each employee. NOTE: An employee can have multiple Order_ID row values for different products. Rows with the same orderID do not count as a separate order. · Calculate Avg_Order by dividing Value_Sold by Orders. · Subset the data to return only the rows for sales in 2011. · Title the report appropriately. b) Rewrite the query created in part a) above and use it as an in-line view. Select Country and the maximum Value_Sold, Orders, and Avg_Order values, as well as the minimum Avg_Order value for each country. Name the report 2011 Sales Summary by Country. Hint: An in-line view cannot use the ORDER BY clause. c) Include appropriate comments/ documentation so that it is clear to the reader what your code does. d) SAVE your program as familyname_student_id_task2.sas e) SAVE your results as familyname_student_id_task2.pdf Paste your completed SAS program in the space below: 3. Use the ORION library and write a SAS program that performs the following tasks: [4 marks] Creating a Table and Adding Data Using a Query a) Create a table containing the following columns and name it work.employees: · Employee_ID · Hire_Date · Salary · Birth_Date · Gender · Country · City Format all date columns with MMDDYY10. and Salary with COMMA12.2. Include only current employees (rows where Employee_Term_Date is missing). Order the output by the year value of Hire_Date (a variable that is not included in the output table) and then by descending Salary. NOTE: The data required can be obtained from these tables: · orion.employee_addresses · orion.employee_payroll b) Display the contents of the new table work.employees using an appropriate query. c) Include appropriate comments/ documentation so that it is clear to the reader what your code does. d) SAVE your program as familyname_student_id_task3.sas e) SAVE your results as familyname_student_id_task3.pdf Paste your completed SAS program in the space below: ACCT20081Practical Assessment Page 7 Grading Rubric Deduction for File Naming 0 ACCT20081 TOTAL MARK 0 Practical Assessment Weight 20% Max Mark Achievable Criteria Excellent Very Good Good Satisfactory Unsatisfactory Task 1 Task 2 Task 3 1. Meets Computational Requirements (70%) no errors 1 error 2-3 errors 4-5 errors > 5 errors 4.20 7.00 2.80 2. Structure and Layout (20%) use of indentation, white space, well structured code layout very good evidence of indentation and layout good evidence of indentation and layout some evidence of indentation and layout no evidence of indentation and layout 1.20 2.00 0.80 3. Code is Commented and Documented (10%) excellent use of comments and documentation. Comments include student information, name, student id, program description. very good use of comments and documentation. Comments include at least two of - student information, name, student id, program description. good use of comments and documentation. Comments include at least one of - student information, name, student id, program description. some evidence of comments and documentation. no evidence of use of comments and documentation. 0.60 1.00 0.40 6.00 10.00 4.00 Financial Data Analytics