data for the mini-case in an Excel format M6: Regression Analysis Mini-Case Assi
ID: 3246019 • Letter: D
Question
data for the mini-case in an Excel format
M6: Regression Analysis Mini-Case Assignment
Scenario Script:
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.
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?
Due Dates
11:59pm ET, Sunday.
Grading Criteria and Guidelines
Grading criteria and detailed instructions for submission of assignments are available on the course website.
Dollars spent on prepared food Family size Gross Monthly income 603.41 5 3753 560.69 3 3778 623 6 3609 416.12 5 2262 323.9 1 1966 418.78 4 2736 506.46 2 3274 552.53 2 3480 586.46 7 3741 637.18 8 3684 244.49 2 1476 507.19 5 2835 512.56 5 2873 312.89 4 1618 329.05 4 1565 243.49 6 1582 560.37 8 3380 599.9 3 3922 657.09 5 3845 394.82 5 2233 556.42 1 3098 596.05 8 3707 365.8 8 2071 489.08 5 3166 495.86 4 3126 642.77 4 3933 364.81 8 1925 619.3 1 3736 238.71 1 1453 378.94 4 2538 302.58 1 1798 231.74 8 1189 428.67 6 2247 286.99 5 1460 268.81 1 1567 329.81 6 1622 627.25 3 3828 421.52 6 2782 656.38 2 3978 400.64 3 2493Explanation / Answer
Answer:
Regression Analysis
R²
0.957
Adjusted R²
0.955
n
40
R
0.978
k
2
Std. Error
29.265
Dep. Var.
Dollars spent on prepared food
ANOVA table
Source
SS
df
MS
F
p-value
Regression
710,425.5414
2
355,212.7707
414.76
4.60E-26
Residual
31,688.2552
37
856.4393
Total
742,113.7966
39
Regression output
confidence interval
variables
coefficients
std. error
t (df=37)
p-value
95% lower
95% upper
Intercept
38.5784
17.2339
2.239
.0313
3.6593
73.4976
Family size
3.5667
2.0624
1.729
.0921
-0.6121
7.7455
Gross Monthly income
0.1475
0.0051
28.729
6.69E-27
0.1371
0.1579
The estimated regression line is
Dollars spent on prepared food =38.5784+3.5667*Family size+0.1475*Gross Monthly income
Calculated F=414.76, P=0.0000 which is < 0.05 level. The regression model is significant.
For one family member increase, there is an increase of 3.5667 in Dollars spent on prepared food.
Test for Family size, calculated t=1.729, P=0.0921 which is > 0.05 level. Family size is not significant.
For one $ increase in Gross Monthly income , there is an increase of 0.1475 in Dollars spent on prepared food.
Test for Gross Monthly income, calculated t=28.729 P=0.0000 which is < 0.05 level. Gross Monthly income is significant.
Regression Analysis
R²
0.957
Adjusted R²
0.955
n
40
R
0.978
k
2
Std. Error
29.265
Dep. Var.
Dollars spent on prepared food
ANOVA table
Source
SS
df
MS
F
p-value
Regression
710,425.5414
2
355,212.7707
414.76
4.60E-26
Residual
31,688.2552
37
856.4393
Total
742,113.7966
39
Regression output
confidence interval
variables
coefficients
std. error
t (df=37)
p-value
95% lower
95% upper
Intercept
38.5784
17.2339
2.239
.0313
3.6593
73.4976
Family size
3.5667
2.0624
1.729
.0921
-0.6121
7.7455
Gross Monthly income
0.1475
0.0051
28.729
6.69E-27
0.1371
0.1579
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.