I am developing a simple project selection tool for our area. I have the usual constraints ($$$ and # of resources). I want to select as many projects with the shortest ROI Periods AND the largest contribution to Cost Reduction. Since some projects are also regulatory in nature, I have to be able to "force" some of them to be selected.
I can easily setup Solver to pick the projects that maximize the Cost reduction, meet the forced-in requirement, and staying within my overall $$$ and # of resource constraints.
However, I am having trouble adding in the criteria to pick projects with the shortest ROI while meeting everything above. I am sure I am missing something obvious, but since I want the shortest ROI periods but the largest cost reduction contributions, I am struggling to setup the problem correctly within Solver for the combined objectives. I am attaching a picture of my setup, but the objective cell is only focused on Cost Reduction (in the picture).
Any thoughts? Thx!
[ATTACH=CONFIG]37857[/ATTACH]
Solver for Project Selection w/ Two Inverse Objectives
-
-
Re: Solver for Project Selection w/ Two Inverse Objectives
I viewed your problem although since it is jpeg I couldnt experiment on it...these are my two cents
1. Solver can only optimise for one target at a time...although I believe there are some software available that can do mutiple optimisation simultaneously
2. If you have to solve for multiple optimisation then one approach is to set the remaining factors as constraints....for eg you set the problem as Minimize ROI st Cost Control is greater than or equal to 20%
3. You may simplify the problem by removing the compulsory projects from the constraints and accordingly reducing the resources and giving yourself minimum ROI and cost control
hope this helps -
Re: Solver for Project Selection w/ Two Inverse Objectives
Thanks.
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.
Thoughts?
Thanks!
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!