Excel LADZ
patreon
Excel LADZ - Weekly Update: Tuesday, 1 August
🕑 Added 2023-08-01 08:17:56 +0000 UTCG’day lads, I’m very excited to announce that the Excel LADZ 2023 Women’s World Cup Model will be published onto YouTube on Friday, the 4th of August! As mentioned in last week’s weekly update, the model takes into account a nation’s FIFA World Ranking, and then uses a formula to simulate the knockout stages.
English Premier League 2023-24 Season Model
In the next coming days, I’ll be releasing a model onto Patreon (the video to debut on YouTube before the 10th of August) that simulates the entirety of the 2023-24 EPL Season. Below is an explanation of the process I’ll be using to do this:
- Compile the ATT & DEF Ratings for every team.
- The teams that played in the 2022-23 EPL Season already have an existing Attack and Defence Rating - I’ll be using those figures for the model.
- Newly promoted teams (i.e. Sheffield United, Burnley and Luton Town) obviously do not have a 2022-23 ATT & DEF EPL Rating. However, they do have a 2022-23 ATT & DEF Championship Rating. Through historical data, I’ll convert these ratings to the EPL equivalent. Put simply, I’ll measure how well newly promoted teams have done in the past, and then obtain a formula to estimate a team’s expected ATT & DEF EPL Ratings based off of their Championship performance in the previous year.
- Scale these ratings to account for uncertainty.
- The scaling process involves revert a team’s calculated ATT & DEF Rating a certain percentage towards the mean. This is to “even” the playing field, and account for variables such as injuries or roster changes that may have happened in the off-season. For example, a very popular statistics website, FiveThirtyEight, reverts every basketball team 25% towards the mean rating (1) at the start of every season. I’ll be using a similar figure for my EPL Model.
- Simulate scores for every match in the season, using the reverted ATT & DEF Ratings.
- This process is relatively simple, and involves using the Excel LADZ Expected Goal calculation (Team’s ATT * Opposition’s DEF * League Average Goals), and then simulating this figure using the Binomial Distribution (BINOM.INV Function). Do this for every fixture in the 38 round season.
- Compile a league table from this simulated season.
- This table would use functions such as SUMIF and COUNTIF to sort teams by their number of Points Scored, then their Goal Differential, then their Goals Scored.
- Simulate the league table itself - 10,000 times.
- Using a Data Table in What-If Analysis, we are able to simulate the entirety of the season over 10,000 times. Beware, the more simulations performed, the slower your spreadsheet will become (as Excel is obviously doing heaps of calculations).
- Compile/Analyse the results
- After the 10,000 seasons have been simulated, simple formulas such as COUNTIF can be used to grab a team’s probability of finishing 1st, 2nd, etc, as well as their probability of being relegated or finishing in the Top 4/6.
NBA In-Season Tournament Model - Part 2
As you’ll have seen lads, Part 1 of the NBA In-Season Model has been published to YouTube. Unfortunately, there has been a few filming & uploading issues with Part 2. However, I’m hoping this video will be up very soon.
A reminder, the file for the NBA In-Season Tournament is readily available for download on the Patreon page!
As always lads, if you have anything to ask/say, don’t hesitate! You can write a message through Patreon or Discord! I look forward to talking with you!
