Question

oliò Description and Requirements Computer Lab Assignment #9 1. Use Excel and the "Restaurant" data set file located in D2L for this assignment 2. For each class (Class 1 to 14), consider the number of customers for day 7. Call this new variable, day data, as X1. Consider the number of waiters/waitresses as variable X2.Consider the customer satisfaction as vanable Y In another word, create a table for variables Y, X.X2 Dependent Variable: Y Independent Variables: X,X2 3. Find a linear regression model that shows the relation between Y as dependent variable and X,X2 as independent vaniables. In another word, find values of bo, bı, b2 in the following equation, using excel 4. Comment on the followings by writing an explanatory paragraph from the results of your regression model R-Square r (coefficient of correlation) Adjusted R-Square 1alu . . of tha roaroccion. modal and accontaneo nf vaiahlocY v up Home w... 2 Trulia Yahool Maps Day 6 Saturday Sunday Day 7 Customer # waiter/waitress satisfaction 3.6 3.3 3.8 3.6 3.6 3.5 4.3 4.2 4.5 4.1 4.1 3.3 3.2 3.4 5 3 6 12 17 15 16 16 15 15 14 13 10 10 Independent Variables: X1, X2 3. Find a linear regression model that shows the relation between Y as dependent variable and X, x2 as independent variables. In another word, find values of bo, br, b in the following equation, using excel: 4. Comment on the followings by writing an explanatory paragraph from the results of your regression model: .R-Square . r (coefficient of correlation) Adjusted R-Square a-values of the regression model and acceptance of vaniables X,X2 . f-value of the regression modell 5. The Computer Lab Assignment must follow the following sequence: Page 1: Title of the Lab Assignment at the top of page, Explanatory paragraphs after the title. Add related paragraphs for parts 1, 2, 3, 4 Page 2, 3: Table for variables Y, X,X2 and Excel regression tables. luepeniueit valabies. X1, 2 3. Find a linear regression model that shows the relation between Y as dependent variable and x, x2 as independent varnables. In another word, find values of bo, bi, b2 in the following equation, using excel: 4. Comment on the followings by writing an explanatory paragraph from the results of your regression model: R-Square r (coefficient of correlation) Adjusted R-Square a-values of the regression model and acceptance of variables X, X2 f-value of the regression model . 5. The Computer Lab Assignment must follow the following sequence: Page 1: Title of the Lab Assignment at the top of page, Explanatory paragraphs after the title. Add related paragraphs for parts 1, 2, 3, 4. Page 2, 3: Table for variables Y, X1, X2 and Excel regression tables. hp Slly National Home W.. Trulia Yahoo! Maps Day 6 Day 7 Customer #waiter/waitress Saturday Sunday Satisfaction 3.6 3.3 3.8 3.6 3.6 3.5 4.3 4.2 4.5 4.1 4.1 3.3 3.2 3.4 3 12 17 15 16 16 15 15 14 13 10 4 10 ay 1 MondayTuesday W Day 3 Day 4Day 5 ednesday Thursday Friday Day 2 Classes Class 1 Class 2 Class 3 Class 4 Class 5 Class 6 Class 7 Class 8 Class 9 Class 10 eriods 10:00 am 11:00 am 11:00 am -12:00 pm 12:00 pm 01:00 pm 01:00 pm 02:00 pmm 02:00 pm - 03:00 pm 03:00 pm 04:00 pm 04:00 pm-05:00 pm 05:00 pm -06:00 pm 06:00 pm 07:00 pm 07:00 pm - 08:00 pm Class 11 08:00 pm 09:00 pm 09:00 pm 10:00 pm Class 1310:00 pm 11:00 pm 11:00 pm 12:00 am 3 3 4 10 15 15 16 12 13 12 14 13 10 13 10 12 12 10 Class 120 6 6 Class 14

Answer 1

data

y	x1	x2
8	3	3.6
9	3	3.3
8	3	3.8
4	2	3.6
2	2	3.6
2	2	3.5
6	3	4.3
11	3	4.2
16	5	4.5
15	4	4.1
15	4	4.1
14	3	3.3
13	3	3.2
10	3	3.4

Excel

data -> data analysis -> regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.913624328
R Square	0.834709413
Adjusted R Square	0.804656579
Standard Error	2.114322363
Observations	14
ANOVA
	df	SS	MS	F	Significance F
Regression	2	248.3260504	124.1630252	27.77473213	5.01608E-05
Residual	11	49.17394958	4.470359053
Total	13	297.5
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	6.279831933	5.445721818	1.153167962	0.273283902	-5.706120975
x1	6.311764706	0.893113823	7.067144792	2.07978E-05	4.346034434
x2	-4.31092437	1.769322904	-2.436482544	0.033028029	-8.205177825

3)
y^= 6.2798 + 6.3118 x1 -4.3109 x2

4)
r^2 = 0.8347
which means 83.47 % of variation in y is explained by this model

r = 0.9136

adjusted r^2 = 0.8047

alpha = 0.05

p-value of x1 = 0.00002 < alpha , hence x1 is significant
p-value of x2 = 0.033 < alpha , hence x2 is also significant

F-value = 27.7747
p-value of model is 0.00005 < alpha
hence the model is significant