Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
1
HR Planning at MedicBit Corporation
Assignment Purpose
The purpose of this assignment is to understand how organizational turnover is calculated and
to estimate number of employees an organization will need to hire given a calculated turnover
rate and under differing organizational growth projections.
File Needed: HR_Planning.xlsx.
Background
MedicBit Corporation is a leader in software development and consulting for the medical device
industry. Annual sales for the next two years are projected to be $22 million and $25 million,
respectively. MedicBit executives believe that this continued success will depend on a
commitment to developing good managers. As such, there is strong support for their
management training programs.
Specifically, you need to answer the following questions:
1. What was the turnover rate for managerial employees for 2020?
2 . What was the average tenure (time the employee stayed with the company) for employees
who left in 2020? Calculated this number in days and in years.
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
2
3 . Given that the organization wants to maintain a target level of 100 managerial employees
and the company experiences the same level of turnover of managers in future years as
occurred in 2020, how many managers will the company need to hire in each of the years 2021–
2025?
The Problem Context
In this organization, the turnover rate for 2020 is expected to continue at the same level across
the planning period. Because virtually all trainees are recruited from college, have degrees in
business, and are approximately the same age, this type of analysis appears to be appropriate.
Note
As you work through the assignment, please be sure to save your work after each section. Save
your work as a new Excel file using your first initial and last name. For example, Joe Smith
would be:
“jsmith_HR_Planning.xlsx”.
Calculations
Part I – Turnover Analysis
The calculations in this section will be performed in the section of the Excel spreadsheet titled
“Turnover Analysis.”
Your first task is to determine the number of managers that left (e.g., turnover) in 2020. You
need to calculate the number of trainees retained after each year, the probability of retention
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
3
the following year, the number of employees remaining one year after that, and turnover losses
each year. The following calculations are required:
Total number of employees at the beginning of the year. This figure is calculated by counting
the number of individuals who have a DOH. This can be computed using the =COUNT() function.
The count function returns the number of cells that contain numeric data in a range of cells—it
ignores empty or text cells. The COUNT function is used to count cells in a certain category. The
function is written as follows:
Average Tenure of Departures refers to the amount of time that an employee works for a given
organization before leaving. It is determined by calculating the amount of time that passed
between the employee’s DOH and their DOT. This is done in EXCEL by subtracting DOH from
DOT. The result is the number of days that between date of hire and date of termination.
Tenure in Days =DOT – DOH
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
4
Part II – Hiring Projections
These analyses will be conducted in the section entitled, “Hiring Projections.”
You have been asked to determine the number of hires needed in each of the next five years
(2021-2025) to meet future staffing needs at MedicBit.
To determine the number of employees you will need the following information.
Starting Employees
• For Assumption 1, this will be 100.
• For Assumption 2, this will be the target employees from the previous year, with
the exception of 2020, which will be 100.
Expected Departures – This is the number of employees you expect to leave the
organization each year.
• For both Assumptions 1 and 2, this is calculated by taking the starting employees
and multiplying it by the turnover rate.