34. Assume that all of a company’s job applicants musttake a test, and that the scores on this test are normallydistributed. The selection ratio is the cutoff point usedby the company in its hiring process. For example, aselection ratio of 25% means that the company willaccept applicants for jobs who rank in the top 25% ofall applicants. If the company chooses a selection ratioof 25%, the average test score of those selected will be1.27 standard deviations above average. Use simulation to verify this fact, proceeding as follows.a. Show that if the company wants to accept onlythe top 25% of all applicants, it should acceptapplicants whose test scores are at least 0.674standard deviation above average. (No simulationis required here. Just use the appropriate Excelnormal function.)b. Now generate 1000 test scores from a normaldistribution with mean 0 and standard deviation1. The average test score of those selected is theaverage of the scores that are at least 0.674. Todetermine this, use Excel’s DAVERAGE function. To do so, put the heading Score in cellA3, generate the 1000 test scores in the rangeA4:A1003, and name the range A3:A1003 Data.In cells C3 and C4, enter the labels Score and.0.674. (The range C3:C4 is called the criterionrange.) Then calculate the average of all applicants who will be hired by entering the formula=DAVERAGE(Data, "Score", C3:C4) in anycell. This average should be close to the theoretical average, 1.27. This formula works as follows.Excel finds all observations in the Data rangethat satisfy the criterion described in the rangeC3:C4 (Score.0.674). Then it averages the values in the Score column (the second argument ofDAVERAGE) corresponding to these entries. Seeonline help for more about Excel’s database “D”functions.c. What information would the company need todetermine an optimal selection ratio? How could itdetermine the optimal selection ratio?
34. Assume that all of a company’s job applicants must
take a test, and that the scores on this test are
distributed
by the company in its hiring process. For example, a
selection ratio of 25% means that the company will
accept applicants for jobs who rank in the top 25% of
all applicants. If the company chooses a selection ratio
of 25%, the average test score of those selected will be
1.27 standard deviations above average. Use simulation to verify this fact, proceeding as follows.
a. Show that if the company wants to accept only
the top 25% of all applicants, it should accept
applicants whose test scores are at least 0.674
standard deviation above average. (No simulation
is required here. Just use the appropriate Excel
normal function.)
b. Now generate 1000 test scores from a normal
distribution with mean 0 and standard deviation
1. The average test score of those selected is the
average of the scores that are at least 0.674. To
determine this, use Excel’s DAVERAGE function. To do so, put the heading Score in cell
A3, generate the 1000 test scores in the
A4:A1003, and name the range A3:A1003 Data.
In cells C3 and C4, enter the labels Score and
.0.674. (The range C3:C4 is called the criterion
range.) Then calculate the average of all applicants who will be hired by entering the formula
=DAVERAGE(Data, "Score", C3:C4) in any
cell. This average should be close to the theoretical average, 1.27. This formula works as follows.
Excel finds all observations in the Data range
that satisfy the criterion described in the range
C3:C4 (Score.0.674). Then it averages the values in the Score column (the second argument of
DAVERAGE) corresponding to these entries. See
online help for more about Excel’s database “D”
determine an optimal selection ratio? How could it
determine the optimal selection ratio?
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 6 images
