HR KPI Scorecard and Dashboard Excel Template Tutorial : Indzara Support

This article will present the step by step tutorial on how to use the HR KPI Scorecard and Dashboard Excel template.

Product page: https://indzara.com/product/hr-kpi-scorecard-dashboard/

Overview of steps:

Enter the starting month and define KPI Categories
Define KPIs in KPIs sheet
Enter Monthly Actuals data in Actuals sheet
Enter Monthly Targets data in Targets sheet
Control the Dashboard & Scorecard settings in this sheet
View Dashboard/Scorecard sheets
View KPI View sheet for specifics on one KPI
Enter Comments in Commentary sheet.

Detailed step by step:

Enter starting Month and KPI Categories.

Starting Month:

If you have monthly data for your KPIs from Jan 2020, enter 01-Jan-2020 as the Starting Month.

The template will use the starting Month and allow tracking data for 24 months. For example, 01-Jan-2020 as Starting month will allow tracking until Dec 2021.

This month is also considered as the starting month for Year to date calculations. For example, if your company reports Apr – Mar as the reporting year, then you would enter 01-Apr-2020 as the starting month and that would allow tracking 24 months until Mar 2022.

KPI Categories:

The template can be used to categorize the KPIs. There are 8 possible categories and these can be customized for your needs. You can remove by just selecting the cells and clearing the contents or deleting. You can rename existing ones or enter your own new categories.

Note: The Scorecard sheet displays the KPIs grouped by category.

Enter KPIs in the KPIs sheet

You can type the information in KPI table.

KPI – Name of the KPI. Please keep it short as it will be displayed in the Scorecard and Dashboard and we would want it to be clearly visible and legible. You can use abbreviations if appropriate to shorten this text.

KPI Category – Choose from the drop down of KPI Categories

Format – The template supports 13 different formatting options.

Each KPI can have a different number formatting applied as needed.

Default currency is $. However, you can modify it in the Settings sheet.

Better

Enter whether Higher value or lower value is better for the company.

For example, a company will aim to keep the Recruiting Cost and Turnover Rate lower, while aiming to keep the Retention Rate and Productivity Rate higher.

Suffix - The template allows giving a suffix that will be displayed in Scorecard and Dashboard.

For example, if a KPI is measured in days and another measured in Hours, you can enter that in the Suffix. This will ensure that the reader of the Scorecard understand what the KPIs represent.

Definition – a brief definition of the KPI. This will be displayed in the KPI view sheet.

Aggregation Type – this is to determine how the KPI should be aggregated when we do Year to Date calculations.

Add All Months: For example, if the KPI is # of hires, to get the year to date number, we need to add all the months’ values together.
Average All Months: For KPIs like productivity rate, we want to provide the average of all months.
Weighted Average: For KPIs where the base changes every month, we should do weighted average instead of average. For example, Recruiting cost per hire was $500 in January when we hired 10 employees. We hired only 1 person in February and the recruiting cost was $700.
1. Year to Date KPI value cannot be $600 (average of $500 and $700). It should be ($500*10) + ($700*1) / (10+1) which is $518.18
2. The weights have to be provided for the template to calculate the weighted average. You can choose another KPI for the weights. For example, Recruiting cost per hire KPI can have # of Hires as the Weights. We will enter that in another column which will be explained shortly.
Latest snapshot: Some KPIs should not be aggregated at all. For example, # of employees, Women Employees %, Diversity Ratio. It would be more appropriate to compare the snapshot at end of month.

Comparison Calculation – how we should calculate the comparison against the previous period for a KPI

The two options are Change and Change %

When calculating Year over Year or Month over Month, we can either calculate absolute change or % change

Target – How the comparison against target should be calculated

Two options: Variance or % Achieved

Variance is calculated as Actual – Target

% Achieved = (Actual/Target)*100

Weights based on – this field is required only for KPIs that need Weighted Average type of Aggregation.

When we discussed Aggregation types above, we mentioned that the weighted averages can be calculated. The weights have to be provided for the template to calculate the weighted average. You can choose another KPI for the weights. For example, Recruiting cost per hire KPI can have # of Hires as the Weights.

Note: After you start entering the Actuals and Targets data (to be explained in the following section), please do not edit the KPIs order in the KPIs sheet. You can rename the KPIs and details. But the order should not change. You can add more KPIs to the bottom. But please do not delete KPIs.

