Home > NFL Power Rankings > Overview: Excel Solver-Based NFL Rankings

Overview: Excel Solver-Based NFL Rankings

Example-NFLTeamRankings

In the Fall of 2014, my term project for NYU’s Decision Models course was a NFL Power Ranking spreadsheet that ranked NFL teams based on scoring.  This is not a new theory – it is borrowed heavily from Wayne Winston’s Microsoft Excel 2013 Data Analysis and Business Modeling team rankings chapter.

The theory is based around Home Team Advantage.  If two teams are even, the Home Team will have a 3-Point Advantage.  This is normally built into a betting spread.  There are three outcomes once a game has been played:

  1. If two teams play each other and the home team wins the game by 3, then the teams are assumed to be even.
  2. If two teams play each other and the home team wins by more than 3, then the home team is a better team than the away team.
  3. If two teams play each other and the away team wins, then the away team is better than the home team.

 

When we look at a series of games, we can rank all 32 NFL teams and update that expected spread.  For example, in NFL Week 6 (2015), my Excel sheet calculated that the Jets had a 11.94 expected spread against the Redskins.  The actual final score was 34-20 (a difference of 14).  This means that the rankings between these two teams is actually further apart than what the model thinks.  In the following week, the rankings will demonstrate this.  I use the GRG Nonlinear Solving Method to minimize the total square differential between the actual score differentiation and my calculated expected differentiation.

The other fun thing about this ranking system is that it allows me to predict interesting things about the NFL.  With 2013 data, I was able to correctly predict 10 out of the 12 (83%) playoff teams.  Although the Las Vegas official spread of the Seahawks/Broncos was -2 in favor of Denver, I had the Seahawks favored – and of course correctly predicted to win.

In December of 2014 I was able to use my model to predict the NFL Postseason, and I correctly predicted that the New England Patriots would defeat the Seattle Seahawks.  My calculated spread was -5.897 in favor of the Patriots.

After every week, I will post my weekly rankings and predictions for the following week, as well as my prediction results from the previous weeks.

Updates will be posted on Tuesdays.

Categories: NFL Power Rankings Tags:
  1. No comments yet.
  1. No trackbacks yet.