Statistics excel file

profileStudyLong730
HW11.xlsx
Home>Mathematics homework help>Statistics homework help>Statistics excel file

HW11

MGMT 650 Summer 2019 Week 11 Homework Answers (Last updated 3/18/2019)

Chi Square

Saeko has a yarn shop and wants to test her theory on what types of colors she is selling.
She believes that Black, White, the Primary Colors, and Tertiary colors sell in equal amounts.
Test Saeko's theory using the 5 step hypothesis testing analysis and Chi Square at the .10 level of significance.
(optional) Use the "Pivot Table Data" tab to create a pivot table that shows Saeko the number of yards that were sold in the various yarn types during the busiest weekend of her shop last year.
The pivot table should contain Color Type, Sum of Yards and Count of Color Type as Column Titles.
Here is the pivot table that you should have created. It is optional so that you can practice your pivot table skills.
Color Type Sum of Yards Count of Color Type
Black 19,762.00 23
Blue 8,127.00 20
Brown 8,027.00 13
Green 6,533.00 12
Purple 7,243.00 12
Red 5,194.00 10
White 17,649.00 26
Yellow 7,229.00 14
Grand Total 79,764.00 130
1) Using the pivot table that you just created, fill in the blanks in the following table:
Primary Colors consists of the sum of Blue, Red, and Yellow yarn sold
Tertiary Colors consists of the sum of Brown, Green, and Purple Colors Sold.
The Total in this chart must equal the Grand Total, Cell D16 in the above table.
Color Type Sum of Yards
Black
White
Primary Colors
Tertiary Colors
Total
This table represents the observed data in the Chi Square analysis.
Find the Expected values for each of the colors. Saeko expects that the colors sell in equal amounts.
Color Type Sum of Yards
Black
White
Primary Colors
Tertiary Colors
Total
Subtract the Expected values from the observed values
Color Type Sum of Yards
Black
White
Primary Colors
Tertiary Colors
Square the values just found
Color Type Sum of Yards
Black
White
Primary Colors
Tertiary Colors
Divide each square by the expected value and add together
Color Type Sum of Yards
Black
White
Primary Colors
Tertiary Colors
Total
This total is your Chi Square test statistic
2) Use the 5 step hypothesis testing procedure to determine if Saeko's hypothesis that the colors sell in equal amounts is true.
What is the null hypothesis?
What is the alternative hypothesis?
What is the level of significance?
What is the Chi Square test statistic? - 0
3) What is the Chi Square critical Value? Use =CHISQ.INV()
4) What is your answer to Saeko?

Pivot Table Data

Customer Color Name Color Type Yards Meters
1 Coriander White 1,067.00 975.66
2 Black Black 917.00 838.50
3 Daffodil Yellow 762.00 696.77
4 Black Black 735.00 672.08
5 Opal Blue 551.00 503.83
6 Toffee Brown 709.00 648.31
7 Ruby Red 792.00 724.20
8 Ash Blue 830.00 758.95
9 Black Black 670.00 612.65
10 Ash Blue 292.00 267.00
11 Black Black 744.00 680.31
12 Whirlpool Blue 943.00 862.28
13 Verde Green 947.00 865.94
14 Regal Purple 945.00 864.11
15 Lynx Brown 774.00 707.75
16 Yellow Rose Yellow 801.00 732.43
17 Chocolate Brown 750.00 685.80
18 Mist White 629.00 575.16
19 Whirlpool Blue 113.00 103.33
20 Alfalfa Green 344.00 314.55
21 Ruby Red 162.00 148.13
22 Verde Green 964.00 881.48
23 Sky White 517.00 472.74
24 Black Black 1,223.00 1,118.31
25 Whirlpool Blue 200.00 182.88
26 Black Black 879.00 803.76
27 Mist White 999.00 913.49
28 Alfalfa Green 598.00 546.81
29 Jade Green 662.00 605.33
30 Yellow Rose Yellow 368.00 336.50
31 Cream White 529.00 483.72
32 Black Black 1,100.00 1,005.84
33 Ruby Red 870.00 795.53
34 Mist White 342.00 312.72
35 Yellow Rose Yellow 747.00 683.06
36 Black Black 1,160.00 1,060.70
37 Sky White 628.00 574.24
38 Periwinkle Purple 185.00 169.16
39 Coriander White 978.00 894.28
40 Black Black 607.00 555.04
41 Yellow Rose Yellow 387.00 353.87
42 Black Black 255.00 233.17
43 Periwinkle Purple 742.00 678.48
44 Black Black 414.00 378.56
45 Blush Red 345.00 315.47
46 Black Black 892.00 815.64
47 Mist White 727.00 664.77
48 Coriander White 584.00 534.01
49 Cream White 321.00 293.52
50 Verde Green 478.00 437.08
51 Black Black 931.00 851.31
52 Daffodil Yellow 539.00 492.86
53 Chocolate Brown 767.00 701.34
54 Daffodil Yellow 369.00 337.41
55 Regal Purple 378.00 345.64
56 Daffodil Yellow 376.00 343.81
57 Coriander White 957.00 875.08
58 Black Black 929.00 849.48
59 Black Black 959.00 876.91
60 Lynx Brown 994.00 908.91
61 Periwinkle Purple 714.00 652.88
62 Daffodil Yellow 912.00 833.93
63 Coriander White 776.00 709.57
64 Verde Green 895.00 818.39
65 Lynx Brown 706.00 645.57
66 Alfalfa Green 105.00 96.01
67 Cream White 165.00 150.88
68 Daffodil Yellow 505.00 461.77
69 Periwinkle Purple 661.00 604.42
70 Cream White 226.00 206.65
71 Black Black 472.00 431.60
72 Opal Blue 184.00 168.25
73 Cream White 191.00 174.65
74 Cream White 238.00 217.63
75 Coriander White 894.00 817.47
76 Mist White 488.00 446.23
77 Verde Green 400.00 365.76
78 Black Black 1,618.00 1,479.50
79 Blush Red 374.00 341.99
80 Opal Blue 359.00 328.27
81 Daffodil Yellow 553.00 505.66
82 Mist White 439.00 401.42
83 Alfalfa Green 448.00 409.65
84 Jade Green 290.00 265.18
85 Ruby Red 907.00 829.36
86 Verde Green 402.00 367.59
87 Cream White 123.00 112.47
88 Black Black 981.00 897.03
89 Chocolate Brown 247.00 225.86
90 Regal Purple 805.00 736.09
91 Sky White 358.00 327.36
92 Regal Purple 461.00 421.54
93 Yellow Rose Yellow 540.00 493.78
94 Lynx Brown 964.00 881.48
95 Regal Purple 317.00 289.86
96 Ash Blue 190.00 173.74
97 Periwinkle Purple 423.00 386.79
98 Black Black 1,761.00 1,610.26
99 Black Black 912.00 833.93
100 Blush Red 258.00 235.92
101 Regal Purple 968.00 885.14
102 Sapphire Blue 517.00 472.74
103 Whirlpool Blue 362.00 331.01
104 Yellow Rose Yellow 125.00 114.30
105 Whirlpool Blue 504.00 460.86
106 Sapphire Blue 427.00 390.45
107 Chocolate Brown 557.00 509.32
108 Toffee Brown 498.00 455.37
109 Sapphire Blue 216.00 197.51
110 Whirlpool Blue 174.00 159.11
111 Black Black 470.00 429.77
112 Regal Purple 644.00 588.87
113 Opal Blue 436.00 398.68
114 Blush Red 768.00 702.26
115 Coriander White 1,456.00 1,331.37
116 Whirlpool Blue 385.00 352.04
117 Ash Blue 869.00 794.61
118 Blush Red 416.00 380.39
119 Black Black 634.00 579.73
120 Ruby Red 302.00 276.15
121 Mist White 892.00 815.64
122 Chocolate Brown 240.00 219.46
123 Opal Blue 523.00 478.23
124 Toffee Brown 710.00 649.22
125 Yellow Rose Yellow 245.00 224.03
126 Sky White 736.00 673.00
127 White White 2,389.00 2,184.50
128 Black Black 499.00 456.29
129 Chocolate Brown 111.00 101.50
130 Ash Blue 52.00 47.55

