Question is on week 4 on the attached excel doc.

ID
Sal
Compa
Mid
Age
EES
SER
G
Raise
Deg
Gen1
Gr
1
58
1.017
57
34
85
8
0
5.7
0
M
E
0 The ongoing question that the weekly assignments will focus on is:
Are males and females paid the same for equal work (under the Equal Pay Act)?
2
27
0.870
31
52
80
7
0
3.9
0
M
B
Note: to simplfy the analysis, we will assume that jobs within each grade comprise equal work.
3
34
1.096
31
30
75
5
1
3.6
1
F
B
4
66
1.157
57
42
100
16
0
5.5
1
M
E
The column labels in the
table mean:
5
47
0.979
48
36
90
16
0
5.7
1
M
D
ID – Employee sample number Sal – Salary in thousands
6
76
1.134
67
36
70
12
0
4.5
1
M
F
Age – Age in years
EES
– Appraisal rating (Employee evaluation score)
7
41
1.025
40
32
100
8
1
5.7
1
F
C
SER – Years of service
G – Gender (0 = male, 1 = female)
8
23
1.000
23
32
90
9
1
5.8
1
F
A
Mid – salary grade midpoint
Raise – percent of last raise
9
77
1.149
67
49
100
10
0
4
1
M
F
Grade – job/pay grade
Deg (0= BS\BA 1 = MS)
10
22
0.956
23
30
80
7
1
4.7
1
F
A
Gen1 (Male or Female)
Compa - salary divided by midpoint, a measure of salary that removes the impact of grade
11
23
1.000
23
41
100
19
1
4.8
1
F
A
12
60
1.052
57
52
95
22
0
4.5
0
M
E
This data should be treated as a sample of employees taken from a company that has about 1,000
13
42
1.050
40
30
100
2
1
4.7
0
F
C
employees using a random sampling approach.
14
24
1.043
23
32
90
12
1
6
1
F
A
15
24
1.043
23
32
80
8
1
4.9
1
F
A
16
47
1.175
40
44
90
4
0
5.7
0
M
C
Mac Users: The homework in this course assumes students have Windows Excel, and
17
69
1.210
57
27
55
3
1
3
1
F
E
can load the Analysis ToolPak into their version of Excel.
18
36
1.161
31
31
80
11
1
5.6
0
F
B
The analysis tool pak has been removed from Excel for Windows, but a free third-party
19
24
1.043
23
32
85
1
0
4.6
1
M
A
tool that can be used (found on an answers Microsoft site) is:
20
34
1.096
31
44
70
16
1
4.8
0
F
B
21
76
1.134
67
43
95
13
0
6.3
1
M
F
Like the Microsoft site, I make cannot guarantee the program, but do know that
22
57
1.187
48
48
65
6
1
3.8
1
F
D
Statplus is a respected statistical package.You may use other approaches or tools
23
23
1.000
23
36
65
6
1
3.3
0
F
A
as desired to complete the assignments.
24
50
1.041
48
30
75
9
1
3.8
0
F
D
25
24
1.043
23
41
70
4
0
4
0
M
A
26
24
1.043
23
22
95
2
1
6.2
0
F
A
27
40
1.000
40
35
80
7
0
3.9
1
M
C
28
75
1.119
67
44
95
9
1
4.4
0
F
F
29
72
1.074
67
52
95
5
0
5.4
0
M
F
30
49
1.020
48
45
90
18
0
4.3
0
M
D
31
24
1.043
23
29
60
4
1
3.9
1
F
A
32
28
0.903
31
25
95
4
0
5.6
0
M
B
33
64
1.122
57
35
90
9
0
5.5
1
M
E
34
28
0.903
31
26
80
2
0
4.9
1
M
B
35
24
1.043
23
23
90
4
1
5.3
0
F
A
36
23
1.000
23
27
75
3
1
4.3
0
F
A
37
22
0.956
23
22
95
2
1
6.2
0
F
A
38
56
0.982
57
45
95
11
0
4.5
0
M
E
39
35
1.129
31
27
90
6
1
5.5
0
F
B
40
25
1.086
23
24
90
2
0
6.3
0
M
A
41
43
1.075
40
25
80
5
0
4.3
0
M
C
42
24
1.043
23
32
100
8
1
5.7
1
F
A
43
77
1.149
67
42
95
20
1
5.5
0
F
F
44
60
1.052
57
45
90
16
0
5.2
1
M
E
45
55
1.145
48
36
95
8
1
5.2
1
F
D
46
65
1.140
57
39
75
20
0
3.9
1
M
E
47
62
1.087
57
37
95
5
0
5.5
1
M
E
48
65
1.140
57
34
90
11
1
5.3
1
F
E
49
60
1.052
57
41
95
21
0
6.6
0
M
E
50
66
1.157
57
38
80
12
0
4.6
0
M
E
http://www.analystsoft.com/en/products/statplusmacle

