Auction drafters: Figure out your draft plan using Excel Solver to create "optimal" lineups (1 Viewer)

[Scoresman]

Those of us who auction draft know that one of the most important benefits of the format is the ability to get any player you want, and to create a draft plan accordingly.

I used Excel Solver to come up with the optimal drafted team using ESPN/Yahoo pricing. Basically, you give it estimated player pricing, projected points scored, and lineup details. The Solver goes through every possible team permutation that fits under the cap and roster requirements and it spits out the team that will give you the max total points.

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

The constraints I used:

- PPR Scoring, 1 QB / 2 RB / 2 WR / 1 TE / 1 Flex.
- Kickers and defense are excluded since I only spend a buck here anyway.
- This only gives optimal starters. Bench not included.
- $185 cap since I would normally allocate that much for starters.

Optimal ESPN Team

POS	NAME	$$$	Pts
QB	Lamar Jackson	30	373
RB1	Chase Brown	27	275
RB2	Alvin Kamara	18	244
WR1	Ja'Marr Chase	59	337
WR2	DeVonta Smith	8	229
TE1	Brock Bowers	34	257
Flex	Courtland Sutton	9	228

Optimal Yahoo Team

POS	Name	$$$	Pts
QB	Jalen Hurts	19	361
RB1	De'von Achane	43	297
RB2	Chase Brown	33	275
WR1	Tyreek Hill	21	260
WR2	Davante Adams	17	251
TE1	Brock Bowers	37	257
Flex	Alvin Kamara	15	244

Analysis

Some different teams here entirely due to the different pricing found on each site. ESPN seems to favor a more stud/dud approach. Both sites suggest splitting your RB pricing among 2nd-3rd tier RBs, avoiding the top dollar players. ESPN favors a more stud/dud approach at WR2 than Yahoo does.

Both site's pricing agrees to spend up at both QB and TE. I also found this to be true in my own auction league's analysis that does not use ESPN or Yahoo. Even if I tweak the QB and TE prices a bit higher than expected, it still spits out teams with both QB and TE favoring the top 3 options.

As mentioned since this is projection based, you have to know what you are looking at and adjust accordingly based on your risk tolerance and player ceilings. For example, this is using the median projection for a player like Tet McMillan, but since his ceiling is through the roof, he probably provides more value than the solver can account for. For players like this that I want to target, I just look at their estimated price and see how they could fit into the optimal team making it better.

I find this to be a really valuable tool for auction prep. Highly recommend if you know how to use Excel solver. Even better if you can tweak the estimated player prices based on your league's historical drafts. For example I know to make Raiders and 49ers a bit more expensive than usual since we have a few fans of both in our league and this has historically been the case for those players.

Anyway, I'm happy to run this for other sites that do auction draft and have estimated prices or even other roster requirements.

 

[J Giles Band]

How about MFL in a similar setup?

Our setup is not exactly the same... but not asking for you to tailor it to our league.

 

[Scoresman]

How about MFL in a similar setup?

Our setup is not exactly the same... but not asking for you to tailor it to our league.

Is MFL average auction pricing available anywhere? Couldnt get anything through Google.

 

[J Giles Band]

How about MFL in a similar setup?

Our setup is not exactly the same... but not asking for you to tailor it to our league.

Is MFL average auction pricing available anywhere? Couldnt get anything through Google.

I honestly don't remember. I will take a look. Thanks

 

[BroncoFreak_2K3]

How about MFL in a similar setup?

Our setup is not exactly the same... but not asking for you to tailor it to our league.

Is MFL average auction pricing available anywhere? Couldnt get anything through Google.

MFL used to have that, but I haven’t been on an MFL site for a while.

ETA: Here's the one from one of the Anarchy Leagues last year: https://www42.myfantasyleague.com/2024/reports?L=27197&R=AAV

 

[chad in Indy]

Those of us who auction draft know that one of the most important benefits of the format is the ability to get any player you want, and to create a draft plan accordingly.

