Descriptive Statistics, including Probability

Anonymous
timer Asked: Oct 20th, 2018
account_balance_wallet $9.99

Question Description

In the attached file complete the problems, and submit your work in an Excel document. Be sure to show all of your work and clearly label all calculations.

Unformatted Attachment Preview

ID Salary Comparatio Midpoint Age Performance Rating Service Gender Raise Degree 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 57.6 28.9 36.1 56.1 47.9 76 41.6 23.4 76.4 24.5 23.5 67.6 41.3 24.4 21.6 42.8 66.7 35.8 25.4 34.7 74.4 56.4 24 55.7 23.3 23.1 48.4 75.8 76.6 47.7 23.2 28 61 28 22.2 23.6 23.9 59.9 33.5 1.010 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 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 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 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 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 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 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.933 1.166 0.985 0.998 1.134 1.039 1.017 1.140 1.064 1.023 1.186 1.032 1.060 0.939 1.070 1.171 1.154 1.105 1.120 1.111 1.175 1.042 1.160 1.013 1.004 1.210 1.131 1.143 0.993 1.009 0.904 1.071 0.902 0.964 1.028 1.040 1.050 1.080 40 41 42 43 44 45 46 47 48 49 50 23.7 38.2 23 75.2 52.7 56.3 60.6 60.1 67.1 55.5 65 1.029 0.956 1.001 1.122 0.925 1.172 1.064 1.055 1.178 0.974 1.141 23 40 23 67 57 48 57 57 57 57 57 24 25 32 42 45 36 39 37 34 41 38 90 80 100 95 90 95 75 95 90 95 80 2 5 8 20 16 8 20 5 11 21 12 0 0 1 1 0 1 0 0 1 0 0 6.3 4.3 5.7 5.5 5.2 5.2 3.9 5.5 5.3 6.6 4.6 0 0 1 0 1 1 1 1 1 0 0 Gender1 Grade 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 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 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 p Note: to simplfy the analysis, we will assume that jobs within each grade comprise equal wor The column labels in the table mean: ID – Employee sample number Salary – Salary in thousands Age – Age in years Performance Rating - Appraisal rating (employe Service – Years of service (rounded)Gender – 0 = male, 1 = female Midpoint – salary grade midpoint Raise – percent of last raise Grade – job/pay grade Degree (0= BS\BA 1 = MS) Gender1 (Male or Female) Compa-ratio - salary divided by midpoint M M F F M F M M F M M A C A F E D E E E E E 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: Sample Standard Deviation: Range: b. 2 Develop a 5-number summary for the overall, male, and female SALARY variable. For full credit, use the excel formulas in each cell rather than simply the numerical answer. Overall Males Females Max 3rd Q Midpoint 1st Q Min 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 Note: be sure to use the ENTIRE salary range for part a when finding the probability. 5 Conclusions: What do you make of these results? Be sure to include findings from this week's lectu In comparing the overall, male, and female outcomes, what relationship(s) see, to exist between the data s Your findings: The lecture's related findings: Overall conclusion: What does this suggest about our equal pay for equal work question? t, our weekly assignments will focus on males and females equally for doing equal work is to develop some Place Excel outcome in Cell K19 Salary data range. numerical answer. Male all Salary data range numerical answer. Male Female Use Excel's =PERCENTRANK.EXC function Use Excel's =STANDARDIZE function Female Show the calculation formula = value/50 or =countif(range,">="&cell)/50 Use "=1-NORM.S.DIST" function findings from this week's lectures as well. see, to exist between the data sets? ...
Purchase answer to see full attachment

Tutor Answer

uoscar
School: UCLA

This is the progress of your work so far.I'll need you lecture note to complete the portion of the excel file that asks to summarise the lecture findings.Please, give me feedback.

ID

Salary

Comparatio

Midpoint

Age

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

57.6
28.9
36.1
56.1
47.9
76
41.6
23.4
76.4
24.5
23.5
67.6
41.3
24.4
21.6
42.8
66.7
35.8
25.4
34.7
74.4
56.4
24
55.7
23.3
23.1
48.4
75.8
76.6
47.7
23.2
28
61
28
22.2
23.6
23.9
59.9
33.5

1.010

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

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

0.933
1.166
0.985
0.998
1.134
1.039
1.017
1.140
1.064
1.023
1.186
1.032
1.060
0.939
1.070
1.171
1.154
1.105
1.120
1.111
1.175
1.042
1.160
1.013
1.004
1.210
1.131
1.143
0.993
1.009
0.904
1.071
0.902
0.964
1.028
1.040
1.050
1.080

Performance Service
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

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

Gender

Raise

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

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

40
41
42
43
44
45
46
47
48
49
50

23.7
38.2
23
75.2
52.7
56.3
60.6
60.1
67.1
55.5
65

1.029
0....

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors