MAA 789 – Accounting Systems and Analytics – Trimester 3 2020 Assessment Task 2 – Data Analysis – Individual Assignment Assessment Task Questions Excel Assessment Task (Extract, Transform and Load) The Excel question has three parts. Before you begin to answer the questions, you will need to: ? load the data from ‘payroll_data.txt’ and ‘reference_data.txt’ into tabs in an Excel document titled ‘Payroll’ and ‘Reference’, respectively. You can find the two datasets in the assessment resource folder under Content? Assessment Resource?Assessment Task 2 ? save the spreadsheet as ‘Payroll Data_.xlsx’. You can keep up to two decimal points. Part I: Import and Validate Data 1. Fill in the following table based on ‘Payroll Data_.xlsx’. For this question, you would need to use the tab based on the ‘payroll_data.txt’ data. Questions Your Answer How many rows are there? How many columns? What is the average salary? 2. Identify and address two potential data quality issues for the ‘Payroll’ tab of the generated Excel file. Part II: Perform the Transformation of the Data (2 marks each) 1) Create a new column and calculate total compensation (salary plus overtime pay, treating those missing pay as zero overtime pay) in the ‘Payroll’ tab. What is the average total compensation? State any assumptions you make in the calculation. 2) Add a column and calculate the employee’s tenure at the company in years as of June 30, 2020. Keep two decimal points. Take a screenshot of the first 10 observations (the screenshot must include at least employee ID and tenure variables), paste the screenshot below together with the formula you used in Excel for the calculation. 3) Create a binary column (0/1) that identifies employees of the ‘Tasty’ operating units. Name the variable as dummy_tasty. Take a screenshot of the first 10 observations (must include at least employee ID and the binary column), paste the screenshot below together with the formula you used in Excel for the calculation. Page 2 of 4 4) Add columns for Job Position and Gender in ‘Payroll’ tab from the ‘Reference Data’ tab. Take a screenshot of the first 10 observations (must include at least employee ID, job position and gender), paste the screenshot below together with the formula you used in Excel for the calculation. Part III Conduct the Analysis (2+2+2=6 marks) 1) What is the average percentage of overtime pay as of total compensation only for employees who work overtime (i.e., those with overtime pay larger than zero)? Paste below the formula you used in Excel for the calculation. 2) Create a pivot table with an average salary by Operating Unit and State and add conditional formatting. Take a screenshot of the pivot table and paste it below. 3) Create a linear regression model using tenure to predict total compensation. What is the adjusted R-squared value? Take a screenshot and paste the screenshot below.