Sports Analysis Project by Fans for Fans

Design a first sheet to include title, date, authors and the team/player studied. Menu (do this step at the end): design a cool navigation bar with links to each sheet (Hyperlink > Place in This Document) and include it (copy and paste) on all sheets. Use text, shapes or pics for menu items. Example. Club/Player page: design an informative sheet about the team and main players or your player, for an individual sport. Add links to historical data and club and players websites; be sure to not show the web address but meaningful text for all links. Add pics, SmartArt, shapes and any other elements of your choice for a great design. Data pages: have one or more sheets with meaningful statistics on the team and its players or, for an individual sport, the player. Collect also meaningful data about the opponents. Provide links to all your sources. An example from NFL: one of the data sources can be NFL Statistics. You can look under categories like “by player”, “by team”, “by player position” for: game stats, passing, rushing, receiving, kicking, field goals, kick returns, punting, sacks, scoring, touchdowns, tackles, interceptions, total yards, etc. Format as Table(s) your data; add a Total Row with some meaningful statistical functions (e.g.: Min, Max, Average, Count, etc.) but do not repeat the calculations in the PivotTables below. Add conditional formatting. Lookup & Findings page: incorporate all of the following Excel functions, tools and elements (in a meaningful way for your data) into an interactive dashboard (some ideas here): o VLOOKUP (or HLOOKUP). Create one or more drop-down lists to retrieve statistics. Watch this video. Here is an example of using a lookup function to display player team and stats from another worksheet when player name is chosen from the drop-down list: o COUNTIF (or COUNTIFS). E.g.: you can see how many times in the last 25 years your team ranked first  What about the opponents? o SUMIF (or SUMIFS) or AVERAGEIF (or AVERAGEIFS). Here is an example of using SUMIF to determine total touchdowns for the NFL season to this point for teams entered by user in the table: Tip: You can also use historical data to cumulate results from many seasons. PivotTable pages: Create two or more PivotTables with two or more PivotCharts. Create an interactive dashboard (watch video from 7:11 to 12:44 and/or see this tutorial file): o Use at least two of the following functions/calculations for Values: Sum, Count, Average, Max, Min or % of Grand Totals. o Group if necessary. o Sort as desired. o Add special filters, like Top 5, Top 10, etc. o Add one Slicer or more. o Create a meaningful calculated field. o Choose appropriate chart types, titles, and format as desired. Salary page: create an interactive dashboard (or a Power View Report) with Salary Data (if salary data is not available, then use other relevant financial data). Provide link(s) to your source(s). Analyze through dynamic charts (based on a table or PivotTable): o Compare salary for your team vs. opponent team (or all other teams). o Compare salaries for your favorite player vs. other great players. o (Optional) You can also compare conferences or divisions (e.g. AFC vs. NFC). Personalized sheet: create a sheet of your choice and use some of the Excel tools and/or functions you liked the most. Apply them to same data or other related information. Embed a relevant YouTube video. Here are two methods: 1. Method 1. Embed the video in a spreadsheet. 2. Method 2. Embed the video in a UserForm. Speaker Notes to each sheet: add text boxes, shapes, or comments explaining your research and findings: o Keep in mind your “professional” audience. o What questions does your research answer? What are the most surprising findings? o Explain your choice of data and significance for the topic and Excel tools used. o Focus on Problem Solving and reveal to the audience your choice of Excel functions and tools. o “Making of your project” - give the audience “behind the scenes” access to the issues you encountered and the solving path you took to get to the result; as you are building your project, keep a written record of things you tried successfully and unsuccessfully. Add text/picture comments, shapes, WordArt, and graphics for an appealing and user-friendly workbook. Add any other enhancements and/or use any other Excel tools not mentioned here for an ideal project. Conclusions page: create a Summary sheet to synthesize your research findings; note that you will need to reference cells from other sheets. To combine all files your group worked on, visit this helpful link. Group sheets and add a meaningful picture in the header (Header & Footer Tools tab > Picture button) and the Sheet Name and Page Number “of” Number of Pages in the footer. Upload (one upload per group) under Final Project Assignment for credit. Important Guidelines (go to first page): Research and Contents o Thoroughly research your data. Do not add data not used in your analysis. Try to keep only meaningful data for your project: read the requirements and use data that makes the most sense. o Check all your data in (pivot) tables, (pivot) charts, check titles, labels, and annotations. o Add content that supports your ideas - this project gives you a lot of flexibility. o Use Spelling checker. Hyperlinks o Use meaningful text; do not display the web address. o Check your links, too. Functions o Use indicated functions in a meaningful way. Add more functions, as needed. o Use cell references, do not type in numbers, for a dynamic workbook. o Double check the results. PivotTables o Create meaningful PivotTables – you will need to collect appropriate data to answer questions. o Add appropriate calculations and functions. Check results. o Group for easy reading, where needed. o Filter, sort and format as needed and label properly. Pivot Charts and Excel Charts: o Be sure the data selection makes sense. o Keep it simple: a chart should be comprehensible and look professional. o Avoid redundant information. o Add titles, data labels, adjust axes as appropriate. o Use consistent styles and formatting features. o Add annotations if necessary. o Make any enhancements you think necessary. Design: o Design informative, user-friendly, appealing and functional interactive dashboard(s). o Use shapes, SmartArt, graphics, Table style, PivotTable style, menu links, etc. o Apply appropriate conditional formatting, filters, slicers, sort, etc. o Use cell styles and a theme of choice to provide your workbook with a uniform appearance. Annotations and Comments o Use text comments and picture comments, controlling their visibility - watch again this video. o You can also add text boxes, shapes or SmartArt graphics to visually convey the important points or to explain functions and/or Excel tools and charts. Final Look: o Group Sheets and check your document in Print Preview. o Adjust the worksheets to fit nicely on page; include the required headers and footers. o Use Print Titles (p. 274) if a worksheet displays information on multiple pages and you would like to see                                                                                                                                                                                                                            

Sample Solution