W

Employee	Degree Attained	Years since graduation	Salary (annual)
1	BSc	3	39,000
2	MS	6	58,800
3	MS	3	50,400
4	PhD	7	95,900
5	BSc	6	48,000
6	PhD	9	105,300
7	MS	4	53,500
8	PhD	3	77,100
9	BSc	7	51,000
10	PhD	6	91,200
11	MS	9	67,200
12	MS	7	61,600
13	PhD	4	82,800
14	BSc	9	57,000
15	BSc	4	42,000

Using a spreadsheet program, follow these instructions to construct your pay policy line:

1. Plot the jobs using a scatter diagram with the Salary Scale on the vertical axis and the JE Points on the horizontal axis.
2. Draw a trend or best-fit line through the middle of all the jobs. Make sure that you include all the data points - not just the trend line.
3. Construct a set of pay boxes (no broad banding) and justify the number of boxes you created.
4. In a separate area of the spreadsheet, explain the rationale behind the width of the grades as well as the number of boxes. Also, explain any "overlap" that occurs.

5. The last column (column 4) represents the results of the survey conducted by the outside HR consultant to come up with the average salary of other individuals performing similar jobs to those employees in Precision Engineering. You are using the survey results to do the pay structure analysis for Precision Engineering.

6. You can use Microsoft EXCEL to create a scatter graph. EXCEL has built-in functionality to create the trendline.

7. To determine the job evaluation (JE) points you need to come up with a system to calculate the points considering the employee data in the above table for degree attained in experience (in columns 2 and 3). Using the system you create you can then calculate the number of JE points for each employee (and job).

8. Before plotting the salary on the vertical axis and JE points on the horizontal axis of the scatter plot, be sure to come up with grades and pay ranges to use. That will be needed to add the "boxes" to the scatter diagram for your analysis.

9. You will notice that some salaries in the same pay box on the scatter plot are for similar jobs but have different JE points. You need to discuss how that happens and why it can potentially be problematic.