HMKW 12 GROUP: ______________ 1 HMKW 12 Data Analysis and Forecasting in the Sports and Health Industries Assignment GROUP 1 : HI206: Hockey Industry. T he data in the template HOCKEY is a data set from Teemu Kivikko, Westlake Fall 200 0 , 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 predic tion 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 p rint 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 th e weaker variables that show co l linearity 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: D 6 ) 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 pag e 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 r 2 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: ______________ 2 The variables Year, Unemploy, Mfg, and GERD were eliminated because they have high correlation with the most of the other independent variables, i.e. r ≥ 0.9 or r 2 ≥ 0.81. HMKW 12 GROUP: ______________ 3 c) For the template HI206 c , p lace 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.7 5 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 r 2 80.79% 69.52% 67.20% biased r 2 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 r 2 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 M SS206: 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: E 19 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 col linear variable(s) and print A 2: 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 t he optimal fit of the truly independent variables using MSSG206c with the data from your MSS206b template. Enter the best independent variable first (highest r 2 ), then Move/Copy MSS 206c and enter the next best , and so on . What is the forecast for a sales person with 35 years experience and a n 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 Extroversion Exp (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 r 2 $ 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