1. Excel TAB 1: complete the job evaluation form for all 5 positions. (Job Descriiption file attached)
I’ve included a set of “suggested solution values” highlighted in green on the excel tab. This is not to say your values are any more “right” or “wrong” just gives you a sense of what another authoritative source suggests the total job evaluation points could look like.
2. TAB 2: Calculate the weighted averages for salary for each of the 5 benchmark positions using the market survey data (file attached as Market Survey Data)
The file attached provides the pay data that has been collected from 6 other competitors (Companies A-F). Included in the collected market survey data are the count of employees in each position, the average of the pay at that company, and the max and minimum.
3. TAB 3: Calculate Predicted Pay for the 5 benchmark positions using the excel template(excel template attached), using a simple regression approach assisted by creating an excel graph.
4. Lastly in word document reflect on these results from the three excel tabs.
Discuss the total job evaluation points across the 5 positions. Does the order of job evaluation points make sense? Does the relative values (differentials between positions) make sense? How do your personal results compare to the green highlighted suggested results? What do you think accounts for these differences?
Discuss the regression and R-squared results. Do the results align between the job evaluation step and the market survey data? If not (i.e. a low R-squared value), what do you think you should next (double check the market survey calcs/data? revisit the job evaluation process, etc).
5. Using the discussion of pay grades and ranges , present your argument for how you would organize these 5 positions into a pay-grades/ranges.
