Analyzing NBA Salaries Team Performance You are tasked with analyzing NBA Player Salary data for the 2017- season as well as NBA team performance 2016-1 season the QMB210N2- file The file consists of the two worksheets describedbelow . PlayerSalories17-18 Provides contract data for 555 unique players for the 2017- 18 season obtained from There 565 player contract rows because ten players are with two teams each. The sheet alsocontains lookup table for for the 2016-17 based on data obtained from 2017.html Miscellaneous Part Data Preparation In the PlayerSalaries1] sheet 1) Delete following columns: 2018- 19, 2019-20, 2020-21, 2021-22, 2012-23, Signed Using and Guaranteed 2) Insert blank column the right of the ContractTm column and label i ContractTn Name Populate column with the full name of the team using the lookup table found in the sheet and the /LOOKUP function Do quick check seel the column popul ated properly by turning filters and browsir through the columi see ifall the teamnames are there. 3) Create a new column next the 2017 -18Salory column nd label i z-score. Calculate the -score reach salary using Excel's STANDARDIZE function Make thecelli formatter correctly i.e., the numbers are displayed with2 decimals and In the TeamStats16 17sheet 4) Playoff teams indicated asterisk (*)inthe Insert blank column right of the Team column and label it Ployoff Team Eachteam with asterisk shoul receive the value "Yes' inthe newly created Playoff Team column Provide the value "No" for the teams that did not make playoffs 5) Insert blank column the right fthe Losses column and label Win% Use formula to compute the in percentage (as decimal, e.g., .63)for eachteam Part 2 Data Analysis Using the PlayerSalaries1 'worksheet 6) Compute the descriptive s statistics for the 2017 -18 Salary variable which should displayed a worksheet labeled -SalaryDesc riptives. 7) Create filter the z-score column and list only those players whose salaries are outliers Le whose salary z-score exceeds 3. Copy these playen along with their data into new worksheet labeled P-SalaryOutliers 8) Remove the filter from 7)so allthe player salaries shown i.e. 565 rows player contract data). a) Create pivot table new sheet show average salary per team using the full team name. The average salaries should shown with the sign, and without decimal places. Label this newworksheet -AvgSalaryByTear b) Copy the Average 2016 Salary column from the TeamStats16- -17 sheet into the 3- yTeam sheet from 8)a). Make sur teams match properly Create chart from Chapter your text book effectively compare average team salaries for the 2016- 17 and 2017- seasons 9) Construct 95% confidence interval estimate for the population characteristics for the variable 2017 Salary. The confidence interval results will appear on new worksheet which should be renamed to I-ConfidencelntSalary. 10) Provide one-tail (upper)hypothesis test determine lithe average salary of the Golden State Warriors players greater Hop Use 0.05 level ofsignificance The will worksheet which should be named Using the TeamStats16 sheet 11) Compute the descriptive statistics (mean, median, mode, variance, standard deviation and kurtosis, range, max, count) the following variables: Average 2016- 17Sclary, ORtg, DRtg PTS, TotalPointsfrom2P, antsfrom3P, and TotalPaintsfromFT The result should appear on a new worksheet that voulabel 6-TeamDescriptives a) Organize the results the worksheet such that the name the statistics (mean median, mode, appears column and the values of the statistic for each variable appear in columns B b) values of all statistics must be in standard numeric format with two decimal places displayed. c) Ensurethat the columns are wide enough to display all digits for your statistica values. 12) Create pivot table that shows average Age, Average 2016 17Salory, ORta. DRtq, PTS, TotalPoi ntsfromFT teams plavoffs versus those that did not. The results should appear on : new worksheet labeled 7 PlayOffTeams. 13) Create pivot table compare. Averoge 2016- 17Salan Plavoff Team and Conference riables. Salary averages should shown with the sign, and without decimal places. Pick chart from Chapter your text book effectively visualize the pivot table data Make sure your chart shows data labels. Be sure follow the guidance for developing good graphs and tables with propertitles and labels discussed ir class Your table and chart will appear onnew worksheets that should labeled SalaryComparisons 14) Analyze the normality Age, Averoge 2016 17Salary, and This can be done by analyzing its box plot or evaluating the proximity ariables mean versus median you create plots, plots appear ksheets rename the worksheets to9- AgeNormality, 9-Avg16 -17SalNormality, 9-PtsFrom3PNormality. 15) Copy TotalF intsfrom3Pand Win%into anew sheet. Computi correlation betwe thes two variables create graph to show the relationship between these variables Rename sheet Correl3P- Win%. 16) Conduct simple linear regression determine Fthe Average 2016 -17 Solary i significant predictor of the Win% Provide the regression line. The regression equation scatterolo will appear on new worksheets Rename these worksheets -Regression and 11- Scatterplot 17) Reorder spreadsheet such that they appear the following order (from lef right): TeamStats16 SalaryOutilers AvgSalaryByTeam 4-ConfidenceIntSalo 5-HypothesisSalary ayOffTeams 8-Salaro 9-Avg16 9-PtsFrom3PNormality Correl3P- Win% page numbers and use 11-point header the document, and save Project F17.dock of your analysis, and the types of found Chapter in your 15)into your report making sure to include summarizes what being illustrated Briefly interpret each table/graph findings Interpret results fov from 6), 9), 10), 11) 14). and 16). Interprel the coefficients and the meaning of R-squared nalysis from Task 16). the Average 2016- Salary a statistically significant predictor ofthe Win%?