**Introduction**

Last year, I experimented with calculating the auction values/position rankings for fantasy football players. I developed a series of calculations and spreadsheets which I found to be quite helpful during my drafts. This year, I have a series of adjustments to my calculations based on my experiences last year.

The biggest changes that I have made for this year:

1) I customized my projections a bit more. Last year, I simply took the averages of several prominent fantasy football websites like Rotoworld, Yahoo/Accuscore, ESPN, CBS Sports, et cetera. This year, I used a weighted three-year average formula and then went through each player with a fine-tooth comb and adjusted each projection up and down based on new circumstances like new team or gaining/loss weapons.

2) My biggest mistake last year was not factoring in supply and demand. When using straight VBD, you figure out the baselines based on the number of starting players. However, in most leagues, players will not trade the 20th-best RB for the 20th-best WR. So in response, I set the RB baseline much lower.

**The Excel XLSX Workbook**

This year, I thought I would make it much easier for you guys to modify the values/rankings for your own leagues.

First things first, here is the base template. It is an Excel Workbook.

**http://heartless-ang...2013-07-06.xlsx**

A quick guide: Each Excel sheet has instructions that you can follow to adjust the values to fit your own needs. However, I will take some time in this post to explain what each spreadsheet does.

**"Projections" Spreadsheet**

You can adjust the individual projections in the "Projections" spreadsheet. If you disagree that Tom Brady will only get 4200 passing yards or Adrian Peterson will only get 35 catches, make your changes there.

You can adjust the projections like yards, TDs, touches, et cetera, but you can also make adjustments based on 'Yards per Rush' (YPR) or 'Yards per Catch' (YPC). The rushing yards and receiving yards are computed by default by multiplying the attempts by the yards per attempt (YPR/YPC). You can edit the yards manually or you can change the attempts and/or the YPR/YPC to get new values.

If you want to drop a player’s stats across the board, change the percentage (Column D). The percentage will not change the YPR or YPC. So if you think for some reason, Adrian Peterson will miss 2 games, you can set his percentage to 87.5%. If you think Tom Brady’s stats will go down by 5% due to the loss of some weapons, set his percentage to 95%.

*Important Note*: If you look at the numbers in each cell, you’ll notice that each number has a formula that is essentially ‘=Stat*$E#’. If you edit that cell and take out the *$E#, then if you go back and adjust the percentage, that cell will not be updated. So if you are going to change Tom Brady’s passing yards, it’s recommended that you change the formula from ‘=4200*$E7’ to ‘=4500*$E7’ just for the sake of consistency.

**"Scoring System" Spreadsheet**

You can set up your league's scoring system in the "Scoring System" page.

For example, the total projected points for ESPN standard-scoring fractional leagues are found with this formula:

=($E2/25)+($F2*4)+(($I2+$L2)/10)+(($J2+$M2)*6)+($K2*0)+(($G2+$N2)*-2)

The formula is, in order, passing yards are 25 per point, passing TDs are 4 points each, rushing and receiving yards are 10 per point, rushing and receiving TDs are 6 points each, receptions are worth 0 points, and turnovers are worth negative-two points.

Change the formula to meet the scoring criteria of each of your leagues, with one column per league. I have set up four scoring systems so far: ESPN standard scoring, ESPN 1-PPR scoring, a league with return yards & passing/rushing/receiving bonuses scoring (CUSTOM1), and another scoring system for a friend (CUSTOM2). Copy the base formula from cell U2 or V2 into Row 2 of a new column, adjust the fractions and TD points, and then copy that formula all the way down the column. The title of the column (Row 1) is what will be used to find the player rankings on the “auction values” page.

CUSTOM1 in Column W is an example of a league that scores return yards, return TDs, and bonuses.

Astute observers will notice that I could have combined the “Projections” spreadsheet and the “Scoring Settings” spreadsheet. However, I separated them in this workbook because I thought it would be easier to follow.

**"Auction Values" Spreadsheet**

This is the hardest spreadsheet to explain, but also the most valuable as it is the one that holds all the values/rankings for the players. I will try to do the best I can with this explanation.

First, you'll find the auction values pages each have long complicated names like "10TM 1Q 2R 2W 1T 1WRT ESPN-STD". That's because I wanted to clarify what the league roster configurations were when I found my values.

I did my best to put all of the values that you would have to customize in a light-orange color. There are detailed instructions starting in Row 28-29.

As you may have guessed, this spreadsheet finds the 'Auction Values' for each player. If you are in a serpentine, just list the players from most expensive to least expensive. In the event of a tie, use the number of points in one of the three columns to the left of the players, with the column in the left taking precedence to the middle column, which takes precedence to the end column.

Step #1: Type the column name of the league scoring settings that you created in the "Scoring System" spreadsheet that want to use in the merged-cell B9:C9. In the first sample, the column name is 'ESPN STD', which is the same as cell U1 of the "Scoring System" spreadsheet. A column number between 20 and 30 inclusive will be shown in cell D9 of the "Auction Values" sheet automatically. Do not change this number, as it is needed for the VLOOKUP formula.

Step #2: Enter the correct number of teams in cell D8.

Step #3: If you are in an auction draft where everyone starts with a number other than $200, enter it in the D10 formula ‘=(D8*200)-50’. If you believe the players in your league will spend an amount other than $50 on all kickers and defenses combined, edit that part of the formula. If you are in a snake draft, don't edit that cell.

Step #4: Highlight the player name and project score columns for one position, and sort from largest to smallest. Do this for each position separately. For example, highlight columns K&L and then sort by L. Do the same for columns Q&R, W&X, and AC&AD.

Step #5: This is the trickiest part. Determine the base cutoff values for each position and list them in cells B2 through E4. The easiest way to do this is by setting the PREMIUM to the cell

**below**the number of starting spots, the BENCH to a good amount of bench players (one below the last good bench player), and the FLIERS to the $1 or late-round players (the value set to the first player you don't think will be drafted). So in a 10-team 1QB, 2RB, 2WR, 1TE league, set cell B2 to a value of '=L12', which is one spot below the 10th ranked QB, set C2 to '=R22', one spot below the 20th ranked RB, and so forth, set D2 to a value of '=X22', and set E2 to a value of '=AD12'. As you will see, I have not done that in this example. If I feel that the true 'cutoff' is higher or lower due to supply and demand, I will set the value to that spot. So even though this is a 10-team league, I set the PREMIUM value to '=L10' because I feel Stafford is the cutoff there. I set the REGULAR value to ='L14' because I feel there is a big drop-off from Romo to Freeman.

The values for Step #5 are all a matter of preference and projected 'tiers'. They're really a best guess. If you are one higher or one lower than me, that won't affect your values too much.

Step #6: Type in the number found in B6 into B5. Do the same for C6 into C5, D6 into D5, and E6 into E5. These are the 'base salaries' for each position. They have to be typed in manually because of 'Excel Circular Reference' errors when calculating each player's total value.

At this point, you will have each player’s auction value for that specific league. If it’s a serpentine, just take the players from highest to lowest. Even in a serpentine, this could be a good tool to use when proposing and evaluating trades.

It's complicated at first, but once you do it a time or two, you should get the hang of it.

**A Note about the Auction Values**

Note that these are just season-long projections. They may not represent the order in which I would draft players, especially bench players. When it comes to the RB31-RB60 for example, a guy that scores 80 total points doing it 4-6 points per game is going to have less value than someone that scores 70 points, but scores 15 points in each of 4 games because the starter in front of him went down (otherwise known as a handcuff.) That's why in the middle-to-late rounds, I'll go against my own projection, and rank guys based on upside, or in other words,

**a projection of what they could do if they got the starting position instead of a projection of their total season.**Keep that in mind.

**Conclusion**

So that's it. I hope that you find this information useful and easy to customize to fit your own needs.

If you have any questions, feel free to ask me, either through PM or this thread.

Thanks.