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)
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!!
Mambo Mambo
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)

17) Have fun in your Auction!!

Mambo Mambo
Last edited by a moderator: