Assignment #4

Background Info:In the group work you started during class, you were given instructions to run a regression.Aregression equation provides a linear relationship between the internal structure of a company's compensation system (job evaluation points) and external data (market rates).

Compensation directors use regression in order to translate the value their company places on their benchmark jobs (given by the number job evaluation points) into salary rates that reflect the (external) salary rates, which are derived from wage and salary survey data.

The external rates for every job on the AwareHR database looks like this one for the Programmer position.As we learned in class, unless KGW used a consulting company to customized survey, these rates were derived from combining information from 3 or more wage and salary surveys.If KGW's Comp/Benefits Manager, Ellen Pump, thought that one or more of those surveys contained data that was more similar to KGW's profile, she might have weighted the information from those surveys more than the others (you can see an illustration of this in the pptx from class).Similarly, if she thought that the economy changed dramatically since the survey data was collected, she would age the data.Usually the term market rate refers to the salary at the 50%

Q1: You are an intern for KGW and you are helping Ellen update KGW's compensation plan. Using the spreadsheet provided, pleasefill in the appropriate job grades and the external market rate (50%) for each position. For the Recruiter position, insert the external salary rate of 60,360. Then, run the regression using a scatter plot and add a linear regression line to your chart, the regression equation, and R². If you need a refresher, see the instructions below:

Video:https://www.khanacademy.org/math/cc-eighth-grade-math/cc-8th-linear-equations-functions/8th-linear-functions-modeling/v/fitting-a-line-to-data

If your version of Excel is different than the one used in the video, these instructions might help: Go to theInserttab on the top ribbon of your Excel spreadsheet. Highlight the Job Evaluation Points and Market Rates columns. Click on the scatter chart.To generate an equation and R²value on your chart, look at the top ribbon of your excel sheet forChart Designand choose the tabChart Elements and then Linear Trendline. Then go back to Chart Elements, find Trendline Options and choose R-square and equation.It's a slightly different look depending upon your version of Excel, so you may have to google it if these instructions don't match your version of excel.

Please copy and paste your R²and equation

Background Info Q2:Ideally, you want an R²value of .90 or above.

So if your R²is < .9o, you will review the data to see if there are any outliers. After consultation with Ellen, you decide that it might make sense to remove the VP position and medical doctors from the data and determine their salary rates separately, using external market information that is more specific to those positions.

Q2:Please explain why this might make sense

Q3:Use the AwareHR database to identify the positions in the chart that require a medical degree. Theneliminate these positions and the VP positionand re-run the regression.Are you satisfied with the value of the R2?Please explain.

Background Information Question #4:You would still like to increase the R². In reviewing the data, you realize that the external data doesn't appear to be consistently current - specifically with respect to the Medical Records Troubleshooter and Center Administrator.You go back to the survey data and decide to use the external salary rates at the 75% for the Medical Records Troubleshooter and Center Administrator positions instead of the rates at the 50%.

Question #4: Please re-run the regression and provide the equation and the R². Are you happier with this R²?

Background Information Question #5:Remember the Informatics Nurse 1position (Assignment #2)? This position is not a benchmark position.Therefore you will calculate the salary by plugging in the job evaluation points for this position (2275) as X in the regression equation.

Question #5:What is the salary for the Informatics Nurse 1 position?

Question #6:You are considering combining job grades 19 and 20.

Go to the AwareHR database and under Analytics, click on Salary Range by Grade.

How many employees are in Salary Grade 19?

How many are in Salary Grade 20?

What are the salary ranges for each of those grades?

Now go up to the Library tab.If you can't find it, click on your name in the upper left corner and scroll down to the Library tab. Once in the Library tab, open the tab: Basic Data by Boss.This is a huge excel spreadsheet.You want to look at the positions in job grades 19 and 20 so you are going to use the filter function to do that. According to the Codebook (tab at the bottom of your spreadsheet), the job grades are in column AZ so highlight the headings, use the filter function and choose job grades 19 and 20. You should have 14 entries.

Copy and paste the following columns for each of the employees in job grades 19 and 20 into a new spreadsheet: