You will need the data for “Current Cap Hit” (CCH), goals and total points (goals plus assists) for this assignment and the other columns of the data can be deleted. Trim the data (delete the rows) for forwards who played fewer than 20 games to eliminate minor league call-ups and players injured most of the season. a) Present two separate graphs: (i) salaries (CCH) on the y-axis and goals (G) on the x-axis and (ii) salaries (CCH) on the y-axis and points (PTS) on the x-axis. Use the Excel graph functions to label your axes and to draw a linear trend through the data in each graph. b) Estimate the impact on salary in dollars of (i) an additional goal and (ii) an additional point to the forwards on your team using the Excel regression function or linear trend line. [If you use regression, you must present the results. If you use the trend line, you must explain your calculations.] c) Which factor (goals or points) appears to explain salaries better? Explain your reasoning briefly. [Hint: If you use the regression function, you can obtain the coefficient of determination (R2 ), which tells you what proportion of the variation in the salary series is explained by the regression line (and therefore how much cannot be explained by the regression line alone).] d) A new star emerges on your team with 30 goals and 60 points in 2022-23. [You assume that there are no restrictions on the player’s salary.] Using your regression or trend line results, predict what this player’s salary will be. e) Estimate the impact on salary in percentage terms of (i) goals and (ii) points for forwards on your team. [Hint: You can use the @log function in Excel to create a log(salaries) series in Excel to estimate percentage impact using regression or the linear trendline.]
