Hello,
For this case study assignment it will need to be completed on Excel. There are 10 tasks and it can be split over 5 different excel sheets (aka tabs) see below. If you feel like the task should be separated to a different sheet, please feel free to do so. I’ve attached the PDF for the case study analysis needed to complete this assignment.
Sheet 1: Task A
Sheet 2: Task B, Task C
Sheet 3: Task D, Task E
Sheet 4: Task F, Task G
Sheet 5: Task H, Task I Task J
You will prepare the SHRM case analysis on
“Designing a Pay Structure,” which consists of your completion of Tasks A–J
that simulate the creation of a compensation system for an organization in
meeting its goals and supporting its mission. In your analysis, respond to the
following tasks found in the case study by using Excel.
Your case analysis should consist of:
Task A: Create a complete job description for the benefits
manager position using O*NET.
Task B: Calculate the job evaluation points for the
administrative assistant, payroll assistant, operational analyst, and
benefits manager jobs. Provide a rationale for assigning specific degrees
to the various jobs.
Task C: If there were any outliers (i.e., extreme data points)
in the data, what would you recommend doing with them? From this point
forward, assume no extreme data points exist in the dataset.
Task D: Conduct a simple regression in Excel to create a market
pay line by entering the job evaluation points (on the X axis) and the
respective weighted average market base pay (on the Y axis) for each
benchmark job.
Task E: What is your R squared (variance explained)? Is it
sufficient to proceed?
Task F: Calculate the predicted base pay for each benchmark
job.
Task G: Because your company wants to lead in base pay by 3%,
adjust the predicted pay rates to determine the base pay rate you will
offer for each benchmark job.
Task H: Create pay grades by combining any benchmark jobs that
are substantially comparable for pay purposes. Clearly label your pay
grades and explain why you combined any benchmark jobs to form a grade.
Task I: Use your answer to Task H to determine the pay range
(i.e., minimum and maximum) for each pay grade.
Task J: Given the pay structure you have generated, consider
the following: Does this pay structure make good business sense? Do you
think it is consistent with the organization’s business strategy? What are
the implications of this pay structure for other HR systems, such as
retention and recruiting?
Your analysis of this case and
your written submission should reflect an understanding of the critical
issues of the case, integrating the material covered in the text, and
present concise and well-reasoned justifications for the stance that you
take. You are to complete this case analysis using Excel in a spreadsheet
analysis format.