ICT 9: Data Analysis and Technical Report

For this assignment, with a partner, you will determine whether NHL teams with larger salaries perform better than those with smaller salaries. If so which team performed the best given the amount of money it spent? This assignment is inspired by Moneyball, where the Oakland A’s of MLB assembled a competitive baseball team despite a disadvantaged revenue situation. There are two major parts to this assignment: data analysis and technical report. Data will be collected and analyzed using a spreadsheet, while results will be summarized in a formal document. Note: This will be an observational study. Unlike an experiment, there are no control groups.

Task 1: Setup

  1. Review the elements of a technical report, which is similar to a science lab report. Example: Formal Lab Report Guidelines. You will eventually be expected to include: a title page, objectives, materials, procedures, data and results, conclusion and discussion.
  2. Create a new spreadsheet and a new document in Google Drive.
  3. Share both the spreadsheet and the document with your partner and teacher.

Task 2: Data Analysis / Spreadsheet (5 marks)

  1. In the spreadsheet, create the following headers:
    • A1: Team
    • B1: Actual Pts
    • C1: Salary
    • D1: Pts per $1M
    • E1: Predicted Pts
    • F1: Difference
  2. Go to NHL 2013-2014 season standings and copy the Name and Points to column A and B respectively. There should be 30 teams, and therefore use 30 rows (31 rows including header).
  3. Go to NHL 2013-2014 team salary and type the teams’ respective salaries (Spending) into column C.
  4. Sort the table alphabetically by Team name in ascending order (A-Z).
  5. Calculate each team’s Pts per $1M using a formula in column D. What do you think these numbers mean?
  6. Calculate the total pts and salary across all NHL teams at the bottom of the spreadsheet in B31 and C31 . Next, calculate the league’s weighted average pts per $1M using the totals.
  7. Use the league average to project the number of pts, based on individual team salaries. Calculate the projection, using a formula, into column E.  What do you think these numbers mean?
  8. Calculate the difference between the actual pts and the predicted pts in column F.  What do you think these positive and negative numbers mean?
  9. Create a Scatter Plot based on the data (X-axis = salary, Y-axis = actual pts) on a separate sheet. scatterplot
  10. Create a bar graph based on the difference between actual and predicted Pts (X = team, Y = difference) on a separate sheet.barchart

Task 3: Technical Report / Document (25 marks)

  1. Review the elements of a lab report: Formal Lab Report Guidelines.
  2. In your report, you are expected to include:
    • title page (2 marks)
    • objective: what the purpose of the lab is. (2 marks)
    • materials (bullet list): excluding software, what resources you used for data collection and analysis (2 marks)
    • procedures (numbered list): how you collected the data and analyzed it. (4 marks)
    • data and results: what you collected and analyzed (in tables and graphs). (6 marks)
    • conclusion: what your results indicate based on your objective. (2 marks)
    • discussion: what your results mean (with specific examples). (2 marks)
  3. The format and the writing style should be similar to the sample lab report (5 marks). Photographs and screenshots are optional.

Due: TBA (end of tutorials)