managerial statistics

profileladdka1
asssignment_4_bus.xlsm
Home>Business & Finance homework help>Management homework help>managerial statistics

Data

ID Salary Compa Midpoint Age Performance Rating Service Gender Raise Degree Gender1 Gr
1 64.4 1.130 57 34 85 8 0 5.7 0 M E 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.4 0.884 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 35.6 1.148 31 30 75 5 1 3.6 1 F B
4 62.3 1.093 57 42 100 16 0 5.5 1 M E The column labels in the table mean:
5 48.7 1.014 48 36 90 16 0 5.7 1 M D ID – Employee sample number Salary – Salary in thousands
6 75 1.120 67 36 70 12 0 4.5 1 M F Age – Age in years Performance Rating - Appraisal rating (employee evaluation score)
7 41.8 1.046 40 32 100 8 1 5.7 1 F C Service – Years of service (rounded) Gender – 0 = male, 1 = female
8 23.7 1.029 23 32 90 9 1 5.8 1 F A Midpoint – salary grade midpoint Raise – percent of last raise
9 76.8 1.147 67 49 100 10 0 4 1 M F Grade – job/pay grade Degree (0= BS\BA 1 = MS)
10 24 1.044 23 30 80 7 1 4.7 1 F A Gender1 (Male or Female) Compa - salary divided by midpoint
11 23.9 1.041 23 41 100 19 1 4.8 1 F A
12 62.5 1.096 57 52 95 22 0 4.5 0 M E
13 42.7 1.067 40 30 100 2 1 4.7 0 F C
14 23.4 1.016 23 32 90 12 1 6 1 F A
15 23.3 1.012 23 32 80 8 1 4.9 1 F A
16 48.7 1.217 40 44 90 4 0 5.7 0 M C
17 65.1 1.142 57 27 55 3 1 3 1 F E
18 34.6 1.116 31 31 80 11 1 5.6 0 F B
19 24.5 1.065 23 32 85 1 0 4.6 1 M A
20 34.9 1.125 31 44 70 16 1 4.8 0 F B
21 76.5 1.142 67 43 95 13 0 6.3 1 M F
22 57.8 1.204 48 48 65 6 1 3.8 1 F D
23 22.7 0.987 23 36 65 6 1 3.3 0 F A
24 56.5 1.177 48 30 75 9 1 3.8 0 F D
25 24.5 1.064 23 41 70 4 0 4 0 M A
26 23.2 1.010 23 22 95 2 1 6.2 0 F A
27 46.8 1.171 40 35 80 7 0 3.9 1 M C
28 76.6 1.144 67 44 95 9 1 4.4 0 F F
29 75.9 1.133 67 52 95 5 0 5.4 0 M F
30 47.4 0.987 48 45 90 18 0 4.3 0 M D
31 25.3 1.101 23 29 60 4 1 3.9 1 F A
32 27.2 0.878 31 25 95 4 0 5.6 0 M B
33 66 1.158 57 35 90 9 0 5.5 1 M E
34 28.1 0.907 31 26 80 2 0 4.9 1 M B
35 22.5 0.980 23 23 90 4 1 5.3 0 F A
36 22.7 0.985 23 27 75 3 1 4.3 0 F A
37 23.4 1.017 23 22 95 2 1 6.2 0 F A
38 58.5 1.026 57 45 95 11 0 4.5 0 M E
39 35.5 1.144 31 27 90 6 1 5.5 0 F B
40 24.8 1.078 23 24 90 2 0 6.3 0 M A
41 45.8 1.144 40 25 80 5 0 4.3 0 M C
42 22.2 0.965 23 32 100 8 1 5.7 1 F A
43 77.4 1.155 67 42 95 20 1 5.5 0 F F
44 58.8 1.032 57 45 90 16 0 5.2 1 M E
45 51.2 1.066 48 36 95 8 1 5.2 1 F D
46 61.3 1.076 57 39 75 20 0 3.9 1 M E
47 64.3 1.128 57 37 95 5 0 5.5 1 M E
48 67.6 1.186 57 34 90 11 1 5.3 1 F E
49 61.3 1.075 57 41 95 21 0 6.6 0 M E
50 66.1 1.159 57 38 80 12 0 4.6 0 M E

questions to answer

