Question

You are a consultant who works for the Diligent Consulting Group. In this Case, you are engaged on a consulting basis by Loving Organic Foods. In order to get a better idea of what might have motivated customers’ buying habits you are asked to analyze the factors that impact organic food expenditures. You opt to do this using linear regression analysis.

Case Assignment
Using Excel, generate regression estimates for the following model:

Annual Amount Spent on Organic Food = α + bAge

After you have reviewed the results from the estimation, write a report to your boss that interprets the results that you obtained. Please include the following in your report:

The regression output you generated in Excel.
Your interpretation of the coefficient of determination (r-squared).
Your interpretation of the coefficient estimate for the Age variable.
Your interpretation of the statistical significance of the coefficient estimate for the Age variable.
The regression equation with estimates substituted into the equation. (Note: Once the estimates are substituted into the regression equation, it should take a form similar to this: y = 10 +2x)
A discussion of how this equation in item 5 above can be used to estimate annual expenditures on organic food.
An estimate of “Annual Amount Spent on Organic Food” for the average consumer. (Note: You will need to substitute the average age into the regression equation for x, the intercept for α, and solve for y.)

This is the DATA used for the assignment

Amount Spent on Organic Food   Age
7348 77
11598 47
9224 23
12991 38
16556 58
11515 44
10469 34
17933 75
18173 32
12305 39
9080 65
9113 48
6185 48
6470 49
6000 57
6760 71
8579 47
7393 47
8161 28
10800 63
6160 24
10800 66
8543 24
17666 38
12644 54
14308 28
9737 58
13301 27
18106   48
11468   26
9547   52
7812   29
15521   75
7598   45
7783   74
17737   56
7824   30
6552   57
11232   41
6540   23
4200   28
7225   23
5370   45
4476   33
2800   42
7839   39
3472   60
8854   57
8900   41
12791   67
12712   73
13321   57
8802   64
14369   24
7908   25
17840   34
15107   78
12070   34
6389   34
6606   41
6291   62
7425   57
11436   23
7612   78
7515   36
13115   44
11870   75
8450   70
16324   38
9331   35
9184   65
16803   68
10709   48
14456   24
16634   46
12227   43
13476   58
14554   66
9393   68
14594   74
6628   32
11240   61
13101   42
14034   60
17837   64
7849   53
10578   62
11325   78
7105   44
16460   58
8390   27
14956   68
10903   21
12054   70
11697   38
12781   25
17456   30
12835   70
13403   37
15051   40
14225   29
11196   54
11475   52
5605   65
9890   72
13227   40
11200   36
9600   43
15703   38
6486   73
9430   41
7755   35
8100   21
14821   59
10650   56
12589   42
11600   46
13000   34
17065   70
16500   55
8600   38
11900   51
16723   66
16759 43

Answer 1

Solution:

In excel install analysis toolpak and then go to

Data>Data analysis>Regression

select Y as Amount spent on organic foo

X as age

You will get

The regression output you generated in Excel.

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.114913
R Square	0.013205
Adjusted R Square	0.005116
Standard Error	3718.777
Observations	124
ANOVA
	df	SS	MS	F	Significance F
Regression	1	22577100	22577100	1.632555	0.203776
Residual	122	1.69E+09	13829306
Total	123	1.71E+09
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%
Intercept	9778.277	1047.234	9.337243	5.74E-16	7705.173	11851.38
Age	26.29286	20.57804	1.277715	0.203776	-14.4434	67.02914

Your interpretation of the coefficient of determination (r-squared).

R sq=0.013205

=0.013205*100

=1.3205

=1.3205*100

=1.32%

1.32 % variation in Amount Spent on Organic Food is explained by age

Your interpretation of the coefficient estimate for the Age variable.

Slope=26.29286

for unit increase in age ,predicted amount Spent on Organic Food increases by 26.29286 units.

Your interpretation of the statistical significance of the coefficient estimate for the Age variable.

For age variable

t Stat	P-value
1.277715	0.203776

p>0.05

Age not significant at 5% level of significance.

Age variable is not significant and is not a significant variable for predicting Annual Amount Spent on Organic Food

The regression equation with estimates substituted into the equation

Annual Amount Spent on Organic Food=   9778.277+26.29286*Age