What's new
Fantasy Football - Footballguys Forums

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

Excel TIP: How to optimize your Budget using Draft Dominator and Excel (1 Viewer)

mambomambo

Footballguy
After reading someone's question in This Thread

I came up with a simple excel solution for finding the Optimal Budget for your team to spend in a Auction Draft.

Instead of relying on intuition, we might as well use the Player Pool CSV file that can be exported from DD's projections based on your own league's setting in order to solve this problem analytically.

This are the simple steps necessary to get this done:

Excel Set up:

1) Once your league is set up in DD correctly with the projections you want to use, Export the Player Pool into a CSV file

2) Sort both these Columns in ascending order, First Column=DynAuction, Second Column=Points and rename the sheet to "Overall"

3) Move the Points Column to the right of the DynAuction Column (this is not needed, but will facilitate the use of the Vlookup function)

4) Move the Player Name Column to the right if the Points Column (this is not needed, but will facilitate the use of the Vlookup function)

5) Add a worksheet to the workbook, this will be your Budget Sheet (call

6) I would add 4 columns, Position / Player Name / Budget / Next Closest Salary / Points. Make sure you add the totals for the Budget and Points column.

7) In each Row under the Position Column, add the starting positions. i.e= QB1, RB1, RB2, RB3, etc....

8) Enter some dummy Values for each position under Budget to make sure it works correctly later

Excel VLookups:

9) For Each Position under the Next Closest Salary use a Vlookup formula to look at the next highest corresponding salary amount in the “Overall” Tab that is under the Budget # for this position.

10) Do the same thing for the Points Column. Use a Vlookup formula to look at the Points Column in the “Overall” Tab that corresponds to the Next Closest Salary # for this position.

11) Do the same thing for the Player Name Column. Use a Vlookup formula to look at the Player Name Column in the “Overall” Tab that corresponds to the Points # for this position.

12) Now the model is ready. Please note that you could combine steps 9-11 into one formula, but I prefer to separate it in order to see the corresponding values.

You could stop here if you are not familiar with the Evolver Function or are not Excel inclined.... You can just use trial error by changing the Budget #’s for each position to Maximize the Total Points Column.

Solver Function: FORGET ABOUT USING SOLVER....USE EVOLVER INSTEAD

FOrget Solver. DOWNLOAD a free trial of the Excel Add-in EVOLVER from Palisades. This is like SOLVER on Steroids. You can use the fully functional free trial for 15 days...plenty of time to get your model going.

The problem with Excel’s Solver is the “hill climbing” issue. What Evolver will do is run thousands of iterations of combinations of solutions in order to find the best overall answer by exploring the entire universe of possible answers. Just to give you an idea, my Run last night took 29 minutes!!!

In 29 Minutes I had found the MOST optimal solution. If you know how to use Solver, Evolver is a piece of cake. Similar, but more powerful set up.

I guarantee you will be impressed.

16) :lmao: WOW!! …Evolver took 29 minutes to find the optimal budgeting solution to my League . You now have found the optimal budget solution. Not only each position will show the corresponding Max Budget allocated to it, but it will also show who is the best player that fit that particular budget.

17) Have fun in your Auction!!

:goodposting:

Mambo Mambo

 
Last edited by a moderator:
That's awesome and I will play with it. I was writing something for the other thread and decided not to post it (lurking league members), but this is an excerpt I think applies here.

VBD is great but without sound ADP data, it's flawed. The same is true for auctions without solid average cost data. If VBD and ADP suggest the number 20 overall player is on average the 75th player chosen, you have to guess the best time to draft him. 20 is stupid and 75 is risky. Probably between 48 and 60 makes sense for being both safe and smart. This also applies to auctions. If value based projections and cost data tell us the #20 overall player on average has the 75th highest price, you have to guess how much to bid for him between those parameters. By mid-August there will be enough mock and real auctions done for us to have very good average cost information (percentages) to generate these kind of value based scenarios for offensive players only.

The IDPs are a problem. There aren't enough redraft IDP auctions to start with, and what's out there is so varied, from start 2s with minimal scoring to start 11s with heavy weighting and everything inbetween, that any ADP-like information is worthless.

 
Excellent idea. We've been auction drafting in my league for the past 5 years of so. I always go in with a budget and a group of targeted players that match that budget. My budget had been allocated by experience, impacts of keepers (2 or 3 per team, escalating salaries), projected stats and values from DD, and prior year drafting trends.

I hadn't really tested the question: "Is my budget the best one to gain the most points".

SO I invested a little time with the idea above, and while it has some real promise, I don't think I'm getting the answer to my question.

First, using solver, the the results for each of the budgets tend to be more influenced by the starting values in the budget fields than I would like. If I start the QB postion at a budget of 50, it tends to stay in that area.

Second, my implementation does not appear to be really maximizing the total points. Different iterations of solver net me wide swings on max points. One version will net 934 points, then 943, then 945, 899, etc. Perhaps I don't have things setup exactly correct, but the help function for Solver says the same things - change the starting values to change the results.

Anyone else run into a similar issue?

 
Excellent idea. We've been auction drafting in my league for the past 5 years of so. I always go in with a budget and a group of targeted players that match that budget. My budget had been allocated by experience, impacts of keepers (2 or 3 per team, escalating salaries), projected stats and values from DD, and prior year drafting trends.

I hadn't really tested the question: "Is my budget the best one to gain the most points".

SO I invested a little time with the idea above, and while it has some real promise, I don't think I'm getting the answer to my question.

First, using solver, the the results for each of the budgets tend to be more influenced by the starting values in the budget fields than I would like. If I start the QB postion at a budget of 50, it tends to stay in that area.