Score: Week 4 Confidence Intervals and Chi Square (Chs 11 - 12)
For questions 3 and 4 below, be sure to list the null and alternate hypothesis statements. Use .05 for your significance level in making your decisions.
For full credit, you need to also show the statistical outcomes - either the Excel test result or the calculations you performed.
<1 point> 1 Using our sample data, construct a 95% confidence interval for the population's mean salary for each gender.
Interpret the results. How do they compare with the findings in the week 2 one sample t-test outcomes (Question 1)?
Mean St error t value Low to High
Males
Females
<Reminder: standard error is the sample standard deviation divided by the square root of the sample size.>
Interpretation:
<1 point> 2 Using our sample data, construct a 95% confidence interval for the mean salary difference between the genders in the population.
How does this compare to the findings in week 2, question 2?
Difference St Err. T value Low to High
Yes/No
Can the means be equal? Why?
How does this compare to the week 2, question 2 result (2 sampe t-test)?
a. Why is using a two sample tool (t-test, confidence interval) a better choice than using 2 one-sample techniques when comparing two samples?
<1 point> 3 We found last week that the degree values within the population do not impact compa rates.
This does not mean that degrees are distributed evenly across the grades and genders.
Do males and females have athe same distribution of degrees by grade?
(Note: while technically the sample size might not be large enough to perform this test, ignore this limitation for this exercise.)
What are the hypothesis statements:
Ho:
Ha:
Note: You can either use the Excel Chi-related functions or do the calculations manually.
Data input tables - graduate degrees by gender and grade level
OBSERVED A B C D E F Total If desired, you can do manual calculations per cell here.
M Grad A B C D E F
Fem Grad M Grad
Male Und Fem Grad
Female Und Male Und
Female Und
Sum =
EXPECTED
M Grad For this exercise - ignore the requirement for a correction factor
Fem Grad for cells with expected values less than 5.
Male Und
Female Und
Interpretation:
What is the value of the chi square statistic:
What is the p-value associated with this value:
Is the p-value <0.05?
Do you reject or not reject the null hypothesis:
If you rejected the null, what is the Cramer's V correlation:
What does this correlation mean?
What does this decision mean for our equal pay question:
<1 point> 4 Based on our sample data, can we conclude that males and females are distributed across grades in a similar pattern
within the population?
What are the hypothesis statements:
Ho:
Ha:
Do manual calculations per cell here (if desired)
A B C D E F A B C D E F
OBS COUNT - m M
OBS COUNT - f F
Sum =
EXPECTED
What is the value of the chi square statistic:
What is the p-value associated with this value:
Is the p-value <0.05?
Do you reject or not reject the null hypothesis:
If you rejected the null, what is the Phi correlation:
What does this correlation mean?
What does this decision mean for our equal pay question:
<2 points> 5.      How do you interpret these results in light of our question about equal pay for equal work?

Sheet1

