Data Analysis with Microsoft Excel: Updated for Office 2007

(Tuis.) #1

380 Statistical Methods


Are Female Faculty Underpaid?

Being satisfi ed with the validity of the regression on males, let’s go ahead
and apply it to the females to see whether they are underpaid. The idea is
to look at the differences between what female faculty members were paid
and what we would predict they would be paid on the basis of the regres-
sion model for male faculty. Your ultimate goal is to choose between two
hypotheses.
H 0 : The mean population salaries of females are equal to the salaries pre-
dicted from the population model for males.
Ha: The mean population salaries of females are lower than the salaries
predicted from the population model for males.
To obtain statistics on the salaries for females relative to males, you must
create new columns of predicted values and residuals.

To create new columns of predicted values and residuals:

1 Create a new blank worksheet named Female Faculty and then go to
the Salary Data worksheet.
2 Click the Gender drop-down list arrow and select only the F
checkbox.
Verify that the range A1:F38 displaying data on only the female fac-
ulty is displayed in the worksheet.
3 Copy the selection and paste it to cell A1 on the Female Faculty
worksheet.
4 In the Female Data worksheet, click cell G1, type Predicted Salary
press Ta b, type Residuals and then press Enter.
5 Select the range G2:H38.
6 In cell G2, type
5 12900.67 1 744.4821*B2 2 783.529*C2 1 373.7354*D2 1 606.1759*E2
(the regression equation for males), and press Ta b.
7 Type =F2–G2 in cell H2, and press Enter.
8 Select the cell range F2:H38 and then click the Fill button from the
Editing group on the Home tab and click Down. Excel inserts the for-
mula into the remaining cells in the two columns. The data should
appear as in Figure 9-17.
Free download pdf