HMKW 12 GROUP: ______________
1
HMKW 12 Data Analysis and Forecasting in the Sports and Health
Industries Assignment
GROUP1:
HI206: Hockey Industry. The data in the template HOCKEY is a data set from
Teemu Kivikko, Westlake Fall 2000, who grew up playing youth hockey with Teemu
Selanne of the Anaheim Ducks in Finland. Teemu used this data for his Econ class in
2001 to check the prediction for 2001 and 2002 sales.
a) Take all variables from HOCKEY and place into HI206a (R2) – make sure you use the correct
dependent variable (Y). Hide rows 1214 and print A1:I19 with row/column borders. Pay careful
attention to which data is history and which is forecasted. HINT: Color is a big clue. Print the table
and first two lines of data and hide the rows not used (don’t print blank columns).
b) Eliminate the weaker variables that show collinearity and print the final coefficient of
determination table. Comment on each
variable you eliminated and why. It should
make no difference which order you
eliminate variables – the final result should
be the same. Print the revised R2 Table
(A3:D6) using wrap-around.
1 Be sure to eliminate any FIGURES and Check Figures from the homework. Answer the questions below and use this Word template
to cut and paste from the Excel HMWK12.xls template. Only show row/column headings if specified. Center or wrap-around Excel
pictures or clip art and don’t indent. Print each problem starting on a separate page (use <Ctrl>+<Enter>) and label appropriately. Try
to keep each section question with the answer below it (no widows or orphans). Don’t try to cram too much information per page so
that it is miniscule and hard to read. Bonus points for appropriate clip art – up to 10% per problem and maximum 5 points bonus for
printing in color. Email me if you have questions – make sure you attach your spreadsheet and rename it with your name or group name –
like LUMATeers12.xls. Always place these instructions as a footnote (NOT footer!) on the first page of your group homework
assignment (make sure it is 8 pt Comic Sans font or smaller font than body).
1
2
3
4
5
6
7
8
9
10
11
15
16
17
18
19
A B C D E F G H I
HI206ab
r 2 Sales Year Unemploy Wages Fed Exp Mfg GERD Pop 15-34
Sales 100.00% 62.67% 66.47% 70.52% 65.25% 61.50% 63.39% 59.27%
Year 62.67% 100.00% 87.17% 93.83% 27.81% 97.82% 98.11% 99.38%
Unemploy 66.47% 87.17% 100.00% 93.70% 33.35% 86.67% 83.60% 83.56%
Wages 70.52% 93.83% 93.70% 100.00% 35.76% 89.72% 89.44% 90.17%
Fed Exp 65.25% 27.81% 33.35% 35.76% 100.00% 30.98% 30.44% 25.53%
Mfg 61.50% 97.82% 86.67% 89.72% 30.98% 100.00% 98.79% 98.15%
GERD 63.39% 98.11% 83.60% 89.44% 30.44% 98.79% 100.00% 98.14%
Pop 15-34 59.27% 99.38% 83.56% 90.17% 25.53% 98.15% 98.14% 100.00%
Y X1 X2 X3 X4 X5 X6 X7
Sales Year Unemploy Wages Fed Exp Mfg GERD Pop 15-34
$165.50 1991 10.40% 379.091 161.207 0.1566677 10.756 9,105,471
$160.20 1992 11.30% 387.788 164.435 0.1576807 11.401 9,035,736
r 2 Table (Coefficient of Determination)
Hockey Industry Sales
/20
r2 Sales Wages Fed Exp
Sales 100.00% 70.52% 65.25%
Wages 70.52% 100.00% 35.76%
Fed Exp 65.25% 35.76% 100.00%

