Nov 23, 2024
HEP 456 Module 5 Section 12 and 13 Planning for Analysis and Interpretation and Gantt chartĀ
HEP 456 Module 5 Section 12 and 13 Planning for Analysis and Interpretation and Gantt chartĀ Name HEP 456: ā¦
ID
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42 Salary
64.3 27.5 35.2 63.4 46.6
72.9 43
22.7 78.1 23.6
23.8 67
40.8
25
24
38.1 68.7 33.2 24.3 34.7 75.7
56.1 24
58.3 23.6 21.9 41.8 74.6 75.4 47.7
23.1 28
61.3
27.7 24
24.1 23.2 58.9 35.7 24.1 46.4
24.9 Comparatio
1.128 0.888 1.135 1.113 0.970 1.088 1.074 0.989 1.165 1.024 1.036 1.176 1.021 1.085 1.042 0.952 1.205 1.070 1.058 1.120 1.130 1.169 1.044 1.214 1.025 0.953 1.046 1.113 1.126 0.993 1.006 0.904 1.076 0.894 1.044 1.049 1.010 1.034 1.151 1.049 1.161
1.082 Midpoint
57
31
31
57
48
67
40
23
67
23
23
57
40
23
23
40
57
31
23
31
67
48
23
48
23
23
40
67
67
48
23
31
57
31
23
23
23
57
31
23
40
23 Age
34
52
30
42
36
36
32
32
49
30
41
52
30
32
32
44
27
31
32
44
43
48
36
30
41
22
35
44
52
45
29
25
35
26
23
27
22
45
27
24
25
32 Performance Rating
85
80
75
100
90
70
100
90
100
80
100
95
100
90
80
90
55
80
85
70
95
65
65
75
70
95
80
95
95
90
60
95
90
80
90
75
95
95
90
90
80 100 Service
8
7
5
16
16
12
8
9
10
7
19
22
2
12
8
4
3
11
1
16
13
6
6
9
4
2
7
9
5
18
4
4
9
2
4
3
2
11
6
2
5
8 Gender
0
0
1
0
0
0
1
1
0
1
1
0
1
1
1
0
1
1
0
1
0
1
1
1
0
1
0
1
0
0
1
0
0
0
1
1
1
0
1
0
0
1 Raise
5.7 3.9 3.6 5.5 5.7 4.5 5.7 5.8 4
4.7 4.8 4.5 4.7 6
4.9 5.7 3
5.6 4.6 4.8 6.3 3.8 3.3
3.8 4
6.2 3.9 4.4 5.4 4.3 3.9 5.6 5.5 4.9 5.3 4.3 6.2 4.5 5.5 6.3 4.3
5.7 Degree
0
0
1
1
1
1
1
1
1
1
1
0
0
1
1
0
1
0
1
0
1
1
0
0
0
0
1
0
0
0
1
0
1
1
0
0
0
0
0
0
0
1
43 76 1.135 67 42 95 20 1 5.5 0
44 60.6 1.063 57 45 90 16 0 5.2 1
45 56.4 1.174 48 36 95 8 1 5.2 1
46 60.2 1.057 57 39 75 20 0 3.9 1
47 64.6 1.134 57 37 95 5 0 5.5 1
48 69.7 1.222 57 34 90 11 1 5.3 1
49 60.4 1.059 57 41 95 21 0 6.6 0
50 61.4 1.078 57 38 80 12 0 4.6 0
Gender1
M
M
F
M
M
M
F
F
M
F
F
M
F
F
F
M
F
F
M
F
M
F
F
F
M
F
M
F
M
M
F
M
M
M
F
F
F
M
F
M
M
F Grade
E
B
B
E
D
F
C
A
F
A
A
E
C
A
A
C
E
B
A
B
F
D
A
D
A
A
C
F
F
D
A
B
E
B
A
A
A
E
B
A
C
A Do not manipuilate Data set on this page, copy to another page to make changes
The ongoing question that the weekly assignments will focus on is: Are males Note: to simplfy the analysis, we will assume that jobs within each grade compr
The column labels in the table mean:
ID ā Employee sample number Salary ā Salary in thousands
Age ā Age in years Performance Rating - Appraisal rating (e
Service ā Years of service (roundGender ā 0 = male, 1 = female
Midpoint ā salary grade midpoinRaise ā percent of last raise
Grade ā job/pay grade Degree (0= BS\BA 1 = MS)
Gender1 (Male or Female) Compa-ratio - salary divided by midpoin
F F
M E
F D
M E
M E
F E
M E
M E
and females paid the same for equal work (under the Equal Pay Act)? rise equal work.
employee evaluation score)
nt
Week 1: Descriptive Statistics, including Probability
While the lectures will examine our equal pay question from the compa-ratio viewpoint, our weekl examining the issue using the salary measure.
The purpose of this assignmnent is two fold:
1. Demonstrate mastery with Excel tools.
2. Develop descriptive statistics to help examine the question.
3. Interpret descriptive outcomes
The first issue in examining salary data to determine if we - as a company - are paying males and f descriptive statistics to give us something to make a preliminary decision on whether we have an is
1 Descriptive Statistics: Develop basic descriptive statistics for Salary
The first step in analyzing data sets is to find some summary descriptive statistics for key
Suggestion: Copy the gender1 and salary columns from the Data tab to columns T and U
Then use Data Sort (by gender1) to get all the male and female salary values grouped tog
a. Use the Descriptive Statistics function in the Data Analysis tab
to develop the descriptive statistics summary for the overall group’s overall salary. (Place K19 in output range.)
Highlight the mean, sample standard deviation, and range.
b. Using Fx (or formula) functions find the following (be sure to show the form and not just the value in each cell) asked for salary statistics for each gender:
Male Female
52 38.67
18.05 18.64
54.5 54.1
Mean: Sample Standard Deviation: Range:
2 Develop a 5-number summary for the overall, male, and female SALARY variable. For full credit, show the excel formulas in each cell rather than simply the numerical an
Overall Males Females
78.1 78.1 76
#NAME? #NAME? #NAME?
42.4 60.2 33.2
#NAME? #NAME? #NAME?
21.9 23.6 21.9
Max
3rd Q
Midpoint
1st Q Min
3 Location Measures: comparing Male and Female midpoints to the overall Salary data ra
For full credit, show the excel formulas in each cell rather than simply the numerical an
Using the entire Salary range and the M and F midpoints found in Q2 Male
0.67
0.7674
a. What would each midpoint’s percentile rank be in the overall range?
b. What is the normal curve z value for each midpoint within overall range?
4 Probability Measures: comparing Male and Female midpoints to the overall Salary data
0.34
0.22
For full credit, show the excel formulas in each cell rather than simply the numerical an Using the entire Salary range and the M and F midpoints found in Q2, find Male a. The Empirical Probability of equaling or exceeding (=>) that value for
b. The Normal curve Prob of => that value for each group
5 Conclusions: What do you make of these results? Be sure to include findings from this
In comparing the overall, male, and female outcomes, what relationship(s) see, to exist b
We observe that the male overall average compa-ratio is higher compared to the fem probabability and distribution results indicate that males and females do not assum that a huge proportion of females are comparatively lower in the range compared
t What does this suggest about our equal pay for equal work question?
Although we have not factored out equal work, the results suggest that males and fe in the company. In other words, there seem to be serious issues of gender pay gap in for further enquiry.
ly assignments will focus on females equally for doing equal work is to develop some ssue or not.
y variables.
U at the right. gether.
Place Excel outcome in Cell K19
Salary
Mean 45.33
Standard Error 2.74
Median 42.4
Mode 24
Standard Deviation 19.37
Sample Variance 375.24
Kurtosis -1.51
Skewness 0.22
Range 56.2
Minimum 21.9
Maximum 78.1
Sum 2266.7
Count 50
mula
nswer. ange.
nswer.
Female
0.37
-0.6264
Use Excel’s =PERCENTRANK.EXC function Use Excel’s =STANDARDIZE function
a range
nswer.
Female
0.64
0.73
Show the calculation formula = value/50 or =countif(range,">="&cell)/50 Use “=1-NORM.S.DIST” function
week’s lectures as well. between the data sets?
males. Additionally, the
me the same distribution and to the males.
emales are not equally paid
n the company which calls
Salary Gender1
35.2F
43F
22.7F 23.6F 23.8F 40.8F
25F
24F
68.7F 33.2F 34.7F
56.1F
24F
58.3F 21.9F 74.6F
23.1F
24F
24.1F 23.2F 35.7F
24.9F
76F
56.4F
69.7F
64.3M 27.5M 63.4M 46.6M 72.9M 78.1M
67M
38.1M 24.3M 75.7M 23.6M 41.8M 75.4M
47.7M
28M
61.3M 27.7M 58.9M 24.1M 46.4M
60.6M
60.2M 64.6M 60.4M
61.4M
Week 2: Identifying Significant Differences - part 1
To Ensure full credit for each question, you need to show how you got your results. This involves eithe or showing the excel formula in each cell. Be sure to copy the appropriate data columns from
As with our examination of compa-ratio in the lecture, the first question we have about salary between What we do, depends upon our findings.
1 As with the compa-ratio lecture example, we want to examine salary variation within the gro a What is the data input ranged used for this question:
Column X and Y
b Which is needed for this question: a one- or two-tail hypothesis statement
Answer:Two- Tail
Why:Since, in this case, we have to determine if there is
c Step 1: Ho:Both male and female salaries has equal varaince
Ha:Difference variance in salary
Step 2:Significance (Alpha): 0.05
Step 3:Test Statistic and test: F-Statistic
Why this test?This test is to determine the hypothesis of equality o
Step 4: Decision rule: Reject if p<0.05
Step 5:Conduct the test - place test function in cell k10
Step 6:Conclusion and Interpretation
What is the p-value: 0.93
What is your decision: REJ or NOT reject the null? Not Reject
Why?Since the result p -vale is grea
What is your conclusion about the variance in the population for male and female salaries?There found to be equal in ma
2 Once we know about variance quality, we can move on to means: Are male and female avera (Regardless of the outcome of the above F-test, assume equal variances for this test.)
a What is the data input ranged used for this question:
Coulumn X and Y
b Does this question need a one or two-tail hypothesis statement and test?
Why: Since we have to know if there is a difference
c Step 1: Ho:There is EQUAL means for male and female salarie
Ha:There is NO Equal means for male and female salar
Step 2:Significance (Alpha): 0.05
Step 3:Test Statistic and test: T-Statistics
Why this test?Since we want to compare means
Step 4: Decision rule: Reject if p<0.05
Step 5:Conduct the test - place test function in cell K35
Step 6:Conclusion and Interpretation
What is the p-value: 0.01
What is your decision: REJ or NOT reject the null?REJ
Why?Since the p-value result is less
What is your conclusion about the means in the population for male and female salaries?There found a significant diff
3 Education is often a factor in pay differences.
Do employees with an advanced degree (degree = 1) have higher average salaries?
Note: assume equal variance for the salaries in each degree for this question. a What is the data input ranged used for this question:
Columns AA and AB
b Does this question need a one or two-tail hypothesis statement and test?
Why: Let us see if people with an Advance degrees obtain a h
not just the different t-test: Two Sample Assuming Eqau
c Step 1: Ho:Average Salaries fo UG degrees => Average salarie
Ha:Average Salaries fo UG degrees < Average salaries
Step 2:Significance (Alpha): 0.05
Step 3:Test Statistic and test: T-Statistic
Why this test?To compare means
Step 4: Decision rule: Reject if p<0.05
Step 5:Conduct the test - place test function in cell K60
Step 6:Conclusion and Interpretation
What is the p-value: 0.29
Is the t value in the t-distribution tail indicated by the arrow in the Ha claim?NO (Ho claim)
What is your decision: REJ or NOT reject the null?REJ
Why?There is NO enough eveidenc
What is your conclusion about the impact of education on average salaries?We can conclude that there is
4 Considering both the compa-ratio information from the lectures and your salary information, It is clear that no significant impact of the education on the salary of an employee i.e people w degree do not have a higher average salary than people with UG degree but there was a signif difference found in the mean salary between Male and Female employees. Hence, we can say has an impact on the salary but not education. There is a significant deviation in “Equal Pay for Equa
Why - what statistical results support this conclusion?
Based on the result, we can conclude that equal pay for equal work.
The independent samples t-test assuming equal variances was performed for two groups - Ma Females to check the difference in mean salary and another independent samples t-test was p check the difference in salaries for people with an advanced degree and a UG degree.
er showing where the data you used is located
the data tab to the right for your use this week. the genders involves equality - are they the same or different?
oups - are they equal? Use Cell K10 for the Excel test outcome location.
F-Test: Two-Sample for Variances
Variable 1 Variable 2
Mean 51.98 37.75
Variance 310.13 322.42
Observations 25 25
df 24 24
F 0.96
P(F<=f) one-tail 0.46
F Critical one-tail 0.5
and test ? a difference
of male and female salaries. ater than 0.05.
ale and female salaries age salaries equal? Use Cell K35 for the Excel test outcome location.
t-Test: Two-Sample Assuming Equal Variances
Variable 1 Variable 2
es Mean 51.98 37.75 ries Variance 310.18 322.42
Observations 25 25
Pooled Variance 316.3 Hypothesized Mea 0 df 48 t Stat 2.83
P(T<=t) one-tail 0 t Critical one-tail 1.68 P(T<=t) two-tail 0.01
t Critical two-tail 2.01
s than 0.05 significance level ference between the two group of salaries
Use Cell K60 for the Excel test outcome location.
One-Tail higher degree average salary and t-Test: Two-Sample Assuming Equal Variances ul variances.
Variable 1 Variable 2
Mean 43.37 46.36
Variance 333.46 400.02
Observations 25 25
Pooled Variance 366.74
Hypothesized Mea 0
df 48
t Stat -0.55
P(T<=t) one-tail 0.29
t Critical one-tail 1.68
P(T<=t) two-tail 0.58
t Critical two-tail 2.01
es for Grad degrees
s for Grad degrees ces to reject the Null Hypothesis s NO significant difference between those people with UG and Grad degrees.
, what conclusions can you reach about equal pay for equal work? with advanced ificant y that gender
al work”.
ale and performed to
