In: Statistics and Probability
Regression Analysis (use Excel)
Need to determine if there is a relationship in the amount a household spends on prepared foods to family size and income. Need to be able to answer if the data is a good fit and what the exact relationship is between the dependent variable and the independent variables. 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, 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 |
Please use excel when solving and provide steps. I'm working through the problem now, but am getting stuck on a few excel interpretations and would like to know what I am missing.
Keep in mind to show Ordinary Least Squares Method, minimizing error, individual y values and the Regression Line, measures of goodness of fit, correlation coefficient r, coefficient of determination, deriving r2 , and standard error of the estimate (se).