This is a homework assignment designed toreinforce the teaching of the current chapter. Assignment submissionisone week from dateassigned.
File needed:Case #3 – Tutorial 5.xlsx
NBA Hoops Blog Jeff DeMarco isa sports enthusiast. Several years ago, he began writing abasketball blog called NBA Hoops thatprovides information, opinions, and analysis related to teams,players, and games in the NationalBasketball Association (NBA). To help him more easily respond tocomments from his readers, he created anExcel table that tracks teams, positions, and salaries for eachplayer in the league. He asks you toanalyze this data. Complete the following:
1. In the Documentationworksheet, enter your name and the date.2. In the Players worksheet,create an Excel table named NBAPlayers. Format the Salarycolumn with the Accounting format andno decimal places. Format the NBAPlayers table with thetable style of yourchoice.3. Make a copy of thePlayers worksheet, and then rename the copied worksheet as SortPosition. Sort the NBAPlayerstablein ascending order by position, then in descending order bysalary.4. Use conditionalformatting to apply a yellow fill with dark yellow text tohighlight all players with a salary greater than$10,000,000.5. Make a copy of thePlayers worksheet, and then rename the copied worksheet as FilterTeam.6. Insert a slicer to filterby Team. Place the slicer to the right of the NBAPlayerstable.Select a slicer style that matches the styleyou used to format the NBAPlayers table. Resize theslicer’s height and width to improve itsappearance.7. Use the slicer to filterthe NBAPlayers table to displayall players on the NY Knicks and Miami Heatteams.8. Expand the filter todisplay NY Knicks and Miami Heat players earning more than$5,000,000. Sort the filtered table inascending order by salary.9. Make a copy of thePlayers worksheet, and then rename the copied worksheet as FilterTop 15%. Filter the NBAPlayerstableto display players whose salaries are in the top 15 percent. Sortthedata by Salary in descendingorder.10. Use the Total row toinclude the average salary at the bottom of the table, and thenchange the Total row label to Average. Addthe Count of the Team column to the Total row. Remove theentryin the Division column of the Total row.11. Make a copy of thePlayers worksheet, and then rename the copied worksheet asSubtotals. Use the Subtotal command todisplay the total salary for each team in the Salarycolumn.12. Based on the data in thePlayers worksheet, create a PivotTable in a new worksheet thattotals salaries by team and position.Place the Position field in the Columns area. Rename theworksheet as PivotTable TeamPosition. Format the salaries in the PivotTable with theAccounting format and no decimal places.Resize the columns as needed to display all thesalaries.13. Create a Division slicerfor the PivotTable. Resize the slicer object and buttons as needed,and then select a slicer style thatmatches the PivotTable. Use the slicer to filter the PivotTabletodisplay teams from theAtlantic, Central, and Southeast divisions.14. Based on the data in thePlayer Salary worksheet, create a PivotTable that calculates thenumber and average salaries byposition in a new worksheet. Format the average salaries, changethe label above the averagesalaries to Avg Salary, and then changethe label above the count to Number. Resize columns asneeded to display all cell contents. Rename the worksheet asPivotTable AvgSal.15. Save the workbook,and submit to ilearn.
NBA Hoops Blog Notes Data T Text Text 8 Field Text Guard], PG LA Lakers, Miami Heat, NY Knicks, Oklahoma Thunder, San Antonio Spurs Text Show transcribed image text NBA Hoops Blog Notes Data T Text Text 8 Field Text Guard], PG LA Lakers, Miami Heat, NY Knicks, Oklahoma Thunder, San Antonio Spurs Text