excelAssignment-jaam

profilestrength
assignment.xlsx
Home>Mathematics homework help>Statistics homework help>excelAssignment-jaam

Descriptive Statistics

Data
Executive Salary Age Gender Education Promotions Pay
1 79 49 Female Bachelors 4 Low
2 76 38 Female Associates 0 Low
3 83 60 Male Masters 4 High
4 92 63 Male Masters 6 High
5 80 55 Female Bachelors 3 Low
6 81 54 Female Masters 6 High
7 77 42 Male Masters 4 Low
8 87 59 Male Masters 6 High
9 69 42 Male Bachelors 1 Low
10 70 36 Female Bachelors 1 Low
11 73 48 Male Associates 2 Low
12 80 53 Male Associates 2 Low
13 81 46 Female Associates 2 High
14 87 57 Male Masters 4 High
15 78 49 Female Associates 1 Low
16 78 40 Female Masters 2 Low
17 77 55 Male Masters 4 Low
18 81 48 Male Bachelors 5 High
19 75 42 Female Associates 1 Low
20 83 52 Male Bachelors 3 High
21 74 41 Female Bachelors 2 Low
22 85 62 Male Masters 3 High
23 86 53 Male Bachelors 5 High
24 73 36 Female Associates 0 Low
25 70 44 Male Associates 2 Low
26 81 50 Female Bachelors 3 High
27 77 50 Male Bachelors 3 Low
28 85 60 Male Associates 3 High
29 69 39 Female Associates 0 Low
30 73 48 Female Associates 2 Low
31 91 59 Male Masters 5 High
32 75 44 Male Bachelors 3 Low
33 90 64 Male Masters 5 High
34 74 42 Female Bachelors 0 Low
35 72 46 Female Bachelors 1 Low
Summary Statistics
Quantitative Variables Qualitative Variables
Salary Age Promotions Gender Frequency % Rel. Freq.
N 35.000 35.000 35.000 Female 16 45.7%
Mean 78.914 49.314 2.800 Male 19 54.3%
Standard Deviation ERROR:#NAME? ERROR:#NAME? ERROR:#NAME? 35 100.0%
Variance ERROR:#NAME? ERROR:#NAME? ERROR:#NAME?
Standard Error ERROR:#NAME? ERROR:#NAME? ERROR:#NAME? Education Frequency % Rel. Freq.
Margin of Error (95%) ERROR:#NAME? ERROR:#NAME? ERROR:#NAME? Associates 11 31.4%
Bachelors 13 37.1%
Minimum 69.000 36.000 0.000 Masters 11 31.4%
P10 ERROR:#NAME? ERROR:#NAME? ERROR:#NAME? 35 100.0%
Q1 74.000 42.000 1.500
Median 78.000 49.000 3.000 Pay Frequency % Rel. Freq.
Q3 83.000 55.000 4.000 Low 21 60.0%
P90 ERROR:#NAME? ERROR:#NAME? ERROR:#NAME? High 14 40.0%
Maximum 92.000 64.000 6.000 35 100.0%
Mode 81.000 42.000 3.000
Range 23.000 28.000 6.000
Interquartile Range 9.000 13.000 2.500
Coefficient of Variation ERROR:#NAME? ERROR:#NAME? ERROR:#NAME?
Skewness 0.332 0.148 0.157
Kurtosis -0.642 -0.996 -0.853
Salary
Salary (by 5)
Bin Bin Frequency Relative Frequency
Mean 78.914 70 66-70 4 11.4%
Standard Error 1.068 75 71-75 8 22.9%
Median 78.000 80 76-80 9 25.7%
Mode 81.000 85 81-85 8 22.9%
Standard Deviation 6.317 90 86-90 4 11.4%
Sample Variance 39.904 91-95 2 5.7%
Kurtosis -0.642 35 100.0%
Skewness 0.332
Range 23.000
Minimum 69.000
Maximum 92.000
Sum 2762.000
Count 35.000
Age
Age (by 5)
Bin Bin Frequency Relative Frequency
Mean 49.314 40 36-40 12 34.3%
Standard Error 1.361 45 41-45 9 25.7%
Median 49.000 50 46-50 6 17.1%
Mode 42.000 55 51-55 5 14.3%
Standard Deviation 8.050 60 56-60 3 8.6%
Sample Variance 64.810 61-65 0 0.0%
Kurtosis -0.996 35 100.0%
Skewness 0.148
Range 28.000
Minimum 36.000
Maximum 64.000
Sum 1726.000
Count 35.000
Promotions
Promotions (by 1)
Bin Bin Frequency Relative Frequency
Mean 2.800 1 0-1 16 45.7%
Standard Error 0.303 2 1-2 7 20.0%
Median 3.000 3 2-3 5 14.3%
Mode 3.000 4 3-4 4 11.4%
Standard Deviation 1.795 5 4-5 3 8.6%
Sample Variance 3.224 5-6 0 0.0%
Kurtosis -0.853 35 100.0%
Skewness 0.157
Range 6.000
Minimum 0.000
Maximum 6.000
Sum 98.000
Count 35.000
Gender
Education
Pay
Salary by Age
Covariance ERROR:#NAME?
Correlation Coefficient 0.846
R-Square 0.715
Salary by Promotions
Covariance ERROR:#NAME?
Correlation Coefficient 0.774
R-Square 0.599
Pay by Gender
Count of Pay Column Labels Count of Pay Column Labels
Row Labels High Low Grand Total Row Labels High Low Grand Total Odds of Pay=High
Female 3 13 16 Female 18.75% 81.25% 100.00% Female 0.231
Male 11 8 19 Male 57.89% 42.11% 100.00% Male 1.375
Grand Total 14 21 35 Grand Total 40.00% 60.00% 100.00%
Pay by Education
Count of Pay Column Labels Count of Pay Column Labels
Row Labels High Low Grand Total Row Labels High Low Grand Total Odds of Pay=High Odds Ratio Log Odds Ratio
Associates 2 9 11 Associates 18.2% 81.8% 100.00% Associates 0.222 0.071 -2.639
Bachelors 4 9 13 Bachelors 30.8% 69.2% 100.00% Bachelors 0.444 0.154 -1.872
Masters 8 3 11 Masters 72.7% 27.3% 100.00% Masters 2.667 4.000 -1.386
Grand Total 14 21 35 Grand Total 40.00% 60.00% 100.00%