HMKW 12 GROUP: ______________
3
c) For the template HI206c, place the truly independent variables starting in X1 in order. Add one
variable at a time and Move/Copy the template and keep going. Give the prediction for 2001 and
2002 using the estimates for the independent variables and comment on how these predictions
compare with what was given on the data sheet. Print A1:G17.
The predicted values for both the years 2001 and 2002 are much larger than the observed
data values, yielding negative residuals.
d) Print the Summary Table below and comment about how much it helped to add the second
independent variable.
CHECK FIGURE:
$316.75 in 2002 for part c)
Adding the second independent variable increases the explained variation up to 14% on the
dependent variable.
HI206c
Minimize Regression Forecast Measures Degrees of
R.M.S.E. R.M.S.E. M.A.D. M.A.P.E. Freedom
Model 19.22 19.84 9.84% 7 n = 10
Y-Bar (y) 43.85 35.93 17.18% 9 $208.24 Y-Bar (y)
unbiased r2 80.79% 69.52% 67.20%
biased r2 85.06% 76.29% 74.49%
a b c d e f
Parameters -$603.29 $0.46 $3.642
F = a + b*X1 + c*X2 + d*X3 + e*X4 + f*X5
2001 291.7 575.0 172.6
2002 316.7 617.0 174.1
Y F X1 X2 X3 X4 X5
Sales Model Wages Fed Exp X3 X4 X5
$165.50 159.5 379.091 161.207
$160.20 175.3 387.788 164.435
Multiple Regression F = a + bX1 + cX2 + dX3 + eX4 + fX5
Hockey Industry
Old New
2001 $227.82 $291.69
2002 $250.10 $316.75
R.M.S.E. R.M.S.E. Degrees of Forecast Model
Error Unbiased r2 Freedom a + bX1 + cX2 +
Y-Bar (y) 43.85 0.00% 9 208.24
Wages 25.25 66.84% 8 -98.88 0.70
Fed Exp 19.22 80.79% 7 -603.29 0.46 3.64
Multiple Regression Summary

HMKW 12 GROUP: ______________
4
MSS206: Monthly Sales Salary. This is an idea taken from a Virginia Tech web site
where I changed the values of the Independent variables. The data is shown on the
tab SALARY.
a) Enter the data from SALARY into the template MSS206a (R2). Print A1:E19 with row and column
borders and a couple of lines of the data (hide rows 814) and discuss which variable(s) are not
truly independent.
The variable Intelligence is not truly independent.
b) Move/Copy the template in a) and remove the collinear variable(s) and print A2:D6 (you’ll need to
reorder the variables. Comment on which variables seem to be able to predict success as a
salesperson and which do not.
The variables Extroversion and Exp (months) can successfully predict success as a salesperson
and the variable Intelligence is not a significant predictor.
c) Determine the optimal fit of the truly independent variables using MSSG206c with the data from
your MSS206b template. Enter the best independent variable first (highest r2), then Move/Copy
MSS206c and enter the next best, and so on. What is the forecast for a sales person with 35 years
experience and an Extrovert score of 15, and another salesperson with 20 years experience and
Extrovert score of 30 and print A1:G17. What is an extra year of experience worth? What about
another point of Extrovert?
1
2
3
4
5
6
7
15
16
17
18
19
A B C D E
MSS206a
r 2 $ Sales/Week Intelligence ExtroversionExp (months)
$ Sales/Week 100.00% 10.86% 40.80% 62.32%
Intelligence 10.86% 100.00% 5.85% 10.99%
Extroversion 40.80% 5.85% 100.00% 6.56%
Exp (months) 62.32% 10.99% 6.56% 100.00%
Y X1 X2 X3
$ Sales/Week Intelligence Extroversion Exp (months)
$2,625 89 21 27.4
$2,700 93 25 29.3
r 2 Table (Coefficient of Determination)
Monthly Sales Salary
MSS206a
r2 $ Sales/Week Extroversion Exp (months)
$ Sales/Week 100.00% 40.80% 62.32%
Extroversion 40.80% 100.00% 6.56%
Exp (months) 62.32% 6.56% 100.00%
Monthly Sales Salary
/20

HMKW 12 Data Analysis and Forecasting in the Sports and Health Industries Assignment

Study Now!

Document Details

Related Documents

The Statistics of Inheritance

Estimation and Hypothesis Testing

Normal Distribution - Amount of Sleep

SPSS Cumulative Assessment Instructions

Probability and Statistics Assignment

Lab Activity 5 Sampling Distributions

Hypothesis Testing � Comparing Two Groups

Applying Hypothesis Tests Discussion #8

STAT 250-004 Data Analysis Assignment 4

Inferential Statistics Week 2 Solution

Company

Explore

Study Tools