ADP-adjusted Dynamic Value Based Drafting

I’ve created an OpenOffice spreadsheet to assist in fantasy football drafting, and am releasing it at the bottom of this post for everyone to use. If you’re not familiar with “value based drafting” or its “dynamic” variant, here are a few good links explaining the basics. Essentially, the method uses players’ projected stats to determine how much value is available at any given pick, compared to baseline projections of bench-level players and what other owners in the draft are expected to do.

It took several steps to complete this ADP-adjusted Dynamic Value Based Drafting (or, ADP-dVBD) spreadsheet. First, projections on players were collected from preferred sources such as Yahoo, FFToolbox, and FFToday. Second, average draft positions (ADPs) for a site I was drafting on were recorded for each player. In this case, I’m using recent point-per-reception (PPR) ADPs for 12-man drafts from MyFantasyLeague.com. Players are listed by position on different sheets and ordered by ADP. Then, the actual ADP-adapted dVBD calculations had to be created inside the spreadsheet. Since the order of players by ADP does not coincide with the order of players by projected fantasy points, this required a multi-step process. The dVBD offsets (or, “opportunity costs”) are calculated for players, and then the highest (and hence, relevant) opportunity cost at each position among players expected to be taken is found. There is a set of input cells for the drafter to estimate how many of each position (RB, WR, etc) will be taken before his next pick, so that the opportunity costs may be calculated. Lastly, there’s a set of cells to record the positions already taken on your roster, so that opportunity costs are reduced for bench players.

The minimal procedure to use the spreadsheet during a draft as picks are made is:

Delete the row (not “hide the row”, not “delete the content”) of a player when he is drafted.

When it’s your pick, enter the estimated number of players at each position that will be taken before your next pick comes around.

The recommended ADP-dVBD position to pick is the one with highest opportunity cost. Player names are given for the best remaining player at each position.

Make your pick, and update your roster listing with the new number of players at each position.

Continue!

Note, you’ll need to enter what draft slot you’re drafting out of in the appropriate cell, before the draft starts. It’s also easier to make predictions about how many players at each position will be taken before your next pick if you also update the positional rosters of other teams as they pick, but that’s not required.

It sounds complicated, but is surprisingly easy to use once you open it up and have a look. If you want to use it for your league, you’ll need to update the ADPs, projected fantasy points, and “need multipliers” to match your league’s site and scoring/roster settings. It can also be extended to use additional roster slots, such as IDPs or flex positions. However, those things are left as an exercise for the user. I also left a blank column for player notes, since the spreadsheet is really great for that. I have all sorts of color coded notes in my personal version.

I learned quite a bit about OpenOffice spreadsheets while creating all this. The OFFSET and INDIRECT functions were of particular value. If you notice any errors or have a particularly slick improvement or optimization to show off, please let me know.

Good luck, and remember this is just a tool. Projections will inevitably be wrong. ADP-dVBD will be wrong. There is no perfect fantasy football drafting strategy, and it’s more art than science. So if the spreadsheet tells you to pick player A, but player B is also right there and you really want to take him instead — go for it and have fun!

DOWNLOAD the spreadsheet. OpenOffice 3.0 or higher required to use. OpenOffice is free, and available for Windows, Mac OS X, and Linux. Filetype is .ODT, although obviously this was initially created as an .ODS (spreadsheet). OpenOffice should automatically open it with the proper application. File updated to version 1.2 (bugfix: one-off on dVBD offset player recommendations) on Saturday, September 5, 2009. It is recommended that everyone upgrades to 1.2.

Also, some shout outs to fantasy football or football sites I enjoy. I already linked Advanced NFL Stats, Football Guys, FFToolbox, FFToday, and Yahoo. But, other good sites include FFLibrarian, Pro Football Reference, Football Outsiders, Pro Football Talk’s Rumor Mill, Rotoworld, Rototimes, FFCalc, and ESPN. Lastly, a special mention for Fantasy Sharks and their Main Tank forum which I often visit.