Question

In: Statistics and Probability

Caption: Princess Foods wants to determine if there is a relationship in the amount a household...

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

Solutions

Expert Solution

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


Related Solutions

Caption: Princess Foods wants to determine if there is a relationship in the amount a household...
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:...
Princess Foods wants to determine if there is a relationship in the amount a household spends...
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...
Scenario Script Caption: Mieke, the head of Marketing has heard about you. And at Princess Foods,...
Scenario Script Caption: Mieke, the head of Marketing has heard about you. And at Princess Foods, like much of the rest of the world, the reward for good work is more work. Mieke: Thank you so much for making time for me. Liwei says that you’re a miracle worker... Caption: Princess Foods Corporation is piloting a new service called “Princess Select,” a new premium meal kit service. Subscribers are mailed kits that each contain all the ingredients that a customer...
M7: Non-Parametric Techniques Scenario Assignment Caption: Princess Foods Corporation has observed the changing awareness of the...
M7: Non-Parametric Techniques Scenario Assignment Caption: Princess Foods Corporation has observed the changing awareness of the population on health and nutrition. Therefore, they want to investigate the acceptance of a low-calorie product and a low-sodium product by market segment.(gender) Are people more concerned about low-calorie soups or low-sodium soups and how does that break down by market segment (age)? Mieke: Here’s what we did: Two hundred customers were selected at random for two different interviews. We were hoping that the...
Scenario Script Caption Text: After several productive years, Princess Foods Corporation has amassed enough capital, marketing...
Scenario Script Caption Text: After several productive years, Princess Foods Corporation has amassed enough capital, marketing identity, and operational expertise to make a major push into new product lines and markets. To help manage this growth, Princess Foods Corporation has hired you as a consultant. Princess Foods Corporation has been considering a new offering, a premium, high-quality ice cream that is low in sugar and calories. This new “Tiara” line will be offered to customers in exotic flavors, such as...
Caption: As you prepared your memo about the new “Princess Select” line, you discovered that the...
Caption: As you prepared your memo about the new “Princess Select” line, you discovered that the sample size that you were provided was insufficiently small. Using a larger sample of 200 customers, you conducted this research and the data is now ready for your analysis. The following results were found: 1. Sample mean for price was $49.20 with a standard deviation of $4.60 2. Proportion who would purchase the new product was 62% Mieke: You have the new data already?...
You are conducting a study to determine if there is a relationship between annual household income...
You are conducting a study to determine if there is a relationship between annual household income and a high school student’s GPA. The school district you are studying is diverse and lower income. a) Before you conduct the study, do you expect there to be an association between these two variables? Why or why not? Which should be the explanatory variable? b) You collect data from a random sample of 15 students. The first row of the table is household...
You are conducting a study to determine if there is a relationship between annual household income...
You are conducting a study to determine if there is a relationship between annual household income and a high school student’s GPA. The school district you are studying is diverse and lower income. a) Before you conduct the study, do you expect there to be an association between these two variables? Why or why not? Which should be the explanatory variable? b) You collect data from a random sample of 15 students. The first row of the table is household...
Show your work in Excel A household- appliance manufacture wants to analyze the relationship between total...
Show your work in Excel A household- appliance manufacture wants to analyze the relationship between total sales and television advertising expenditures. The following data was collected. All data are in millions of dollars. TV ad $M spent Total Sales $M 8.3 361.1 6.3 344 9.9 377.9 9.4 371.5 10.4 365.4 9.0 364.5 9.2 372.9 10.6 379.4 9.3 362.6 10.5 387.5 a. Which variable is the dependent/response variable? b. Which is the independent/explanatory variable? Enter the data into your Excel file...
Regression Analysis (use Excel) Need to determine if there is a relationship in the amount a...
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...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT