Doing the right things, but seeing the results?
One interesting thing we came across during both our time working as home care operators as well as when helping home care owners with their retention efforts is that while there are many ideas on potential ways to improve caregiver engagement and retention, not many businesses track the effectiveness of the efforts on a regular basis.
Recognition celebrations and certificates of achievements are helpful, but how do you know whether they actually lead to increased retention and, more importantly, create a better working environment?
What does it mean to track my programs?
As a team building software to help caregivers better organize and control their work, this is something we live and breathe everyday. However, you don't have to work with us in order to get a sense of whether your programs are working or not. It can be as simple as
The beautiful thing about ROI tracking is that you can track almost anything if you have the right system in place. For most of this article, we're talking about the different actions you can take after the caregiver is hired, but this is something you can begin implementing during the application process as well.
For example, if you were rolling out a new job posting and wanted to see whether the applicants who applied through the new postings were
Whatever your goal is (getting from application to hire at a higher rate, retaining a higher percentage of caregivers, etc.), it’s important to 1) keep track of what you’ve been doing to get to those goals and 2) measure whether your efforts are working or not. In short, take attendance.
(Note: If you’re an Excel wizard, you can go back to your day and we look forward to seeing you in our next post. If calculating turnover is something you’ve been meaning to do, but don’t quite understand how to, this next section is for you)
One illustrative example - Job descriptions and caregiver turnover:
Practically speaking, this is how a tracking process for realistic job descriptions would play out. (We are assuming you or your team have access to some form of spreadsheet program - Microsoft Excel, Google Sheets, etc.).
The question we are looking to answer is: Do caregivers hired with the realistic job description stay longer than caregivers who aren’t?
Start with an export of your caregivers. This can be from a payroll report, your scheduling software, etc. For this example, we are making up a group of 27 caregivers with hire dates between January 1st, 2020 and December 30th, 2020 (Note: this make believe agency is fairly small, but more manageable to follow along than if the example had 1,000 caregivers). Notice the column "Job Posting" on the far-right. This will be important later on when we track how effective the job postings are.
As you can see, some of the caregivers are still working at the company while others aren't. To calculate our total turnover rate, we need to a) count the number of caregivers who have churned b) the number of caregivers on the roster at the beginning of the year and c) the number of caregivers on the roster at the end of the year. This is where Excel formulas come in
The formula we're going to use today is called the IF statement. To find whether a caregiver was on a roster on a given day, we will use the IF statement. We'll be using a pivot table to get the total number of caregivers who churned and the number of caregivers in the workforce on Jan 1 and Dec 31.
Finding who was on the roster at the beginning and end of the year:
In order to figure out whether the number of caregivers turning over in a year is normal or cause for concern, you have to first figure out what you’re dividing that number by. In industries such as home care, the number of workers employed varies day-by-day and it’s difficult to pinpoint an exact number. Therefore, we do the best we can, which is taking the average of the roster at the beginning of the year (January 1st) and the end of the year (December 31st)
In order to figure out who was working at the company at the beginning of the year, we’ll need two pieces of information: hire date and separation date (if applicable). The way to think about the January 1st roster is
January 1st roster = (People who were hired before January 1st and still active) + (People who were hired before January 1st and left after January 1st)
So the three questions we need to answer using Excel are
The IF statement in Excel helps us answer those three questions by returning a TRUE or FALSE after every question you ask it. The formula is structured as
IF (“True or false question”, “Value if true”, “Value if false”)
Let’s set up the formula to answer the three questions above to answer whether a caregiver is on the roster on January 1st, 2020
IF (“Hire date is on or before Jan 1, 2020”,
(“Check to see if there’s no separation date”, “If no separation date - Yes”,
“If there is a separation date - check to see if it’s after Jan 1, 2020”, “If it is - Yes”,”If not - No”),
“No - the caregiver is not on the roster as of Jan 1, 2020”)
To figure out who’s on the roster for December 31st, simply change the date from Jan 1, 2020 to Dec 31, 2020
After doing that, we should have two rows on the right hand side for “Jan 1 Roster” and “Dec 31 Roster” with a series of “Yes” and “No” to answer whether they were around at the beginning or end of the year. Now, we can move onto counting the caregivers in each group.
Calculating the 2020 caregiver turnover rate:
Now that we know who was on the roster both in the beginning and end of the year, we can use pivot tables to grab the appropriate numbers to calculate caregiver turnover. To figure out who’s around and who has since left, we will use the “status” column from our payroll export (if your payroll or scheduling export doesn't show employee status, reach out to Jason via LinkedIn and we can figure out another way to calculate it).
The video below shows how to set up a pivot table to come up with the relevant numbers (# of caregivers who left, # of caregivers on Jan 1, # of caregivers on Dec 31)
The next video shows how to get the breakdown of employees by employment status (“Status”). Notice how Excel originally tries to give us the sum of Employee IDs, and we have to change it to unique counts (so each Employee ID is only one person). We can see that in 2020, we had 27 employees in total, 20 who are still with us, and 7 who aren’t
# of caregivers who left in 2020 = 7
Now, we can use the results of our helpful IF function from earlier to figure out how many caregivers were on the roster as of January 1st, 2020. In this particular example, our first employee was hired on January 1st, so there is only one caregiver who was there at the beginning of the year.
# of caregivers on Jan 1 = 1
We then repeat for the roster on December 31st, 2020. This time, we see that there are 20 caregivers who are around at the end of the year
# of caregivers on Dec 31 = 20
For the purposes of this example, turnover is calculated as
# of caregivers who churned / average of (# of caregivers on Jan 1, # of caregivers on Dec 31)
Using the numbers we calculated from above, we can see that it’s
7 caregivers churned / average of (1 caregiver on Jan 1, 20 caregivers on Dec 31)
Which gets us to a turnover rate of 67%, around the industry average
Comparing turnover rates for old vs. new job descriptions
Now that we know how to calculate turnover rates, we can use that to see if caregivers who were hired through the new job descriptions turn over at a different rate than caregivers who were hired through the old one (if you haven’t thought about this since high school, here’s a useful article on correlation vs. causation)
The way to differentiate between caregivers who were hired through the old job description vs. new is the “Job posting” column, which we can split out by putting it into the “Column” field as shown below. (Note: this is likely not a field that comes from your payroll or scheduling system, which is why it’s important to keep track of these things, also known as “taking attendance”)
After doing the same for the Jan 1 and Dec 31 rosters, we come to the following results
In this particular example, we’re able to see that caregivers hired through the old job description were more likely to be inactive than caregivers hired through the new job descirption, but that can’t be taken as face value. If we wanted to dig deeper into this, we’d look at questions such as: timing (is this because the old job description was more or less phased out towards the middle of the year?), tenure (do caregivers stay longer even if they end up quitting?), or other factors not addressed in this analysis. However, a wise man (Voltaire) once said, “Don’t let perfect be the enemy of good,” and this should be no different. If your team wasn’t tracking this before, don’t let the fact that there may be other factors stop you from moving your business in the right direction. You can’t improve what you can’t measure, and all it takes is one little step to start seeing improvements.
To conclude
Our team’s goal is to help you and your team develop the tools and habits to do this type of work yourself. If this post has de-mystified Excel and calculating things such as “turnover rate,” we’ll feel pretty good about ourselves. The important thing to note here is that in order for any of these analyses to be helpful, the data must exist. That means keeping accurate records of who works at your company and keeping track of who does what. In short, take attendance.
“We love working with the Levo team! They provided us with valuable advices that supercharged our hiring process.”