&"Calibri,Italic"&A

Salary Histogram

Relative Frequency

66-70 71-75 76-80 81-85 86-90 91-95 0.11428571428571428 0.22857142857142856 0.25714285714285712 0.22857142857142856 0.11428571428571428 5.7142857142857141E-2

Salary (k$)

% Relative Frequency

Salary by Age Scatter Diagram

Age

79 76 83 92 80 81 77 87 69 70 73 80 81 87 78 78 77 81 75 83 74 85 86 73 70 81 77 85 69 73 91 75 90 74 72 49 38 60 63 55 54 42 59 42 36 48 53 46 57 49 40 55 48 42 52 41 62 53 36 44 50 50 60 39 48 59 44 64 42 46

Age

Salary (k$)

Salary by Promotions Scatter Diagram

Promotions

79 76 83 92 80 81 77 87 69 70 73 80 81 87 78 78 77 81 75 83 74 85 86 73 70 81 77 85 69 73 91 75 90 74 72 4 0 4 6 3 6 4 6 1 1 2 2 2 4 1 2 4 5 1 3 2 3 5 0 2 3 3 3 0 2 5 3 5 0 1

Age

Salary (k$)

Age Histogram

Relative Frequency

36-40 41-45 46-50 51-55 56-60 61-65 0.34285714285714286 0.25714285714285712 0.17142857142857143 0.14285714285714285 8.5714285714285715E-2 0

Age (Years)

% Relative Frequency

Promotions Histogram

Relative Frequency

0-1 1-2 2-3 3-4 4-5 5-6 0.45714285714285713 0.2 0.14285714285714285 0.11428571428571428 8.5714285714285715E-2 0

Promotions

% Relative Frequency

Gender Pie Chart

Female Male 0.45714285714285713 0.54285714285714282