I used Excel Solver to come up with the optimal drafted team using ESPN/Yahoo pricing. Basically, you give it estimated player pricing, projected points scored, and lineup details. The Solver goes through every possible team permutation that fits under the cap and roster requirements and it spits out the team that will give you the max total points.

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

The constraints I used:

- PPR Scoring, 1 QB / 2 RB / 2 WR / 1 TE / 1 Flex.
- Kickers and defense are excluded since I only spend a buck here anyway.
- This only gives optimal starters. Bench not included.
- $185 cap since I would normally allocate that much for starters.

Optimal ESPN Team

POS	NAME	$$$	Pts
QB	Lamar Jackson	30	373
RB1	Chase Brown	27	275
RB2	Alvin Kamara	18	244
WR1	Ja'Marr Chase	59	337
WR2	DeVonta Smith	8	229
TE1	Brock Bowers	34	257
Flex	Courtland Sutton	9	228

Optimal Yahoo Team

POS	Name	$$$	Pts
QB	Jalen Hurts	19	361
RB1	De'von Achane	43	297
RB2	Chase Brown	33	275
WR1	Tyreek Hill	21	260
WR2	Davante Adams	17	251
TE1	Brock Bowers	37	257
Flex	Alvin Kamara	15	244

Analysis

Some different teams here entirely due to the different pricing found on each site. ESPN seems to favor a more stud/dud approach. Both sites suggest splitting your RB pricing among 2nd-3rd tier RBs, avoiding the top dollar players. ESPN favors a more stud/dud approach at WR2 than Yahoo does.

Both site's pricing agrees to spend up at both QB and TE. I also found this to be true in my own auction league's analysis that does not use ESPN or Yahoo. Even if I tweak the QB and TE prices a bit higher than expected, it still spits out teams with both QB and TE favoring the top 3 options.

As mentioned since this is projection based, you have to know what you are looking at and adjust accordingly based on your risk tolerance and player ceilings. For example, this is using the median projection for a player like Tet McMillan, but since his ceiling is through the roof, he probably provides more value than the solver can account for. For players like this that I want to target, I just look at their estimated price and see how they could fit into the optimal team making it better.

I find this to be a really valuable tool for auction prep. Highly recommend if you know how to use Excel solver. Even better if you can tweak the estimated player prices based on your league's historical drafts. For example I know to make Raiders and 49ers a bit more expensive than usual since we have a few fans of both in our league and this has historically been the case for those players.

Anyway, I'm happy to run this for other sites that do auction draft and have estimated prices or even other roster requirements.

this is awesome. Do you have the excel form?

 

[Scoresman]

Those of us who auction draft know that one of the most important benefits of the format is the ability to get any player you want, and to create a draft plan accordingly.

I used Excel Solver to come up with the optimal drafted team using ESPN/Yahoo pricing. Basically, you give it estimated player pricing, projected points scored, and lineup details. The Solver goes through every possible team permutation that fits under the cap and roster requirements and it spits out the team that will give you the max total points.

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

The constraints I used:

- PPR Scoring, 1 QB / 2 RB / 2 WR / 1 TE / 1 Flex.
- Kickers and defense are excluded since I only spend a buck here anyway.
- This only gives optimal starters. Bench not included.
- $185 cap since I would normally allocate that much for starters.

Optimal ESPN Team

POS	NAME	$$$	Pts
QB	Lamar Jackson	30	373
RB1	Chase Brown	27	275
RB2	Alvin Kamara	18	244
WR1	Ja'Marr Chase	59	337
WR2	DeVonta Smith	8	229
TE1	Brock Bowers	34	257
Flex	Courtland Sutton	9	228

Optimal Yahoo Team

POS	Name	$$$	Pts
QB	Jalen Hurts	19	361
RB1	De'von Achane	43	297
RB2	Chase Brown	33	275
WR1	Tyreek Hill	21	260
WR2	Davante Adams	17	251
TE1	Brock Bowers	37	257
Flex	Alvin Kamara	15	244

Analysis

