Hiring at Western Electric - Assignment 2
The workers at Western Electric were hired from several different sources. Your job is to determine if the hiring source makes any difference for the quality of the employee. You are to measure "quality" both by their expected productivity and their expected tenure with the firm. For this assignment, you should consider the variable QUITTER as your measure of tenure rather than the continuous variable TENURE. It is quite possible that quit behavior is influenced by education as well as recruiting source. The relationship with education level is likely to be nonlinear. (Many analysts believe that quits are high for low schooling level people and high schooling level people on this job.)
The Western Electric data are available on an EXCEL spreadsheet . You should print out the variable information that is located in the SPSS file information file.
As in assignment 1, you need to start to determine the predicted value of workers. For this exercise, we must assume that there is no relationship between recruiting source and the wage paid. We will also use all our education-wage data and assume that it is the same across locations. (Use averages over all the sample for large metro area workers). Thus, the relative value of workers by recruiting source is simply measured by their relative productivity. We can also compute their quit probability by recruiting source. Because quitters are a small percentage of the workers, I would like you to do the evaluation by recoding education into three groups: less than high school, high school (12 years) or college. You can do this with a nested IF command IF(educ<12,1,IF(educ=12,2,3)). Determine quit probabilities for each school-recruiting source group (you should have 9 groups) (a pivot table). What do you conclude?
The cost of an employee in this example is her wage plus her probability of quitting times our cost of hiring and recruitment. We can compute the wage from the CPS data set for each of the education groups. We know the productivity of each education-hiring source group (You have nine groups - 3 education levels times 3 hiring sources, so you need to compute average productivity for each of the 9 groups and average wages for each of the nine groups. Wages are identical across each of the recruiting sources, so you need only 3 different wages. Use the wages in large metro areas again.) In some groups, the cost per unit of output for wages is high (wage/productivity) and the probability of quitting is also high. We obviously do not want to hire these workers. In other cases, the cost of wages might be high, but the probability of quitting might be low or the cost of wages might be low but the probability of quitting might be high. We therefore need to compute a "break-even" cost of turnover that would allow us to choose the appropriate education-recruiting source cells to minimize our costs. Our quit probability is the probability of quitting in 6 months. For this exercise, assume that this is evenly distributed across the six months so that the average quitter stays 3 months with the firm. Then the cost per hour for quitters is (turnover cost) divided by (3 months times 4 weeks per month times 40 hours per week) plus the quitter's wage. The cost per hour of non quitters is just their wage. Therefore, the expected cost by group is their wage plus their expected quit probability times the turnover cost per hour. Note that I have simply converted quitting costs in a cost per hour so that I could add it to wages, which is already measured in a cost per hour.
You have 9 groups to compare (3 education levels times 3 recruitment sources). The easiest way to do this is to use EXCEL's capability of evaluating formulas at various costs. You are unlikely to really know turnover cost, but you can guess at a cost. For example, assume a very low turnover cost like $100 and a very high turnover cost like $20000. Then choose some values in EXCEL and allow it to compute the total cost at each individual assumed turnover value. You can do this by using a series of values for turnover cost ranging from $100 to $20000. (If you select EDIT-FILL-SERIES and select step value =100 and end value=20000, EXCEL will fill in the series for you) This should allow you to come close to the value where both groups are equally profitable. In most instances, you can use your judgment to choose one group or the other. You can compute the exact break-even turnover cost per employee with a formula, but this is not required. This would require setting up the cost formula for two groups and then solving for the one unknown value (turnover cost) when the two costs are equal. You simply compare the total cost/productivity for each group ASSUMING a value for turnover. If that value must be lower than a reasonable number (like $1000), the group is probably out of the running because its quit costs are too high. If that value must be very large (like over $10000) for this job, then that group is probably out of the running because its wage costs relative to its productivity are too high. In between, you will need to use your judgment, or indicate that closer study of turnover costs is necessary to decide on the choice of groups. You can also find the breakeven turnover cost by graphing your results and looking at the intersection of the lines.
To support your result, you should present some charts that support your analysis. This assignment is to start to get you to factor in other issues in hiring. You need to consider other factors beyond simply productivity and wages - especially if turnover costs are high. This would be true when you have invested in significant training for the worker.