Gender Bar Graph

Female Male 0.45714285714285713 0.54285714285714282

% Relative Frequency

Education Bar Graph

Associates Bachelors Masters 0.31428571428571428 0.37142857142857144 0.31428571428571428

% Relative Frequency

Education Pie Chart

Associates Bachelors Masters 0.31428571428571428 0.37142857142857144 0.31428571428571428

Pay Bar Graph

Low High 0.6 0.4

% Relative Frequency

Pay Pie Chart

Low High 0.6 0.4

Probability Distributions

Discrete Distributions Continuous Distributions
(k=8) (p=.2) (n=6 p=.2) (μ=3) (a=30 b=100) (μ=50 σ=5) (μ=1/3) Seed= (a=0 b=1)
D. Uniform Bernoulli Binomial Poisson C. Uniform Normal Exponential x Pr C. Uniform
D. Uniform (k=8)
(by 1)
Bin
Bernoulli (p=.2)
(by 1)
Bin
Binomial (n=6 p=.2)
(by 1)
Bin
Poisson (μ=3)
(by 1)
Bin
C. Uniform (a=30 b=100)
(by 10)
Bin
Normal (μ=50 σ=5)
(by 5)
Bin
Exponential (μ=1/3)
(by .4)
Bin
Central Limit Theorem
Coin Flip Set 1 Set 2 Set 3 Set 4 Set 5 Set 6 Set 7 Set 8 Set 9
1
2
3
4
5
6
7
8
9
10
Proportion Heads:
Proportion Heads Proportion Probability
Sample Population Heads Frequency Sample Population
Mean
Variance
Central Limit Theorem
Law of Large Numbers: as the number of sets (300) increases, the sample curve approaches the population curve.
Central Limit Theorem: as the number of coin flips (10) increases, the curves become more bell-shaped.

&"Calibri,Italic"&A

Inferential Statistics

