Homework 12 | Complete Solution

GROUP :

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.

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.

 
 
d)    Print the Summary Table below and comment about how much it helped to add the second independent variable.

 CHECK FIGURE: $316.7B in 2002 for part c)

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.

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.

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?

 
PC204: Prostate Cancer. The worksheet Prostate contains the incident rates per 100,000 males for malignant prostate cancer. The three columns represent the rates for all races, whites and blacks.

a)    Make up a line graph on the Prostate worksheet using all three data sets without markers as you did for HMWK01 for female breast cancer. Then print and comment on what you learn about the prostate cancer rates for all American males, Whites & Blacks. Pay attention to the peak year and how close All Races is to White Males and comment. Get rid of grid lines and bring the legend inside and stretch it horizontally. Comment on the differences between the races. Is the gap widening?

b)    Use the template PC204 for White American Males for all the years and determine the optimal weights for a 2-period and 3-period moving average, minimizing RMSE. Print A1:H31 with row/column borders for the best model (shrink a bit to fit on this page – remove blank rows above). Make sure you adjust the graph.
 

c)     Print the Summary Table and comment on what you would predict for 2012 and why you choose that prediction (Naïve, 2-period or 3-period Solver models).
 

News205: Newspaper Cancellations. A large metropolitan newspaper has been experiencing a lot of cancellations of their print edition but is hopeful that this is slowing down. Given the data on the template News, it seems to be following a pattern during its 4-week billing cycle. Bills go out every 4 weeks, and for 2009, Week3 is the beginning of the cycle. Thanks to Matt B. from Fall 2009 for this data, which has been disguised and is the total of all the reasons for cancellations.

a)    Complete the input data in the template News205a and resize the graphs as needed. Print A1:K21 with row/column borders and comment on what you learn from just looking at the graphs about seasonality and cancellations over time and also the indices.  
 

b)    Use the template News204b with the adjusted sales data to determine the optimal 2-period moving average that minimizes RMSE and then Move/Copy and do the optimal 3-period. Print A1:H31 of the best Solver model.  
 

c)    Complete the Summary for the News204b template and paste M4:Q7 into the Summary in News204a in I25:M28. Print G23:O38. Which model of the three would you choose? Why? Do the number of cancellations seem to be slowing down. Why is this not necessarily good news? HINT: Do we know the % of cancellations?

 
INDIVIDUAL BONUS: To be done ONLY by each individual – same instructions as Group; email the professor for help but not each other. I need to know what you truly understand and what you don’t. Don’t share copies of your work with your teammates for this one. Eliminate Figures and Check Figures. 10% for clip art – most importantly do the work. Make these instructions a footnote.
I-PC204: Prostate Cancer. The worksheet Prostate contains the incident rates per 100,000 males for malignant prostate cancer. The three columns represent the rates for all races, whites and blacks.

a)    Use I-PS204 and add the data for Black American Males for all the years of data, determine the optimal weights for the 2-period, minimizing RMSE. Make sure you adjust the graph. Print A1:H31 with row/column borders for the model.  
b)    Then move/copy the template and compute the 3-period moving average and print A1:H31 for the 3-period model.
 

c)    Then print the Summary Table and comment on what you would predict for 2012 and why (Naïve, 2-period or 3-period Solver models). HINT: Pay attention to r2! Also, comment on how effective the Solver moving average is for Whites vs. Blacks.
 

CHECK FIGURE: Best RMSE = 16.99