HR Jetpack
Home / HR Courses / HR Analytics / Analytics - A Deeper Dive / Descriptive Analytics Demo

Descriptive Analytics Demo

This video is premium content

Register or sign in to gain access.

Lesson:

Descriptive Analytics Demo

  • Course: HR Analytics
  • Module: Analytics - A Deeper Dive
  • Lesson Type: Video
  • Lesson Duration: 5:34

Lesson Content

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.

Excel Screenshare:

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.

Excel Screenshare:

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.

Christina Danforth

Instructor:

Christina Danforth

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....

Christina's Full Bio

HR Courses

Human Resources Training Programs

Self-paced HR Courses

The following HR courses are self-paced (asynchronous), and qualify for both SHRM and HRCI recertification credits. These courses are included in the HR Recertification Subscription.

Effective Disciplinary Action Policies and Procedures

Duration: 1 hr 1 min
SHRM: 1.0 PDC
HRCI: 1.0 General

Liz LaForte

Liz LaForte

RFP Process for HR

Duration: 1 hr 19 mins
SHRM: 1.25 PDC
HRCI: 1.25 General

Mark Fogel

Mark Fogel

Organizational Ethics for HR Managers

Duration: 1 hr 30 mins
SHRM: 1.5 PDC
HRCI: 1.5 General

Dawn Tedesco

Dawn Tedesco

HR as a Business Partner

Duration: 1 hr 5 min
SHRM: 1.0 PDC
HRCI: 1.0 Business

Scott Pitts

Scott Pitts

Blockchain for HR

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Michael Wilson

Michael Wilson

HR Strategic Planning

Duration: 1 hr 2 mins
SHRM: 1.0 PDC
HRCI: 1.0 Business

Hayley Buonopane

Hayley Buonopane

Employee Engagement & Retention

Duration: 1 hr 4 mins
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

HR Metrics That Matter

Duration: 1 hr 16 mins
SHRM: 1.25 PDC
HRCI: 1.25 Business

Christina Danforth

Christina Danforth

Finance for Absolute Beginners

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Anna Samorukova

Anna Samorukova

Change Management 101

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Hayley Buonopane

Hayley Buonopane

Labor Relations

Duration: 1 hr 42 mins
SHRM: 1.50 PDC
HRCI: 1.75 General

Matthew Kerzner

Matthew Kerzner

Diversity and Inclusion

Duration: 1 hr 25 mins
SHRM: 1.25 PDC
HRCI: 1.50 General

Christina Danforth

Christina Danforth

HR Analytics

Duration: 1 hr 31 mins
SHRM: 1.50 PDC
HRCI: 1.50 Business

Christina Danforth

Christina Danforth

Performance Management: Development & Deployment

Duration: 1 hr 18 mins
SHRM: 1.25 PDC
HRCI: 1.25 General

Christina Danforth

Christina Danforth

The Role Assessments Play in Talent Management

Duration: 2 hr 5 mins
SHRM: 2.0 PDC
HRCI: 2.0 General

Craig Haas

Craig Haas

AI for HR

Duration: 1 hr 3 mins
SHRM: 1.0 PDC
HRCI: 1.0 General

Michael Wilson

Michael Wilson

Compensation 101

Duration: 1 hr 8 mins
SHRM: 1.0 PDC
HRCI: 1.25 General

Pamela Sande

Pamela Sande

Effective Leadership Communications for HR Professionals

Duration: 2 hr 30 mins
SHRM: 2.5 PDC
HRCI: 2.5 General

Christina Danforth

Christina Danforth

Recruiting Strategies to Hire the Best Candidate

Duration: 1 hr 6 mins
SHRM: 1.0 PDC
HRCI: 1.0 General

Lois Krause

Lois Krause

Cybersecurity 101 for HR Pros

Duration: 1 hr 2 mins
SHRM: 1.0 PDC
HRCI: 1.0 General

Michael Wilson

Michael Wilson

EQ Competence: Fostering a Talent Advantage that Drives Organizational Performance

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Craig Haas

Craig Haas

Thinking Lean

Duration: 1 hr 8 mins
SHRM: 1.0 PDC
HRCI: 1.25 Business

Christina Danforth

Christina Danforth

Coaching for HR Professionals

Duration: 1 hr 2 mins
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

The Employment Cycle

Duration: 1 hr 45 mins
SHRM: 1.75 PDC
HRCI: 1.75 General

Christina Danforth

Christina Danforth

Behavioral and Situational Interviewing

Duration: 1 hr 8 mins
SHRM: 1.0 PDC
HRCI: 1.25 General

Stephanie Legatos

Stephanie Legatos

Dynamic Dialogues

Duration: 1 hr 10 mins
SHRM: 1.0 PDC
HRCI: 1.25 General

Stacey Zackin

Stacey Zackin

Employment Law 101

Duration: 3 hr 11 mins
SHRM: 3.0 PDC
HRCI: 3.25 General

Mark Addington

Mark Addington

Thrown to the Wolves: Preparing Your New Leaders to Lead the Pack

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Karen Hinds

Karen Hinds

Introduction to Organizational Development

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Practical Meeting Preparation: Getting Ready for a Productive Meeting

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Succession Planning: Build Your Bench

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Practices for Hiring Successfully

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Team Development: Building Collaboration & Effectiveness

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Leading and Managing the Need for Both

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Coaching: Guiding the Management Team

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Being A Change Agent

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Hayley Buonopane

Hayley Buonopane

Leading a Multigenerational Team

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Karen Hinds

Karen Hinds

Multi-Generational Workforce: Problem or Competitive Advantage?

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Virtual Reality: The Future of HR

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christina Danforth

Christina Danforth

Unconscious Bias: Impact on Recruitment and Retention

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christina Danforth

Christina Danforth

Business Fundamentals for HR Pros

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

Influence and Communications as an HR Pro

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

Value Based Decision Making for HR Pros

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

Strategic Planning to Support HR Initiatives

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Dr. Deborah Osgood

Dr. Deborah Osgood

Meet in the Middle: Best Methods for Conflict Management

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Economics for HR Professionals

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

Business Plans: Why HR Pros Should Care

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

Emotional Intelligence: What is it and Why Does it Matter

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Marketing/Selling Continuum (From Idea to Revenue)

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

Diversity Equity & Inclusion The Lens We Wear

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

Welcome! What HR can Learn from the Field of Hospitality

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

Accounting and Financial Reporting for HR Pros

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

How HR Can Establish & Influence a Culture Of Hospitality

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

Raising Capital (Who, What, When, Where, Why & How)

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 Business

Jack Antonich

Jack Antonich

Cultural Competence: Fostering Diversity Equity & Inclusion

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Stacey Zackin

Stacey Zackin

DiSC Communication Styles: What are They & How They can Help You

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Effective Meeting Facilitation for HR Pros

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti

Best Practices in Providing Effective Performance Feedback

Duration: 1 hr
SHRM: 1.0 PDC
HRCI: 1.0 General

Christine Gatti

Christine Gatti