Re: Solver for Project Selection w/ Two Inverse Objectives
I was hoping to define the target with a formula to capture the idea of the lowest ROI + the highest Cost Reduction as a way to maintain a single target, but with my two criteria. However, anything I came up with rarely yielded good selections.
For example, I inverted the Cost Reduction percentages so that the goal would be to reduce both the ROI and the Cost Reduction (so target was Sum of ROI Projects Selected + ( 1 - Sum of Cost Reduction Projects Selected)) and I told Solver to minimize the target by changing the projects selected AND staying within the constraints of my budget and people available for projects.
Specifically, the goal of minimizing the ROI would be to simply not do any projects (bad choice), so generally, the Solver would drive very few projects being selected, leaving me with money and people available to still work on projects...
My only solution (so far) was based on your suggestion of an additional constraint. I outlined the minimum number of projects I SHOULD be able to do with the $$$ and people available, and set that as a constraint (i.e. Solver had to pick at least that many projects). The solutions are better, but still doesn't really select shorter ROI projects when it can/should.