Are You Harnessing the Full Potential of Level 2?

June 17, 2015

You have a feeling that one of your training participants is not progressing well, but how do you know whether or not their poor in-class behavior will negatively impact their on-the-job performance?

In this quick tip, Stephanie Austin explains step by step how to use basic Excel functions to conduct very valuable statistical analyses that provide insight that you can act on immediately.

Read on to learn how to support your gut feelings with statistical evidence, even if you’re not a math whiz.

Imagine you are leading a month-long new hire training class, and after a week, you have a feeling that Derrick is not performing well. He seems to only half participate in activities, and he asks either no questions or more simplistic questions over information that has been covered at length. You want to provide some additional coaching but are not really sure where to start. When you approach Derrick’s supervisor about your observations, she asks if he is learning the material and tasks that will be performed on the job. She also wants to know if you have some kind of proof to show how much he has learned and if this will impact his future performance. What do you do?

This scenario is not all that unusual. The hardest part about being a trainer in this position is: How do you know if his behavior is truly impeding his learning or his future performance? True, we all know that engagement is an indicator of learning, but some people are gifted and can pick up on information with little effort. You have probably had or know someone who has had a trainee with behavior issues, yet, when they begin their actual duties, they perform at a high level. You may also know of someone who seemed to do very well and was fully engaged in training but found the transition to the actual job difficult. For these reasons, I have begun looking at ways to really analyze data collected in training (using the Kirkpatrick model of assessment) to give me valuable insights into how my learners are actually performing.

It starts like this…

Do you incorporate Level 2 assessments in your training? If you do not, have you considered it? What has stopped you? If you do use assessments, great! Now, what do you do with this very valuable information? Do you review the results? Do you send the information to a supervisor or member of leadership for their review? Then what? I have found that by taking and analyzing the assessment scores a little further, I can use the data to make immediate changes to my facilitation. These changes make my training classes more impactful and successful for all participants.

The ideas behind this are all rooted in a little statistical analysis. Now, please do not get worried if you struggled with math in school. Some easily attainable insights can be found with a little start-up time, some Excel know-how and, voila, you have predictors of future behaviors and performance!

Here is how you can gain this valuable insight.

First, create an Excel worksheet that shows all of the historical data on all the participants who have taken a particular training. Click here and see Figure 1 for an example of what this sheet might look like.

As you can see, this file is set up to show each participant’s information going across each row and the corresponding scores for each assessment in each column. These assessments include mini quizzes, activities, formal tests, etc.

One of the most important components of this file is found in the last column entitled “Performance at 90 Days.” In this example, once a trainee had completed training and started their new role, supervisors were contacted after one, two and three months to track how the trainees were performing. Each supervisor then rated the new employees on a 1 to 4 scale: 1 – Below Expectations, 2 – Sometimes Meets Expectation, 3 – Meets Expectations, 4 – Exceeds Expectations. This additional piece of information provides insight into how the skills learned in training relate and transfer to the job being performed.

At the bottom of this spreadsheet, the correlation function found within Excel is used to show the relationship (or lack thereof) between how well a participant performs on a particular assessment and their performance on the job at 90 days. The correlation function is “=correl” where the user then selects the assessment being compared to the performance at 90 days. Click here and see Figure 2 for an example.

Here, we are comparing the values in column C, which represent the first assessment, to the performance of the individual in column P. The formula seen here is “=correl(C2:C46, P2:P46)”. This comparison returned a value of approximately 0.4637. This value helps us determine where we can step in to give additional coaching.

The correlation values (which we found above) tell us a story about which assessments have the potential to impact the trainee’s performance on the job. If the values are high enough, we can say there is a “statistical significance” (or strong reason to believe) that when a person scores high on that particular assessment, they will be doing well on the job at 90 days. However, if the person scores low, this is an indicator that the trainer needs to step in and give additional coaching to help improve that person’s chances of performing at a higher level at 90 days.

The final step is to compare the correlation values to the Significance Table for Correlation. This table tells the facilitator if a correlation value is high enough to be considered a true relationship. This table can be found on many different statistical websites, but I offer one here (see Figure 3).

If the correlation value that was calculated (in our example, 0.4637) is greater than the value on this table, then there is a high level of correlation. In other words, if a trainee performs poorly on an assessment, this is a flag that he/she might perform poorly on the job, and additional coaching should be provided to help get him/her caught up.

Here is how you use the table. In my example, I had tracked the progress of 45 people. The first column of the above table is labeled df (degrees of freedom). To get the df, take the number of people you have in your dataset and subtract 2. So, 45 – 2 = 43. You then look on the table for this number or the one that is closest to but less than this number. In my example, the df value closest to but less than 43 is 40. Next, you select the Confidence Level you want to have for the relationship (90% is strong, but 99% is stronger). In my example, I used 99%. Therefore, in order to say that something is statistically significant, my correlation value must exceed 0.393. Now, I know for sure that my correlation value of 0.4637 is significant, and by providing additional coaching, I could impact how that person performs later on.

This is great news because now you have something quantifiable that you can go back to and say that a person needs more coaching. It is not just a gut feeling anymore. Now, when you say that Derrick is not participating and is not engaged in training, you can go to his assessment scores and show with a form of proof that he needs additional coaching. Imagine how much easier this conversation will be with supervisors and members of leadership when you have something you can show them to demonstrate your point.

Using your Level 2 assessments in this manner might be new and seem a little intimidating, but I have found that the most difficult task is just getting the historical data. Once this is done, inputting the formulas into Excel and comparing values to the Significance Table is very easy and gives you worlds of insight you did not have before.

Let us go back to the conversation you were having with Derrick’s supervisor. Derrick has not been engaged in training, and there is concern that he will not retain the information being discussed. When you approach his supervisor, she asks if you have any proof to support your ideas. Now, with this method of tracking training assessments, you can say,

“Well, Derrick has not been asking very many questions, and when he does, the questions do not seem to show much depth. In addition, I have been tracking how participants do on certain training assessments, and there is a strong correlation between how a trainee does on this particular quiz and how they later perform in their daily tasks. Derrick received a 60% on this assessment, and I am therefore concerned about how much information he is retaining. I recommend we review the information on this topic with him again, or else we run the risk that it will impact how he performs when he starts his job responsibilities.”

Wow, now who would not be a little impressed with that?


Click here for a printable version of this article, which contains all figures. Stephanie has also provided her examples in this Excel spreadsheet.

Join the Discussion

Do you think you will be able to apply this method? Do you have other tips for using your Level 2 data? Here are some ways to join the conversation:


Don’t Miss a Feature

Register with us to receive Kirkpatrick Quick Tips delivered to your inbox each Wednesday.


Additional Resources:

Kirkpatrick Four Levels® Evaluation Certification Program – Bronze Level

Transferring Learning to Behavior

“Pull Up a Chair” to Gain Qualitative Data

Is More Training Evaluation Data Better?

Use Active Training Evaluation Sonar to Maximize Training Value – Part 3

Scroll to top Arrow