Excel LADZ - March Madness Model (2024)

G’day lads,

In this explanation I’ll go through how I built the ‘Excel LADZ - March Madness Model’ using ELO Ratings.

ELO Ratings

In order to build a simulation model, you need to be able to define the strength of each team prior to the game starting. 

Usually this would involve measuring each team’s Attacking (ATT) and Defending (DEF) strength so that an xPoints/xGoals figure can be calculated. However, this can be difficult when the schedule for each club is significantly different. For example, an average ATT Rating in the English Premier League is better than a great ATT Rating in the MLS.

While there is the option of adjusting each team’s ATT & DEF Rating using a Strength of Schedule measure, often this is very difficult to calculate accurately.

That’s why I like using ELO Ratings for knockout competitions. A team’s ELO Rating is an overall measure of their strength. From Wikipedia, “After every game, the winning player takes points from the losing one. The difference between the ratings of the winner and loser determines the total number of points gained or lost after a game.”

ELO Ratings are great for forecasting Win/Loss scenarios - there is a formula to forecast the Win % of each player. Considering the ELO Rating for Team A (Ra) & Team B (Rb), the formula for Team A’s Win Probability is: 

Furthermore, ELO Ratings are often available publicly online for popular sports. In this model, I used the ELO Ratings from this website: https://www.warrennolan.com/basketball/2024/elo

I imported these ELO Ratings into Excel with a Power Query in the worksheet ‘ELO Ratings’. In order to refresh the ratings, all I’ve got to do is go into the ‘Data’ tab and select the icon ‘Refresh All’ - this will refresh the Power Query’s connection with the internet and update the ratings.

Tournament Simulation

The main goal of the model is to calculate each team’s probability of winning the whole tournament. In order to do that, a single simulation has to be run first. Then, the results of the tournament can be simulated over and over again (in this model 2,000 times) to get the probabilities.

In the ‘NCAA Bracket Simulation’ worksheet, each game in the First Round has been written out. Next to the match is the Win Probability for each side (using the ELO Win % Formula above). These figures are used to simulate a random decimal, which is then matched to the simulated ‘winner’ of the match that moves onto the next round. Consider the example below.

In cell E4, Connecticut is the winner in their simulated contest with Stetson. They moved onto the Second Round against FAU, who also had a ‘simulated win’ against Northwestern.

This process is done for every game, in every round, until a Tournament Winner is decided in cell N3.

Tournament Simulations & Analysis

Now that the tournament has been simulated once, I’ve used a Data Table to re-run this entire tournament 2,000 times. This has been done in the ‘Simulations’ worksheet.

I then analysed these simulations in the ‘Analysis’ worksheet. The final table gives the probability of each team being knocked out at each stage of the tournament.

Using the Model

The model is very easy to use! Consider the Connecticut vs Stetson game in the First Round.

As of right now, the game hasn’t been played yet. That’s why in cell E4 (see image above) the winning team that progresses to the Second Round has been simulated using a formula. Pretend Connecticut win this match, and we want to update the model to reflect this change in probabilities.

In this scenario, I would just write over the simulation formula in E4 with =B2 or the text ‘Connecticut’. This locks Connecticut into the Second Round, which the simulations and the ‘Analysis’ worksheet will reflect. On the other hand, it is now impossible for Stetson to progress past the First Round, as the model ‘forgets’ them.

Do this for every game once it has been played.

Note: Also, ELO Ratings will update in response to teams winning/losing. Remember to select ‘Refresh All’ so that these can be reflected in your model.

Thanks for reading lads! If you have any questions, don’t hesitate to ask in the comments or over Discord.