Posts by zzzzigs

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!

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]