K
Kevin
Greetings! I am using Solver to optimize my capacity planning and am getting
strange results. I have projects that draw resources from various development
groups. Obviously, for a project to move forward, there must be availability
in all groups related to a project for it to move forward, so the hours
designated for any particular group within a project can not vary. The
project is all or nothing. To accomplish this, I created a coefficient of
either 0 or 1 for each project and each of the group hour values for the
project are multiplied by this coefficient. I directed Solver to adjust the
coefficients to optimize the project blend to minimize the number of unused
hours without any groups running into negative hours. The following is a
brief example:
Unused Hours 150 0 -20
Available Hours 500 300 30
Project Hours 350 300 50
Coef. Total Group1 Group2
1 100 100 0
1 250 200 50
In the above scenario, Solver should optimize it as follows:
Unused Hours 230 200 30
Available Hours 500 300 30
Project Hours 100 100 0
Coef. Total Group1 Group2
1 100 100 0
0 0 0 0
And it does solve it correctly some of the time, but as projects are added
or deleted from the list, it often returns the message that "Solver could not
find a feasible solution" even when one clearly exists.
I have tried adjusting all of the Solver options for precision, tolerance,
etc. but to no avail. I do not know where to go from here. I created a
smaller model that works every time, but my larger real-world data does not
work consistently.
I would appreciate any help you could give. Also, I would like to know of
any resources that are available for Solver that might help me to fine tune
the settings. The Excel help files are very inadequate on this topic.
Thanks!
strange results. I have projects that draw resources from various development
groups. Obviously, for a project to move forward, there must be availability
in all groups related to a project for it to move forward, so the hours
designated for any particular group within a project can not vary. The
project is all or nothing. To accomplish this, I created a coefficient of
either 0 or 1 for each project and each of the group hour values for the
project are multiplied by this coefficient. I directed Solver to adjust the
coefficients to optimize the project blend to minimize the number of unused
hours without any groups running into negative hours. The following is a
brief example:
Unused Hours 150 0 -20
Available Hours 500 300 30
Project Hours 350 300 50
Coef. Total Group1 Group2
1 100 100 0
1 250 200 50
In the above scenario, Solver should optimize it as follows:
Unused Hours 230 200 30
Available Hours 500 300 30
Project Hours 100 100 0
Coef. Total Group1 Group2
1 100 100 0
0 0 0 0
And it does solve it correctly some of the time, but as projects are added
or deleted from the list, it often returns the message that "Solver could not
find a feasible solution" even when one clearly exists.
I have tried adjusting all of the Solver options for precision, tolerance,
etc. but to no avail. I do not know where to go from here. I created a
smaller model that works every time, but my larger real-world data does not
work consistently.
I would appreciate any help you could give. Also, I would like to know of
any resources that are available for Solver that might help me to fine tune
the settings. The Excel help files are very inadequate on this topic.
Thanks!