More Related Content
Similar to City of Carlsbad - Employee Data Analysis (20)
City of Carlsbad - Employee Data Analysis
- 1. City of Carlsbad Employee Data Analysis Review 4/2/2015 Andee Johnson Sr. Management Analyst Candidate
- 2. Table of Contents • Assignment/Objective • Basic Employee Statistics • Average Pay by Tenure Rate (year) • Average Pay by Tenure (years) and Department • Employees at Minimum Pay Rate by Tenure (years) • Summary of Health Plans • Executive Summary of Analysis • Questions • Notes/Caveats • Appendix 2
- 3. Assignment/Objective • Give an oral presentation (maximum 20 minutes). • Prepare a PowerPoint slide deck designed to give an executive summary of the data contained in the Excel file received. • The executive summary should include your analysis/findings regarding trends and patterns noticed within the data. 3
- 4. Basic Employee Statistics Department Employee Count Employee % of Total Average Pay/Hour (Dollars) Employees Receiving Max Pay (%) Employees Receiving Min Pay (%) Average Years of Service (Tenure) FIRE 70 16.4% $29.72 12.9% 0.0% 1.66 LIBRARY 176 41.1% $17.89 25.0% 0.0% 7.52 PARKS & REC 182 42.5% $11.80 1.6% 27.5% 3.36 GRAND TOTAL 428 100.0% $17.24 13.1% 11.7% 4.79 • Parks and Rec employees compose the largest portion of total employees but have the lowest pay on average and are the only group to have employees receiving minimum pay. • Fire Employees make the highest average pay but are the smallest group. • Library employees make the highest percent max pay but also have the longest tenure. 4
- 5. Average Pay Rate by Tenure (years) $14.38 $18.23 $16.38 $19.12 $22.31 $17.24 114 170 75 40 29 428 0 50 100 150 200 250 300 350 400 450 $0 $5 $10 $15 $20 $25 <1 >=1-5 >5-10 >10-15 15+ Grand Total Count AveragePayRate Average Pay Rate Count • Employees with more tenure, on average, earn more. • Tenure rate ranges are in years. 5
- 6. Average Pay by Tenure (years) and Department • Employees with more tenure, on average, earn more, but this is dependent on the department (tenure rate distribution in years). $27.10 $30.68 $31.54 $29.72 $14.31 $16.28 $17.08 $19.76 $24.27 $17.89 $10.77 $11.87 $12.62 $14.67 $16.13 $11.80 $17.24 $0.00 $5.00 $10.00 $15.00 $20.00 $25.00 $30.00 $35.00 <1 >=1-5 >5-10 <1 >=1-5 >5-10 >10-15 15+ <1 >=1-5 >5-10 >10-15 15+ FIRE FIRE Total LIBRARY LIBRARY Total PARKS & REC PARKS & REC Total Grand Total Average of HOURLY PAY RATE 6
- 7. Employees at Minimum Pay Rate by Tenure (years) DEPARTMENT Tenure Range (years) Average of Hourly Pay Rate Count Average of Time In Job (years) PARKS & REC <1 $9.78 23 0.71 >=1-5 $10.41 22 2.41 >5-10 $11.60 5 6.26 PARKS & REC Total $10.24 50 2.01 DEPARTMENT Average of Time In Job (years) PARKS & REC 2.01 • The only employees receiving the minimum pay rate are in the Parks & Rec department and some are quite tenured. 7
- 8. Summary of Health Plans 9% 80% 11% 80% 11% 9% 32% 33% 35% 48% 32% 21% 0% 10% 20% 30% 40% 50% 60% 70% 80% 90% FAMILY SINGLE SINGLE +1 FAMILY SINGLE SINGLE +1 FAMILY SINGLE SINGLE +1 FAMILY SINGLE SINGLE +1 FIRE LIBRARY PARKS & REC ALL %OFDEPT % OF DEPT DEPT COUNT = 70 DEPT COUNT = 176 DEPT COUNT = 182 TOTAL COUNT = 428 • Each department has vast differences in the health plan they have chosen as a percentage of total department employees. 8
- 9. Executive Summary of Analysis • Parks and Rec Employees compose the large portion of total employees but have the lowest pay on average. • These are the only employees making the minimum pay but some are quite tenured. • Fire Employees make the highest average pay but are the smallest group. • Library employees make the highest percent max pay but also have the longest tenure. • Employees with more tenure, on average, earn more, but this is dependent on the department. • Each department has vast differences in the health plan they have chosen as a percentage. 9
- 10. Questions? Thank you for your time!!! 10
- 11. Notes/Caveats • Average Years of Service and Tenure (years) are calculated based on the hire date through 3/29/2015. • Since hours per week worked per week and full time/part time were not included, an assessment of total payroll costs could not be included. • In addition, if an employee took a leave of absence and/or left and came back, the total years of service could be flawed in some cases. • Federal Minimum Wage 2015: $7.25 • California Minimum Wage 2015: $9.00 11
- 12. Appendix 12
- 13. Pay Rate and Time in Job by Pay Range and Department 13 Dept Count Average of HOURLY PAY RATE FIRE 70 $29.72 FIRE 1 32 $26.83 FIRE 2 21 $29.09 FIRE 3 17 $35.94 LIBRARY 176 $17.89 LIB 1 47 $11.51 LIB 2 87 $16.76 LIB 3 42 $27.39 PARKS & REC 182 $11.80 PR 1 20 $9.09 PR 2 102 $10.66 PR 3 50 $13.46 PR 4 10 $20.68 Grand Total 428 $17.24 Dept Count Average of Time In Job FIRE 70 1.66 FIRE 1 32 0.75 FIRE 2 21 2.03 FIRE 3 17 2.93 LIBRARY 176 7.52 LIB 1 47 5.25 LIB 2 87 7.65 LIB 3 42 9.78 PARKS & REC 182 3.36 PR 1 20 2.09 PR 2 102 1.89 PR 3 50 5.83 PR 4 10 8.41 Grand Total 428 4.79