Archived

This topic is now archived and is closed to further replies.

ZidaneValor

2013 Auction Tools

74 posts in this topic

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.

8 people like this

Share this post


Link to post
Share on other sites
Are you from the future?

Yes, indeed I am.

Anyone have any questions or need something explained a little better?

Share this post


Link to post
Share on other sites

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points for reception, so it's great to be able to adjust for scoring, too.

Share this post


Link to post
Share on other sites

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you? He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

Share this post


Link to post
Share on other sites

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you? He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

Do you read much?

2 people like this

Share this post


Link to post
Share on other sites
Don't read much do you? He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

I don't think you read gordo4s correctly. He/she was happy that the scoring on the spreadsheet could be changed.

Share this post


Link to post
Share on other sites

This is great! Thank you!!

I play in a league where quarterbacks get six points for passing touchdowns and there are no points for reception, so it's great to be able to adjust for scoring, too.

Don't read much do you? He clearly details how to change the scoring, all you need is a remedial understanding of Excel to know how to do it.

Do you read much?

Touche, I apologize, I saw a "d" instead of an "s",

I read it as "It'd be great", instead of "It's great"

Awesome work Z.

Share this post


Link to post
Share on other sites

This is awesome, thank you for putting it together and sharing. My question is regarding the auction values. I was using my leagues auction results from last year as a rough guide to values. I hope I'm not being too nitpicky about this or naive to the subtle differences between leagues, I've only been playing an auction league for a couple years and I'm still trying to figure out the best way to value players. The only other websites I've used (last year) to value players were FFToolbox and ESPN, and I during the draft, I realized that those were off as well. My league's settings are ESPN standard with a 200 budget, so all I did was change the # of teams and looked at the differences spit out by Excel. After doing that, I noticed that, compared to the spreadsheet, in my league...

QBs are valued higher at the top of the sample

RBs are valued WAY lower all the way through the sample

WRs are valued slightly higher at the top and considerably higher the farther down I go

TEs are valued higher for everyone except the #1

QB1, QB5, QB10, QB15, QB20 10 Team Standard 38, 16, 6, 0, 0

QB1, QB5, QB10, QB15, QB20 14 Team Standard 58, 23, 6, 1, 0

QB1, QB5, QB10, QB15, QB20 14 Team Last Year 67, 39, 12, 3, 1

RB1, RB5, RB10, RB15, RB20 10 Team Standard 66, 59, 43, 35, 30

RB1, RB5, RB10, RB15, RB20 14 Team Standard 101, 90, 65, 51, 44

RB1, RB5, RB10, RB15, RB20 14 Team Last Year 72, 52, 44, 34, 18

WR1, WR5, WR10, WR15, WR20 10 Team Standard 40, 21, 19, 10, 4

WR1, WR5, WR10, WR15, WR20 14 Team Standard 62, 31, 28, 13, 4

WR1, WR5, WR10, WR15, WR20 14 Team Last Year 68, 35, 30, 28, 23

TE1, TE5, TE10, TE15, TE20 10 Team Standard 33, 8, 2, 1, 0

TE1, TE5, TE10, TE15, TE20 14 Team Standard 52, 11, 2, 1, 0

TE1, TE5, TE10, TE15, TE20 14 Team Last Year 35, 18, 6, 4, 2

My other question is more general, why do values go up the more teams there are in the league?

Thanks to anyone who answers, I came across this board a couple months ago and it is examples like this that clearly make it the best source of info for fantasy. I only wish I'd found it sooner....

Share this post


Link to post
Share on other sites
My league's settings are ESPN standard with a 200 budget, so all I did was change the # of teams and looked at the differences spit out by Excel.

My other question is more general, why do values go up the more teams there are in the league?

Ah, I didn't explain myself well.

If you look at Cell D18 of the "Auction Values" page, it says the number of players currently in the pool. This number should be the number of teams * the number of roster spots.

So if you change the number of teams from 10 to 14 for example, you also have to adjust the baselines so that enough players are included in the pool. So if a 10 team league with 15 roster spots, you need cell D18 to be 150 players. If you have a 14 team league with 16 roster spots, cell D18 needs to be 224 players.

