HR KPI Scorecard

Learn how to create an HR KPI Scorecard in Excel using modern charts to visualize the most important human resource indicators.

If you are looking for a simple but useful template, you are at the right place. The first example uses a grid layout visualization. The second example is HR Analytics which uses typical HR metrics like Turnover, Recruiting, Retention, and Training & Development.

HR KPI Scorecard: Metrics

Key Performance indicators play an important role, and we will show the frequently used HR metrics:

• Training effectiveness can help us determine what parts of the training works well and what parts need to improve.
• The acceptance Ratio is 80% if we have ten candidates and eight candidates accepted job offers in the period.
• 90-day quit rate uses the formula: (terminated contracts / new hires) * 100
• Absence Rate: the number of absence days as a percentage of workdays.
• Absence Cost is the cost of an employee’s habitual absence from work.
• Benefits satisfaction is the level of contentment employees feel with their job.
• Productivity rate: It’s simply: Output / Hours worked
• Satisfaction index: gives us feedback on how satisfied employees are with their current situation.
• The engagement index results from a survey in which employees assess their engagement at work.
• Internal promotion rate evaluates the rate at which open positions are filled using an internal promotion within the organization.
• Net Promoter Score: We are using it with a single-question survey.
• Quality of hire shows how much a new hire contributes to a company’s success.
• The turnover rate is the % of employees left a company within a given period.

Build a HR Scorecard

Let’s take a quick overview!

• Drop-down list: If you want to create a kpi scorecard, use a drop-down list. This small form of control helps us to select the month that we want to display. For example, we use 12 months to track and analyze the KPIs, as mentioned earlier.

• Take a closer look at the data visualization. In this case, you can find useful information on it. Our HR KPI template displays the indicator’s name, actual value, and trends using a panel chart style. We need to show the variance between the actual months and previous months also.

Conditional formatting is a must-have tool if you want to highlight cells. So, if you find a space-saving solution, use scorecards to present your data.

Download the practice file and check the details.

Human Resource KPI Dashboard

What is the key role of HR in a company? Medium and large enterprises today are inconceivable without a Human Resources department. HR is responsible for recruitment, handling employees’ records, and payroll. Follow up the performance and ensure 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.

Let’s take a closer look at the figure below. We will use people graphs to build a dashboard template and manage your HR-related decisions! We created a gauge section to display eye-catching visualization for the main indicators.

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.

We have briefly described a key performance indicator in the column Description. Finally, in column Performance, you will see the KPIs for each group in a percentage format.

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

Formula:

=(3*30% + 2*55% + 3*80% + 3*45%) / 2 = 52%

And with this, we are done with the calculation part of the human resource KPI scorecard. And now, we only have to do is to create the design. So, finally, let’s sum up the phrases used in making the dashboard.

Human Resource 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 the cost of 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

• Preventable Turnover: This indicator refers to the rate of those turnovers that could have been evaded with better management, expressing expectations more clearly during the hiring process, or creating an appealing workplace atmosphere. (When the reasons why the employee leaves are out of the company’s control, we talk about Non-Preventable Turnover.)
• Diversity Turnover: This metric describes the turnover rate in a given group of employees (based on gender, race, or age distinction) compared to the number of all turnovers in the company.

Financial Impact of Employee Turnover

• 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.

Final Words

As you can see, using our Human Resources KPI Scorecard, one can easily present a fancy, appealing insight into the most important and generally used HR metrics.

Download the practice file.

Recommended articles and tutorials:

• How to create an Excel dashboard?
• Excel Chart Templates