Use the data file NFLPassing.xlsx to answer this question. The National Football
ID: 3310262 • Letter: U
Question
Use the data file NFLPassing.xlsx to answer this question. The
National Football League (NFL) records a variety of performance data for
individuals and teams. To investigate the importance of passing on the percentage
of games won by a team, the following data show the conference (Conf), average
number of passing yards per attempt (Yds/Att), the number of interceptions thrown
per attempt (Int/Att), and the percentage of games won (Win%) for a random sample
of 16 NFL teams for the 2011 season (NFL web site, February 12, 2012).
Use excel to answer the following questions. In addition to your written response,
submit an excel file that shows your work labelled “Q 5 . x l s x”.
a) Develop the estimated regression equation that could be used to predict the
percentage of games won, given the average number of passing yards per
attempt. What proportion of variation in the sample values of proportion of
games won does this model explain?
b) Develop the estimated regression equation that could be used to predict the
percentage of games won, given the number of interceptions thrown per attempt.
What proportion of variation in the sample values of proportion of games wo
does this model explain?
c) Develop the estimated regression equation that could be used to predict the
percentage of games won, given the average number of passing yards per attempt
and the number of interceptions thrown per attempt. What proportion of
variation in the sample values of proportion of games won does this model
explain?
d) The average number of passing yards per attempt for the Kansas City Chiefs
during the 2011 season was 6.2, and the team’s number of interceptions thrown
per attempt was 0.036. Use the estimated regression equation developed in part
(c) to predict the percentage of games won by the Kansas City Chiefs during the
2011 season. Compare your prediction to the actual percentage of games won
by the Kansas City Chiefs. (Note: For the 2011 season, the Kansas City Chiefs’
record was 7 wins and 9 losses.)
CAN YOU PLEASE SHOW ME HOW TO DO THIS ON EXCEL
Explanation / Answer
a)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.76
R Square
0.58
Adjusted R Square
0.55
Standard Error
15.87
Observations
16
ANOVA
df
SS
MS
F
Significance F
Regression
1
4814.25
4814.25
19.11
0.00
Residual
14
3527.42
251.96
Total
15
8341.67
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Intercept
-58.77
26.18
-2.25
0.04
-114.91
Yds/Att
16.39
3.75
4.37
0.00
8.35
58% of the variation in dependent variable is explained for by the independent variable.
b)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.66
R Square
0.44
Adjusted R Square
0.40
Standard Error
18.30
Observations
16
ANOVA
df
SS
MS
F
Significance F
Regression
1
3652.80
3652.80
10.91
0.01
Residual
14
4688.87
334.92
Total
15
8341.67
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Upper 95%
Intercept
97.54
13.86
7.04
0.00
67.81
127.27
Int/Att
-1600.49
484.63
-3.30
0.01
-2639.92
-561.06
44% of the variation in dependent variable is explained for by the independent variable.
c)
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.87
R Square
0.75
Adjusted R Square
0.71
Standard Error
12.60
Observations
16
ANOVA
df
SS
MS
F
Significance F
Regression
2
6277.01
3138.51
19.76
0.00
Residual
13
2064.66
158.82
Total
15
8341.67
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Intercept
-5.76
27.15
-0.21
0.84
-64.41
Yds/Att
12.95
3.19
4.06
0.00
6.07
Int/Att
-1083.79
357.12
-3.03
0.01
-1855.29
75% of the variation in dependent variable is explained for by the independent variable.
d)
y=-5.76+12.95* Yds/Att-1083.79* Int/Att
y=-5.76+12.95* 6.2-1083.79* 0.036
y=35.51
Actual wins was 7/16=0.4375 and hence the residual would be 0.0824
You can run the same in excel using the data analysis toolpak. In data analysis toolpak, use regression and use y values and x values and click on labels if you are including the headers. y would be the dependent variable and x would be the independent variable/variables. If data analysis tool pak is not installed, please see the procedure below: -
SUMMARY OUTPUT
Regression Statistics
Multiple R
0.76
R Square
0.58
Adjusted R Square
0.55
Standard Error
15.87
Observations
16
ANOVA
df
SS
MS
F
Significance F
Regression
1
4814.25
4814.25
19.11
0.00
Residual
14
3527.42
251.96
Total
15
8341.67
Coefficients
Standard Error
t Stat
P-value
Lower 95%
Intercept
-58.77
26.18
-2.25
0.04
-114.91
Yds/Att
16.39
3.75
4.37
0.00
8.35
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.