Data
Executive Salary Gender Education Pay PayD Salary2
1 79 Female Bachelors 81
2 76 Female Associates 75
3 83 Male Masters 88
4 92 Male Masters 95
5 80 Female Bachelors 83
6 81 Female Masters 84
7 77 Male Masters 82
8 87 Male Masters 90
9 69 Male Bachelors 81
10 70 Female Bachelors 71
11 73 Male Associates 79
12 80 Male Associates 73
13 81 Female Associates 78
14 87 Male Masters 88
15 78 Female Associates 78
16 78 Female Masters 80
17 77 Male Masters 88
18 81 Male Bachelors 86
19 75 Female Associates 69
20 83 Male Bachelors 79
21 74 Female Bachelors 77
22 85 Male Masters 89
23 86 Male Bachelors 93
24 73 Female Associates 79
25 70 Male Associates 76
26 81 Female Bachelors 75
27 77 Male Bachelors 81
28 85 Male Associates 81
29 69 Female Associates 66
30 73 Female Associates 78
31 91 Male Masters 93
32 75 Male Bachelors 75
33 90 Male Masters 87
34 74 Female Bachelors 70
35 72 Female Bachelors 73
Salary
Variable: Salary (Normal)
One Population: All Executives
Unknown variance so use the t distribution; if variance is known use the z distribution.
Two Populations: Male Executives vs. Female Executives
Unknown variances so use the t distribution; if variances are known use the z distribution.
If variances are assumed unequal use the SE with the special DF. If variances are assumed equal use the SE (P) with the regular DF.
Gender Gender
All Male Female Difference in Means
N Standard Error
Mean Special DF 29.520 - Use ROUND function with 0 digits.
Variance Pooled Variance
Standard Error Standard Error (P)
DF Regular DF
Confidence Intervals
One Population Confid. Alpha DF Mean SE Critical t MOE Lower CL Upper CL
90% 0.10
95% 0.05
99% 0.01
Two Populations Confid. Alpha DF Δ Means SE Critical t MOE Lower CL Upper CL
(Unequal Variances) 90% 0.10
95% 0.05
99% 0.01
Two Populations Confid. Alpha DF Δ Means SE Critical t MOE Lower CL Upper CL
(Equal Variances) 90% 0.10
95% 0.05
99% 0.01
Hypothesis Tests
One Population Null Hypothesis Alpha DF Mean SE t-statistic L Critical t U Critical t P-value Decision
μ ≥ 77 0.10
μ ≤ 77 0.10
μ = 77 0.10
μ ≥ 77 0.05
μ ≤ 77 0.05
μ = 77 0.05
μ ≥ 77 0.01
μ ≤ 77 0.01
μ = 77 0.01
Hypothesis Tests
Two Populations Null Hypothesis Alpha DF Δ Means SE t-statistic L Critical t U Critical t P-value Decision
(Unequal Variances) μM - μF ≥ 8 0.10
μM - μF ≤ 8 0.10
μM - μF = 8 0.10
μM - μF ≥ 8 0.05
μM - μF ≤ 8 0.05
μM - μF = 8 0.05
μM - μF ≥ 8 0.01
μM - μF ≤ 8 0.01
μM - μF = 8 0.01
Two Populations Null Hypothesis Alpha DF Δ Means SE t-statistic L Critical t U Critical t P-value Decision
(Equal Variances) μM - μF ≥ 8 0.10
μM - μF ≤ 8 0.10
μM - μF = 8 0.10
μM - μF ≥ 8 0.05
μM - μF ≤ 8 0.05
μM - μF = 8 0.05
μM - μF ≥ 8 0.01
μM - μF ≤ 8 0.01
μM - μF = 8 0.01
Equivalent Two Population Hypothesis Tests
Salary
Male Female
Other Hypothesis Tests
Pay
Variable: PayD (Bernoulli)
One Population: All Executives
It must be that np ≥ 5 and n(1-p) ≥ 5. Use the z distribution.
For HT use the hypothesized value in calculating the SE.
Two Populations: Male Executives vs. Female Executives
It must be that np ≥ 5 and n(1-p) ≥ 5 for both populations. Use the z distribution.
For HT when the hypothesized value is 0, the variances are presumed equal so use the SE (P).
Gender Gender
All Male Female
N Difference in Props
Proportion Standard Error
Variance Pooled Variance
Standard Error Standard Error (P)
Confidence Intervals
One Population Confid. Alpha Proportion SE Critical z MOE Lower CL Upper CL
90% 0.10
95% 0.05
99% 0.01
Two Populations Confid. Alpha Δ Props SE Critical z MOE Lower CL Upper CL
90% 0.10
95% 0.05
99% 0.01
Hypothesis Tests
One Population Null Hypothesis Alpha Proportion SE z-statistic L Critical z U Critical z P-value Decision
p ≥ 0.50 0.10
p ≤ 0.50 0.10
p = 0.50 0.10
p ≥ 0.50 0.05
p ≤ 0.50 0.05
p = 0.50 0.05
p ≥ 0.50 0.01
p ≤ 0.50 0.01
p = 0.50 0.01
Hypothesis Tests
Two Populations Null Hypothesis Alpha Δ Props SE z-statistic L Critical z U Critical z P-value Decision
(Unequal Variances) pM - pF ≥ 0.15 0.10
pM - pF ≤ 0.15 0.10
pM - pF = 0.15 0.10
pM - pF ≥ 0.15 0.05
pM - pF ≤ 0.15 0.05
pM - pF = 0.15 0.05
pM - pF ≥ 0.15 0.01
pM - pF ≤ 0.15 0.01
pM - pF = 0.15 0.01
Two Populations Null Hypothesis Alpha Δ Props SE z-statistic L Critical z U Critical z P-value Decision
(Equal Variances) pM - pF ≥ 0 0.10
pM - pF ≤ 0 0.10
pM - pF = 0 0.10
pM - pF ≥ 0 0.05
pM - pF ≤ 0 0.05
pM - pF = 0 0.05
pM - pF ≥ 0 0.01
pM - pF ≤ 0 0.01
pM - pF = 0 0.01
Equivalent Two Population Hypothesis Tests
PayD
Male Female
Test of Independence
Test of Independence

&"Calibri,Italic"&A

Linear Modeling