Some different teams here entirely due to the different pricing found on each site. ESPN seems to favor a more stud/dud approach. Both sites suggest splitting your RB pricing among 2nd-3rd tier RBs, avoiding the top dollar players. ESPN favors a more stud/dud approach at WR2 than Yahoo does.

Both site's pricing agrees to spend up at both QB and TE. I also found this to be true in my own auction league's analysis that does not use ESPN or Yahoo. Even if I tweak the QB and TE prices a bit higher than expected, it still spits out teams with both QB and TE favoring the top 3 options.

As mentioned since this is projection based, you have to know what you are looking at and adjust accordingly based on your risk tolerance and player ceilings. For example, this is using the median projection for a player like Tet McMillan, but since his ceiling is through the roof, he probably provides more value than the solver can account for. For players like this that I want to target, I just look at their estimated price and see how they could fit into the optimal team making it better.

I find this to be a really valuable tool for auction prep. Highly recommend if you know how to use Excel solver. Even better if you can tweak the estimated player prices based on your league's historical drafts. For example I know to make Raiders and 49ers a bit more expensive than usual since we have a few fans of both in our league and this has historically been the case for those players.

Anyway, I'm happy to run this for other sites that do auction draft and have estimated prices or even other roster requirements.

this is awesome. Do you have the excel form?

Thought about releasing this as a download, but it's not exactly plug and play. You need to download and install the Solver excel add-in and know a little bit about how to start it up, what to expect. I'd also have to make a ton of changes to make it customizable for different roster sizes.

I'm happy to run this for other AAP values from different sites if they are freely available to download.

 

[sushinsky4tsar]

Great idea. In a standard auction, I would guess that the real question then becomes how much above par value are you willing to go for the optimal players.

Our league has only done auction once and this season will be #2. Still trying to devise the best way to price check the default valuations and perfectly customize something to our rosters and settings. The safe and easy way is simply not to go over for anybody, but I definitely think there's going to be exceptions where some players are undervalued (the ones popping up as optimal).

 

[Scoresman]

Great idea. In a standard auction, I would guess that the real question then becomes how much above par value are you willing to go for the optimal players.

Our league has only done auction once and this season will be #2. Still trying to devise the best way to price check the default valuations and perfectly customize something to our rosters and settings. The safe and easy way is simply not to go over for anybody, but I definitely think there's going to be exceptions where some players are undervalued (the ones popping up as optimal).

I reframe that question a bit. So for my league, the solver has been basically telling me to spend up at QB and TE. So I ask "How expensive do the elite options at QB/TE have to get before it suggests something else." So I manually increase elite QB prices incrementally and rerun until the solver says to punt.

In my case, QB prices can inflate almost 20% before it says to go cheap at the position. At TE, however, if prices go up by even just a dollar or two over estimated, it tells me to basically punt the position. I can use this during the auction to adjust my plan as necessary depending on actual prices.

Basically I know how expensive elite QB/TE can get before I should wait on the position. This is really good info to have when seeing how pricing is going during the auction.

And yes, avoid strict rules like not going over your budget values. You have to be flexible and a bit aggressive during an auction.

 

