complete WEEK 4 TAB ONLY of the Statistics Excel Spreadsheet . Please show formulas used to get the answers, or it will not count. There is data sheet on the spreadsheet to be used as well.
2019_10_canvas_bus_308_oct_student_worksheet__week4.xlsx
Unformatted Attachment Preview
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
Salary Compa- Midpoint
ratio
65.3
27
34
64.6
47
73.6
41.9
23.7
75.8
23
24.1
61.8
42
23.2
24.8
43
64.1
35.4
24.2
34.7
73.4
55.8
23.5
55.3
24.1
23.9
38.9
76.3
76.8
46.8
23.3
27.7
56.6
28.3
23.7
23.2
21.6
62.1
33.4
24.6
1.146
0.870
1.098
1.134
0.979
1.099
1.047
1.031
1.132
1.002
1.049
1.085
1.049
1.010
1.080
1.074
1.125
1.142
1.054
1.120
1.095
1.162
1.023
1.153
1.047
1.041
0.972
1.139
1.147
0.976
1.011
0.892
0.993
0.914
1.029
1.008
0.941
1.090
1.076
1.068
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
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
Performance Service Gender
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
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
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
Raise Degree Gender1
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
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
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
41
42
43
44
45
46
47
48
49
50
42.1
24.1
74.1
68.4
58
64.8
64.9
67.3
60.5
66.7
1.052
1.046
1.106
1.200
1.208
1.137
1.139
1.181
1.061
1.171
40
23
67
57
48
57
57
57
57
57
25
32
42
45
36
39
37
34
41
38
80
100
95
90
95
75
95
90
95
80
5
8
20
16
8
20
5
11
21
12
0
1
1
0
1
0
0
1
0
0
4.3
5.7
5.5
5.2
5.2
3.9
5.5
5.3
6.6
4.6
0
1
0
1
1
1
1
1
0
0
M
F
F
M
F
M
M
F
M
M
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
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 and females paid the same
Note: to simplfy the analysis, we will assume that jobs within each grade comprise equal work.
The column labels in the table mean:
ID – Employee sample number Salary – Salary in thousands
Age – Age in years
Performance Rating – Appraisal rating (employee evaluation scor
Service – Years of service (rounded)
Gender – 0 = male, 1 = female
Midpoint – salary grade midpointRaise – percent of last raise
Grade – job/pay grade
Degree (0= BSBA 1 = MS)
Gender1 (Male or Female)
Compa-ratio – salary divided by midpoint
C
A
F
E
D
E
E
E
E
E
Column1
Mean
45.268
Standard Error 2.74641501
Median
42.05
Mode
24.1
Standard Deviation
19.4200868
Sample Variance
377.139771
Kurtosis
-1.5525893
Skewness
0.21278236
Range
55.2
Minimum
21.6
Maximum
76.8
Sum
2263.4
Count
50
Week 1: Descriptive Statistics, including Probability
While the lectures will examine our equal pay question from the compa-ratio viewpoint, our weekly assignments will
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 females equally for
descriptive statistics to give us something to make a preliminary decision on whether we have an issue or not.
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 variables.
Suggestion: Copy the gender1 and salary columns from the Data tab to columns T and U at the right.
Then use Data Sort (by gender1) to get all the male and female salary values grouped together.
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.
Using Fx (or formula) functions find the following (be sure to show the formula
and not just the value in each cell) asked for salary statistics for each gender:
Male
Female
Mean: 52.36
38.176
Sample Standard Deviation: 18.2882 18.1865
Range: 52.7
54.7
b.
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 answer.
Overall Males
Females
Max
76.8
76.8
76.3
3rd Q
64.475
65.3
55.3
Midpoint
42.05
60.5
33.4
1st Q
24.175
33.6
23.7
Min
21.6
24.1
21.6
3
Location Measures: comparing Male and Female midpoints to the overall Salary data range.
For full credit, show the excel formulas in each cell rather than simply the numerical answer.
Using the entire Salary range and the M and F midpoints found in Q2
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 range
For full credit, show the excel formulas in each cell rather than simply the numerical answer.
Using the entire Salary range and the M and F midpoints found in Q2, find
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 thi
In comparing the overall, male, and female outcomes, what relationship(s) see, to exist between the data se
There is definitely a pay gap betweem the men and women, salaries for males are definitely higher for men
What does this suggest about our equal pay for equal work question?
This data shows that there is not equal pay for equal worl. Men are paid much higher.
Place Excel outcome in Cell K19
Column1
Mean
Standard Error
Median
Mode
45.268
2.746415
42.05
24.1
Standard Deviation
19.42009
Sample Variance
377.1398
Kurtosis
-1.55259
Skewness
0.212782
Range
55.2
Minimum
21.6
Maximum
Sum
Count
76.8
2263.4
50
Male
0.666
Female
0.372
0.7843
-0.6111
Male
0.34
0.216419575419116
Be sure to include findings from this week’s lectures as well.
(s) see, to exist between the data sets?
males are definitely higher for men than women.
Use Excel’s =PERCENTRANK.EXC function
Use Excel’s =STANDARDIZE function
Female
0.64 Show the calculation formula = value/50 or =c
0.729439871141919 Use “=1-NORM.S.DIST” function
Gender1 Salary
F
34
F
41.9
F
23.7
F
23
F
24.1
F
42
F
23.2
F
24.8
F
64.1
F
35.4
F
34.7
F
55.8
F
23.5
F
55.3
F
23.9
F
76.3
F
23.3
F
23.7
F
23.2
F
21.6
F
33.4
F
24.1
F
74.1
F
58
F
67.3
M
65.3
M
27
M
64.6
M
47
M
73.6
M
75.8
M
61.8
M
43
M
24.2
M
73.4
M
24.1
M
38.9
M
76.8
M
46.8
M
27.7
M
56.6
M
28.3
M
62.1
M
M
M
M
M
M
M
24.6
42.1
68.4
64.8
64.9
60.5
66.7
F-Test Two-Sample for Variances
Variable 1 Variable 2
Mean
52.36
38.176
Variance
334.4592 330.7502
Observations
25
25
df
24
24
F
1.011214
P(F<=f) one-tail 0.489217 F Critical one-tail 1.98376 t-Test: Two-Sample Assuming Equal Variances Variable 1 Variable 2 Mean 52.36 38.176 Variance 334.4592 330.7502 Observations 25 25 Pooled Variance 332.6047 Hypothesized Mean Difference 0 df 48 t Stat 2.749727 P(T<=t) one-tail 0.004192 t Critical one-tail 1.677224 P(T<=t) two-tail 0.008384 t Critical two-tail 2.010635 t-Test: Two-Sample Assuming Unequal Variances Variable 1 Variable 2 Mean 43.824 46.712 Variance 367.7002 397.9494 Observations 25 25 Hypothesized Mean Difference 0 df 48 t Stat -0.52186 P(T<=t) one-tail 0.302085 t Critical one-tail 1.677224 P(T<=t) two-tail 0.604169 t Critical two-tail 2.010635 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 either showing wh or showing the excel formula in each cell. Be sure to copy the appropriate data columns from the data tab to As with our examination of compa-ratio in the lecture, the first question we have about salary between the genders in What we do, depends upon our findings. 1 As with the compa-ratio lecture example, we want to examine salary variation within the groups - are they a What is the data input ranged used for this question: Columns X and Y b c. Which is needed for this question: a one- or two-tail hypothesis statement and test ? Answer: two-tail test Why: we are not testing if one group is larger than the other, just that they are Step 1: Step 2: Step 3: Step 4: Step 5: Ho: Male and female have equal salary variances Ha: Male and female have different salary variances Significance (Alpha): 0.05 Test Statistic and test: F test, F statistic Why this test? The F test is the type of test to check variances. Decision rule: we will reject the null hypothesis if we get a P value les than 0.05 Conduct the test - place test function in cell k10 Step 6: Conclusion and Interpretation What is the p-value: 0.978434266 What is your decision: REJ or NOT reject the null? Not reject Why? the P value is greater than 0.05 What is your conclusion about the variance in the The group has equal variances in salaries population for male and female salaries? 2 Once we know about variance quality, we can move on to means: Are male and female average salaries eq (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: the mea salaries male vs. females. b c. Step 1: Step 2: Step 3: Step 4: Step 5: Does this question need a one or two-tail hypothesis statement and test? Why: T-test because we are just testing for equality Ho: male and female salry means are equal Ha: The male and female mean alaries are not equal Significance (Alpha): 0.05 Test Statistic and test: T-test because we are just testing for equality Why this test? Because we are testing the differences between 2 groups Decision rule: we will reject the null hypothesis if we get a P value les than 0.05 Conduct the test - place test function in cell K35 Step 6: Conclusion and Interpretation What is the p-value: 0.008383809 What is your decision: REJ or NOT reject the null? We can reject the null because it meets the decision Why? The p value is less than 0.05 What is your conclusion about the means in the population for male and female salaries? There is a significant difference between male and f 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: salary and degree means b c. Step 1: Step 2: Step 3: Step 4: Step 5: Does this question need a one or two-tail hypothesis statement and test? Why: One tail test, because we are testing if employes with a 1 degree have higher avera Ho: Average salaries for undergrad degree is greater or equal to gradu Ha: average salaries for undergrad degree is less than graduate degree Significance (Alpha): 0.05 Test Statistic and test: one tail t-test Why this test? we are comparing means. Decision rule: The p value is less than 0.05 Conduct the test - place test function in cell K60 Step 6: Conclusion and Interpretation What is the p-value: 0.30208466 Is the t value in the t-distribution tail indicated by the arrow in the Ha claim? What is your decision: REJ or NOT reject the null? We cannot reject the null hypothesis Why? the p value is greater than 0.05 What is your conclusion about the impact of education on average salaries? There is no significant different with people who ha 4 Considering both the compa-ratio information from the lectures and your salary information, what conclus Education or degrees does not seem to be a factor when deciding salaries Why - what statistical results support this conclusion? Calculating the test and using the P value to reject or accept the null hypothisis helps us come to this concl olves either showing where the data you used is located mns from the data tab to the right for your use this week. between the genders involves equality - are they the same or different? Use Cell K10 for the Excel test outcome location. F-Test Two-Sample for Variances Variable 1 Variable 2 Mean Variance 52.36 38.176 334.4592 330.7502 Observations 25 25 df 24 24 F 1.011214 P(F<=f) one-tail 0.489217 F Critical one-tail 1.98376 Use Cell K35 for the Excel test outcome location. t-Test: Two-Sample Assuming Equal Variances Variable 1 Variable 2 Mean Variance Observations Pooled Variance 52.36 334.4592 330.7502 25 332.6047 Hypothesized Mean Difference 0 df t Stat 38.176 48 2.749727 25 se it meets the decision rule P(T<=t) one-tail 0.004192 t Critical one-tail 1.677224 P(T<=t) two-tail 0.008384 t Critical two-tail 2.010635 nce between male and female mean salaries Use Cell K60 for the Excel test outcome location. egree have higher average salary greater or equal to graduate degree ess than graduate degree t-Test: Two-Sample Assuming Unequal Variances Variable 1 Variable 2 Mean Variance 43.824 367.7002 397.9494 Observations 25 Hypothesized Mean Difference 0 df 48 t Stat -0.52186 P(T<=t) one-tail 0.302085 t Critical one-tail 1.677224 P(T<=t) two-tail 0.604169 t Critical two-tail 2.010635 ent with people who have advanced degrees to undergraduate degrees ormation, what conclusions can you reach about equal pay for equal work? ps us come to this conclusion 46.712 25 Salary Gender1 34 F 41.9 F 23.7 F 23 F 24.1 F 42 F 23.2 F 24.8 F 64.1 F 35.4 F 34.7 F 55.8 F 23.5 F 55.3 F 23.9 F 76.3 F 23.3 F 23.7 F 23.2 F 21.6 F 33.4 F 24.1 F 74.1 F 58 F 67.3 F 65.3 M 27 M 64.6 M 47 M 73.6 M 75.8 M 61.8 M 43 M 24.2 M 73.4 M 24.1 M 38.9 M 76.8 M 46.8 M 27.7 M 56.6 M 28.3 M 62.1 M 24.6 42.1 68.4 64.8 64.9 60.5 66.7 M M M M M M M Salary Degree 65.3 0 27 0 61.8 0 42 0 43 0 35.4 0 34.7 0 23.5 0 55.3 0 24.1 0 23.9 0 76.3 0 76.8 0 46.8 0 27.7 0 23.7 0 23.2 0 21.6 0 62.1 0 33.4 0 24.6 0 42.1 0 74.1 0 60.5 0 66.7 0 34 1 64.6 1 47 1 73.6 1 41.9 1 23.7 1 75.8 1 23 1 24.1 1 23.2 1 24.8 1 64.1 1 24.2 1 73.4 1 55.8 1 38.9 1 23.3 1 56.6 1 28.3 24.1 68.4 58 64.8 64.9 67.3 1 1 1 1 1 1 1 Anova: Single Factor SUMMARY Groups Count A 15 B 7 C 5 D 5 E 12 F 6 ANOVA Source of Variation SS Between Groups 18142.46 Within Groups 337.3918 Total 18479.85 Sum Average 355 23.66667 220.5 31.5 207.9 41.58 262.9 52.58 767.1 63.925 450 75 df Variance 0.598095 13.37333 2.437 27.922 10.58568 2.18 MS F P-value 5 3628.491 473.1994 4.64E-37 44 7.667996 49 F crit 2.42704 Week 3: Identifying Significant Differences - part 2 To Ensure full credit for each question, you need to show how you got your results. This involves either showing wh or showing the excel formula in each cell. Be sure to copy the appropriate data columns from 1 A good pay program will have different average salaries by grade. Is this the case for our company? a What is the data input ranged used for this question: Note: assume equal variances for each grade, even though this may not be accurate, for purposes of this question. b. Step 1: Step 2: Step 3: Step 4: Step 5: Ho: all salary means are equal Ha: atleast one salary mean is different fom the rest Significance (Alpha): alpha Test Statistic and test: ANOVA TES test Why this test? Because we comparing multiple groups Decision rule: p<.05 we will reject the null hypothesis Conduct the test - place test function in cell K08 Step 6: Conclusion and Interpretation What is the p-value: What is your decision: REJ or NOT reject the null? Why? What is your conclusion about the means in the population for grade salaries? 2 4.64E-37 reject it goes against the rule p<.05 there is atleast one mean salary difference If the null hypothesis in question 1 was rejected, which pairs of means differ? (Use the values from the ANOVA table to complete the follow table.) Groups Compared Mean Diff. T value used +/- Term Low A-B 7.833333333 2.015 2.55407 5.27926398 A-C 17.91333333 2.015 2.97006 14.943276 A-D 28.91333333 2.015 2.88138 26.0319545 A-E 40.25833333 2.015 2.16103 37.5630502 A-F 51.33333333 2.015 2.69528 51.3333333 B-C B-D B-E B-F 10.08 21.08 32.425 43.5 2.015 2.015 2.015 2.015 3.26718 3.26718 2.65371 3.1043 6.8128235 17.8128235 29.7712919 40.3957047 C-D C-E C-F 11 22.345 33.42 2.015 2.015 2.015 3.52895 2.97006 3.37872 7.47104605 19.3749427 30.0412838 3 D-E D-F 11.345 22.42 2.015 2.015 2.97006 3.37872 8.37494268 19.0412838 E-F 11.075 2.015 2.78988 8.28511694 One issue in salary is the grade an employee is in - higher grades have higher salaries. This suggests that one question to ask is if males and females are distributed in a similar pattern across the a What is the data input ranged used for this question: b. Step 1: Step 2: Step 3: Step 4: Step 5: Ho: MALES AND FEMALES ARE DISTRIBUTED EVENLY ACROSS GRA Ha: MALES AND FEMALES ARE NOT DISTRIBUTED EVENLY ACROSS Significance (Alpha): 0.05 Test Statistic and test: CHI-SQUARE TEST Why this test? Decision rule: P<0.05 Conduct the test - place test function in cell K54 Step 6: Conclusion and Interpretation What is the p-value: 0.03558 What is your decision: REJ or NOT reject the null? REJECT Why? the males and females are not distribted evenly th the males and females are not distribted evenly th What is your conclusion about the means in the population for male and female salaries? 4 What implications do this week's analysis have for our equal pay question? Why - what statistical results support this conclusion? This involves either showing where the data you used is located he appropriate data columns from the data tab to the right for your use this week. he case for our company? yes Use Cell K08 for the Excel test outcome location. ate, for purposes of this question. Anova: Single Factor SUMMARY Groups Count Sum Average A 15 B 7 220.5 C 5 207.9 D 5 262.9 E 12 767.1 F 6 450 Variance 355 23.66667 0.598095 31.5 13.37333 41.58 2.437 52.58 27.922 63.925 10.58568 75 2.18 ANOVA Source of Variation SS Between Groups 18142.46 ary difference to High 10.3874 20.88339 31.79471 42.41937 54.02862 df MS F 5 3628.491 473.1994 Within Groups 337.3918 44 7.667996 Total 18479.85 49 Difference Significant? Why? YES INTERVAL DOES NOT CONTAIN 0 YES YES YES 13.34718 24.34718 35.07871 46.6043 YES YES YES YES 14.52895 25.31506 36.79872 YES YES YES 14.31506 25.79872 YES YES 13.86488 YES d in a similar pattern across the salary grades? Use Cell K54 for the Excel test outcome location. 0.035579215 UTED EVENLY ACROSS GRADES RIBUTED EVENLY ACROSS GRADES Place the actual distribution in the table below. A B C D Male 3 3 3 2 Female 12 4 2 3 15 7 5 5 Place the expected distribution in the table below. A B C D Male 7.5 3.5 2.5 2.5 males are not distribted evenly throughout Female 7.5 3.5 2.5 2.5 males are not distribted evenly throughout the grades Data Input Table: Group name: List salaries within each grade P-value 4.63505E-37 F crit 2.42704012 E 10 2 12 F 4 2 6 E 6 6 F 3 3 25 25 50 A 23.7 23 24.1 23.2 24.8 24.2 23.5 24.1 23.9 23.3 23.7 23.2 21.6 24.6 24.1 B 27 34 35.4 34.7 27.7 28.3 33.4 Salary Range Groups C D 41.9 47 42 55.8 43 55.3 38.9 46.8 42.1 58 E 65.3 64.6 61.8 64.1 56.6 62.1 68.4 64.8 64.9 67.3 60.5 66.7 F 73.6 75.8 73.4 76.3 76.8 74.1 Salary Grade 23.7 A 23 A 24.1 A 23.2 A 24.8 A 24.2 A 23.5 A 24.1 A 23.9 A 23.3 A 23.7 A 23.2 A 21.6 A 24.6 A 24.1 A 27 B 34 B 35.4 B 34.7 B 27.7 B 28.3 B 33.4 B 41.9 C 42 C 43 C 38.9 C 42.1 C 47 D 55.8 D 55.3 46.8 58 65.3 64.6 61.8 64.1 56.6 62.1 68.4 64.8 64.9 67.3 60.5 D D D E E E E E E E E E E E 66.7 73.6 75.8 73.4 76.3 76.8 74.1 E F F F F F F Gender1 Grade F A F A F A F A F A F A F A F A F A F A ... Purchase answer to see full attachment

We can help you complete this assignment or another one similar to this. Just hit "Order Now" to get started!

error: Content is protected !!