Question

Caption: Princess Foods wants to determine if there is a relationship in the amount a household spends on prepared foods to family size and income. Parthika: Well, we still have data collected from a previous marketing study. Let’s use that. We have an Excel file. I am sure we can find the spreadsheet. It should have the exact information we need. Liwei: Yes, this could be interesting. We may find enough evidence to rethink the meal preparation kits again. Bonnie: Great idea. We need to know it the data is a good fit and what the exact relationship is between the dependent variable and the independent variables. We can use this information to help us design perhaps a new line of prepared frozen foods. ParthikaYes, what about the prepackaged salad bowls. We really need to see this data. Bonnie: Yes, let’s get right on this.

Please use the below data to determine the equation that represents the relationship and explain the goodness of fit. Based on the data, write a detailed memo/explanation and interpret the results. How might this data be used?

Dollars spent on Prepared food	Family size	Gross monthly income
495.86	4	3126
642.77	5	3933
364.81	3	1925
619.3	5	3736
238.71	1	1453
378.94	2	2538
302.58	1	1798
231.74	2	1189
428.67	3	2247
286.99	3	1460
268.81	1	1567
329.81	2	1622
627.25	5	3828
421.52	3	2782
656.38	5	3978
400.64	3	2493
603.41	6	3753
560.69	4	3778
623	5	3609
416.12	2	2262
323.9	1	1966
418.78	3	2736
506.46	4	3274
552.53	2	3480
586.46	4	3741
637.18	8	3684
244.49	2	1476
507.19	5	2835
512.56	5	2873
312.89	1	1618
329.05	2	1565
243.49	2	1582
560.37	8	3380
599.9	5	3922
657.09	5	3845
394.82	2	2233
556.42	4	3098
596.05	8	3707
365.8	4	2071
489.08	3	3166

Answer 1

Excel > Data > Data Analysis > Regression > Give X and Y values range (select lables if present) > OK  

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.980501258
R Square	0.961382717
Adjusted R Square	0.959295296
Standard Error	27.83077172
Observations	40
ANOVA
	df	SS	MS	F	Significance F
Regression	2	713455.378	356727.689	460.5601123	7.16905E-27
Residual	37	28658.41861	774.5518544
Total	39	742113.7966
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	61.42165489	14.32509969	4.287694761	0.00012396	32.39624586	90.44706391	32.39624586	90.44706391
Family size	9.693091949	3.607684799	2.686790141	0.01074241	2.3832282	17.0029557	2.3832282	17.0029557
Gross monthly income	0.132165589	0.007535354	17.5393996	1.6178E-19	0.116897511	0.147433667	0.116897511	0.147433667

Dollars spent on Prepared food = 61.4217 + 9.6931 * Family size + 0.1322 * Gross monthly income

If family size increases 1 unit then Dollars spent on Prepared food increases 9.6931 units when other independent variables constant

If Gross monthly income increases 1 unit then Dollars spent on Prepared food increases  0.1322 units when other independent variables constant

If Independent variables are equal to 0 then Y intercept is the mean value of Dollars spent on Prepared food

R = 0.9805, means Strong and positive correlation present

R^2 = 0.9614, means 96.14% of variation in Y variable is explained by regression model

R^2 represents goodness of fit also. If it is nearer to 1, which means it is best fit