HR Analytics Dashboard - Download Free Excel Templates

In this guide, you will learn how to create an HR Analytics dashboard in Excel through useful examples and download free resources.

What is the key role of HR in a company?

Today, medium and large enterprises are inconceivable without a Human Resources department. HR is responsible for recruitment, handling employees’ records, and payroll. It also follows up on performance and ensures that employees receive the necessary training for their development. The dashboard shows the differences between targets and goals to track the company’s activities and the employees.

Don’t forget to use the one-page dashboard rule and show the main metrics on a single Worksheet.

HR Analytics Dashboard

In connection with the HR project, we will display the following key performance indicators: Total Salary, Total Bonus, Total Overtime, Total Sick Days, and average related to these indicators.

Formulas to calculate performance

Check the formulas to see how they work! How to calculate the sub-KPIs?

Choose your KPI so you will know the value of sub-KPIs. You can fill the column ‘Weight ‘with values between 1 and 10 according to the displayed value’s importance. If the value equals 1, it represents the least important value in the company; 10 is the most important in the given field.

In the column Description, we briefly describe a key performance indicator. Finally, in the column Performance, you will see the KPIs for each group in percentage format.

How do we calculate the performance of the Employee turnover group? It is a group indicator.

Formula:

=(330% + 255% + 380% + 345%) / 2 = 52%

Download the practice file!

HR Indicators

Employee Turnover

• Cost per Hire: The sum of advertising and marketing fees, employee referrals, relocation expenses, recruiter salary, and other expenses incurred until hiring, divided by the total number of hires.
• Turnover Cost: It refers to replacing an employee who leaves the company. Recruiting a new employee includes hidden expenses like the time it takes to train the newcomer to the same productivity level.
• Turnover Rate: The primary formula for turnover rate percentage is the number of separations divided by the average number of employees in the given month. Partitions include employees who quit, are dismissed, move to another business, or retire.
• Time to Fill: One crucial element in measuring the recruiting function’s efficiency and effectiveness is measuring the time it takes to fill a vacancy. In order words, it means the days between publishing a job post and receiving a signed employment contract.

Recruiting

• Vacant Period: Number of overall days the positions were vacant
• New Hires Performance Appraisal: Average performance appraisal of new hires in comparison to the previous period
• New Hires Performance Appraisal: Evaluation of new employees’ average performance. Newcomers are usually evaluated before the probation period so that the employer can decide whether to extend the contract with the employee or terminate the employment.
• Turnover Rates of New Hires is the ratio of total turnovers among employees hired in the last 12 months to the total number of employees engaged in the previous 12 months.
• Financial Impact of Bad Hire: A bad hire can negatively impact a company. On the one hand, the company pays an employee’s salary, benefits, and hiring costs that do not meet expectations. But, on the other hand, when this employee leaves the company, they most likely need to hire a new one, which costs additional hiring.

Retention

• Learning and Growth Opportunities: This metric shows the percentage of employees satisfied with the learning and training opportunities and the career prospects provided by the company.
• Job Satisfaction: This indicator shows the average satisfaction of the company’s employees. So, there are many different approaches to measuring employee satisfaction with varying questions and weights. The most common aspects are communication with managers, cooperation with colleagues, working conditions, salary, and benefits.

Additional resources:

• Social media dashboard