Designing a Pay Structure case study worksheet

This case, “Designing A Pay Structure (Links to an external site.)Links to an external site.,” can be accessed in the Student Development materials available from the SHRM website.

The student workbook can be found under the link above, or by searching the SHRM website. Make sure to select “Student Development” to filter results.

Use the attached form to complete/fill out the case study.

Task C – Determine if there are outliers (i.e., extreme data points)

• Use the provided form for recording the results of any identified outliers (if any)
• Analyze the survey pay data and generate “weighted average means” for each benchmark job. Enter results into provided form.

Task D – Conduct a simple regression in Excel to create a market pay line by entering into an Excel spreadsheet the correct job evaluation points (on the x axis) and the respective weighted average market base pay (on the y axis) for each benchmark job.

• Can use the LINEST function in Excel
  • Excel Path: Formulas – More Functions – Statistics – LINEST – using weighted average market base pay enter into y values field (copy all & paste) – using corresponding points enter into x values field (copy all & paste) – result should provide both the “slope coefficient” and “y-intercept”.
  • Note: Example formula – 225.42 (X) – 15678.89 (these are not the correct amounts only an example.)
  • Note: In some versions of Excel may need to use a combination of functions including: Slope and Intercept to obtain data to build formula.
• Enter results into provided form.

Task E – Record your resulting R squared on the provided_form.
Follow the same path above with the RSQ function.

Task F – Calculate the predicted base pay for each benchmark job and enter results into the provided form.