I am struggling with this one.. please help! Please use Excel Megastat to solve
ID: 3244344 • Letter: I
Question
I am struggling with this one.. please help!
Please use Excel Megastat to solve the problem. Your calculated values must come from the excel output. Copy and paste relevant excel output on this assignment or attach excel output to it – do not include the data, fit all excel output on one page.
Be sure to write in all required information below each question, you will not receive credit if you only turn it the output.
Show ALL the steps.
Treadmills Case:
Consumer Reports provided extensive testing and ratings for 24 treadmills. An overall score, based primarily on ease of use, ergonomics, exercise range, and quality, was developed for each treadmill tested. In general, a higher overall score indicates better performance. The following data (Excel file available in Connect “Lab assignment 5” folder) contains data on the price, overall score, and quality rating for the 24 treadmills (Consumer Reports, February 2006).
Use the “good” quality rating as the base when creating the dummy variables for quality.
Brand & Model
Price ($)
Score (points)
Quality
Landice L7
2900
86
Excellent
NordicTrack S3000
3500
85
Very good
SportsArt 3110
2900
82
Excellent
Precor
3500
81
Excellent
True Z4 HRC
2300
81
Excellent
Vision Fitness T9500
2000
81
Excellent
Precor M 9.31
3000
79
Excellent
Vision Fitness T9200
1300
78
Very good
Star Trac TR901
3200
72
Very good
Trimline T350HR
1600
72
Very good
Schwinn 820p
1300
69
Very good
Bowflex 7-Series
1500
83
Excellent
NordicTrack S1900
2600
83
Very good
Horizon Fitness PST8
1600
82
Very good
Horizon Fitness 5.2T
1800
80
Very good
Evo by Smooth Fitness FX30
1700
75
Very good
ProForm 1000S
1600
75
Very good
Horizon Fitness CST4.5
1000
74
Very good
Keys Fitness 320t
1200
73
Very good
Smooth Fitness 7.1HR Pro
1600
73
Very good
NordicTrack C2300
1000
70
Good
Spirit Inspire
1400
70
Very good
ProForm 750
1000
67
Good
Image 19.0 R
600
66
Good
A. Using Excel Megastat output, report multiple regression equation for predicting the price of treadmills when knowing their overall score and quality rating.
B. Interpret the meaning of each of the regression coefficient starting with b0 as an estimate of 0, and continuing with b1, b2…. For each “bn” report a value and which “X” is it a coefficient for.
Partial regression coefficient for the y-intercept, b0 = _______
Interpretation of b0 –
Does the interpretation of the y-intercept make sense? Why yes/no?
Partial regression coefficient for X1 (score in points), b1 = ________
Interpretation of b1 –
…Continue in the same fashion until you have interpreted all partial regression coefficients.
C. Test the significance of the overall regression model at = .01. Use the 7-step hypothesis test and either of the two approaches (critical or p-value).
D. Report the value of the adjusted multiple coefficient of determination and interpret its meaning.
E. Test the partial regression coefficient for the variable “score”. Is the coefficient significant at = 0.1? (p-value approach hypothesis test)
F. Test the partial regression coefficient for the first dummy variable (2). Is the coefficient significant at = 0.1? (p-value approach hypothesis test)
G. Report a 95% interval for the mean value of all treadmills that are in “good” condition and have a score of 79. Interpret the meaning of the interval in the context of the problem.
H. Report a 95% interval for the price of an individual treadmill in “good” condition with a score of 79. Interpret the meaning of the interval in the context of the problem.
I. Analyze the correlation matrix, do you see any multicollinearity in the model? Comment on which variables appear to be problematic?
Brand & Model
Price ($)
Score (points)
Quality
Landice L7
2900
86
Excellent
NordicTrack S3000
3500
85
Very good
SportsArt 3110
2900
82
Excellent
Precor
3500
81
Excellent
True Z4 HRC
2300
81
Excellent
Vision Fitness T9500
2000
81
Excellent
Precor M 9.31
3000
79
Excellent
Vision Fitness T9200
1300
78
Very good
Star Trac TR901
3200
72
Very good
Trimline T350HR
1600
72
Very good
Schwinn 820p
1300
69
Very good
Bowflex 7-Series
1500
83
Excellent
NordicTrack S1900
2600
83
Very good
Horizon Fitness PST8
1600
82
Very good
Horizon Fitness 5.2T
1800
80
Very good
Evo by Smooth Fitness FX30
1700
75
Very good
ProForm 1000S
1600
75
Very good
Horizon Fitness CST4.5
1000
74
Very good
Keys Fitness 320t
1200
73
Very good
Smooth Fitness 7.1HR Pro
1600
73
Very good
NordicTrack C2300
1000
70
Good
Spirit Inspire
1400
70
Very good
ProForm 750
1000
67
Good
Image 19.0 R
600
66
Good
Explanation / Answer
Answer:
Data recoded as follows with dummy variables:
Price ($)
Score (points)
Excellent
Very good
2900
86
1
0
3500
85
0
1
2900
82
1
0
3500
81
1
0
2300
81
1
0
2000
81
1
0
3000
79
1
0
1300
78
0
1
3200
72
0
1
1600
72
0
1
1300
69
0
1
1500
83
1
0
2600
83
0
1
1600
82
0
1
1800
80
0
1
1700
75
0
1
1600
75
0
1
1000
74
0
1
1200
73
0
1
1600
73
0
1
1000
70
0
0
1400
70
0
1
1000
67
0
0
600
66
0
0
Using Excel Megastat output, report multiple regression equation for predicting the price of treadmills when knowing their overall score and quality rating.
B. Interpret the meaning of each of the regression coefficient starting with b0 as an estimate of 0, and continuing with b1, b2…. For each “bn” report a value and which “X” is it a coefficient for.
Partial regression coefficient for the y-intercept, b0 = -3539.6584
Interpretation of b0 is the predicted price when overall score is 0 and quality rating is good.
Does the interpretation of the y-intercept make sense? Why yes/no?
No.
Partial regression coefficient for X1 (score in points), b1 = 65.1181
Interpretation of b1 – when score increase by 1, the price increases by 65.1181 while quality remains the same.
Partial regression coefficient for X2 (score in points), b2 = 794.99
Interpretation of b2 – when quality is excellent, the price increases by 794.99 while score remains the same.
Partial regression coefficient for X3 (score in points), b3 = 418.92
Interpretation of b3 – when quality is Very Good, the price increases by 418.92 while score remains the same.
C. Test the significance of the overall regression model at = .01. Use the 7-step hypothesis test and either of the two approaches (critical or p-value).
Calculated F=6.24, P=0.0036 which is < 0.01 level. Ho is rejected. The model is significant.
D. Report the value of the adjusted multiple coefficient of determination and interpret its meaning.
R square = 0.483
48.3% of variation in price is explained by the model.
E. Test the partial regression coefficient for the variable “score”. Is the coefficient significant at = 0.1? (p-value approach hypothesis test)
Calculated t=1.885, P=0.0741 which is < 0.10 level. The score is significant.
F. Test the partial regression coefficient for the first dummy variable (2). Is the coefficient significant at = 0.1? (p-value approach hypothesis test)
Calculated t=1.189, P=0.2485 which is > 0.10 level. The first dummy variable (2) is not significant
G. Report a 95% interval for the mean value of all treadmills that are in “good” condition and have a score of 79. Interpret the meaning of the interval in the context of the problem.
95% CI = (465.605, 2743.738). we are 95% confident that mean value of all treadmills that are in “good” condition and have a score of 79 falls in the interval.
H. Report a 95% interval for the price of an individual treadmill in “good” condition with a score of 79. Interpret the meaning of the interval in the context of the problem.
95% PI = (-181.813, 3390.157). we are 95% confident that price of an individual treadmill that are in “good” condition and have a score of 79 falls in the interval.
Analyze the correlation matrix, do you see any multicollinearity in the model? Comment on which variables appear to be problematic?
There is a problem of multicollinearity in the model, because Correlation between dummy variables Excellent and very good is large(-0.759).
Regression Analysis
R²
0.483
Adjusted R²
0.406
n
24
R
0.695
k
3
Std. Error
659.143
Dep. Var.
Price ($)
ANOVA table
Source
SS
df
MS
F
p-value
Regression
8,130,191.3852
3
2,710,063.7951
6.24
.0036
Residual
8,689,391.9481
20
434,469.5974
Total
16,819,583.3333
23
Regression output
confidence interval
variables
coefficients
std. error
t (df=20)
p-value
95% lower
95% upper
Intercept
-3,539.6584
2,368.9302
-1.494
.1507
-8,481.1602
1,401.8434
Score (points)
65.1181
34.5541
1.885
.0741
-6.9606
137.1968
Excellent
794.9907
668.8223
1.189
.2485
-600.1481
2,190.1295
Very good
418.9221
504.5425
0.830
.4162
-633.5353
1,471.3794
Predicted values for: Price ($)
95% Confidence Interval
95% Prediction Interval
Score (points)
Excellent
Very good
Predicted
lower
upper
lower
upper
Leverage
79
0
0
1,604.672
465.605
2,743.738
-180.813
3,390.157
0.686
Correlation Matrix
Score (points)
Excellent
Very good
Score (points)
1.000
Excellent
.589
1.000
Very good
-.154
-.759
1.000
24
sample size
± .404
critical value .05 (two-tail)
± .515
critical value .01 (two-tail)
Price ($)
Score (points)
Excellent
Very good
2900
86
1
0
3500
85
0
1
2900
82
1
0
3500
81
1
0
2300
81
1
0
2000
81
1
0
3000
79
1
0
1300
78
0
1
3200
72
0
1
1600
72
0
1
1300
69
0
1
1500
83
1
0
2600
83
0
1
1600
82
0
1
1800
80
0
1
1700
75
0
1
1600
75
0
1
1000
74
0
1
1200
73
0
1
1600
73
0
1
1000
70
0
0
1400
70
0
1
1000
67
0
0
600
66
0
0
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.