This sample project examines trends in employee numbers, average annual salaries, hiring trends, employee count by department, employee tenure, and the job sources where most recruited employees applied.

The dataset spans from 2006 to 2018, providing a perfect opportunity to identify any patterns that need attention.

The dataset can be found and downloaded for your reference on Kaggle

Tools Used for this Project: Power Pivot, Data Modeling, Power Query, DAX, Slicers, and the Built-in Charts of Microsoft Excel

Data Preparation

I modified the dataset to suit my needs. Instead of loading it all at once in power query, I divided it into four parts and added some categorization. For example, the Department dataset has two columns: Department ID and Department. The Manager dataset includes Manager ID and Manager columns, and the Position dataset contains Position ID and Position columns. These are my dimension tables.

Now, for the fact table, I downloaded the HR dataset and cleaned and organized the data using the Power Query editor.

Note:
I cleaned the data before loading it into Power Query but still made modifications within the editor to ensure the dataset's integrity. I removed unnecessary columns and formatted the data to fit the project's needs. For this project, I added some helper columns based on my dimension tables, including Position, Department, Manager, Age, Age Bracket, Tenure, and Tenure Bracket to support my analysis.

Data Modeling

For data modeling, since the dataset is not large, I used a relational model for this.

Analysis

In the first part of the analysis, we can focus on the employee structure to identify the total population in the dataset. Here is the summary:

Total Employees: 311
Active Employees: 207
Terminated Employees: 104

Categorized by:
Voluntarily Terminated: 88
Terminated for Cause: 16

And categorized by year

Next, we looked at the departments and positions to identify which department has the most employees and the breakdown of employees by position. In summary, we found that the Production department has the highest number of employees, followed by IT/IS.

The gender Distribution for all employees are summarized below:

All Employees:
Female: 176
Male: 135

Active Employees:
Female: 116
Male: 91
Total: 207

Here we can see that the majority of our workforce is made up of women, both in total employees and active employees.

Next, we need to look into the Age Bracket. This will help us identify the age range of most of our employees.

We can see that most of our employees fall within the age bracket of 40-49 years, both among total employees and active employees.

We can also see that the majority of our employees have been with us for 11-15 years, for both total employees and active employees.

And on this more detailed table, we can see that the Production Department has the most tenured employees which is 55.31%

We can also check which platform our employees used the most to apply. Based on our data, 87 employees applied through Indeed!

We also want to identify the average annual salary of our employees. Based on the dataset, we can see that it is $69,020.68.

The highest average salary is in our IT/IS department, followed by software engineering (excluding the Executive Office).

Now let’s take a look at our hiring trend versus our termed trend.

Based on our hiring trend, we see higher numbers in January and July, while our termed trend peaks in September, followed by April, with a total of 104 employees.

We also want to see the Performance Score of our employees to check if there is a correlation between employee satisfaction and engagement.

Based on our data from the IT Department, the performance score exceeds expectations, with a satisfaction rating of 4.7 and an engagement rating of 4.6. Hence, we can assume that there is a correlation.

Visualization

Based on the analysis, I created a dashboard highlighting the key metrics that had the most impact on our findings. The dashboard is shown below.

For the final output of this analysis. You can download it on Kaggle.

Advertisement

If you have a similar project that you need help with, feel free to contact me.