Identifying metrics, measuring output and analyzing information are common practices for every business. Leveraging data to make the best decisions is key to building a successful organization. There’s no doubt data analytics is here to stay. However, did you know 80% of HR professionals score themselves low on their ability to analyze data? As a result, an opportunity to provide guidance and value can be completely lost.
After this course, you’ll understand the important terms and concepts associated with HR analytics and the purpose of developing them in your organization. Start using techniques to begin the analytics process. Learn a new skill that'll help you and your HR department gain credibility and make better decisions.
HR Jetpack is recognized by SHRM to offer Professional Development Credits (PDCs) for SHRM-CP or SHRM-SCP. This program is valid for 1.50 PDCs for the SHRM-CP or SHRM-SCP. For more information about certification or recertification, please visit shrmcertification.org.
This activity, has been approved for 1.50 HR (Business) recertification credit hours toward aPHR™, PHR®, PHRca®, SPHR®, GPHR®, PHRi™ and SPHRi™ recertification through HR Certification Institute® (HRCI®). For more information about certification or recertification, please visit the HR Certification Institute website at www.hrci.org.
The use of the HRCI seal confirms that this activity has met HR Certification Institute's® (HRCI®) criteria for recertification credit pre-approval.
Title: Descriptive Analytics Demo
Module: Analytics - A Deeper Dive
To best understand the application of descriptive analytics, I'd like to demonstrate how to calculate an average and standard deviation of a data set in excel.
A pretty popular metric is employee turnover or attrition. Employee attrition can be further categorized as being voluntary and involuntary. If voluntary, it can be for a number of reasons spanning from career development to needing a new location. For our purposes we'll work to calculate the average voluntary attrition number and attrition rate for one year. This is calculated by taking the number of voluntary separations and dividing them by the average number of employees.
To get us started, I’ve populated the spreadsheet with sample data. I identified each month in column A, the number of voluntary separations in column B and the number of employees for each month in column C. At the bottom I have several headings to capture the total number of voluntary separations, average number of separations and the turnover percentage.
First, we want to add the total number of voluntary separations by placing the cursor in cell B14. We enter the = sign, type sum, open parenthesis and select cells B2 through B13, select close parenthesis and hit enter. This is the total number of voluntary separations. Those employees who left the organization voluntarily for whatever reason, career change, poor management, etc.
Next, let’s move to the average. Excel does a fabulous job of keeping it simple! We enter the = sign, type AVERAGE, open parenthesis, select cells B2 through B13, select close parenthesis and hit enter! So the mean or average attrition is 2. Therefore an average of 2 employees are leaving the organization each month.
Let’s move over to the number of employees. We need an average to calculate the voluntary attrition rate. Type the = sign, type AVERAGE, open parenthesis, select cells C2 through C13, select close parenthesis and hit enter. We had an average of 487 employees at our facility each month.
Now, what’s our turnover rate? Go to cell B16.
In excel, we can update the format of the cell to give us a % and enter the following calculation, total number of voluntary separations (B14) and divided by the average number of employees (C15). Enter = sign, B14, divided by average number of employees, (C15) and we have 6%! Thus the voluntary turnover rate for your organization is 6%. An important metric for you and your leadership team to be aware of.
Let’s go to another spreadsheet for a demonstration of standard deviation. This measures the spread or distribution of numbers in a set of data around its average or mean. Think bell curve.
Let’s say you want to figure out if there is a great disparity between salaries in a department. I’ve open my excel spreadsheet and typed in sample data for 8 positions.
First, I want to find the average of the salaries. Go to cell B9, type the = sign, AVERAGE, open parenthesis, select B1 to B8, and close parenthesis. Our average is $38,500. Now we need to measure the variance or how widely these values are dispersed from this average value. Remember the average is also called the mean.
Go to the next cell down, type the = sign, type the function for standard deviation ST-DE-VP, select B1 to B8 and hit close parenthesis. The variance is $.
Finally, we need to calculate our range. Subtract the variance (B10) from the average (B9) in cell B11 and add the variance to the average in C11. This tells you that the salary range should be between $ and $. Notice some employees don’t fall into this range and it will be important to investigate why.
You completed 0% of this lesson
You completed 0% of this course
Lessons Not Completed:
Christina A. Danforth, SHRM-SCP & SPHR, launched HR Jetpack in 2016 to support the development and professional growth of her fellow HR colleagues. She started her HR career in 2002. After obtaining her Master’s in Business Administration degree, Christina joined United Technologies Corporation. She moved across the United States...