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
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
Get Answers For Free
Most questions answered within 1 hours.