Data Exercise 2
Download this document to your computer and please read all directions carefully. You may work on this assignment individually or in a group. This exercise is worth 15 points.
If you choose to work in a group, please do the following.
Create a group contract/report consisting of short bullet-point notes on what each person contributed to the work. Submit your contract with your completed work.
Group size no bigger than 5 people
Indicate all group members’ names on your submission
Do NOT collaborate with students outside of your group
Please download the dataset at this link. These data are City of Los Angeles payroll data from 2013-2016. Each row in the dataset includes information on payments made to individuals who work for the City. Other data attributes include department titles, job titles, projected annual salaries (with breakdowns of quarterly pay), bonuses, and benefits information. You will need to create a new worksheet in the provided workbook to conduct your analyses.
Using what you have learned about data analysis in Excel, please answer the following questions. Include a brief explanation for your answers including the steps you took in Excel to perform your analysis. If you encounter problems with the datasets (inaccurate data, missing data, etc.), provide a narrative explanation of how you overcame those problems. Note – there is no single “right” answer to these questions. However, you should use the Excel formulas and functions we have learned plus your analytical skills to produce answers to these questions.
(3 pts) How do benefits and salaries differ for employees across departments (mean, median)? *Note: (You do not need to compare all departments but you should include three or more departments in your analysis)
(3 pts) Are there any unusually large differences between the lowest and highest employee salaries? You can compare across departments or across job classes.
(3 pts) How have salaries changed from 2013-2016?
(3 pts) Have the costs of benefits changed dramatically during the data collection period?
(3 pts) How do payments differ between quarters (i.e. Q1, Q2, etc)?
Turn in
At the completion of the exercise, please turn in the following:
Group contract (if you worked in a group)
Excel workbook with original data and calculations
Word document with answers to the five questions above including a brief description of your analysis in Excel
If you work in a group, every individual in the group should make submit the same materials to Canvas. Every group member must submit the work in Canvas to receive credit.
The post Data Exercise 2 Download this document to your computer and please read appeared first on PapersSpot.
