5 BSC 6 48,000 6 PhD 9 105,300 7 MS 4 53,500 8 PhD W 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,000Assignment Details: In this assignment, you are asked to use the results of a salary survey and map out the pay levels for a fictional company based on the case below. Using a spreadshe program, you will construct scatter-plot diagram that produces a pay policy line for the company. For this scatter-plot, you will need to define both grade and range. When you analyze this diagram, you will find some jobs with different point values in the same grade. This is as expected as grades group jobs that are similar. There also be jobs with lower point values that are paid more than a job with a higher point value in the same grade. Finally, you need to comment on the results of this analysis. Are higher paying jobs with lower point values a concern? Explain why and what you would recommed to about it. Case: Precision Engineering Precision Engineering is a small consulting firm. A salary survey was completed by an outside HR firm and the results averaged. The results for the Benchmark jobs a presented below. They are mid-level professional jobs. Employee Degree Attained Years since graduation Salary (annual) BSC 3 39,000 2 MS 6 58,800 MS W 50,400 4 PhD 7 95,900Additional Clarication '|.The data in column 'I, 2 and 3 of the table above is the employee data for the fictitious company (Precision Engineering) for each employee (i.e. job). 2. The last column (column 4} represents the results of the surveyr conducted by the outside HR consultant to come up with the average salary of other individuals performing similarjobs to those employees in Precision Engineering. You are using the survey results to do the pay structure analysis for Precision Engineering. 3. You can use Microsoft EXCEL to create the scatter graph. EXCEL has built in functionality to create the trendline. 4. To determine thejob 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 ofJE points for each employee (and job}. 5. Before plotting the salary on the vertical axis andJE 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. 6. You will notice that some salaries in the same pay box on the scatter plot are for similarjobs but have different jE points. You need to discuss how that happens and why it can potentially be problematic. 7. Don't do assignment under module 8. It is very similar to module 6 which is due this week. 8. Here is a sample scatter diagram that I mocked up (with fake data} to show you: Forthe assignment in module 6 you will need to create a scatter diagram to show the pay policy line. I attach a few videos to help you understand how to draw a scatter diagram in EXCEL if you are not sure. Keep in mind you are working with two variables. The X Axis will be thejob evaluation point range and Y Axis is the pay range. We will talk more aboutthis in the live collaborate session this week. Vid e0 #1: httpszyoutubechF PDVBIIo Video #2: httpszyoutubeNGWxHENePg Instructions Using a spreadsheet program. followr these instructions to construct your pay policy line: 'I. Plot the jobs using a scatter diagram with the Salary Scale on the vertical axis and theJE Points on the horizontal axis. [Use landscape view and use the whole page). 2. Draw a trend or best t 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. Note: No jobs can be green circled. red circled. or rest on a pay box boundary. Criteria Every assignment is graded according to the following criteria: - Listing the correct number of grades (2 points) - Demonstrating the range heights (2 points} - Explaining the overlap and providing an explanation (2 points) - Discussing the different salaries in one pay box (2 points) - Overall look and general accuracy [2 points} Additional Clarication 'I.The data in column 'I, 2 and 3 of the table above is the employee data forthe ctitious company {Precision Engineering) for each employee (i.e. job). 2. 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 similarjobs to those employees in Precision Engineering. You are using the survey results to do the pay structure analysis for Precision Engineering. 3. You can use Microsoft EXCEL to create the scatter graph. EXCEL has built in functionality to create the trendline. 4. To determine thejob 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 0ij points for each employee (and job}. 5. Before plotting the salary on the vertical axis andJE 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