A few months ago I built a solver tool for work that optimized cuts to various projects. Given the number of projects, the number of decision variables was greater than standard solver could handle, so I used Premium Solver Platform from Frontline and it all worked great. So well in fact, that I've been asked to basically build the whole thing over again, but this time for a smaller number of projects so that it can be done with standard solver, and thus shared widely in the organization rather than only to those of us who have Premium Solver Platform licenses.
The data setup looks like this:
<tbody>
</tbody>
So the idea is that the different projects are weighted, and they having a starting "risk status" in the start column. The "cut" columns represent dollars that can be taken from these projects' budgets and leave them in the corresponding risk category. So for instance Project A is currently green, and the project manager has identified a $1000 cut that will make it orange, and a $500 cut that will still leave it green. Not every cut column will have a value. The "taken" columns are the decision variables and will be binary, and if there is no cut identified, there won't be a decision variable added to the model. There will be "cascading" constraints, so for example in Project A you can't take the $1000 orange cut unless you also take the $500 green cut. Project D can't take the $2500 red cut unless it first takes the $250 yellow cut, etc. There is a "roll up" rule that gives an overall risk color to the set of projects, based on the colors of the individual projects and their weights. The goal of the optimization will be to maximize the dollars cut and leave the overall set at each of the different risk colors.
If you're still reading, thank you!
Here's where I need help...or at least some advice before I embark on coding this. With the API for Premium Solver Platform, you don't have to specify the bychange parameter in the solverok call. You could just loop your way along making calls that looked like:
to add your decision variables, and calls like:
to handle the "cascading" constraints. And when you got to the end you could call
and all your binary variables would be there complete with their cascading constraints.
I don't believe it works this way with the standard solver, does it? Do I first need to loop through and identify all of the decision cells and concatenate them all together into a string and call
so that I can get them all into the model, and then loop through again and add all the binary and cascading constraints?
I hope this question makes sense....please feel free to hit me up for clarification.
The data setup looks like this:
| Project | Weight | Start | Finish | Red Cut | Taken | Orange Cut | Taken? | Yellow Cut | Taken | Green Cut | Taken | Total Cuts |
| A | 0.1 | Green | ? | 0 | 0 | -1000 | 0 | 0 | 0 | -500 | 0 | 0 |
| B | 0.25 | Yellow | ? | -5000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| C | 0.05 | Orange | ? | -1000 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| D | 0.15 | Yellow | ? | -2500 | 0 | 0 | 0 | -250 | 0 | 0 | 0 | 0 |
| etc |
<tbody>
</tbody>
So the idea is that the different projects are weighted, and they having a starting "risk status" in the start column. The "cut" columns represent dollars that can be taken from these projects' budgets and leave them in the corresponding risk category. So for instance Project A is currently green, and the project manager has identified a $1000 cut that will make it orange, and a $500 cut that will still leave it green. Not every cut column will have a value. The "taken" columns are the decision variables and will be binary, and if there is no cut identified, there won't be a decision variable added to the model. There will be "cascading" constraints, so for example in Project A you can't take the $1000 orange cut unless you also take the $500 green cut. Project D can't take the $2500 red cut unless it first takes the $250 yellow cut, etc. There is a "roll up" rule that gives an overall risk color to the set of projects, based on the colors of the individual projects and their weights. The goal of the optimization will be to maximize the dollars cut and leave the overall set at each of the different risk colors.
If you're still reading, thank you!
Here's where I need help...or at least some advice before I embark on coding this. With the API for Premium Solver Platform, you don't have to specify the bychange parameter in the solverok call. You could just loop your way along making calls that looked like:
Code:
solveradd cellref:="$M$" & i+1
solveradd cellref:="$M$" & i+1, relation:=5, formulatext:=binaryto add your decision variables, and calls like:
Code:
solveradd cellref:="$M$" & i+1, relation:=1, formulatext:=$I$" & i+1to handle the "cascading" constraints. And when you got to the end you could call
Code:
solverok setcell:=$Q$12", MaxMinVal:=2, ValueOf:="0", Engine:=3
set some options
solversolveand all your binary variables would be there complete with their cascading constraints.
I don't believe it works this way with the standard solver, does it? Do I first need to loop through and identify all of the decision cells and concatenate them all together into a string and call
Code:
solverok setcell:=blah, MaxMinVal:=2, ValueOf:=0, ByChange:=decision_variable_string, Engine:=3so that I can get them all into the model, and then loop through again and add all the binary and cascading constraints?
I hope this question makes sense....please feel free to hit me up for clarification.
