Question

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.

Parthika

Yes, what about the prepackaged salad bowls. We really need to see this data.

Bonnie:

Yes, let’s get right on this.

Mini-Case Assignment

Please use the attached spreadsheet 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 explain using the regression option on the Data Analysis pack in Excel. Thank you!

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

1. Put the values in excel as shown below.

2. We use the regression option under the Data analysis tab.

3. Input the data as shown below.

4. The output will be generated as follows

5. We formulate the regression equation using the output (highlighted in green)

Write the reqression equation

Dollars spent on Prepared food =
61.42165489 + 9.6930 (Family size)+ 0.1321(Gross monthly income)

Intrepreting the equations.

For 1 member increase in the family size, the dollars spend on prepared food increases by 9.6930.
For 1 unit increase in gross monthly income, the dollars spent on the prepared food increases by 0.1321

Also from the pvalue of the regression coefficient, we see that both the variable are significant in predicting the dependent variable.

The goodness of fit of the equation is given by the Rsquare (highlighted in blue). Rsquare gives an indication of the amount of variability explained by the model. Higher the value, better is the model.

In this case, the rsquare = 0.9613