Assume you work at a company with the demographic characteristics as given in Tables 1 and 2 on the following page.

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 company’s workforce. You consider three strategies: a) Hire as many X’s 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 X’s in your workforce more closely matching their representation in the pools of qualified applicants; c) Hire individuals “randomly” from the hiring pools but give X’s 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 X’s 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 X’s in your workforce. Hints: 1) Assume that adequate representation in your workforce is achieved when the percentage of X’s 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 X’s as the number that leave. You have no control on the number of X’s 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 X’s or not X’s. 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 X’s, then more X’s 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.