Assume you work at a company with the demographic characteristics as given in Tables 1 and 2 on the following page. You are concerned with the under-representation of individuals in Group X at your company and you would like to see greater representation of Group X in your companys workforce. You consider three strategies:
a) Hire as many Xs as possible until their representation in your workforce matches their representation in the pools of qualified applicants;
b) Hire individuals blindly from the hiring pools which should eventually result in the proportion of Xs in your workforce more closely matching their representation in the pools of qualified applicants;
c) Hire individuals randomly from the hiring pools but give Xs an increased chance of being hired by raising the hiring pool percentage by 15% (i.e. is if 30% of the hiring pool is X, then hire giving Xs a 45% chance).
Strategy a) might be considered to X biased and is probably illegal; Strategy b) might be considered neutral and is probably legal; and Strategy c) might be considered Pro X and of unknown legal status.
Assignment:
Your job is to simulate the attrition and subsequent hiring on a yearly basis for a five year period for the above three strategies. In addition to showing the details of your simulation, you should also show a year by year summary of the workforce compositions of the three job categories by strategy. Further you should write a summary report assessing the effectiveness of the three strategies in the different job categories with respect to your objective of increasing the number of Xs in your workforce.
Hints:
1) Assume that adequate representation in your workforce is achieved when the percentage of Xs in the workforce matches the percentage representation in the appropriate hiring pools. This means that for Strategy a), even though biased toward X, if you achieve your goal you will hire only as many Xs as the number that leave. You have no control on the number of Xs with strategies b) and c).
2) You can assume that all simulated probability distributions are independent of one another and that the total size of the workforce in each category will remain the same over the five year period.
3) Use the Poisson distribution to simulate total attrition for each job category for each of the five years. These total attrition rates should be the same for all three strategies.
4) Assume that the attrition is blind to whether a person is X or not X. Accordingly you will have to determine how many of the people leaving will be Xs or not Xs. This will vary from year to year since the composition of the workforce will change over time. In addition, it will vary with the strategy since if the strategy results in a workforce with more Xs, then more Xs will leave. Simulate this with the Binomial distribution.
5) Replace the individuals who have left. In the case of Strategy a), this is done on a de facto quota basis. In the case of Strategies b) and c) use the Binomial distribution to determine how many new X employees are hired.
Table 1.
Workforce Starting Composition
Category # X # not X Total Turnover Rate
Executive/Managerial 5 25 30 3% per Year
Professional 45 105 150 12% per Year
Clerical 120 180 300 25% per Year
Table 2.
Qualified Applicants for Position in Category
Category X Not X
Executive/Managerial 30% 70%
Professional 40% 60%
Clerical 50% 50%
Additional Requirements
Min Pages: 3
Level of Detail: Show all work
Other Requirements: There should be a total of 9 simulations over a 5 year period. The format must be in excel.