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 access

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. Student Name:
Task C
Identification of Outliers (extreme data points, if any):
Weighted Average Means for each benchmark job:
Job
Front Desk Receptionist
Weighted Mean
$
Administrative Assistant
$
Payroll Assistant
$
Operations Analyst
$
Benefits Manager
$
Task D
Regression equation for the market pay line is Y=m(x)+b
Enter your values: m= slope coefficient
b= y-intecept
Equation for market pay line:
Task E
Record your resulting R squared:
Task F
Using the formula from Task D above, calculate the Predicted Pay for each benchmark job.
Job
Front Desk Receptionist
Administrative Assistant
Payroll Assistant
Operations Analyst
Predicted Pay
$
$
$
$
Calculations
Y=
Y=
Y=
Y=
Benefits Manager
$
Y=

Purchase answer to see full
attachment