Showcase Excel - HR Analytics Dashboard Part II - Light Theme

Don't forget to go to tab "DATA" and hit the "Refresh All" button under Data (top ribbon)!

Data

• Kaggle
• Dummy data
• CSV/Excel File Type
• 1000 records
• 14 fields (columns)

Tools

• Excel: intermediate Pivot and nested functions, special attentions goes to rounded line graph, lollipop chart and handmade uniform slicer panel.
• Power Point: Formatting features, adjusting color scale settings and implementing in Excel.
• Chat GPT: Desk research (Internet) insights frequently asked HR KPI’s (such as turnover, new & bad hire retention, length of service).

Business Task

Execute “Descriptive Analysis” for a HR department and deliver dashboard (report) that contains:

• Turnover (rate & trend) over the years
• Demographical/job title/department distributions
• Investigate possible correlation(s)
• Discover trend(s)

Brief Insights - The Analysis

• In 2022 KPI Total Salary is 4,4% higher than 2021
• In 2022 KPI Average Salary is 5,6% higher than 2021
• In 2022 KPI Average Age 36 remains the same as 2021
• In 2022 KPI (Count) Hired Employees (75) remains the same as 2021 (76)
• In 2022 KPI (Count) Left Employees – Turnover/Attrition (9/10,7%) remains the same as 2021 (9/10,6%)
• In 2022 KPI New Hire Retention (NHR) (9/10,7%) exceeds 2021 (4/4,7%). These are employees who leave the company within one year. The year 2022 contains the highest score NHR since 1996 (10,0%), “Negative Trend”!
• Trend Hiring new employees keeps increasing 1993 – 2022. In this period total 897 employees were hired and 103 left the company. The reasons why they left wasn’t available in the dataset!
• In general the Length of Service (LOS) active employees in total & average days keeps declining (period 1993 – 2022). It seems like employees do resign faster.
• In 2022 the IT department has the highest score for hiring (20) and turnover (3). This is in line with the trend 1993 -2022. This can be a risk if you also take into account all technological developments besides as I mentioned earlier that employees resign faster nowadays.
• Gender: Overall active male (433/43,3%)/female (464/46,4%) is generally equally present. Overall inactive male (52/5,2%)/female (51/5,1%) was generally equal present
• Higher Management (Vice President, Director and Managers): In 2022 (2) employees (higher management) left. In 2022 (7) employees (non-management) left
• Job Title: In this period 2011-2019 you'll see the greatest terminations (47/46%) from the overall (103) terminations. Top 2 Job Titles 2011 - 2019: Vice President (6), Sr. Manager (6) 12 from total 47 = 25%. Top 3 Job Titles Grand Total: Vice President (10), Sr Analyst (9) en Sr. Manager (9) - 29/103 = 28%
• Age (Groups): Age Group 45 – 54 contains the highest active employees (61) and also the highest turnover (30). Second place 25 – 34 active (222) and inactive (27). Age Group 25 – 34 contains the highest salary costs in 2022 for $5,9M (top 3 Engineering (1,2M), IT (1,1M) and Accounting (0,9M). Over a longer period there is a “positive” linear relationship between the average days LOS left employees and average age. Main mutations take place in the age (group) 35 – 60 with an average LOS in days between 2.000 – 6.000. Also discovered outliers at the age 62.
• Over a longer period there is a “negative” linear relationship between the average days LOS left employees and Salary. Main mutations take place till a salary $300.000 with an average LOS in days between 2.000 – 6.000. Also discovered outliers at the from salary above $300.000. The majority of mutations occur within the non-management group.

Recommendations

• Secure onboarding process starters (feeling welcome)
• Communication is key: Stay in touch - stay connected from the start (new hires)
• Communication is key: Implement regular work meetings and/or feedback conversations
• Conduct short customer surveys in the first year to empower/research overall employee satisfaction (special attention to IT department)
• Brainstorm ways to reduce retention in positions where it’s often more challenging to keep employees
• Upper Management should periodically (e.g. yearly) establish clear (HR) goals
• Organize/discuss timely periodic HR-Reporting and hold management accountable when exceeding established KPI’s
• Make use of Data Driven Decisions (e.g. forecasting)
• Examining Recruiting/Onboarding Process and Mismatches (especially NHR)/Exit Process