ANOVA

Saeko owns a yarn shop and want to expands her color selection.
Before she expands her colors, she wants to find out if her customers prefer one brand
over another brand. Specifically, she is interested in three different types of bison yarn.
As an experiment, she randomly selected 21 different days and recorded the sales of each brand.
At the .01 significance level, can she conclude that there is a difference in preference between the brands?
Misa's Bison Yak-et-ty-Yaks Buffalo Yarns
343 365 360
308 368 346
349 351 381
304 339 306
348 366 314
346 331 307
Total 1,998.00 2,120.00 2,014.00
5) What is the null hypothesis?
What is the alternative hypothesis?
What is the level of significance?
6) Use Tools - Data Analysis - ANOVA:Single Factor
to find the F statistic:
7) From the ANOVA ooutput: What is the F value?
8) What is the F critical value?
9) What is your decision?

Regression

Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with which they actually purchase products and/or services online. The following data show respondents age and answer to the question “How many minutes do you browse online retailers per week?”
Age (X) Time (Y)
13 5662
19 4549
16 3772
44 1872
32 2799
52 1355
39 1966
15 5682
40 1602
53 1186
48 1832
37 2253
36 2241
42 1001
30 2474
42 1943
28 3021
11 5682
32 2192
39 1784
23 2707
37 1801
17 4827
11 2693
18 4340
50 1399
52 1593
9 9154
41 1504
26 2627
30 2575
32 2711
53 2368
10) Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.
11) Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error.
The strength of the correlation motivates further examination.
12) a) Insert Scatter (X, Y) plot linked to the data on this sheet with Age on the horizontal (X) axis.
b) Add to your chart: the chart name, vertical axis label, and horizontal axis label.
c) Complete the chart by adding Trendline and checking boxes
Read directly from the chart:
13) a) Intercept =
b) Slope =
c) R2 =
Perform Data > Data Analysis > Regression.
14) Highlight the Y-intercept with yellow. Highlight the X variable in blue. Highlight the total standard error in orange
SUMMARY OUTPUT
Use Excel to predict the number of minutes spent by a 37-year old shopper. Enter = followed by the regression formula.
15) Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results.
16) Is it appropriate to use this data to predict the amount of time that a 68-year-old will be on the Internet?
If yes, what is the amount of time, if no, why?

Cleaning Data with Outlier

17) On this worksheet, make an XY scatter plot linked to the following data:
X Y
92 22
87 23
102 23
80 25
91 27
100 20
95 21
109 19
77 28
100 221
98 25
89 27
97 23
93 22
89 27
91 22
97 21
105 21
88 22
83 24
86 27
89 26
79 30
88 22
94 24
18) Add trendline, regression equation and r squared to the plot.
Add this title. ("Scatterplot of X and Y Data")
19) The scatterplot reveals a point outside the point pattern. Copy the data to a new location in the worksheet. You now have 2 sets of data.
Data that are more tha 1.5 IQR below Q1 or more than 1.5 IQR above Q3 are considered outliers and must be investigated.
It was determined that the outlying point resulted from data entry error. Remove the outlier in the copy of the data.
Make a new scatterplot linked to the cleaned data without the outlier, and add title ("Scatterplot without Outlier,") trendline, and regression equation label.
Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?
20)