Chat with us, powered by LiveChat I have a homework on Anova, Pivot table,pivot table data, regression, cleaning data with an outliner. | All Paper
+1(978)310-4246 credencewriters@gmail.com
Select Page

Answer the questions in the various excel worksheets with the formulas in the formula bar and graphs where required.
copy_of_copy_of_hw11_mgmt650_spring2019_questions.xlsx

Unformatted Attachment Preview

MGMT 650
Spring 2019 Week 11 Homework Questions
5/5/2019
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 15 different days and recorded the sales of each brand.
At the .05 significance level, can she conclude that there is a difference in preference between the brands?
Misa’s Bison
98
85
91
87
98
Yak-et-ty-Yaks
79
86
72
82
91
1)
2)
What is the null hypothesis?
What is the alternative hypothesis?
3)
What is the level of significance?
Use Tools – Data Analysis – ANOVA:Single Factor
to find the F statistic:
4)
From the ANOVA ooutput: What is the F value?
5)
What is the F critical value?
6)
Buffalo Yarns
65
83
78
66
193
Anova: Single Factor
Saeko has a yarn shop and wants to expand the colors in her yarn lines. She sends you the raw data in the “Piv
This data consists of the number of yards and meters of the color names, and the color types that each name b
Use the “Pivot Table Data” tab to create a pivot table that shows Saeko the number of yards and meters that w
7)
8)
9)
Using the pivot table that you just created, determine which of the color types sold the least amount of yards u
Using the pivot table that you just created, determine which of the color types sold the largest amount of yard
If Saeko wants to expand the colors in both the best selling and the worst selling color type, which two color ty
Least amount sold:
Largest amount sold:
10)
number of yards and meters that were sold in the various yarn types during the busiest weekend of her shop last year.
Customer
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
40
41
42
43
44
45
46
Color Name
Coriander
Black
Daffodil
Black
Opal
Toffee
Ruby
Ash
Black
Ash
Black
Whirlpool
Verde
Regal
Lynx
Yellow Rose
Chocolate
Mist
Whirlpool
Alfalfa
Ruby
Verde
Sky
Black
Whirlpool
Black
Mist
Alfalfa
Yellow Rose
Cream
Black
Ruby
Mist
Yellow Rose
Black
Sky
Periwinkle
Coriander
Black
Yellow Rose
Black
Periwinkle
Black
Blush
Black
Color Type
White
Black
Yellow
Black
Blue
Brown
Red
Blue
Black
Blue
Black
Blue
Green
Purple
Brown
Yellow
Brown
White
Blue
Green
Red
Green
White
Black
Blue
Black
White
Green
Green
Yellow
White
Black
Red
White
Yellow
Black
White
Purple
White
Black
Yellow
Black
Purple
Black
Red
Black
Yards
Meters
651
211
762
735
551
709
792
830
530
292
744
943
947
945
774
801
750
629
113
344
162
964
313
223
200
491
999
598
662
368
529
513
870
342
747
160
628
185
978
607
387
255
742
414
345
892
595.3
192.9
696.8
672.1
503.8
648.3
724.2
759.0
484.6
267.0
680.3
862.3
865.9
864.1
707.7
732.4
685.8
575.2
103.3
314.6
148.1
881.5
286.2
203.9
182.9
449.0
913.5
546.8
605.3
336.5
483.7
469.1
795.5
312.7
683.1
146.3
574.2
169.2
894.3
555.0
353.9
233.2
678.5
378.6
315.5
815.6
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
Mist
Coriander
Cream
Verde
Black
Daffodil
Chocolate
Daffodil
Regal
Daffodil
Coriander
Black
Black
Lynx
Periwinkle
Daffodil
Coriander
Verde
Lynx
Alfalfa
Cream
Daffodil
Periwinkle
Cream
Black
Opal
Cream
Cream
Coriander
Mist
Verde
Black
Blush
Opal
Daffodil
Mist
Alfalfa
Ruby
Verde
Cream
Black
Chocolate
Regal
Sky
Regal
Yellow Rose
White
White
White
Green
Black
Yellow
Brown
Yellow
Purple
Yellow
White
Black
Black
Brown
Purple
Yellow
White
Green
Brown
Green
White
Yellow
Purple
White
Black
Blue
White
White
White
White
Green
Black
Red
Blue
Yellow
White
Green
Green
Red
Green
White
Black
Brown
Purple
White
Purple
Yellow
727
584
321
478
131
539
767
369
378
376
957
929
959
994
714
912
776
895
706
105
165
505
661
226
472
184
191
238
894
488
400
618
374
359
553
439
448
290
907
402
123
981
247
805
358
461
540
664.8
534.0
293.5
437.1
119.8
492.9
701.3
337.4
345.6
343.8
875.1
849.5
876.9
908.9
652.9
833.9
709.6
818.4
645.6
96.0
150.9
461.8
604.4
206.7
431.6
168.2
174.7
217.6
817.5
446.2
365.8
565.1
342.0
328.3
505.7
401.4
409.7
265.2
829.4
367.6
112.5
897.0
225.9
736.1
327.4
421.5
493.8
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
Lynx
Regal
Ash
Periwinkle
Black
Black
Blush
Regal
Sapphire
Whirlpool
Yellow Rose
Whirlpool
Sapphire
Chocolate
Toffee
Sapphire
Whirlpool
Black
Regal
Opal
Blush
Coriander
Whirlpool
Ash
Blush
Black
Ruby
Mist
Chocolate
Opal
Toffee
Yellow Rose
Brown
Purple
Blue
Purple
Black
Black
Red
Purple
Blue
Blue
Yellow
Blue
Blue
Brown
Brown
Blue
Blue
Black
Purple
Blue
Red
White
Blue
Blue
Red
Black
Red
White
Brown
Blue
Brown
Yellow
964
317
190
423
761
219
258
968
517
362
125
504
427
557
498
216
174
470
644
436
768
456
385
869
416
634
302
892
240
523
710
245
881.5
289.9
173.7
386.8
695.9
200.3
235.9
885.1
472.7
331.0
114.3
460.9
390.4
509.3
455.4
197.5
159.1
429.8
588.9
398.7
702.3
417.0
352.0
794.6
380.4
579.7
276.1
815.6
219.5
478.2
649.2
224.0
Studies have shown that the frequency with which shoppers browse Internet retailers is related to the frequency with
data show respondents age and answer to the question “How many minutes do you browse online retailers per week
Age (X)
11
12
12
14
14
14
15
18
19
20
20
22
26
28
28
30
30
42
43
43
45
47
49
49
50
51
52
53
53
54
55
59
64
Time (Y)
1851
984
841
1063
883
1044
985
902
1040
918
976
728
859
960
893
709
786
463
560
791
591
440
258
250
480
919
262
184
231
246
205
163
300
11)
Use Data > Data Analysis > Correlation to compute the correlation checking the Labels checkbox.
12)
Use the Excel function =CORREL to compute the correlation. If answers for #1 and 2 do not agree, there is an error
13)
14)
15)
The strength of the correlation motivates further examination.
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
16)
17)
18)
a) Intercept =
b) Slope =
c) R2 =
Perform Data > Data Analysis > Regression. Highlight the Y-intercept with yellow. Highlight the X variable in blue. Hi
19)
20)
Using your highlighted cells, what is the equation of the regression line (round to two decimal places)?
21)
Use Excel to predict the number of minutes spent by a 41-year old shopper. Enter = followed by the regression form
Enter the intercept and slope into the formula by clicking on the cells in the regression output with the results.
22)
Is it appropriate to use this data to predict the amount of time that a 70-year-old will be on the Internet?
23)
If yes, what is the amount of time, if no, why?
t retailers is related to the frequency with which they actually purchase products and/or services online. The following
s do you browse online retailers per week?”
g the Labels checkbox.
#1 and 2 do not agree, there is an error.
horizontal (X) axis.
24)
On this worksheet, make an XY scatter plot linked to the following data:
X
22
48
37
30
24
10
42
30
41
29
16
36
45
11
31
26
31
33
46
22
13
22
32
49
35
Y
3872
9312
5217
4230
4536
1820
8274
121
6314
3828
2448
6156
7515
1309
3534
4576
5797
4983
6670
2464
2197
3278
5408
7497
5705
25)
26)
Add trendline, regression equation and r squared to the plot.
Add this title. (“Scatterplot of X and Y Data”)
27)
The scatterplot reveals a point outside the point pattern. Copy the data to a new location in the worksheet. You now
Data that are more tha 1.5 IQR below Q1 or more than 1.5 IQR above Q3 are considered outliers and must be inves
28)
It was determined that the outlying point resulted from data entry error. Remove the outlier in the copy of the data.
29)
Make a new scatterplot linked to the cleaned data without the outlier, and add title (“Scatterplot without Outlier,”) tre
30)
Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?