In this article, we walk through step by step how to use the Training (Learning & Development) Dashboard Google Sheet Template.
Overall, there are five key steps in this process.
Detailed explanation of these steps are given below:
Step 1: Enter Employees data in Employees sheet
To begin with, add all existing employees.
- Employee ID is required. It is a unique identifier of an employee.
- Enter Employee Name
- Enter Location, Department, Job Level, Manager, Job Title, Email are fields you can populate
- You can also rename these fields and use it to store other attributes of employees
- Join Date is a required field. This is the date when the employee joins the company.
- Exit date is the last date of employment of an employee. Enter this if the employee is no longer employee with the company.
When a new employee joins the company, add to this table. If an employee leaves the company, enter the date of leaving in the Exit Date column.
The sheet can handle up to 1000 employees by default.
Step 2: Create training offerings
Once the training needs are identified, take steps to provide the training needed. It could be developing the training material internally or reaching out to external trainers/companies.
Enter trainings available in the Trainings sheet.
- Enter Training ID, a unique identifier of a training offering.
- Enter the name of the Training
- Start Date & End Date: These are the dates during which the training is actually available.
- For example, if the training is a day long training and is being offered for an entire month of Jan 2022, you will enter start date as 1-JAN-2022 and end date of 31-JAN-2022. Employee can start their training anyday between 1-JAN-2022 and 31-JAN-2022.
- Please note that this is not the duration of how long an employee will be in that training. If it is an 8 hour training, you will enter the number of hours in the Hours column. If it is a two day long training (16 hrs), then enter number of hours as 16.
- If the training is offered only on Jan 15, 2022, then enter both start and end dates as 15-JAN-2022.
- If the training is always open, enter an end date far in the future. (example 31-DEC-2025)
- Trainer Name
- Source: This could be used to flag whether the training is internally developed or external.
- Mode: This is to track whether the training is an online/virtual versus In-person training.
- Category & Subcategory: Categorize your trainings in two levels.
- Cost: Enter the cost your company has to pay for each employee who signs up for the specific training
- Hours: Enter the number of Hours of training associated with that specific training offering.
- # of Seats: Enter the maximum number of employees who can participate in that training.
- If no limit, enter a very high number like 1000 that cannot be reached.
Source, Mode, Category, Subcategory: You can enter any values that are relevant to your company.
To change Currency, type in your currency as shown below.
By default you can enter up to 300 trainings.
Step 3: Entering training enrollments
Once a training offering is scheduled, inform employees who can benefit from that training. Encourage them to sign up for training.
When employees sign up for training, enter them in the Enrollments sheet.
- Enter employee ID from drop-down list
- Enter training ID from drop-down list.
- Enter the date when the employee will take part in that training.
- Enter the status as ‘Enrolled’.
- If after enrolling the employee cancels the enrollment, change status to ‘Cancelled’.
By default you can enter up to 2000 enrollments.
Monitoring training enrollments and promote
As employees sign up, monitor the number of employees enrolled in each training.
In the Trainings sheet, you can see the number of seats still available, # enrolled & # cancelled.
Status of the training will be displayed as
- Training Not Started Yet – If the Training start date is in future.
- Training in progress – If the training start date has passed but the end date is in future
- Full – If the Training has not started but all the available seats have been taken
- Training Ended – if the training end date has passed.
Step 4: Record Benefits and Feedback of Employees
As the training start date arrives, it is time for enrolled employees to participate in trainings. After they complete the training, they should inform you. You can update the status of the enrollment to ‘Completed’ in the Enrollments sheet.
In addition to Status, record the Rating and Benefits.
Rating is the numeric assessment by the employee of the training they participated in. You can do a rating system according to your needs as long as it is numeric. For example, the maximum rating can be 5, or 10. In the above screenshot, we used 5.0 as the maximum rating.
Benefit is the financial benefit you perceive the company will get due to the training received. If the employee’s performance will improve due to the training, enter an estimate of the benefit. This will help us assess the return on investment of the training program.
For example, an employee attends a time management or productivity training that costs $100. With the improved productivity the employee can now take on more tasks and that results in estimated $200 per year of increased company revenue or reduced expenses. Now, the benefit is $200. This estimation method can vary by company and as an organization you can agree on a specific estimation method. Ultimately there needs to be a quantified benefit of each training received.
Note that the same training can have different benefits on different employees, because not all employees have the same skill level to begin with. This is why we enter the Benefits in the Enrollments table and not in the Trainings table.
Step 5: Measure the Training program for its effectiveness & efficiency
Having entering the data in previous steps, we are ready to benefit from all the automated calculations that the template performs.
Dashboard sheet has 4 pages.
- Training Dashboard (KPIs and Metrics)
- Training View
- Employee view
- Monthly Trends
Training Dashboard displays all the relevant KPIs and metrics to measure your Training or Learning & Development program.
The following are the KPIs and metrics.
- Enrollment %
- Training Completion %
- Hours Trained per employee
- Avg. Enrollment per Training
- Cost per employee
- Cost per enrollment
- Avg. Rating
- Return on Investment
- # of Active Employees
- Enrolled Employees
- # Completed Trainings
- # of Hours Trained
- # of Trainings
- Hours per Training (not shown on first page of Dashboard, but available in Training view page)
For more on definitions of these KPIs and Metrics, please see Definitions of Training Metrics
The dashboard is completely dynamic.
You can change the reporting dates. You can also filter by Training attributes as well as Employee attributes.
Training view report
The second page of the dashboard allows us to evaluate training program along training related attributes.
This is also a dynamic report. You can view all the metrics by Training Category, Subcategory, Training Name, Trainer, Mode and Source.
You can also choose which metric to sort the table by.
This allows us to analyze the data by various ways.
Employee View report
In the third page, like the training view, we can view metrics by employee attributes.
In the final 4th page, we can view the monthly trends of all Training KPIs and metrics.