Comprehensive HR Analytics Dashboard for Workforce Insights and Turnover Analysis

This project showcases a comprehensive HR analytics system built using Excel, integrating multiple datasets to analyze employee demographics, tenure, performance, and termination reasons across departments. The solution merges structured HR data with departmental and performance metadata, delivering actionable workforce insights for strategic HR decision-making.

Key Components:
Employee Master Data (HR Database Sheet):

Age, gender, hire/termination dates

Position, education level, department, salary

Exit reason breakdown: resignation, dismissal, mutual agreement

Performance Data (EmployeeInfo Sheet):

Performance review scores (1–10)

Promotion status and salary alignment

Overdue vacation flags and location mapping

Department Structure (DeptInfo Sheet):

Manager-to-department mappings for org hierarchy

Pre-built Summary Tables (Visuals Sheet):

Gender distribution

Average tenure by department

Performance score trends

Dashboard Sheet (Structure Present):

Placeholder for future KPI visualizations

Key Insights Uncovered:
High attrition risk in Legal and Strategy departments based on average tenure under 4 years

Performance scores above 9 found in Legal and Finance, suggesting strong retention potential

Employees with overdue vacation days also had longer tenures, indicating policy enforcement gaps

Dismissal rates and unfair dismissal concentrated among younger hires (ages 22–29)

Gender parity is achieved in overall staff composition (56% male, 44% female), though senior roles skew male

Technical Approach:
Excel Functions Used: VLOOKUP, IF, COUNTIF, AVERAGEIF, INDEX-MATCH

Pivot Table Analysis: To segment data by department, performance, and tenure

Visual Summary Preparation: Department-level performance bars, pie charts on termination reasons

Preparation for BI Tools: Database structure optimized for Power BI or Tableau export

Skills Demonstrated:
HR data modeling and dashboarding in Excel

KPI identification and breakdown: tenure, attrition, performance

Cross-sheet data integration and lookup logic

Structuring HR data for analytics and BI transformation