Relationship between evaluation and performance at XYZ Corp

XYZ corporation has a very similar pay structure for its managers as we saw in the Merck case study.  The data set that you will use provides information for all managers in this company over the period of approximately 1990 to 1994.  I would like you to look at the relationship between salary increases and performance evaluations. In this assignment, I really want you to basically duplicate the material that was in the Merck study for another company. The goal is to produce a table like the table in Merck, with COMPA ratio categories across the top and performance ratings down the side. The entries in the first table are the average percentage increases for each cell. In the second table, you do the same thing except you provide the average bonus payments in the cells.  You should probably look over Kevin Murphy's chapter on the case study (on your reading list).  The tables and analysis in that chapter are exactly what you will do in this project.  These tables are sometimes referred to as "Green Cards" because HR managers are given the table with salary increase percentages as guidelines for setting of salary increases that is often laminated on green paper.

1. PREVSAL is their salary prior to the wage increase (the wage increase is measured in percentage points by PERCENT).

2. Compute a new variable COMPA as the ratio of their previous salary and the midpoint of their salary range. (COMPA=PREVSAL/MIDPOINT  both these are variables in the data set)

3. Recode COMPA into the following categories: 0 to <.8; .8 to <1.0; 1.0 to <1.2; 1.2+ .  Since this is a continuous variable, you need to be very careful when you recode it.  You can do this with a nested IF statement or by using a table and a LOOKUP function.  Call the new variable CATEGORY.  I suppose that you can also do this by sorting the data and then making the CATEGORY column by hand.  This takes quite a bit longer, but will accomplish the job.

4. The performance rating variable (NEWPERF) has been coded into the following groups:  0=NO PERFORMANCE EVALUATION MADE; 1=UNSATISFACTORY; 2=NEEDS IMPROVEMENT; 3=GOOD; 4=OUTSTANDING; 5=DISTINGUISHED.  Please ignore all cases where no performance evaluation was made (eliminate these cases from ALL tables below).  The evaluations were made, but not recorded.  Therefore, we cannot make judgments about the system based on these data.

5.  All your tables will have NEWPERF across the rows and CATEGORY down the columns.  The tables will have 6 x 4 cells.  Label the rows and columns properly once the pivot tables are made.  You will make the tables with the Pivot Table command.

6.  Produce a table with average percentages (Average of PERCENT) and an identical table with the number of workers in each cell. (Count of PERCENT).  This table should only use merit raises (ACTION=18 or ACTION=19). You will need to sort the data to use only the rows with ACTION=18 or ACTION=19.  You will need to use another subset below, so be careful to keep the original data.

7. Produce a table that yields the percentage that receive bonus amounts for managers who are eligible for a bonus based on performance (BONUS=3).   This can be done using BONUS as pages and average of RECEIVEB (RECEIVEB=1 if a bonus is paid and zero otherwise) as the entry in the table (NEWPERF and CATEGORY are still the columns and rows). ("PAGES" refers to the pivot table option to drag a variable to PAGE - You can again do this by sorting for only rows with BONUS=3 and not using the PAGE option).   Then select the table page with BONUS=3. Do this same table showing the numbers of managers who receive bonuses in each of these cells. (NOTE:  If you have selected only data with ACTION=18 or ACTION=19 you will get very few entries since the ACTION for BONUS is NOT a merit raise - Go back to the original data and do it over)

8. Compute a new table with average bonus amounts (BONUSAMT) as entries in the table.  You will again need to use BONUS as the "page" (see Project 1) and NEWPERF and CATEGORY as the rows and columns.  Add RECEIVEB as a page.  This will allow you to produce the table of average bonus amounts across all BONUS=3 managers (BONUS=3 and RECEIVEB=All) and also a table that computes this average only for persons who actually get bonus payments for performance. (BONUS=3 and RECEIVEB=1)

9.  Recompute the tables in #8 showing the number of managers in each cell rather than the bonus amount. (ie use COUNT instead of AVERAGE)

Do you have any recommendations?  In particular, are the problems with this company similar to problems in the Merck study?  Is there any offsetting of these problems that seems to be accomplished by using bonuses? (THESE ARE IMPORTANT IN YOUR GRADE ON THIS PROJECT).  Does XYZ seem to be using bonuses to offset problems with using wage ranges and giving low salary increases to people at the top of the range?

The data are contained in an EXCEL file (file name=445total2.xls).  

READ THIS! ->

Administered pay systems often run into problems because high performs buck up against top levels for their pay grades.  This can reduce incentives.  Merck tried to deal with this problem and other problems in performance evaluation.  One possible solution is to introduce a system of bonuses to offset the problem.  This assignment is to introduce you to how you might look at the data to determine if you do have a motivation problem similar to Merck.