need help designing a model and using excel

jpildave

Hello Everyone,

I would like some help developing a model. I am using the Solver in Excel, but the results are giving me counter-intuitive results. I believe it is because my model is incorrect. Once I get some help perfecting the model, then I will worry about translating it for the Solver in Excel. The following is a description of what I'm looking for.

Let's say I have a car and I want to maximize my ROI.
1. I can invest in any component of the car, like the engine, suspension, brakes, tires, interior, sound, etc.
2. I rank the order of importance of these components using AHP (don't need help with this).
3. I enter in my current baselines, which are completely subjective, but something like 40% across the board for all of the components (for example sake). In other words, my car is an all-around 40% of my ideal 100%.
4. I enter in what I want to invest in and what I think my gain will be. For example, one of my investments is that for $5000, I can improve my suspension by 20%. So forth and so on.

I want the program to be able to tell me how to re-arrange my dollars by maximizing my ROI. However, keep in mind that each component has its own importance ranking. Also, consider a saturation curve in that as components go towards 100%, the saturation increases, thus making a less attractive investment.

There are other constraints like my total initial investment amount must equal the total suggested investment amount, but I won't name them all, and I probably need help developing those too.

Another final piece to this puzzle is something like sustaining a component, but first I would like to solve the simpler version. In case you are wondering though, the sustainment piece also incorporates something like: If I don't invest $1000 in my tires, then I will lose 20% in tires.

I don't know how difficult this model is on a scale of 1 to 10. Any help would be appreciated.

Thanks.

jpildave

Nevermind, I think I got it. But if anyone wants to send comments, feel free. Thanks.