Week 1.
Describing the data.
<Use right click on the row numbers at the left to insert rows below each question for your results and comments.>
1Using the Excel Analysis ToolPak function descriptive statistics, generate and show the descriptive statistics for each appropriate variable in the sample data set.
a.
For which variables in the data set does this function not work correctly for?
Why?
The require doutput is given below,
Sal
Compa
Mid
Age
EES
SER
G
Raise
Deg
Mean
45 Mean
1.06248 Mean
41.76 Mean
35.72 Mean
85.9 Mean
8.96 Mean
0.5 Mean
4.938 Mean
0.5
Standard E 2.715488 Standard E 0.010865 Standard E 2.295436 Standard E 1.166904 Standard E 1.614286 Standard E 0.8086067635 Standard E 0.071429 Standard E 0.122654 Standard E 0.071429
Median
42.5 Median
1.051 Median
40 Median
35 Median
90 Median
8 Median
0.5 Median
4.9 Median
0.5
Mode
24 Mode
1.043 Mode
23 Mode
32 Mode
95 Mode
8 Mode
0 Mode
5.7 Mode
0
Standard D
19.2014 Standard D 0.076825 Standard D 16.23119 Standard D 8.251258 Standard D 11.41472 Standard D 5.7177132578 Standard D 0.505076 Standard D 0.867295 Standard D 0.505076
Sample Va 368.6939 Sample Va 0.005902 Sample Va 263.4514 Sample Va 68.08327 Sample Va 130.2959 Sample Va
32.692244898 Sample Va 0.255102 Sample Va
0.7522 Sample Va 0.255102
Kurtosis
-1.445227 Kurtosis
-0.174608 Kurtosis
-1.517166 Kurtosis
-0.7747 Kurtosis
-0.035461 Kurtosis
-0.3991207282 Kurtosis
-2.085106 Kurtosis
-0.793594 Kurtosis
-2.085106
Skewness
0.23788 Skewness -0.316686 Skewness
0.160991 Skewness
0.262221 Skewness -0.820437 Skewness
0.7278826994 Skewness
-5E-018 Skewness -0.163763 Skewness
-5E-018
Range
55 Range
0.34 Range
44 Range
30 Range
45 Range
21 Range
1 Range
3.6 Range
1
Minimum
22 Minimum
0.87 Minimum
23 Minimum
22 Minimum
55 Minimum
1 Minimum
0 Minimum
3 Minimum
0
Maximum
77 Maximum
1.21 Maximum
67 Maximum
52 Maximum
100 Maximum
22 Maximum
1 Maximum
6.6 Maximum
1
Sum
2250 Sum
53.124 Sum
2088 Sum
1786 Sum
4295 Sum
448 Sum
25 Sum
246.9 Sum
25
Count
50 Count
50 Count
50 Count
50 Count
50 Count
50 Count
50 Count
50 Count
50
For the variables Gen1 and Gr this function does not work as these variables are containing non numeric data.
2
Sort the data by Gen or Gen 1 (into males and females) and find the mean and standard deviation for each gender for the following variables:
sal, compa, age, sr and raise.
Use either the descriptive stats function or the Fx functions (average and stdev).
For Females:
Sal
Compa
Mid
Age
EES
SER
G
Raise
Mean
38 Mean
1.06872 Mean
34.88 Mean
32.52 Mean
84.2 Mean
7.92 Mean
1 Mean
4.88
Standard E 3.658779 Standard E 0.014069 Standard E 2.990162 Standard E
1.37613 Standard E 2.718455 Standard E 0.9813596011 Standard E
0 Standard E 0.183848
Median
34 Median
1.043 Median
31 Median
32 Median
90 Median
8 Median
1 Median
4.9
Mode
24 Mode
1.043 Mode
23 Mode
32 Mode
90 Mode
8 Mode
1 Mode
5.7
Standard D
18.2939 Standard D 0.070345 Standard D 14.95081 Standard D 6.880649 Standard D 13.59228 Standard D 4.9067980055 Standard D
0 Standard D 0.919239
Sample Va 334.6667 Sample Va 0.004948 Sample Va 223.5267 Sample Va 47.34333 Sample Va
184.75 Sample Va 24.0766666667 Sample Va
0 Sample Va
0.845
Kurtosis
-0.336387 Kurtosis
-0.775295 Kurtosis
-0.267883 Kurtosis
-0.035485 Kurtosis
-0.705589 Kurtosis
0.8815005594 Kurtosis
#DIV/0! Kurtosis
-0.782134
Skewness
0.99657 Skewness
0.33759 Skewness
1.011154 Skewness
0.622436 Skewness -0.641979 Skewness
1.0552719395 Skewness
#DIV/0! Skewness -0.440096
Range
55 Range
0.254 Range
44 Range
26 Range
45 Range
18 Range
0 Range
3.2
Minimum
22 Minimum
0.956 Minimum
23 Minimum
22 Minimum
55 Minimum
2 Minimum
1 Minimum
3
Maximum
77 Maximum
1.21 Maximum
67 Maximum
48 Maximum
100 Maximum
20 Maximum
1 Maximum
6.2
Sum
950 Sum
26.718 Sum
872 Sum
813 Sum
2105 Sum
198 Sum
25 Sum
122
Count
25 Count
25 Count
25 Count
25 Count
25 Count
25 Count
25 Count
25
For Males:
Sal
Compa
Mid
Age
EES
SER
G
Raise
Mean
52 Mean
1.05624 Mean
48.64 Mean
38.92 Mean
87.6 Mean
10 Mean
0 Mean
4.996
Standard E 3.555278 Standard E 0.016758 Standard E 2.937618 Standard E
1.67722 Standard E 1.734935 Standard E 1.2714820749 Standard E
0 Standard E 0.165376
Median
58 Median
1.052 Median
57 Median
39 Median
90 Median
9 Median
0 Median
4.9
Mode
60 Mode
1.052 Mode
57 Mode
52 Mode
95 Mode
16 Mode
0 Mode
5.7
Standard D 17.77639 Standard D 0.083789 Standard D 14.68809 Standard D
8.3861 Standard D 8.674676 Standard D 6.3574103743 Standard D
0 Standard D 0.826882
Sample Va
316 Sample Va 0.007021 Sample Va
215.74 Sample Va 70.32667 Sample Va
75.25 Sample Va 40.4166666667 Sample Va
0 Sample Va 0.683733
Kurtosis
-1.159625 Kurtosis
-0.148414 Kurtosis
-0.991871 Kurtosis
-0.608574 Kurtosis
-0.557075 Kurtosis
-0.9847253303 Kurtosis
#DIV/0! Kurtosis
-1.068919
Skewness -0.371617 Skewness -0.648136 Skewness -0.548426 Skewness -0.218315 Skewness -0.582884 Skewness
0.4272605622 Skewness
#DIV/0! Skewness
0.256016
Range
53 Range
0.305 Range
44 Range
28 Range
30 Range
21 Range
0 Range
2.7
Minimum
24 Minimum
0.87 Minimum
23 Minimum
24 Minimum
70 Minimum
1 Minimum
0 Minimum
3.9
Maximum
77 Maximum
1.175 Maximum
67 Maximum
52 Maximum
100 Maximum
22 Maximum
0 Maximum
6.6
Sum
1300 Sum
26.406 Sum
1216 Sum
973 Sum
2190 Sum
250 Sum
0 Sum
124.9
Count
25 Count
25 Count
25 Count
25 Count
25 Count
25 Count
25 Count
25
3
What is the probability for a:
Required Probability =
#NAME?
Required Probability =
#NAME?
c.
Why are the results different?
As some Grade E is also positioned by females so the results varies.
4
Find:
a. The z score for each male salary, based on only the male salaries.
b. The z score for each female salary, based on only the female salaries.
c. The z score for each female compa, based on only the female compa values.
d. The z score for each male compa, based on only the male compa values.
e. What do the distributions and spread suggest about male and female salaries?
Why might we want to use compa to measure salaries between males and females?
The calculations and Z scores are given in Sheet2.
As there are various factors affects salary like education, experience etc so unless we use compa we are not using proper variable for comparison.
5
Based on this sample, what conclusions can you make about the issue of male and female pay equality?
Are all of the results consistent with your conclusion?
If not, why not?
From the descriptive statistics it is clear that there is a significant difference between salaries of males and females.
But it might or might not be true. As many factors affect salary so to be sure we have to analyze for them also.
a.
Randomly selected person being a male in grade E?
b.
Randomly selected male being in grade E?