Sal Compa G Mid Age EES SR G Raise Deg SUMMARY OUTPUT SUMMARY OUTPUT
24 1.045 1 23 32 90 9 1 5.8 1
24.2 1.053 1 23 30 80 7 1 4.7 1 Regression Statistics Regression Statistics
23.4 1.018 1 23 41 100 19 1 4.8 1 Multiple R 0.7050179484 Multiple R 0.9931286935
23.4 1.017 1 23 32 90 12 1 6 1 R Square 0.4970503076 R Square 0.9863046018
22.6 0.983 1 23 32 80 8 1 4.9 1 Adjusted R Square 0.4132253589 Adjusted R Square 0.9840220355
22.9 0.995 1 23 36 65 6 1 3.3 0 Standard Error 0.0561252686 Standard Error 2.4352822665
23.1 1.003 1 23 22 95 2 1 6.2 0 Observations 50 Observations 50
23.3 1.011 1 23 29 60 4 1 3.9 1
22.7 0.985 1 23 23 90 4 1 5.3 0 ANOVA ANOVA
23.5 1.023 1 23 27 75 3 1 4.3 0 df SS MS F Significance F df SS MS F Significance F
23 1.002 1 23 22 95 2 1 6.2 0 Regression 7 0.1307500775 0.0186785825 5.9296225662 0.0000782906 Regression 7 17938.424611863 2562.632087409 432.1033638177 5.29906273684337E-37
24 1.042 1 23 32 100 8 1 5.7 1 Residual 42 0.1323019225 0.0031500458 Residual 42 249.085188137 5.9305997175
35.5 1.145 1 31 30 75 5 1 3.6 1 Total 49 0.263052 Total 49 18187.5098
34.7 1.119 1 31 31 80 11 1 5.6 0
35.5 1.146 1 31 44 70 16 1 4.8 0 Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0% Coefficients Standard Error t Stat P-value Lower 95% Upper 95% Lower 95.0% Upper 95.0%
35.2 1.136 1 31 27 90 6 1 5.5 0 Intercept 0.9486238772 0.0817167716 11.6086803119 0 0.7837127557 1.1135349987 0.7837127557 1.1135349987 Intercept -4.8714544587 3.54570071 -1.3739045839 0.1767599037 -12.0269681853 2.2840592678 -12.0269681853 2.2840592678
40.4 1.01 1 40 32 100 8 1 5.7 1 Mid 0.0034995027 0.0006492568 5.3900133356 0.0000029767 0.0021892495 0.0048097559 0.0021892495 0.0048097559 Mid 1.2284155048 0.0281713308 43.6051641629 1.32019333894083E-36 1.1715634576 1.2852675521 1.1715634576 1.2852675521
42.7 1.068 1 40 30 100 2 1 4.7 0 Age 0.0005527738 0.0014459446 0.3822925256 0.7041721007 -0.0023652605 0.0034708081 -0.0023652605 0.0034708081 Age 0.0368279425 0.0627397124 0.5869957178 0.5603489282 -0.0897859231 0.1634418081 -0.0897859231 0.1634418081
53.4 1.112 1 48 48 65 6 1 3.8 1 EES -0.0018462553 0.0010252155 -1.8008461371 0.0789105539 -0.0039152239 0.0002227133 -0.0039152239 0.0002227133 EES -0.0821579785 0.0444842245 -1.8469014451 0.0718147225 -0.171930778 0.007614821 -0.171930778 0.007614821
51.5 1.072 1 48 30 75 9 1 3.8 0 SR -0.0004182288 0.0018278101 -0.2288141345 0.820123898 -0.004106899 0.0032704414 -0.004106899 0.0032704414 SR -0.0778484529 0.079308905 -0.9815852701 0.3319249969 -0.2379003029 0.0822033971 -0.2379003029 0.0822033971
49.8 1.037 1 48 36 95 8 1 5.2 1 G 0.0646649961 0.0183396697 3.5259629624 0.001034866 0.0276540443 0.101675948 0.0276540443 0.101675948 G 2.9145083112 0.7957605113 3.6625445343 0.000693549 1.3085985836 4.5204180389 1.3085985836 4.5204180389
68.3 1.198 1 57 27 55 3 1 3 1 Raise 0.0146549564 0.0139088976 1.0536389608 0.2980722322 -0.0134143354 0.0427242483 -0.0134143354 0.0427242483 Raise 0.6763294824 0.6035087689 1.1206622295 0.2687988764 -0.5416005215 1.8942594864 -0.5416005215 1.8942594864
65.4 1.148 1 57 34 90 11 1 5.3 1 Deg 0.0014675988 0.0161098249 0.0910996125 0.9278465471 -0.0310433441 0.0339785418 -0.0310433441 0.0339785418 Deg 0.0345044482 0.6990072742 0.0493620731 0.9608647532 -1.3761493419 1.4451582383 -1.3761493419 1.4451582383
78.4 1.17 1 67 44 95 9 1 4.4 0
75.9 1.133 1 67 42 95 20 1 5.5 0
24 1.044 0 23 32 85 1 0 4.6 1
23.3 1.012 0 23 41 70 4 0 4 0
24.1 1.049 0 23 24 90 2 0 6.3 0
27.5 0.887 0 31 52 80 7 0 3.9 0 t-Test: Two-Sample Assuming Equal Variances
27.1 0.875 0 31 25 95 4 0 5.6 0
27.7 0.895 0 31 26 80 2 0 4.9 1 Variable 1 Variable 2
40.8 1.019 0 40 44 90 4 0 5.7 0 Mean 1.06684 1.04836
43.9 1.097 0 40 35 80 7 0 3.9 1 Variance 0.00430164 0.00648099
41 1.025 0 40 25 80 5 0 4.3 0 Observations 25 25
48.7 1.014 0 48 36 90 16 0 5.7 1 Pooled Variance 0.005391315
49.4 1.029 0 48 45 90 18 0 4.3 0 Hypothesized Mean Difference 0
64.4 1.13 0 57 34 85 8 0 5.7 0 df 48
64.5 1.132 0 57 42 100 16 0 5.5 1 t Stat 0.8898352784
58.9 1.033 0 57 52 95 22 0 4.5 0 P(T<=t) one-tail 0.188996287
57.9 1.016 0 57 35 90 9 0 5.5 1 t Critical one-tail 1.6772241961
59 1.035 0 57 45 95 11 0 4.5 0 P(T<=t) two-tail 0.3779925741
63.3 1.111 0 57 45 90 16 0 5.2 1 t Critical two-tail 2.0106347576
56.8 0.996 0 57 39 75 20 0 3.9 1
58 1.017 0 57 37 95 5 0 5.5 1
62.4 1.094 0 57 41 95 21 0 6.6 0
63.8 1.12 0 57 38 80 12 0 4.6 0
79 1.179 0 67 36 70 12 0 4.5 1
77 1.149 0 67 49 100 10 0 4 1
74.8 1.116 0 67 43 95 13 0 6.3 1
76 1.135 0 67 52 95 5 0 5.4 0