Question

*please use excel and provide formulas  

Super Markets Inc. is considering expanding into the Scottsdale, Arizona, area. You as director of planning must present an analysis of the proposed expansion to the operating committee of the board of directors. As a part of your proposal, you need to include information on the amount people in the region spend per month for grocery items. You would also like to include information on the relationship between the amount spent for grocery items and income. Assume amount spent is the dependent variable and monthly income is the independent variable.

Using Excel, run a regression analysis (ensure level of significance = 0.05). Ignore the questions in the textbook problem. Instead, respond to the following: a. Create a scatter diagram for the independent variable acting on the dependent variable. What type of relationship exists? Explain your rationale. b. What is the Null Hypothesis for the overall model? c. What is the Alternative Hypothesis for the overall model? d. What is the regression equation? e. What is the standard error? Describe what this means as related to the problem. f. Interpret the appropriate R2 value. g. What does the overall statistical significance explain with respect to the model?

Household	Amount	Income
1	$278	$5,068
2	$393	$5,596
3	$512	$3,780
4	$551	$5,247
5	$691	$4,377
6	$686	$4,230
7	$831	$4,257
8	$735	$5,326
9	$632	$5,132
10	$616	$5,996
11	$571	$4,749
12	$629	$4,841
13	$556	$4,918
14	$748	$5,564
15	$600	$6,033
16	$557	$4,993
17	$678	$5,102
18	$636	$5,653
19	$678	$5,911
20	$687	$6,550
21	$1,075	$5,683
22	$922	$7,380
23	$648	$7,285
24	$1,067	$7,651
25	$832	$7,599
26	$892	$6,768
27	$934	$8,895
28	$1,066	$8,004
29	$1,258	$7,893
30	$997	$7,696
31	$983	$8,904
32	$1,265	$8,345
33	$1,109	$9,423
34	$887	$9,555
35	$1,081	$8,901
36	$1,226	$9,747
37	$1,155	$9,942
38	$1,374	$9,977
39	$1,511	$9,056
40	$1,148	$9,740

Answer 1

Answer:

a)

b) Ho: There is no relation between amount spend and income

c) H1: There is a relation between amount spend and income

d) amount =31.8023+0.1193*income

e) standard error=172.1930.

This is measure of variation in predicted values of amount spend by income.

f) R2 value=0.6415

64.15% of variation in amount spend is explained by income.

g) Calculated F=67.995, P=0.000 which is < 0.05 level. The model is significant.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.800932501
R Square	0.641492872
Adjusted R Square	0.632058474
Standard Error	172.1930293
Observations	40
ANOVA
	df	SS	MS	F	Significance F
Regression	1	2016085	2016085	67.99510306	5.43468E-10
Residual	38	1126717	29650.44
Total	39	3142801
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	31.80232112	102.0007	0.311785	0.756907886	-174.6872164	238.2919
Income	0.119304063	0.014468	8.245914	5.43468E-10	0.090014594	0.148594