[Jacob's Ladder]

I have never been a part of an auction draft that went according to plan. From the jump, things tend to get sideways very quickly. If you're playing in established leagues where you understand other owners to a higher degree, it's a little easier. Newer leagues or newer owners create very volatile and unpredictable drafts. I've pretty much disregarded average prices and predetermined values at this point. You have to stay flexible, think quickly, and do your best to drive up prices paid by other owners whenever possible. Auction drafts are so much fun.

 

[Scoresman]

I have never been a part of an auction draft that went according to plan. From the jump, things tend to get sideways very quickly. If you're playing in established leagues where you understand other owners to a higher degree, it's a little easier. Newer leagues or newer owners create very volatile and unpredictable drafts. I've pretty much disregarded average prices and predetermined values at this point. You have to stay flexible, think quickly, and do your best to drive up prices paid by other owners whenever possible. Auction drafts are so much fun.

Yup, being flexible needs to be built into your plan.

My plan at QB for my auction league is essentially to get an elite QB, unless they go above X amount, then wait at the position.
My RB strategy is to not let the top guys go for a discount, but ideally should be targeting that 2nd tier of RBs, Achane, Bucky, Chase.
My WR strategy is again to not let extreme values go, but everything is telling me to go cheap here.
My TE strategy is the same as QB. Elite so long as they are under X amount, otherwise punt.

Using the solver essentially helped me put this together. I like it because it's aggressive, yet very flexible. And it also makes sense IMO with this year's landscape. Especially at WR which is so deep, it might make sense for that position to be the one I spend the least on.

I also like to nominate guys I do like instead of the usual advice given to nominate guys you dont like. The earlier you can get your plan to take shape in an auction, the better. This means getting clarity on pricing for guys you might be targeting.

 

[BroncoFreak_2K3]

Absolute fantastic strategy tool for us auction drafters. Well done @Scoresman !

 

[barackdhouse]

Those of us who auction

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

Well hello neighbor! So yeah I also love auction drafting and in fact my oldest and very first FF league is an auction league that is run on ESPN each year. It's coming up in a couple weeks and it is the only league I actually care about from a pride/bragging rights standpoint. I've won 10's of thousands on big contests but this league is the one I *expect* to win or I'm disappointed. I took 3rd last year and I'm still kinda ticked off.

I've been using my own Excel tool(s) for these drafts for over a decade. But possibly in a crucial difference between our two approaches here, I am a go-get-my-guy kind of drafter and to the extent that I look at projections from anyone it is only to gain information about the state of the market. I don't make my own projections but I make my own tiers and draft accordingly. In snake drafts that is one thing but in this big auction draft, I want to go into draft day with as many realistic permutations of roster constructions as possible. So I suppose I could automate the process like you have done and simply base it off my own projections if I were to make some.

But in addition to getting my guys, I am also an avoid-landmine-players-at-all-costs kind of drafter and there are TONS of players that are going to be projected for stats that give them some sort of value on paper, but who I am crossing off my list before I arrive. And going back several years now, my "correct" rate on avoiding certain players is ~75%. I absolutely miss out from time to time on some incredible deals, but I'm typically correct. (trust me this is circling back to excel and auctions) Deebo and Kupp were notable mistakes on my part when they had their ONE good season.

Hehe ok so thing is I want to come up with mock ups of my-guy squads and then as the draft unfolds I am able to pivot in real time really quick by adjusting values in the DB on the fly. I set the baseline price for every player based on my historic knowledge of how these drafts go combined with the usually ludicrous ESPN prices. And then when player X comes off the board for waaaaay more or less than I was expecting or even ESPN, I mark that price and my excel DB adjusts. And so I set range of outcome budgets for each position. I think you said 185/200 for your starting lineup. Yeah, that sounds about right to me as well. (We have 18 roster spots and 9 starters)

I could go on and on but funny thing is I was just about to start building the 2025 list and start making mock up rosters for this draft coming up. But many of the players on your optimized list are personal favorites and looks just like the kind of team I'd be looking to build. Kamara took my family to Disneyland with his Christmas game. Lamar is a staple throughout my portfolio every year and all 11 of my opponents in this league know I'm in love with him. There are a couple crazy Raiders fans in our group along with their brothers and sisters in a couple instances and so someone like Bowers might go for 55 or something crazy. I would guess Bijan Barkley and Gibbs all go 65+

 

[Scoresman]

Those of us who auction

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

Well hello neighbor! So yeah I also love auction drafting and in fact my oldest and very first FF league is an auction league that is run on ESPN each year. It's coming up in a couple weeks and it is the only league I actually care about from a pride/bragging rights standpoint. I've won 10's of thousands on big contests but this league is the one I *expect* to win or I'm disappointed. I took 3rd last year and I'm still kinda ticked off.

I've been using my own Excel tool(s) for these drafts for over a decade. But possibly in a crucial difference between our two approaches here, I am a go-get-my-guy kind of drafter and to the extent that I look at projections from anyone it is only to gain information about the state of the market. I don't make my own projections but I make my own tiers and draft accordingly. In snake drafts that is one thing but in this big auction draft, I want to go into draft day with as many realistic permutations of roster constructions as possible. So I suppose I could automate the process like you have done and simply base it off my own projections if I were to make some.

But in addition to getting my guys, I am also an avoid-landmine-players-at-all-costs kind of drafter and there are TONS of players that are going to be projected for stats that give them some sort of value on paper, but who I am crossing off my list before I arrive. And going back several years now, my "correct" rate on avoiding certain players is ~75%. I absolutely miss out from time to time on some incredible deals, but I'm typically correct. (trust me this is circling back to excel and auctions) Deebo and Kupp were notable mistakes on my part when they had their ONE good season.

Hehe ok so thing is I want to come up with mock ups of my-guy squads and then as the draft unfolds I am able to pivot in real time really quick by adjusting values in the DB on the fly. I set the baseline price for every player based on my historic knowledge of how these drafts go combined with the usually ludicrous ESPN prices. And then when player X comes off the board for waaaaay more or less than I was expecting or even ESPN, I mark that price and my excel DB adjusts. And so I set range of outcome budgets for each position. I think you said 185/200 for your starting lineup. Yeah, that sounds about right to me as well. (We have 18 roster spots and 9 starters)

I could go on and on but funny thing is I was just about to start building the 2025 list and start making mock up rosters for this draft coming up. But many of the players on your optimized list are personal favorites and looks just like the kind of team I'd be looking to build. Kamara took my family to Disneyland with his Christmas game. Lamar is a staple throughout my portfolio every year and all 11 of my opponents in this league know I'm in love with him. There are a couple crazy Raiders fans in our group along with their brothers and sisters in a couple instances and so someone like Bowers might go for 55 or something crazy. I would guess Bijan Barkley and Gibbs all go 65+

I used to solely use a "get your guy" approach but that backfired one year I was targeting Kelce. He had never gone for more than $22 historically, and my solver tool was basically saying he should be going for around $30. I figured there was no way I wouldn't get him and at a discount, but he ended up going for $32 that year. It left me in the lurch with no real backup plan and the rest of the draft was a mess.

I think you can go in with a get your guys approach but it has to be flexible like every other auction strategy. You need a contingency plan in case you dont get your guys.

 

[barackdhouse]

Those of us who auction

One note is that I dont use this to target a specific team of players to draft. This is projection based, and projections dont account for range of outcomes. What this is valuable for is to find out what general draft strategies the optimal team is employing. Should you spend up at QB? Spend up at TE? Spend most of your dollars at RB or WR? This has really helped me formulate my draft plans the last couple years.

Well hello neighbor! So yeah I also love auction drafting and in fact my oldest and very first FF league is an auction league that is run on ESPN each year. It's coming up in a couple weeks and it is the only league I actually care about from a pride/bragging rights standpoint. I've won 10's of thousands on big contests but this league is the one I *expect* to win or I'm disappointed. I took 3rd last year and I'm still kinda ticked off.

I've been using my own Excel tool(s) for these drafts for over a decade. But possibly in a crucial difference between our two approaches here, I am a go-get-my-guy kind of drafter and to the extent that I look at projections from anyone it is only to gain information about the state of the market. I don't make my own projections but I make my own tiers and draft accordingly. In snake drafts that is one thing but in this big auction draft, I want to go into draft day with as many realistic permutations of roster constructions as possible. So I suppose I could automate the process like you have done and simply base it off my own projections if I were to make some.

But in addition to getting my guys, I am also an avoid-landmine-players-at-all-costs kind of drafter and there are TONS of players that are going to be projected for stats that give them some sort of value on paper, but who I am crossing off my list before I arrive. And going back several years now, my "correct" rate on avoiding certain players is ~75%. I absolutely miss out from time to time on some incredible deals, but I'm typically correct. (trust me this is circling back to excel and auctions) Deebo and Kupp were notable mistakes on my part when they had their ONE good season.

Hehe ok so thing is I want to come up with mock ups of my-guy squads and then as the draft unfolds I am able to pivot in real time really quick by adjusting values in the DB on the fly. I set the baseline price for every player based on my historic knowledge of how these drafts go combined with the usually ludicrous ESPN prices. And then when player X comes off the board for waaaaay more or less than I was expecting or even ESPN, I mark that price and my excel DB adjusts. And so I set range of outcome budgets for each position. I think you said 185/200 for your starting lineup. Yeah, that sounds about right to me as well. (We have 18 roster spots and 9 starters)

I could go on and on but funny thing is I was just about to start building the 2025 list and start making mock up rosters for this draft coming up. But many of the players on your optimized list are personal favorites and looks just like the kind of team I'd be looking to build. Kamara took my family to Disneyland with his Christmas game. Lamar is a staple throughout my portfolio every year and all 11 of my opponents in this league know I'm in love with him. There are a couple crazy Raiders fans in our group along with their brothers and sisters in a couple instances and so someone like Bowers might go for 55 or something crazy. I would guess Bijan Barkley and Gibbs all go 65+

I used to solely use a "get your guy" approach but that backfired one year I was targeting Kelce. He had never gone for more than $22 historically, and my solver tool was basically saying he should be going for around $30. I figured there was no way I wouldn't get him and at a discount, but he ended up going for $32 that year. It left me in the lurch with no real backup plan and the rest of the draft was a mess.

I think you can go in with a get your guys approach but it has to be flexible like every other auction strategy. You need a contingency plan in case you dont get your guys.

well yeah it needs to be 'guys' plural and not 'guy' singular. harder at TE I suppose but yes I always have lots of backup plans. But I also don't mind going a little overboard (in this particular league) because I know the late draft values are going to be insane. If I have to "overspend" I really don't mind. You should be able to do that with maybe 2 or 3 players throughout a draft. I'm pretty much always thinking of multiple options but I want to know what they are when I pivot, which is why I do lots of scenarios. Like ok so I'm willing to spend 30 on Kelce say (in that year) and here are 4 or 5 permutations of a Kelce build if I spend 30. But here are a bunch of builds around much cheaper TEs that you still like.

Tricky part is if you end up spending just 20 in total on TE when you were planning on spending something like 40, for example with a backup or two, then that "extra" 20 could be used somewhere else for a different premium piece. And the trick is whether that has already occurred or not. Maybe you're willing to spend 30+ on Lamar but not if you get Kelce. Well what if you don't get Kelce but Lamar already came off and you didn't have the nerve to bid him up? Lots of possible forks in the road and one of the many reasons I love auctions. But the take home is I do tons of mock ups.

Being a go get your guy drafter doesn't mean you don't get sniped or outbid once in a while.

 

[TVT 0 N S T A]

Something similar, to what I believe is being discussed here, exists on reddit. I am not very reddit savvy but El Boberto 2025 has customizable league settings, starter/bench % of Auction Amount, etc.

It is quite nice... I have just started to use it and the anticipated amounts seem off, as it is a keeper league. I guess if you are able to keep Achane for $4, you are much more likely to overspend on some guys (as my league tends to do).

It has been decent at showcasing my keeper values (as I am torn between Worthy for $2 and MHJr for $29, especially with Rice suspension looming). Interested to hear if anyone has used the spreadsheet I am talking about and what their thoughts are on it.

 

[BroncoFreak_2K3]

Something similar, to what I believe is being discussed here, exists on reddit. I am not very reddit savvy but El Boberto 2025 has customizable league settings, starter/bench % of Auction Amount, etc.

It is quite nice... I have just started to use it and the anticipated amounts seem off, as it is a keeper league. I guess if you are able to keep Achane for $4, you are much more likely to overspend on some guys (as my league tends to do).

It has been decent at showcasing my keeper values (as I am torn between Worthy for $2 and MHJr for $29, especially with Rice suspension looming). Interested to hear if anyone has used the spreadsheet I am talking about and what their thoughts are on it.

Using that tool as well, but I did have Scoresman run his excel app on my El Boberto 2025 league setup to see what it showed for potential strategy. It definitely helped solidify even more my current draft strategy.