Microsoft Excel Tutorial: Modeling the rest of the MLS Season 100,000 times.
Which teams are still in the hunt for the MLS Supporters Shield?
Update from September 25, 2023: Over the weekend, Cinci won their match. This leaves only Cinci, New England, Orlando City, and Philly in the hunt. Cinci's percentage has improved to 99.292%. All that Cinci has to do to wrap up the Supporters Shield is win one match or draw one match and have New England and Philly draw or lose.
To download this workbook: www.mrexcel.com/youtube/1VLFg...
Welcome to episode 2626 of MrExcel's netcast, where we tackle the burning question of who is still in the hunt for the MLS Supporters' Shield as of September 22, 2023. In this video, we will use Excel to perform Monte Carlo analysis and simulate the remaining matches to determine the potential winners of the Shield.
As of September 23rd, 2023, Cincinnati has been the clear favorite for the Supporters' Shield, with a 15-point lead since June or July. However, their recent winless streak in September has allowed other teams to close the gap. In this video, we will explore the possible outcomes if Cincinnati were to lose all remaining matches and determine which teams have a chance to surpass their current 59 points.
Using the RAND function in Excel, we will simulate the remaining matches and calculate the points gained by each team. We will also utilize the new VSTACK function to stack the home and away team points into a single array. Then, using the SUMIF function, we will determine the total points gained by each team and their final points tally.
Initially, I made a critical math error and calculated 39 matches left instead of 39 to the third power, which is an insanely large number. To overcome this, we will run the simulation 100,000 times using the SEQUENCE function and the data table feature in Excel. This will give us a better understanding of the possible outcomes and the percentage chance of each team winning the Supporters' Shield.
In the end, Cincinnati still has a 96.7% chance of winning the Shield, but there is a 1.3% chance for Orlando City to overtake them and a 0.3% chance for Philadelphia to win outright. There are also some interesting scenarios where tiebreakers may come into play, such as the game between New England and Philly on Decision Day.
Even if your team is not currently in the running, there is still a chance for them to win the Supporters' Shield. We could run this simulation another 100,000 times and have a completely different set of results. So, good luck to all the teams still in the hunt and thank you for watching this episode of MrExcel's netcast. Don't forget to Like, Subscribe, and Ring the Bell for more Excel tips and tricks. Feel free to leave any questions or comments down below. See you next time!
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
You can help my channel by clicking Like or commenting below: www.mrexcel.com/like-mrexcel-...
Table of Contents:
(00:00) MLS Supporters Shield Calculation Using Monte Carlo analysis in Excel
(00:22) Explanation of Supporters' Shield
(00:47) Teams still in the hunt in Eastern and Western Conferences
(01:07) Number of matches left and iterations for analysis
(01:21) Dates and teams for remaining matches
(01:37) Explanation of simulation using RAND function
(02:12) Calculation of points gained by remaining teams
(02:27) Use of VSTACK function
(02:53) Explanation of maximum points and tiebreakers
(03:13) Realization of large number of possible outcomes
(03:30) Explanation of running simulation 100,000 times
(04:17) Use of data table function in Excel
(05:10) Unique finishers for Supporters' Shield and percentage of times won
(06:01) Other potential outcomes and interesting scenarios
(06:26) Acknowledgement of Cincinnati's strong season
(06:44) Explanation of calculation options
(07:05) Final results and good luck to remaining teams
(07:26) Clicking Like really helps the algorithm
#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial #simulation #montecarlo
This video answers these common search terms:
how to perform monte carlo simulation excel
how to use monte carlo simulation in excel
can excel do monte carlo simulation
how to run a monte carlo simulation in excel
how to do a simulation in excel
how to do a monte carlo simulation in excel
how to program a montecarlo simulation in excel
what is simulation in excel
Buy Bill Jelen's latest Excel book: www.mrexcel.com/products/latest/
14 июл 2024