Data
Executive Salary Age Gender Education Promotions AgeC GenD AgeC*GenD
1 79 49 Female Bachelors 4
2 76 38 Female Associates 0
3 83 60 Male Masters 4
4 92 63 Male Masters 6
5 80 55 Female Bachelors 3
6 81 54 Female Masters 6
7 77 42 Male Masters 4
8 87 59 Male Masters 6
9 69 42 Male Bachelors 1
10 70 36 Female Bachelors 1
11 73 48 Male Associates 2
12 80 53 Male Associates 2
13 81 46 Female Associates 2
14 87 57 Male Masters 4
15 78 49 Female Associates 1
16 78 40 Female Masters 2
17 77 55 Male Masters 4
18 81 48 Male Bachelors 5
19 75 42 Female Associates 1
20 83 52 Male Bachelors 3
21 74 41 Female Bachelors 2
22 85 62 Male Masters 3
23 86 53 Male Bachelors 5
24 73 36 Female Associates 0
25 70 44 Male Associates 2
26 81 50 Female Bachelors 3
27 77 50 Male Bachelors 3
28 85 60 Male Associates 3
29 69 39 Female Associates 0
30 73 48 Female Associates 2
31 91 59 Male Masters 5
32 75 44 Male Bachelors 3
33 90 64 Male Masters 5
34 74 42 Female Bachelors 0
35 72 46 Female Bachelors 1
Data (cont.)
AgeC Promotions AgeC*Promos GenD EducD1 EducD2 GenD*EducD1 GenD*EducD2
1. Salary by Age
2. Salary by Gender
Equivalent Two Population Hypothesis Tests
Salary
Male Female
3. Salary by Education
Equivalent Three Population Hypothesis Test
Salary
Associates Bachelors Masters
4. Salary by Promotions
5. Salary by Age, Gender
6. Salary by Age, Gender, Age*Gender
7. Salary by Age, Promotions
8. Salary by Age, Promotions, Age*Promotions
9. Salary by Gender, Education
10. Salary by Gender, Education, Gender*Education
Moving Average (Interval = 3)
Week Sales Forecast Squared Error
1 17
2 21
3 19
4 23
5 18
6 16
7 20
8 18
9 22
10 20
11 15
12 22
Exponential Smoothing (Optimized α)
Week Sales Forecast Squared Error
1 17
2 21
3 19
4 23
5 18
6 16
7 20
8 18
9 22
10 20
11 15
12 22
Multiplicative Model (Trend, Seasonal)
Moving Seasonal Seasonal Deseasonalized Squared
Quarter Sales Average Centered MA Irregular Value Index Sales Forecast Error
1 4.8
2 4.1
3 6.0
4 6.5
5 5.8
6 5.2
7 6.8
8 7.4
9 6.0
10 5.6
11 7.5
12 7.8
13 6.3
14 5.9
15 8.0
16 8.4
17
18
19
20
Multiplicative Model (Trend, Seasonal)
Year Quarter
2012 F
W
Sp
Su
2013 F
W
Sp
Su
2014 F
W
Sp
Su
2015 F
W
Sp
Su
2016 F
W
Sp
Su

&"Calibri,Italic"&A

Charts

Female 45.7%
Male 54.3%
Associates 31.4%
Bachelors 37.1%
Masters 31.4%
Low 60.0%
High 40.0%

Gender Bar Graph

Female Male 0.45714285714285713 0.54285714285714282

% Relative Frequency

Gender Pie Chart

Female Male 0.45714285714285713 0.54285714285714282

Female Male 0.45714285714285713 0.54285714285714282

Education Bar Graph

Associates Bachelors Masters 0.31428571428571428 0.37142857142857144 0.31428571428571428

% Relative Frequency

Education Pie Chart

Associates Bachelors Masters 0.31428571428571428 0.37142857142857144 0.31428571428571428

Pay Bar Graph

Low High 0.6 0.4

% Relative Frequency

Pay Pie Chart

Low High 0.6 0.4

Pivot

Count of Pay Column Labels
Row Labels High Low Grand Total
Associates 2 9 11
Bachelors 4 9 13
Masters 8 3 11
Grand Total 14 21 35