In this article, we will cover step by step how to use the Compliance Training Dashboard Google Sheet template.
Enter List of trainings needed.
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.
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, get your employee's training due dates. This can be exported as PDF or printed.
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.
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.
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.
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.
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.
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.