In: Statistics and Probability
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 and Excel to determine the equation that represents the relationship and explain the goodness of fit. Based on the data, write a memo and interpret the results. How might this data be used?
*Please show step by step how to complete regression analysis using the Data Analysis Toolpack in Excel.
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 |
first go to data analysis tool pack and select regression
now,
select the input Y range (dollar spend on food)
input X range (family size , monthly income together)
and select a cell for output result
a summary will be displayed
SUMMARY OUTPUT | ||||||||
Regression Statistics | ||||||||
Multiple R | 0.980501 | |||||||
R Square | 0.961383 | |||||||
Adjusted R Square | 0.959295 | |||||||
Standard Error | 27.83077 | |||||||
Observations | 40 | |||||||
ANOVA | ||||||||
df | SS | MS | F | Significance F | ||||
Regression | 2 | 713455.4 | 356727.7 | 460.5601 | 7.17E-27 | |||
Residual | 37 | 28658.42 | 774.5519 | |||||
Total | 39 | 742113.8 | ||||||
Coefficients | Standard Error | t Stat | P-value | Lower 95% | Upper 95% | Lower 95.0% | Upper 95.0% | |
Intercept | 61.42165 | 14.3251 | 4.287695 | 0.000124 | 32.39625 | 90.44706 | 32.39625 | 90.44706 |
Family size | 9.693092 | 3.607685 | 2.68679 | 0.010742 | 2.383228 | 17.00296 | 2.383228 | 17.00296 |
Gross monthly income | 0.132166 | 0.007535 | 17.5394 | 1.62E-19 | 0.116898 | 0.147434 | 0.116898 | 0.147434 |
so ,
dollar spend = 61.42165 + 9.693092*(family size) + 0.132166 * (income)
................
R2 =0.96138
it implies that there is 96.14% of data can be explained by the equation
also R = 98.05 , it represents the strong relationship between the dependent and independent variables
thanks