Second, my implementation does not appear to be really maximizing the total points. Different iterations of solver net me wide swings on max points. One version will net 934 points, then 943, then 945, 899, etc. Perhaps I don't have things setup exactly correct, but the help function for Solver says the same things - change the starting values to change the results.

Anyone else run into a similar issue?
I hear you. Late last night I decided against going with Solver for some of its limitations. You can still do it, but your constraints need to be 100% correct.My SUGGESTION:

FOrget Solver. DOWNLOAD a free trial of the Excel Add-in EVOLVER from Palisades. This is like SOLVER on Steroids. You can use the fully functional free trial for 15 days...plenty of time to get your model going.

The problem with Excel’s Solver is the “hill climbing” issue. What Evolver will do is run thousands of iterations of combinations of solutions in order to find the best overall answer by exploring the entire universe of possible answers. Just to give you an idea, my Run last night took 29 minutes!!!

In 29 Minutes I had found the MOST optimal solution. If you know how to use Solver, Evolver is a piece of cake. Similar, but more powerful set up.

I guarantee you will be impressed.

 
Just FYI...something I noticed in my league. I'm using my new budget done using Evolver and most people would be surprised to see what the allocation of $ per position is versus what you thought intuitively it should be.....I was WAY off in my 1st draft of my Budget.....Good thing I corrected in time for my draft that is ongoing now.....Once I'm done, I'll post my Optimum Budget Team....

 
Just FYI...something I noticed in my league. I'm using my new budget done using Evolver and most people would be surprised to see what the allocation of $ per position is versus what you thought intuitively it should be.....I was WAY off in my 1st draft of my Budget.....Good thing I corrected in time for my draft that is ongoing now.....Once I'm done, I'll post my Optimum Budget Team....
link to draft ? :popcorn:
 
Just FYI...something I noticed in my league. I'm using my new budget done using Evolver and most people would be surprised to see what the allocation of $ per position is versus what you thought intuitively it should be.....I was WAY off in my 1st draft of my Budget.....Good thing I corrected in time for my draft that is ongoing now.....Once I'm done, I'll post my Optimum Budget Team....
Something to be careful of here; errors in projections of players are generally not symmetric by position. That is, QBs as a class tend to have their projections overstated by a larger amount than WRs or RBs as a class. This is a problem with anything based on VBD, but it's especially a problem if you're drawing conclusions based on solving for a bunch of equations using skewed VBD numbers.
 
Just FYI...something I noticed in my league. I'm using my new budget done using Evolver and most people would be surprised to see what the allocation of $ per position is versus what you thought intuitively it should be.....I was WAY off in my 1st draft of my Budget.....Good thing I corrected in time for my draft that is ongoing now.....Once I'm done, I'll post my Optimum Budget Team....
Something to be careful of here; errors in projections of players are generally not symmetric by position. That is, QBs as a class tend to have their projections overstated by a larger amount than WRs or RBs as a class. This is a problem with anything based on VBD, but it's especially a problem if you're drawing conclusions based on solving for a bunch of equations using skewed VBD numbers.
True, but then again, in the last 3 years of auction leagues that I played on (8 leagues), only once the winning team had a top5 pre-season QB on roster.....the other 7 times teams that won had between QB8 and QB 14
 
Can someone please give an example of the VLOOKUP's? I do not understand how to look up a position such as QB and find the highest remaining player under a budget number.

Thanks in advance,

SB

 
Can someone please give an example of the VLOOKUP's? I do not understand how to look up a position such as QB and find the highest remaining player under a budget number.Thanks in advance,SB
Is there anyone with an example they can email or explain? The approach looks promising and I would like to play with it, but I'm stuck on some basics.
 
Can someone please give an example of the VLOOKUP's? I do not understand how to look up a position such as QB and find the highest remaining player under a budget number.Thanks in advance,SB
Is there anyone with an example they can email or explain? The approach looks promising and I would like to play with it, but I'm stuck on some basics.
I will post my excel model tomorrow here for all to see.
 
Made some adjustments here.....should be good for this fall.....
Is there supposed to be an Excel file attached here? If you posted your Excel spreadsheet, I would be ecstatic, so I could actually see how you set everything up. I am intrigued by this Evolver solution since I have always maintained there must be an optimal budget allocation.
 
Is there supposed to be an Excel file attached here? If you posted your Excel spreadsheet, I would be ecstatic, so I could actually see how you set everything up. I am intrigued by this Evolver solution since I have always maintained there must be an optimal budget allocation.
Same here.
 
Made some adjustments here.....should be good for this fall.....
Is there supposed to be an Excel file attached here? If you posted your Excel spreadsheet, I would be ecstatic, so I could actually see how you set everything up. I am intrigued by this Evolver solution since I have always maintained there must be an optimal budget allocation.
Yes, it optimizes your budget allocation, and I use it for that reason. But be aware that it is using point projections and best guesses at pricing to optimize. I used it with average pricing from a couple of years of history and the corresponding point projections for the position rank (think AVT). It optimizes and finds the best mix for those assumptions. I tried a variety of constraints to see what would happen if prices were higher for a position etc. to see if it would find something close (within 16 pts is a wash). An excellent tool, but be careful about just accepting the optimal selection based on projected data.
 
Stickboy is one of my power users :pickle: !!

SOrry for the long delay....

I've been swamped with other serious issues such as IDP drafts, etc... :thumbup:

I started writing an article that Pasquino requested last year about this, but never finished, I;m working on this now.

I will post an excel sheet hopefully this week. But you are on your own with Evolver as it is a $1000+ application.....if you want me to run your evolver scenario, I will do it, but it won't be free :stirspot:

 

Users who are viewing this thread

Back
Top