Excel LADZ
patreon
Excel LADZ - Weekly Update Friday, 11 August
🕑 Added 2023-08-11 12:36:33 +0000 UTCG’day lads, as you will have seen, the Excel LADZ - EPL 2023-24 Simulator has been published on the Patreon page! The video explaining how to build it will hopefully be uploaded in the next few days. Below are the steps I took to create the EPL Model:
- I collected the 2022-23 EPL stats, from the end of season table. From here, I worked out the ATT & DEF Strength of the Top 17 Teams (excluding those relegated). This is found on the ‘2022-23 EPL Results’ worksheet.
- To estimate the ATT & DEF strength of the newly promoted teams Burnley, Luton Town and Sheffield United, I compared the performance of newly promoted teams in the last 10 years. Of these 30 teams promoted, I collected their ATT & DEF Ratings in their Championship promotion season, as well as their subsequent Premier League ATT & DEF Rating. This data is found on the ‘Historical Championship Data’ worksheet.
- I then used a linear regression analysis to come up with a predictive equation for the ATT & DEF Ratings. By substituting a team’s ATT & DEF strength in their Championship season, the formula will be able to estimate their equivalent strength in the Premier League (which will obviously be relatively lower). This analysis can be found on the ‘ATT Equation’ and ‘DEF Equation’ worksheets.
Below is the equation for the ATT Rating. There is an upwards slope in the observed values, as a team with a higher Championship ATT Rating commonly achieves a relatively higher EPL ATT Rating than a Championship team with a lower ATT Rating. The line, represented by the equation, is the line of best fit between these observed values (i.e. the data from the last 10 years).
- After putting Burnley, Luton Town and Sheffield United’s Championship Ratings into the ATT & DEF Ratings formula, an ATT & DEF Rating can be obtained for all 20 teams.
This rating is then reverted 25% back to the mean, to account for the uncertainty surrounding the start of the season (e.g. squad changes, injuries, etc). For example, a team with a 2022-23 ATT Rating of 1.80, will have this adjusted to a 1.60. These ratings are located within the ‘Team Strength’ worksheet of the model.
- The full 2023-24 EPL Season Fixtures were copied into the ‘Fixtures’ worksheet. Using the ATT & DEF Ratings, as well as assuming the average amount of goals scored in a game by a team is 1.4263 goals (average for EPL 2022-23 season), every match was able to be simulated using the BINOM.INV (Binomial Inverse) function.
While it sounds complicated, it is simply randomly distributing an expected score for each team in a game. For example, Man City will have a higher Expected Goals total (ATT Rating * DEF Rating * Average goals per game) than Everton in a matchup. However, Everton have a certain likelihood of winning, that when simulating the scores, is possible to achieve.
- After every game has been simulated in the ‘Fixtures’ worksheet, a single season table can be compiled within the ‘Table’ worksheet.
- I then created a ‘Simulations’ worksheet to run the season table simulation 1,000 times. This was done using a Data Table within ‘What-If Analysis’.
- I collected the simulations in the ‘Analysis’ worksheet. Here, I’ve totalled a team’s probability of finishing in positions 1-20, as worked out by the 1,000 simulations in the ‘Simulations’ worksheet.
As always, if you have any questions, let me know here or on Discord!
I look forward to hearing from you lads!
