Since the early 1970s, Jeff Sagarin has been publishing sports team ratings. For most sports, including the NFL, his ratings are calculated so that the difference between two opponent's ratings, plus a home field adjustment, forecast a game's point spread. His ratings are widely recognized as some of the best around. They can be found every week of the NFL season on the USA Today site. Sagarin has never published his exact algorithms, but we can easily build a very good facsimile.
Excel has a powerful tool called "Solver." It's one of those thousand or so features that Microsoft packs into its Office products that no one ever knows about. In fact, you do don't even see it on the Tools menu until you enable it from the "Tools|Add Ins..." command.
If you go to Microsoft's on-line help site for Solver, the example problem provided is an exercise estimating point spreads for NFL games. The sample spreadsheet is for all the game scores from the 2002 season.
Basically all you do is create a table of ratings for each team. The ratings don't have to mean anything yet. For now they can be your best guess, or all ones, or anything. Solver will calculate them later. Then for each game, you calculate what the ratings suggest should be the point spread. The ratings are intended to work just like Jeff Sagarin's ratings. If team A's rating is 5 and team B's rating is 8, then when team A plays team B the point spread should be 3 in favor of team B. Factoring in a league-wide value for home field advantage, say 3, and the spread becomes 6 if team B is at home.
Next, using the LOOKUP function to grab the ratings from the table, you calculate the error between the expected spread and the actual result for each game. Square the error (as every good statistician would). In a cell, sum all the squared errors for all the games in the season. In another cell, enter a point value for home field advantage--3 points is a good initial guess. Solver takes over from here.
In the Solver dialog box, you tell it to minimize the value in the cell for the sum of squared errors. Then you tell it to do so by varying the values in the table for the team ratings and the cell for the home field advantage. (You can also add in a constraint that says the average for all the teams' ratings should be zero, so that good teams will have positive ratings and poor teams will have negative ratings.)
Solver will compute the team ratings necessary to best fit the actual point spreads. And now you have your very own homemade Sagarin ratings.
I noticed that Sagarin's average rating is 20 instead of 0, which makes sense because the average NFL score is about 20 points. So I altered the Solver constraint accordingly. For the 2007 season, including the playoffs, the homemade ratings were nearly identical to Sagarin's.
Comparison of Sagarin and MS Solver Team Ratings for 2007
The differences beween Sagarin's and our homemade ratings may come from the method of solving. Solver uses a "brute force" numerical iteration method, and Sagarin's method is unknown. Sagarin may also weight recent games heavier. Notice how the difference in the Giants' rating is one of the more significant. The Giants finished the 2007 season on quite a win streak.
Doug Drinen of Pro-Football-Reference.com discusses a very similar method for ranking teams based on margin of victory which he calls the Simple Rating System (SRS). His post includes a good discussion on the advantages and disadvantages of a pure margin of victory ranking system.
Sagarin actually uses two different systems. One is called Pure Points, which is based solely on point differential. This is the system which the method discussed above mimics. His other method is called Elo Chess, which considers only wins and losses, and ignores points. This system is based on a method devised to rate chess players by Arpad Elo, a physics professor and master chess player. In the next post I'll demonstrate how to mimic the Elo ratings.