Last year, we demonstrated how someone can make a fantasy football draft spreadsheet to help make the best decision in each round of a traditional snake draft. While most leagues use snake draft, eventually you might be faced with a league where someone suggests an auction draft. They might cite something like this article to make their case. At first you might feel intimidated, but once you remember that every auction round is just a data driven decision, and that spreadsheets are great at solving data driven decisions, you can build something to give yourself the best chance to succeed!
Spreadsheet Inputs
- Projected season long point totals for each NFL player (similar to our previous post, we use the FantasyPros season projections)
- Number of teams (10 is standard)
- Amount of auction money allotted ($200 is pretty standard)
- Number of players at each position (1 quarterback, 3 wide receivers, 2 running backs, 1 tight end, 1 defense and 1 kicker)
Desired Spreadsheet Output
We want our spreadsheet to tell what the “fair value” of each player that comes up to auction is, based on which players have already been drafted and how much money has been spent so far.
Spreadsheet Solving
First, we set up our spreadsheet – by entering our league settings assumptions and putting in each player, their projected points and position rank:
Now the logic – how much should you pay for each player? How much are Aaron Rodgers’ 321 points worth in terms of auction dollars? We will use a similar value over replacement player methodology as we did in the snake draft spreadsheet. With 10 teams in the league and 1 QB per team, the 11th QB should be worth $0 as every team would already have a QB and not need to pay for another QB. So by purchasing Aaron Rodgers, you’d be getting 321 points minus Ryan Tannehill’s 265 points = 56 points above replacement value. We make the same calculation for the next 9 QBs, then do the same for every other position, and finally we sum up the total points above replacement and then divide by the total number of dollars available to get each player’s fair value
We add in a column the text combination of “Position&Rank” so that we can look up the point total for the replacement player with a VLOOKUP.
Then we add in formulas to calculate the points over replacement player. Here we use a max function so that if the player is worse than replacement, it returns 0. We also vlookup to bring in the point value of the replacement player from up top:
Now we add in formulas to calculate the total dollars available ($200/team x 10 teams) and the total points above replacement (sum column F), and divide to get the fair dollars per point. Then the fair value of each player is just their points above replacement multiplied by the fair dollars per point:
Here’s the Spreadsheet: Fantasy Football Auction Draft Excel Spreadsheet
And that’s pretty much it – we’ll leave it there, but an additional interesting exercise would be to add in a feature where you can enter how much a player was auctioned for, and then the spreadsheet would recalculate the value of all the remaining players using the remaining money pool and the remaining points above replacement. Another thought is to add a FLEX slot, although I think the easiest way to do this is just to add 3-4 positions to the rank of the replacement player for running back, tight end and wide receiver. Finally, the nice part about making your own spreadsheet instead of relying on a website is that you can adjust the values as you see fit, and can change things like making defenses and kickers worth $0 if you wish.
Here’s the spreadsheet with live updates: Fantasy Football Auction Draft Excel Spreadsheet with Live Value Updates