Overview of Steps
1. Enter Settings (one-time)
2. Enter Starting List of Employees
3. Enter any changes
a) New employees
b) changes in existing employees or
c) Employees leaving company
4. View Dashboard and take actions
Detailed Understanding of each Step
1. Enter Settings
In the Settings sheet, you can enter the values for each of the key fields we use in our Dashboard.
To begin with, Gender, Ethnicity and Hire Source are Employee attributes that we can define.
As shown in the image above, you can enter the values that are applicable for your company.
Next, enter list of office locations and Departments/Business Units.
Job classification is an integral part of HR employee data. Each company may have a different way of classifying /grouping /categorizing jobs. This template allows three fields to do so. Job Type, Job Category and Job Level.
Enter list of values relevant to your company.
Next, enter list of Job Titles.
The template also supports salary grade data. You can define a Salary Grade name, its minimum, midpoint and maximum salaries.
In this template, we don't enter only active employees data. We also enter information on employees who leave the company. You can define Exit Types and Exit Reasons.
And unique to our template is the ability to update employee data if employee profile changes. For example, an employee moves from one role to another, gets promoted, or gets a salary increase. These updates can be categorized.
Age of an employee is an attribute we track in our template and for reporting purposes, we group ages into age buckets/groups.
You can define the thresholds for each bucket and also have a label (name).
Similar to Age group, you can define Tenure groups. Tenure is the duration of time the employee has been employed in the company.
2. Enter Employee Data
Please make sure that you fille out the Employee ID as a unique ID or number for each employee. Please make sure that there are no duplicates.
Also, enter information in all the fields where possible.
Entering data in this sheet is straight forward. The template also provides drop down lists in field values to assist in clean data entry.
3. Entering Employee Updates
When any change happens in an employee's profile (other than employee leaving the company), enter them in the EMPLOYEE_UPDATES sheet.
AS OF DATE is the date when the change becomes effective.
RECORD REASON is the reason for making the update record.
Then, enter employee's new attributes like Location, Department, Job Type, Job Category, Job Level, Job Title, Manager, Salary and Salary Grade.
Important: Enter all the fields even if change is happening in only one field. For example, an employee moves from one Department to another with all other attributes (like location, job title, etc.) remaining same. You will still have to enter all the fields.
Enter Employee Exits
When an employee leaves the company, enter data in the EXITS sheet.
These are the only data entries you need to do!
4. View HR Admin Dashboard
The Dashboard sheet is fully automated and it has 5 reports in it.
1. Headcount Report
As the name indicates, this report shows the number of active employees right now. It also breaks down the number of employees by Department, Location, Job Type, Job Category, Job Level, Gender, Age group and Tenure group.
2. Diversity Report
It is an extremely important responsibility of HR organization to ensure that the workforce is diverse.
The diversity report shows the Gender mix, Age group mix, Tenure mix and Ethnicity mix of active employees.
In addition, it also shows the mix in each department. This allows you to identify if there are any departments where the diversity mix is less than expected/desired.
3. Salary Grade Report
Salary Grade report shows the minimum and maximum Salaries set for each salary grade. (Please see chart on top left).
The top right table shows the salary grades and their key attributes (min, midpoint, max, spread, range, midpoint differential, and overlap), enter # as 1 to populate the table in order of the Salary grade given in the settings.
The chart on bottom left shows the min and max of actual salaries of the active employees.
Finally, the bottom right chart shows the comparison of the median annual salary of active employees vs the midpoint set for each salary grade.
4. Salary Distribution Report
The report shows the salary distribution across organizational and employee groups.
As an example: of the total annual salary of all active employees, what % is for employees in IT vs Sales vs Marketing?
5. Pay Equity Report
Just like Diversity, Pay Equity is another very important and sensitive topic.
It has to be ensured that within specific salary grade, employees are treated fairly when it comes to Pay.
This report shows the median salary by Gender, Ethnicity, Age Group and Tenure Group. You can filter to specific Salary grade using the slicer at the top right.
All the above charts can be filtered by several filters. The list of filters is shown below.
Finally, we have the Trends Report in Trends sheet.
6. Trends Report
All the previous reports showed the data of currently active employees.
It is also important to measure the progress over time of the HR metrics. That's where the Trends report come in.
You can view the trend of headcount, median tenure, median age and median salary.
You can filter this report also using slicers. For example, you can see the HR metric trends for specific department or location.
You can see the 12 month trends dynamically for any window by entering any end date.