The reason all your player values went up is because by increasing the number of teams, all that did was increase the total pool of money. Now that extra money is being divided up among the same group of players, so all their values went up.

The way I would recommend doing this is to adjust the FLIER baselines on cells B4, C4, D4, and E4 first. Lower those baselines until you get the correct number of players. Then, adjust the PREMIUM and BENCH baselines to more accurately reflect the approximate baselines/tiers.

If you need help with this, I can work out an example this evening after work/gym.

Share this post


Link to post
Share on other sites

Thank you "Future Man"

Share this post


Link to post
Share on other sites

Thanks for the response Zidane. Yeah, I'm a little confused. My Excel skills suck.

So the steps are:

#1 change the total number of teams in D8 from 10 to 14

#2 change the total number of players in D18

So I have 17 roster spots (not counting an IR) 14 x 17 = 238

Do I not have to also adjust D15-18 to reflect this new total? (cause those totals still add up to 160)

#3 Change the baselines. This is what I'm most confused about. So first I change the Flier baselines in B4/C4/D4/E4, followed by the Bench baselines in B3/C3/D3/E3, then the Premium baselines in B2/C2/D2/E2? So what should those baselines be? Is premium the first 14 QBs in a 14 team league? Is premium for RBs the first 14 or the first 28? I tried changing those values and the numbers started fluctuating wildy (top QBs went up to just over $100).

My last question is separate from changing the values. Is there a way to highlight (fill) the cells with the player names who are in lower (shaded) tiers? For the premium tier players who have no shading in the cells, I'm able to right click the player name cell and fill it with a color (I use red, yellow, green, blue). When I do this for players in the shaded tiers, the color doesn't show up. Is there a way to remove that shading without messing with the calculations? This isn't a huge deal, if I have to I'll print it out and use highlighters on the paper.

Thanks again for your help, it is MUCH appreciated!

Share this post


Link to post
Share on other sites

Again, like last year this thing rocks.

However... it frickin rocks even more now. I really like how you make this incredibly objective (outside of projections). A point is a point no matter where it comes from.

Thanks for all the hours of hard work!

Share this post


Link to post
Share on other sites

Great stuff, man, really. I didn't find the instructions difficult at all--you did a fine job explaining step-by-step. But I had one issue maybe you or one of the repliers might be able to help with.

I'm on a Mac, but I do have Excel X for Mac. But for some reason, my comp won't let me open it in Excel, it opens in Numbers. Weird. So--and here's my issue--I can't sort the columns as you outline in step 4 of the Auction Values sheet. It just beeps at me when I try to select the column(s) KL and sort. When I try to do it manually, from the menu at the top, the Sort commands are grayed-out. I'm not great with spreadsheets, so maybe I'm missing something obvious?

Thanks again for your work.

Share this post


Link to post
Share on other sites

I will answer your specific questions this weekend. Sorry for the delay.

Until then, here is the second draft of my auction values. I updated a couple of projections, added some deeper league players, and more importantly, gave examples of values for 10, 12, and 14 team leagues in standard and 1-PPR.

The third draft, I'll include some 0.5 PPR values.

Please inform me about any inconsistencies or issues with the Excel Workbook. Thanks.

http://heartless-angel.com/Fantasy/FantasyFootball_AuctionValues_2013-07-10.xlsx

Share this post


Link to post
Share on other sites

what do the players in grey signify? i remember you explaining last year but i forgot.. thanks!

Those differentiate between his tier levels. They're relatively subjective.

Thanks again Zidane, I really appreciate you doing this, especially adding the 14 team values. Advice to others: Look at your draft results from the previous year(s) for comparison. Pay attention to the value of the position, not the name attached to it. After doing so, I found that a large majority of these projected values match up with what players were going for in the previous drafts. For example, in a 14 team standard, Zidane has his QB6 @ 25, exactly what the QB6 went for last year in my draft. The important thing to look for is the patterns of discrepancy. Now I can clearly see where the value is in the rankings, and where people are overpaying. The trends are easy to see.

