In this article, we will cover step by step how to use the Compliance Training Dashboard Excel template.
Enter List of trainings needed.
In this Excel template, we can track compliance training for new employees.
In the Settings sheet, we will enter the training name and the Nth day by which an employee needs to complete that specific training.
In the above example, Workplace safety training should be completed within 30 days from Employee Start date.
If an employee starts on Sep 1st, he/she should complete this training by Sep 30th. Due date is Sep 30th for that specific training for that specific employee.
The template can handle 20 trainings. If you need this to be expanded, please reach out to support@indzara.com for customized development.
Enter Employee Data
In the Employees sheet, we will enter each employee’s information who starts employment with the company.
Employee ID, Employee Name and Start Date are entered.
The template can handle 500 employees by default and can be extended to more if needed.
The next set of columns are used for entering the dates of training for each employee.
Hide columns that are not needed
Though the template can handle up to 20 trainings and in this example, we only need 10. So, first, we will hide the columns that we won’t need.
Right click on the columns and choose Hide.
Certificate
Now that you have entered employee data with Start date, you can provide a PDF export of due dates for a specific employee easily.
In the Certificate sheet, choose Due Dates list.
Enter the Employee ID on the top right.
You can now see the employee’s training due dates.
You can export to PDF as usual in Excel and send to the employee.
Now, the employee knows the due dates for each training. The employee will schedule or plan to take the trainings on certain dates. The employee informs you about those scheduled training dates.
You can now enter them in the Employees sheet.
Entering Training dates
When an employee has informed when he/she will be taking the training, enter those dates in the corresponding date columns.
In this template, Compliance is defined as completion of the training on or prior to the Due date.
As you enter the template will provide visual feedback via fill and font colors.
Click on the image to view in full size.
In the above example, you can see the three scenarios.
- Not Scheduled & Too late (Pink fill color)
- These are trainings that the employee has not scheduled (or completed) yet and the due date has passed.
- Non-Compliant & can be fixed (blue fill color)
- These are trainings that the employee has scheduled but the training date is after due date. Hence the employee will not be compliant. If we can advance the training date, we could become compliant. So, this is an opportunity to take action to ensure compliance.
- Non-Compliant & Too late (red font)
- Training has been completed but after due date. This is non-compliant, but it is too late to fix as the due date has passed.
In addition to the above columns, we can also enter employee attributes in the next set of columns.
Contact information (Email): This column can be used to store any contact information.
There are four employee attributes that can be customized. By default, we have provided Location, Department, Job level and Manager. You can rename these to any and use as needed.
These four attributes are special because they are also shown in the Dashboard which we will see shortly.
Job Title is another column that you can rename and use for other purposes. However, it will not appear on the Dashboard.
Finally, there are 4 calculated columns with formulas.
Compliance status will be ‘Compliant’, ‘Non-Compliant’ or ‘In Progress’. In progress status indicates that the employee has not completed the training yet, but the training dates scheduled (or not scheduled yet) are within the due dates. As time goes by In Progress can become ‘Compliant’ or ‘Non-Compliant’ based on when trainings are taken.
Max Due Date is the maximum of all the due dates for that employee. If the employee is being compliant, all the trainings should be completed on or prior to this date.
Completion Status will be either ‘Not Completed’ or ‘Completed’. This is very straight forward. If all the trainings are completed (training date <= Today) then it is considered ‘Completed’.
Action Reason will indicate either ‘Not scheduled yet’ or ‘Scheduled late’. ‘Scheduled late’ is assigned to employees who have any training dates in the future which are non-compliant. If the employee has not entered any dates for one or more training, the flag will be ‘Not Scheduled yet’. If both are true, ‘Scheduled Late’ will be displayed.
There are slicers (filters) available for Compliance status, Completion Status and Action Reason to get to specific employee records that are actionable.
Click on the image to view in full size
You can also filter by Due Date
Dashboard
After you have entered data for a group of employees, you can view the overall KPIs such as Compliance Percentage for the whole company.
Please click on ‘Refresh All’ in the Data ribbon, to update the dashboard.
Tip: Refresh only after you have entered data for at least one employee fully.
You can filter easily by Location, Department, Job level or Manager name, on the left side bar.
You can view the overall KPIs at the top.
- Chart 1: You can see the # of employees who started at the company every month
- Chart 2: Compliance Percentage by Month
- Chart 3: # of employees by Compliance Status every month
- Chart 4: Completion Percentage by Month
You can also identify specific groups which are lower in compliance if any. In the second page of the dashboard, you can see the compliance and completion Percentage by each attribute.
Certificate of completion
Finally, you can also issue a certificate of completion to an employee.
Choose ‘Status Certificate’ and then choose Employee ID.
You can enter the Certified By and Certified Date in the bottom right of the page.
You can then print or export to PDF the certificate.