Baseball There are 30 Major League Baseball (MLB) teams. The table on the Baseball worksheet lists the 2019 and 1990 payroll and win totals for each team (notice that four teams were added after the 1990 season). Some MLB fans complain because the league does little to regulate the amount of money teams pay for salaries. These fans argue that the teams that spend the most money will win the most games. This would put teams from small markets (teams that earn less revenue) at a disadvantage. Complete the tasks to analyze if small market teams are at a disadvantage. |n cell L4), use the COUNT function to calculate the number A of MLB teams in 1990. 0 Reference range E4:E33 ] in your calculation. E. In cell 5), use the COUNT function to calculate the number A of MLB teams in 2019. 0 Reference range D4:D33 ] in your calculation. E. In cell ), use the AVERAGE function to calculate the A average salary for the teams in 1990. E6) In cell I10], use the AVERAGE function to calculate the A average salary for the teams in 2019. E. In cell I12 I, use the STDEV.P function to calculate the A standard deviation for wins in 1990. [E@ In cell I14 I, use the STDEV.P function to calculate the A standard deviation for wins in 2019. EG) Based on your standard deviation calculations, were teams A more similar in terms of wins in 1990 or in 2019? a. Recall that standard deviation is a measure of how similar items in a list are (large standard deviations imply less similarity). b. Select either 1990 or 2019 from the drop-down list in cell I16]. E. In cell I18], use the CORREL function to determine if there is A a relationship between the number of wins a team earned in 2019 and the and the size of the team's salary. ES) ln cell 121 I, use the CORREL function to determine if there is A a relationship between the number of wins a team earned in 1990 and the size of the team's salary. E6) 2.10 ) Is the relationship between wins and salary stronger in i 1990 or in 2019? . Select 1990 or 2019 using the drop-down list in cell 124). 2.11 ) In cell (27), use the FORECAST. LINEAR function to determine how many games a team could expect to win in 2019 if the team's salary was $150,000,000. . Reference the salary amount in cell 128) and the appropriate ranges in the Major League Baseball Salaries and Wins by Team table as arguments for your function. 2.12 ) In cell (31), use the CONFIDENCE.NORM function to determine the 95% confidence interval for wins in 2019. a. Use 0.05 as the alpha level in your analysis. b. Reference the appropriate calculations for size and standard deviation as the arguments for your function. 2.13 ) In cell 133), calculate the upper limit for the 95% confidence interval for your win prediction for a team paying $150,000,000 in salaries in the year 2019. . Reference your prediction in cell (27 ) and the confidence interval calculation in cell 131 ) in your formula. = 3In cell I35 I, calculate the lower limit for the 95% confidence A interval for your win prediction for a team paying $150,000,000 in salaries in the year 2019. 0 Reference your prediction in cell |27 I and the confidence interval calculation in cell l3'l lin your formula. F K A B C D E G H M Major League Baseball Salaries and Wins by Team Statistical Analysis DIVISION TEAM 2019 PAYROLL 1990 PAYROLL 2019 WINS 1990 Wins Response Task AL East Baltimore Orioles $73,370,109 $10,037,084 54 76 Number of MLB Teams in 1990 AL East Boston Red Sox $229,196,106 $20,983,333 84 88 AL East New York Yankees $223,019,037 $20,991,318 103 67 Number of MLB teams in 2019 AL East Tampa Bay Rays $64,178,722 NA 96 NA $18,486,834 67 86 Average salary for the teams in 1990. 00 AL East Toronto Blue Jays $111,371,067 9 AL Central Chicago White Sox $91,371,201 $9,496,238 72 94 10 AL Central Cleveland Indians $107,693,747 $15,152,000 93 77 Average salary for the teams in 2019. 11 AL Central Detroit Tigers $114,631,137 $18,092,238 47 79 12 AL Central Los Angeles Angels $161,270,385 $21,870,000 72 80 Population standard deviation for wins 1990. 13 AL Central Minnesota Twins $125,256,003 $15,106,000 101 74 14 AL West Kansas City Royals $104,773,003 $23,873,745 59 75 Population standard deviation for wins in 2019. 15 AL West Los Angeles Dodgers $207,000,814 $21,618,704 106 86 16 AL West Oakland Athletics $93,394,531 $19,987,501 97 103 Were teams more similar regarding wins in 1990 or 2019? 17 AL West Seattle Mariners $144,391,293 $12,841,667 68 77 18 AL West Texas Rangers $148,538,766 $15,104,372 78 83 Correlation between the number of wins a team earned in 19 NL East Atlanta Braves $143,947,963 $13,328,334 97 65 2019 and the amount teams' salary amount. 20 NL East Houston Astros $168,804,925 $18,830,000 107 75 21 NL East New York Mets $146,335,812 $22,418,834 86 91 Correlation between the number of wins a team earned in 22 NL East Philadelphia Phillies $160,192,244 $13,953,667 81 77 1990 and the amount teams' salary amount. 23 NL East Washington Nationals* $172,307,808 $16,656,388 93 85 24 NL Central Chicago Cubs $221,590,085 $14,496,000 84 77 Is the relationship between wins and salary stronger in 1990 25 NL Central Cincinnati Reds $128,391,569 $14,769,500 75 91 or 2019? 26 NL Central Milwaukee Brewers $135,889,019 $20,019,167 74 27 NL Central Pittsburgh Pirates $72,731,474 $15,656,000 59 95 How many wins could a team expect to earn in 2019 if they 28 NL Central St. Louis Cardinals $174,317,164 $20,923,334 91 70 $150,000,000 paid $150,000,000 in salary? 29 NL West Arizona Diamondbacks $118,927,905 NA 85 NA 30 NL West Colorado Rockies $157,162,629 NA 71 NA 31 NL West Florida Marlins $75,596,271 NA 57 NA What is the 95% confidence interval for wins in 2019? 32 NL West San Diego Padres $104,254,790 $18,588,334 70 75 33 NL West San Francisco Giants $178,582,126 $20,942,333 77 85 Upper 95% confidence interval level. 34 * The Washington Nationals were the Montreal Expos in 1990 35 NA = The team did not exist in 1990 Lower 95% confidence interval level. 36 37 38 39