Excel LADZ
patreon
Building an NFL Model - Part 2 | Overtime & Simulations
🕑 Added 2024-09-16 08:07:04 +0000 UTCG’day, lads! In this second article, I’ll explain how to resolve ties using a simplified version of overtime and how to simulate an entire NFL game thousands of times to generate a distribution of outcomes.
If you haven’t already, download the NFL Model for daily analysis and join the most active sports modelling community on YouTube.
Download the ‘Excel LADZ - 2024 NFL Model’: https://www.patreon.com/posts/excel-ladz-2024-111333965
Overtime
In the previous article, the LET function generated both an expected score and a simulated score. However, it’s possible for the simulated score to result in a tie, such as 14-14, since it only accounts for four 15-minute quarters. In actual NFL games, tied scores at the end of regulation time lead to overtime.
So, I’ll keep the expected score calculation in cells I1 and I3 and create a new worksheet called “Simulations”. In this new sheet, I’ll simulate a single game. If the game ends in a tie, it will go into overtime.
Unlike soccer, where overtime is simply about scoring the most goals, NFL overtime follows different rules. In the 2024 NFL season, if the game is tied at the end of regulation, a 10-minute overtime period begins. Each team is guaranteed one possession unless the team receiving the kickoff scores a touchdown on their first drive. If both teams have had possession and the game is still tied, the next score wins. However, if no one scores during overtime, the game ends in a tie for regular season games.
To accurately forecast the outcome of overtime, a model would need to simulate each play in a drive and its outcome. However, because overtime is a brief and relatively rare part of the game, its impact on the Moneyline, Spread and Total Points markets is minimal, even when one team has a significant advantage. Therefore, I decided to standardise overtime for each match, giving each team three drives to score. The game finishes once a team outscores the other or after three scoreless drives from both teams.
In line with the 2024 NFL overtime rules, the model simulates a situation where both teams are guaranteed one possession unless a touchdown is scored on the first drive. If neither team outscores the other after their initial drives, the model allows for subsequent possessions where the next score wins. This method simulates scenarios such as one team kicking a field goal on their first drive, followed by the other team getting a chance to tie or win, or both teams failing to score, resulting in a tie.
In the example above, the Browns and Jaguars were tied 10-10 at the end of regulation time. The Browns won the game 13-10 with a field goal on their second overtime drive. The table preceding this calculation lists all the possible outcomes of a drive and their likelihood during the 2023 NFL season.
For simplification, I’ve adjusted the outcomes so that each team has the following probabilities of ending one of their three drives:
40% chance of punting
25% chance of scoring a touchdown
15% chance of kicking a field goal
19.75% chance of losing possession (turnover)
0.25% chance of scoring a safety
Since both teams have the same probabilities and the team that starts with the ball is randomised, each team has a 50% chance of winning in overtime.
Simulations
With the final scores calculated in cells J33 and K33, we can now simulate an entire NFL match. To the left of the “Simulations” worksheet, I’ll create a Data Table where the model will run 5,000 simulations of the match between the Browns and the Jaguars. This is done by running a single simulation in cells B2 and C2, selecting the range A2:C5002, going into the ‘Data’ tab, selecting ‘What-If-Analysis’, then clicking on ‘Data Table…’, then entering a ‘Column input cell’ outside of the dataset (e.g. E14), and finally, pressing OK.
In the first simulation, the Browns won 38-17, and in the second, the Jaguars won 30-13. These simulations continue through 5,000 iterations, ending on Row 5002. With such a large number of simulations, the match outcomes form a distribution. To find the probability of the Browns beating the Jaguars, I’ll count how many times they won and divide that by the total number of trials, 5,000. This method is known as experimental probability.
This will be covered in more detail in Article 3 of the series, so be sure to check it out when it’s released. To download the 2024 NFL Model, join our community on Discord, and access all the other benefits of sports modelling in Excel, become an Official Excel LAD today!
