Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
1
Equal Employment Opportunity and Workforce Management
at MobiCom Telecommunications
Assignment Purpose
Legal considerations, obligations, and requirements play a very important role in the
recruitment strategies of most companies. Equal employment opportunity (EEO) laws affect an
organization’s search for applicants, whether inside or outside of the organizations. A company
must do more than show that they do not purposefully discriminate against a legally protected
Background
MobiCom Telecommunications Company (MTC) is a leader in the telecommunications industry.
MTC attempts to hire the most qualified individuals to perform the duties necessary to provide
the best telecommunications services at the lowest cost. It is MTC’s policy to provide equal
employment opportunity for all persons without discrimination on the basis of race, color,
creed, national origin, religion, or disability. The overall objective of MTC’s EEO/AAP is to
achieve a balance with respect to employee race and sex in all job classifications.
Next, a 10% reduction in force scenario will be tested. The Vice President for Human Resource
Management established three criteria to accomplish the reduction: EEO/AAP classification,
performance appraisals, and length of service. If MTC cannot achieve the required reduction in
the size of the workforce using these criteria, then absences, education level, and managerial
discretion will also be considered (in that order).
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
2
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_EEO.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)
EEO/AAP Data for Managerial Personnel
This table (A5) below explains the data contained in the MTC spreadsheet. Use this information
to help you interpret the output.
Cell Title
Cell Coding Guide
Employee Name (NAME)
Equal Employment Opportunity (EEO) Classification
1 = White Males
2 = White Females
3 = Black or African American Males
4 = Black or African American Females
Date of Hire (DOH)
Absence Record (ABS)
Promotability Rating (PROMO)
1 = Yes, 2 = No
9 = Native Hawaiian or Pacific Islander Males
10 = Native Hawaiian or Pacific Islander
Females
11 = Native American or Alaska Native Male
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
3
Education Level (EDU)
1 = HS Diploma
2 = Some College
3 = Bachelor’s Degree
Part I – Frequency Distribution of EEO Categories
The purpose of this section of the spreadsheet (A84) is to compute a frequency distribution of
EEO categories. Each EEO category is designated by a number (e.g., White Males = 1). A
frequency distribution tells you how many times each such value occurs. In this case, we are
interested in the number of employees that fall into each EEO category. The frequency
distribution will provide the basis for the utilization analysis as well as identify employees from
each EEO category for the reduction in force analysis.
Frequency Distribution. In this section, you will use the COUNTIF function to determine the
number of employees in each EEOC category.
The COUNTIF function is used to count cells in a certain category. The function is written as
follows:
= 𝐶𝑂𝑈𝑁𝑇𝐼𝐹 (𝑟𝑎𝑛𝑔𝑒, 𝑐𝑟𝑖𝑡𝑒𝑟𝑖𝑎)
Where: range = the range of cells to count.
criteria = the criteria that controls which cells should be counted.
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
4
Part II – Utilization and Availability Analysis for MOBICOM Managers
Determining an organization’s “utilization” of ethnic minorities and women is a prerequisite to
establishing diversity goals and timetables. The availability analysis refers to the representation
of women and minorities in the relevant labor market (i.e., the labor area surrounding the
organization). These availability percentages were compiled by MTC’s HR Department and are
found in row 110. Row 111 shows the number of applicants MTC has received from applicants
in each group.
1. Employees #. Calculate the number of managers employed at MTC, organized by EEOC
category. These data come from the calculations you just made. Calculate the total for each
ethnic group and for males and females.
2. Employees %. To calculate this percentage, divide the total in each employee group by the
total number of managers.
Johnson, Human Resource Information Systems, 5e
SAGE Publishing, 2021
5
For the purpose of this assignment, you will refer to the values derived in row 113 and assess
whether they yield a TRUE or FALSE result (will appear as 1 or 0, respectively). Perform the
function to find the rest of the values in the row.
Study Questions. Answer the following questions in a separate document.
1. What ethnic group has the highest percentage of employees? Does this surprise you?