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.
Familiarize yourself with the spreadsheet before beginning your work. For example, notice that
the file contains three date fields: DOB (date of birth), DOH (date of hire), and DOT (date of
termination). Date of birth is used to determine eligibility of employee for specific age-related
benefits. Date of hire indicates the date when the employee started working for the
organization. Date of termination indicates the last day the employee worked for the
organization.
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.
MedicBit training program is based on the idea that effective managers can be developed
through systematic training. Trainees are recruited primarily from college campuses, although a
small percentage are recruited through other sources. MedicBit has used the training program
to attract high potential employees.
The Vice President for Human Resources, Cynthia Thompson, has asked you to prepare a report
on the impact of turnover of managerial trainees on future staffing levels. There are many
models that can be used to forecast human resource needs. Using data from our HRIS and
downloading it to a spreadsheet for quick analysis allows HR to generate a human resource
planning model and then examine various scenarios by simply changing a few numbers in the
model.
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.
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?
4 . Assuming the organization has a target level of 100 managers in 2021, but expects it will
need 10% annual growth in the number of managers for 2022-2025 and the organization
expects to experience levels of turnover consistent with that from 2020 during this time period,
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
Make sure you round calculations performed in the spreadsheet when it makes sense. After all
you can’t have 1/3 of a person. Here is how to use the ROUND function in Excel:
=ROUND (number, num_digits)
Where,
number = number (or cell) that you are rounding. You can also put an Excel formula
here.
num_digits = the number of digits to which number should be rounded.
As with any of the functions used in this assignment, Microsoft and YouTube have excellent
tutorials that can assist you in using them.
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
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:
¿ COUNT (range )
Where:
range = the range of cells to count.
Number of managers who left. This figure is calculated by counting the number of individuals
who have a DOT date, using =COUNT().
Turnover rate is calculated by dividing the number of managers who left by the number of
managers who worked for the organization at the beginning of the year.
Total Number of Turnover Losses
Turnover Rate=
Total Number of Trainees maining
Place this answer in cell D109.
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
Calculate this value for each employee that left the organization in 2020. Then average those
values and place the answer in cell D112. Then divide this answer by 365 days per year to
calculate the average tenure of departing employees in years. Round both of these answers to
the first decimal place.
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.
The number of employees that work in an organization is determined by (a) the number of
employees an organization hires, (b) the number of employees that leave the organization and
(c) whether the organization wants to remain at its current size or whether it wants to increase
or decrease its number of employees over time.
Your assignment is to complete projections for the number of managers that need to be hired
in each of next five years under two sets of assumptions.
1. In the first, assume the turnover rate in each of the years in your analysis is the same as
the turnover rate you calculated for 2020, and the organization wants to maintain a target
number of 100 employees at the end of each year.
2. In the second, assume that same turnover rate as in Part 1, but the organization wants to
have 100 managers at the end of 2021, but then increase that number by 10 percent
annually through 2025.
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.
Target Employees
In Assumption 1, it will be 100 each year.
In Assumption 2, it will start at 100 and grow by 10% each year.
Hiring Projection – This is the number of new employees you will need to hire each year.
This is calculated using the following formula:
Hiring Projection=Target Employees−(Starting Employees−Turnover)