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.
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.
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.
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.
- 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
- 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
- Entering whole numbers as they are. Example 30
- Number with decimals
- You can enter the decimals needed. For example, 0.45
- For 100 dollars, you can enter as 100 or $100.
- 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 15th, 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.
You can type in any symbol that you would like to use for currencies.
In the Insert ribbon, choose Symbol
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.
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.
A closer look at each tile.
Here are some screenshots.
2021 YTD (Jan to May 2021) compared with Jan to Apr 2021.
2021 YTD (Jan to May 2021) compared with Jan to May 2020.
May 2021 compared with Apr 2021.
May 2021 compared with May 2020
Highlighting KPIs that do not meet targets.
By default, the KPIs are ordered in the same order entered in the KPIs sheet. However you can change to a custom order easily.
Choose Custom instead of Default, and then enter your KPIs in the order you would like to see. That’s it. Very simple.
The KPI View sheet allows looking closely on one KPI.
You can choose a KPI from the drop-down list.
You can also control the Starting Month.
The first chart shows the Actuals vs Targets and highlights in red when actuals did not meet Target.
The second chart presents the monthly actuals comparing two years.
While presenting KPI Scorecards, it is very important to have notes or comments providing context and explanation for the results.
You can enter your brief comments in the Commentary sheet for each KPI and each month.
You can add more rows to this table as needed. These comments will automatically appear on Scorecards.