statistics question

MGMT650HOMEWORK.xlsx

Home >Mathematics homework help >Statistics homework help >statistics question

HW11

MGMT 650 Summer 2020 Week 11 Homework Questions (Last updated 4/1/2020)

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.
	The primary colors are blue, red, and yellow; while the tertiary colors are Brown, Green, and Purple.
	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.
	Here is the pivot table that you should have created. It is optional so that you can practice your pivot table skills.
	Row Labels	Count of Color Type	Sum of Yards
	Black	23	35856
	Blue	16	17053
	Brown	13	13426
	Green	12	12509
	Purple	12	12131
	Red	8	8393
	White	26	37666
	Yellow	12	12874
	(blank)
	Grand Total	122	149908
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 D19 in the above table.
	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
2)	This total is your Chi Square test statistic
	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?
3)	What is the Chi Square test statistic?
4)	What is the Chi Square critical Value?		Use =CHISQ.INV()
	What is your answer to Saeko?

Pivot Table Data

Customer	Color Name	Color Type	Yards	Meters
1	Coriander	White	1155	1,056.13
2	Black	Black	1504	1,375.26
3	Daffodil	Yellow	904	826.62
4	Black	Black	1850	1,691.64
5	Opal	Blue	1497	1,368.86
6	Toffee	Brown	929	849.48
7	Ruby	Red	918	839.42
8	Ash	Blue	584	534.01
9	Black	Black	2363	2,160.73
10	Ash	Blue	816	746.15
11	Black	Black	1685	1,540.76
12	Whirlpool	Blue	1402	1,281.99
13	Verde	Green	972	888.80
14	Regal	Purple	590	539.50
15	Lynx	Brown	1263	1,154.89
16	Yellow Rose	Yellow	791	723.29
17	Chocolate	Brown	1331	1,217.07
18	Mist	White	2425	2,217.42
19	Whirlpool	Blue	848	775.41
20	Alfalfa	Green	990	905.26
21	Ruby	Red	1269	1,160.37
22	Verde	Green	1441	1,317.65
23	Sky	White	2269	2,074.77
24	Black	Black	1496	1,367.94
25	Whirlpool	Blue	815	745.24
26	Black	Black	1570	1,435.61
27	Mist	White	1999	1,827.89
28	Alfalfa	Green	1217	1,112.82
29	Jade	Green	737	673.91
30	Yellow Rose	Yellow	1063	972.01
31	Cream	White	1799	1,645.01
32	Black	Black	2721	2,488.08
33	Ruby	Red	575	525.78
34	Mist	White	2305	2,107.69
35	Yellow Rose	Yellow	828	757.12
36	Black	Black	2037	1,862.63
37	Sky	White	2157	1,972.36
38	Periwinkle	Purple	1363	1,246.33
39	Coriander	White	2179	1,992.48
40	Black	Black	1846	1,687.98
41	Yellow Rose	Yellow	1290	1,179.58
42	Black	Black	1894	1,731.87
43	Periwinkle	Purple	973	889.71
44	Black	Black	2393	2,188.16
45	Black	Black	2476	2,264.05
46	Mist	White	2428	2,220.16
47	Coriander	White	2488	2,275.03
48	Cream	White	2379	2,175.36
49	Verde	Green	600	548.64
50	Black	Black	1720	1,572.77
51	Daffodil	Yellow	1160	1,060.70
52	Chocolate	Brown	1264	1,155.80
53	Regal	Purple	1441	1,317.65
54	Daffodil	Yellow	915	836.68
55	Coriander	White	839	767.18
56	Black	Black	1468	1,342.34
57	Black	Black	831	759.87
58	Lynx	Brown	936	855.88
59	Periwinkle	Purple	854	780.90
60	Daffodil	Yellow	1250	1,143.00
61	Coriander	White	1352	1,236.27
62	Verde	Green	1163	1,063.45
63	Lynx	Brown	1329	1,215.24
64	Alfalfa	Green	1176	1,075.33
65	Cream	White	703	642.82
66	Daffodil	Yellow	836	764.44
67	Periwinkle	Purple	1468	1,342.34
68	Cream	White	742	678.48
69	Black	Black	1305	1,193.29
70	Cream	White	1254	1,146.66
71	Cream	White	703	642.82
72	Coriander	White	774	707.75
73	Mist	White	701	640.99
74	Verde	Green	589	538.58
75	Black	Black	697	637.34
76	Blush	Red	1113	1,017.73
77	Opal	Blue	732	669.34
78	Daffodil	Yellow	1393	1,273.76
79	Mist	White	1496	1,367.94
80	Alfalfa	Green	1440	1,316.74
81	Jade	Green	987	902.51
82	Verde	Green	1197	1,094.54
83	Cream	White	585	534.92
84	Black	Black	1488	1,360.63
85	Chocolate	Brown	914	835.76
86	Regal	Purple	852	779.07
87	Sky	White	922	843.08
88	Regal	Purple	1339	1,224.38
89	Yellow Rose	Yellow	1311	1,198.78
90	Lynx	Brown	739	675.74
91	Regal	Purple	731	668.43
92	Ash	Blue	1485	1,357.88
93	Periwinkle	Purple	827	756.21
94	Black	Black	992	907.08
95	Black	Black	581	531.27
96	Blush	Red	708	647.40
97	Regal	Purple	1152	1,053.39
98	Whirlpool	Blue	1434	1,311.25
99	Yellow Rose	Yellow	1133	1,036.02
100	Sapphire	Blue	734	671.17
101	Chocolate	Brown	1221	1,116.48
102	Toffee	Brown	906	828.45
103	Sapphire	Blue	1423	1,301.19
104	Whirlpool	Blue	1287	1,176.83
105	Black	Black	1277	1,167.69
106	Regal	Purple	541	494.69
107	Opal	Blue	501	458.11
108	Blush	Red	1104	1,009.50
109	Coriander	White	1187	1,085.39
110	Whirlpool	Blue	1408	1,287.48
111	Ash	Blue	820	749.81
112	Blush	Red	1427	1,304.85
113	Black	Black	517	472.74
114	Ruby	Red	1279	1,169.52
115	Mist	White	788	720.55
116	Chocolate	Brown	508	464.52
117	Opal	Blue	1267	1,158.54
118	Toffee	Brown	832	760.78
119	Sky	White	981	897.03
120	White	White	1056	965.61
121	Black	Black	1145	1,046.99
122	Chocolate	Brown	1254	1,146.66

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 .10 significance level, can she conclude that there is a difference in preference between the brands?
		Misa's Bison	Yak-et-ty-Yaks	Buffalo Yarns
		799	776	799
		784	640	931
		807	822	794
		675	856	920
		795	616	731
		875	893	837
	Total	4,735.00	4,603.00	5,012.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 year?”
	Age (X)	Time (Y)
	34	123,556.00
	17	92,425.00
	42	250,908.00
	35	204,540.00
	19	77,897.00
	43	197,012.00
	51	195,126.00
	50	177,100.00
	22	83,230.00
	58	140,012.00
	48	265,296.00
	35	189,420.00
	39	235,872.00
	39	230,724.00
	59	238,655.00
	40	138,560.00
	60	259,680.00
	22	93,208.00
	33	91,212.00
	36	153,216.00
	28	77,308.00
	22	56,496.00
	28	106,652.00
	44	242,748.00
	54	195,858.00
	30	178,560.00
	28	190,876.00
	16	98,528.00
	52	169,572.00
	22	79,420.00
	28	167,928.00
	35	215,705.00
	50	146,350.00
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 R Square in orange
15)	Use Excel to predict the number of minutes spent by a 22-year old shopper. Enter = followed by the regression formula.
	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 9-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
	1.01	2.8482
	1.48	4.2772
	1.8	4.788
	1.81	5.3757
	1.07	2.5252
	1.53	3.0906
	1.46	4.3362
	1.38	3.2016
	1.77	4.3542
	1.88	4.8692
	1.32	3.8676
	1.75	3.9375
	1.94	5.7424
	1.19	2.4752
	1.31	26.2
	1.56	4.5708
	1.16	2.842
	1.22	2.44
	1.72	5.1256
	1.45	4.3355
	1.43	4.2471
	1.19	3.5343
	2	5.46
	1.6	3.84
	1.58	3.8552
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.
	X	Y
	1.01	2.8482
	1.48	4.2772
	1.8	4.788
	1.81	5.3757
	1.07	2.5252
	1.53	3.0906
	1.46	4.3362
	1.38	3.2016
	1.77	4.3542
	1.88	4.8692
	1.32	3.8676
	1.75	3.9375
	1.94	5.7424
	1.19	2.4752
	1.56	4.5708
	1.16	2.842
	1.22	2.44
	1.72	5.1256
	1.45	4.3355
	1.43	4.2471
	1.19	3.5343
	2	5.46
	1.6	3.84
	1.58	3.8552
	Compare the regression equations of the two plots. How did removal of the outlier affect the slope and R2?
20)

statistics question

HW11

Chi Square

Pivot Table Data

ANOVA

Regression

Cleaning Data with Outlier

Sheet1