Sensitivity Analysis Option Not Showing

Hi all,

I am working on a fantasy football model using solver, I am simply modifying a model previously posted on these forums but am wanting to do a sensitivity analysis to see how changing the number of players selected affects the total points. I am looking to create the sensitivity analysis report by using solver, however when I use solver there is no option for the sensitivity report so I am completely stumped. I am not an expert with solver so perhaps my model is off that it does not allow a sensitivity analysis. Can somebody please look over my model and help me figure out what I need to change in order to make this happen? Thank you!

Trey

If Solver finds a (locally or globally) optimal solution, and there are no integer constraints, two additional reports are available: the Sensitivity Report and the Limits Report.

Because you have set range "Selec" to binary.

Alf

Thank you for the response Alf,

How would I change my model to allow this sensitivity analysis? Going off what you are saying, I would have to delete the "Selec" column and change my constraints. I am changing the constraints to the following: sum(E2:E12) = 1 to effectively force that constraint to be 1, the same for the rest of the positions.


One type of analysis I want to do would be changing the numbers for each position. I know I can change two positions most at one time, but I am not sure how to modify the model to show this. I was thinking of adding a cell called “Additional”, all the constraints for the number of each position is the sum of the number I would put like 2 and that cell. If i were to change the cell to 1 let's say, it would add a player to another position.

I also want to show a sensitivity analysis for salary if possible.


Trey

With a bit of time on my hands I've set up a slightly different model, main difference is the layout. I've increased the maximum number of players for QB and Defence with one player extra for each group. I've also "relaxed" the setting for "Player Selection", setting it "equal to or less than".

I tested a run where I started with 8 players in cell M211, then changed this value in steps of 1 up to 12 runnin solver after each change.

Have a go at it and see if you find it usefull.

Alf

I think I was able to figure it out, I used solver table and reworked some of the constraints.

I am now trying to create a summary table/tab that shows what my picks were. I am trying to use a VLOOKUP function, however am having trouble because at times there can be multiple running backs, QBs, etc drafted so I am not sure how to tell this function how to lookup those who are selected. Any insight on this?

Trey

Not sure what you wish to achieve. Can you upload a sample sheet with solver results and how your summary table should look. As I'm more into macros than Excel functions my solution if I can find one will probably be macro based. Would that be ok?

Alf