Share this post


Link to post
Share on other sites

Ok, I apologize for the delay. Let me get to these questions.

Thanks for the response Zidane. Yeah, I'm a little confused. My Excel skills suck.

So the steps are:

#1 change the total number of teams in D8 from 10 to 14

Correct.

#2 change the total number of players in D18

So I have 17 roster spots (not counting an IR) 14 x 17 = 238

Do I not have to also adjust D15-18 to reflect this new total? (cause those totals still add up to 160)

#3 Change the baselines. This is what I'm most confused about. So first I change the Flier baselines in B4/C4/D4/E4, followed by the Bench baselines in B3/C3/D3/E3, then the Premium baselines in B2/C2/D2/E2?

These steps are backwards. The number of players updates automatically, so you do not need to change D15-D18 (i.e., skip Step #2).

When you change the FLIER baselines in Row 4, the total number of players (D18) will increase to fit the new baselines. That's why I would do the FLIER baseline first. If you are in a 12-team league, add fliers until you get to 180 (15-round draft) or 192 (16-round draft). Try to bring those numbers close to what you think would happen in your real draft. So if you think 20 QBs will get drafted, set B4 to the 21st ranked QB. If you think 70 RBs will be drafted, set C4 to the 71st ranked RB.

So what should those baselines be? Is premium the first 14 QBs in a 14 team league? Is premium for RBs the first 14 or the first 28? I tried changing those values and the numbers started fluctuating wildy (top QBs went up to just over $100)

In all honestly, setting the baselines is a lot of guesswork, which is the biggest flaw of the spreadsheet.

What I try to do is to try to get the general pool of players in line with their approximate ADPs. Now obviously, individual players could be ranked much higher or much lower. But in general, if in a real draft, 4 QBs are taken in the first four rounds, then that is what I try to aim for (plus/minus a couple).

I try to set the PREMIUM baseline to approximately the number of starters in a league for QB/WR/TE. So in a 12-team league, the baselines (in theory) would be the 13th QB, the 25th WR, and the 13th TE. I usually set the RB baseline to about 8-10 extra guys because of supply in demand. This year in my projections, there was a natural drop off in standard leagues from Bell, Bradshaw, and Lacy (150ish) to Mathews (136) to Giovani Bernard (123). I set the 10-TM PREMIUM baseline at Mathews and the 12-TM at Bernard, but would not argue if you wanted to set both to either guy.

Then I set the BENCH baselines last to a good spot in the middle where I have approximately three rounds of "FLIERS" but deep enough where I can really work in the "Supply and Demand". So QBs and TE get a shallow pool of BENCH players (4-5 guys), WR gets a deeper pool, and RB gets the deepest pool.

The number of points that the top BENCH player scores over the top FLIER player is really important to my "BASE $AL". That number is the column labeled "B", which is columns N, T, Z, and AF. That's because my salary formula for each player is this:

VALUE = $1 + (BENCH PTS * BENCH $/PT) + (PREMIUM PTS * PREMIUM $/PT).

BASE $AL = $1 + ("HIGHEST POS" BENCH PTS * BENCH $/PT)

I know I've made this way complicated, but let me explain.

Each player in the BENCH tier has a number of BENCH points that he is above the top FLIER, like the PREMIUM points which is classic Value Based Drafting. The total number of BENCH points is added up (Cell E16) and then given a $ value based on the amount of money in the BENCH pool (Cell C16). In this spreadsheet, the total amount of money allocated to the BENCH tier is found by (# BENCH players * $3). Why $3? Trial and Error mostly. The total money allocated (Cell 16) is divided by the total number of BENCH points (Cell E16) to find the value of $/BENCH POINT (Cell F16). Each player in the BENCH tier is then given a value of $1 + (BENCH PTS * BENCH $/PT). The highest BENCH value of each position then becomes the BASE $AL of that position (Cells B6:E6).

Now, why did I explain all of that? I did so to help you set your tiers. The basic cliffs is: the higher the difference between the BENCH tier and the FLIER tier, the higher the BASE $AL is.

Let's look at the 10-TM ESPN-STD values from my second draft. In that sheet, the highest BENCH QB is RG3 (45 pts), the highest BENCH RB is Ryan Mathews (73 pts), the highest bench WR is Antonio Brown (51 pts), and the highest bench TE is Owen Daniels (14 pts). So the order of highest to lowest BENCH points is RB, WR, QB, and TE. That's why the BASE $AL values from highest to lowest are RB, WR, QB, and TE.

One other note on tier setting is the expected ADP issue. It is a very valid question in a 12-team league to ask "Zidane, why did you put the PREMIUM baseline between Peyton and RG3 instead of Romo and Freeman when Romo is the 12th ranked QB anyway the difference in projection is so huge?" The answer is because if I did that, then QBs would be valued too highly. If you look at Cells A21:I26, you'll see a quick and dirty breakdown of the number of players at each position that would be drafted by round according to my rankings. For instance, in a 12-TM ESPN-STD, I have 2 QBs, 20 RBs, 1 WR, and 1 TE through the end of Round 2 and 4 QBs, 27 RBs, 14 WRs, and 3 TEs through the end of Round 4. Those numbers make sense to me based on personal preference. If you think that I don't have enough WRs for example through 4 rounds, set the PREMIUM WR baseline lower. Anyway, if I change the PREMIUM QB baseline from RG3 to Freeman and change nothing else (make sure to change Cells B5:E5 to match B6:E6), then all of a sudden I have 8 QBs in my first three rounds. Maybe that's valid in your league, and if so, feel free to change it. Usually in my drafts (and my personal preference), people wait longer on QBs. That's why I set my PREMIUM QB baseline to the 9th QB instead of the 13th QB in a 12-team league.

Like I said, it's all trial and error unfortunately. If you need help with your personal league settings, feel free to ask.

My last question is separate from changing the values. Is there a way to highlight (fill) the cells with the player names who are in lower (shaded) tiers? For the premium tier players who have no shading in the cells, I'm able to right click the player name cell and fill it with a color (I use red, yellow, green, blue). When I do this for players in the shaded tiers, the color doesn't show up. Is there a way to remove that shading without messing with the calculations? This isn't a huge deal, if I have to I'll print it out and use highlighters on the paper.

Thanks again for your help, it is MUCH appreciated!

A much easier question, lol. The light grey and dark grey cells are the result of conditional formatting. If you go to "Conditional Formatting" > "Manage Rules", select "This Worksheet" in the 'Show Formatting Rules for:" box, you'll see formulas like "=$N2 > 0" applies to =$K$2:$P$80. You can go ahead and delete that. It will not change the formula. I did that because it's MUCH easier to see where I set the baselines.

There are also some extra formulas I noticed like for instance "=$Z81 > 0" applies to =$X$81:$AB$94 in the 12TM ESPN-STD sheet. Those are the result of me copying and pasting things all the time. You can delete those too.

Nothing you delete in the Conditional Formatting will affect the value formulas.

Great stuff, man, really. I didn't find the instructions difficult at all--you did a fine job explaining step-by-step. But I had one issue maybe you or one of the repliers might be able to help with.

I'm on a Mac, but I do have Excel X for Mac. But for some reason, my comp won't let me open it in Excel, it opens in Numbers. Weird. So--and here's my issue--I can't sort the columns as you outline in step 4 of the Auction Values sheet. It just beeps at me when I try to select the column(s) KL and sort. When I try to do it manually, from the menu at the top, the Sort commands are grayed-out. I'm not great with spreadsheets, so maybe I'm missing something obvious?

Thanks again for your work.

Even though I'm a Software Engineer by profession, I almost never use Macs. Sorry.

Share this post


Link to post
Share on other sites

Great stuff and thanks for sharing this. My league award 0.2 points for each completion. What is the easiest way to incorporate that?

Share this post


Link to post
Share on other sites
Guest
This topic is now closed to further replies.