Please see the attached file and follow along. I'm trying to create a solver/optimization for 2 variables. Cost and Capability Score.
Please see the constraints in the solver to understand the optimization. Basically, the allocation of Flight hours to aircraft must be done at the lowest cost possible while maximizing the capability score AND not exceeding the budget constraint or the required amount of flight hours for each capability.
While this solver works, I have noticed a few issues inherent with the optimization:
1. It placed 500 Hover hours on the Fixed Wing Prop even though the fixed wing prop has a capability score of 0 when it comes to hovering....
The solver should never put any amount of hours on an aircraft that is "unacceptable" when it comes to performing the capability(ala fixed wing aircraft can't hover... duh
2. It placed 500 High Alt. hours on the Fixed Wing Prop because there wasn't enough $$$$ left to fly those on the Jet.... Okay this makes sense, but what if I consider High Alt. Hours more important than say the Hover Hours. I think this is where a "Importance Score" has to play a role. For example, what if it is more desirable fore me to have a SOLVEDNUMBER of high altitude hours performed by a Jet than it is to have a SOLVEDNUMBER of Hover hours performed by a Helicopter.
Conceptually, the idea of #2 and an Importance Score seems logical; however, its incorporation into the solver constraints is what I can't figure out...
3. Lastly, I told excel that anything in the green shaded area should be an integer, solver doesn't seem to want to listen to that.
PS If there is a better way to formulate "lowest cost" please let me know, I didn't know how to articulate this idea without including a manual budget constraint field