15 Jan 18) Add trendline, regression
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? –
3) What is the Chi Square critical Value? Use =CHISQ.INV()
4) What is your answer to Saeko?
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
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?
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
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?
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.
20) Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteEdu. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
Do you need help with this question?
Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.
WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.
With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Chat with us today! We are always waiting to answer all your questions.