So, before you move to the next step, please make sure that the order of KPIs is what you prefer to have.

Enter Actuals data in Actuals sheet

We enter the Actual KPI data in the Actuals sheet. A sample is shown below.

The KPI names will auto-populate. You have to enter only the values.

The sheet is designed to handle all types of numeric data. Just type in data without any specific format applied.

Entering different formats of data:

Whole Number
1. Entering whole numbers as they are. Example 30
Number with decimals
1. You can enter the decimals needed. For example, 0.45
Currency
1. For 100 dollars, you can enter as 100 or $100.
Percentage
1. Example: When entering a percentage, you can enter 80% or 0.80. Our calculations can handle both. If you enter 80%, Excel will convert the cell’s formatting to Percentage. If it is entered as 0.80, Excel will retain ‘General’ format.

Enter Targets data in Targets sheet

Entering Targets is exactly the same as Actuals.

Settings for Scorecard and Dashboard

In the settings sheet, we have a section to control certain inputs to Scorecard and Dashboard.

Month or Year to Date.

To begin with, we can create scorecard for a Month or Year to Date.

Let’s say we want to create Year to Date. This is applicable when we want to produce a Scorecard for 2021 until now.

A ‘Year’ option will appear where you can type in the year. I have typed in 2021.

On the other hand, if we want to display the Scorecard only for May 2021 we need to choose the ‘Month’ option.

When we choose Month, a new option pops up asking for ‘Latest Month or a Specific month’.

If we choose ‘Latest Completed Month’, the Scorecard will automatically always show data for the last completed month. If

today is June 15^th, 2021, May 2021 is the last fully completed month.

If you choose ‘Choose Specific Month’, a drop down will appear for selecting a month.

# of Months of Trend to display

In Scorecard and dashboard, we display monthly trend sparklines. This setting is to control how many months should be displayed in those trends.

Highlight KPIS that have not met target

Choose Yes if you would like to highlight KPIs that have not met the target. Such KPIs will be highlighted with red fill color in the Scorecard and Dashboard.

Compare Month over Month or Year over Year

When we compare KPIs for current period with a previous period, we can either compare with the prev month or same month prev year.

This setting applies to all KPIs.

Tolerance to allow when comparing actuals vs Targets.

This is used in coloring the KPIs when we compare against targets. Let’s assume the tolerance is 10%. That means if the actual KPI value is within 10% of target, we consider it yellow and not red. If actual is outside the 10%, it is considered red.

For a KPI where higher value is better (example: Retention rate), if actual value < 90% of Target then it will be colored in red. If actual value is >=90% but less than 100% of target, then it will be yellow. If actual >=100% of target, then we color it green.
For a KPI where lower value is better (example: Recruiting cost), if actual value > 110% of Target then it will be colored in red. If actual value is >100% but <= 110% of target, then it will be yellow. If actual <=100% of target, then we color it green.

Currency

You can type in any symbol that you would like to use for currencies.

In the Insert ribbon, choose Symbol

View Scorecard

The Scorecard is fully automated. It displays 24 KPIs in one page. You can change the Page Number on the top right, if you have more than 24 KPIs.

Comments for the corresponding month appear automatically. Comments are entered in the Commentary sheet.

The below is an example scorecard that represents Year to Date 2021 (Jan to May), compared to 2020 (Jan to May).

If we had chosen to compare against previous month (Month over Month), it would appear as below. Year to Date 2021 (Jan to May), compared to previous month (Jan 2021 to Apr 2021).

If we had chosen to view Scorecard for one month (instead of Year to Date) against previous month (Month over Month), it would appear as below. May 2021, compared to previous month (Apr 2021).

If we had chosen to view Scorecard for one month (instead of Year to Date) against same month previous year, it would appear as below. May 2021, compared to Same month previous year (May 2020).

Even if the KPIs are entered in a different order, the scorecard sheet will group KPIs by category.

If, in Settings, we choose to highlight metrics that did not meet targets, then those will be highlighted as shown below.

KPI Dashboard

Similar to the Scorecard, Dashboard also updates automatically based on settings we entered in Settings sheet. Each page displays 24 KPIs. You can update page number on the top right to see next set of 24 KPIs if you have more than 24.