Hi,
First, here is the spreadsheet I am referring to: http://www.filedropper.com/solvertest
I have a list of mutual funds, some normalized performance metrics I want to rank them by, and their future returns. The normalized metrics are in columns G:J, and are weighted and consolidated into a single score in column K. Each score is then assigned a rank from 1-20. In column O are index formulas used to find the future return associated with the fund in each rank. Then in cell R3, I take the average of the funds ranking 1-5.
I want to use the solver to come up with the ideal weights for each of the metrics, which will yield the highest average future return for the top 5 ranked funds (R3). The only constraint is that of course the weights must sum to 1.
When I run the solver, it says it finds a solution but nothing changes. Is this because of the index formulas? Is there a way for this to work or is there perhaps a better way of approaching it?
Thank you!
First, here is the spreadsheet I am referring to: http://www.filedropper.com/solvertest
I have a list of mutual funds, some normalized performance metrics I want to rank them by, and their future returns. The normalized metrics are in columns G:J, and are weighted and consolidated into a single score in column K. Each score is then assigned a rank from 1-20. In column O are index formulas used to find the future return associated with the fund in each rank. Then in cell R3, I take the average of the funds ranking 1-5.
I want to use the solver to come up with the ideal weights for each of the metrics, which will yield the highest average future return for the top 5 ranked funds (R3). The only constraint is that of course the weights must sum to 1.
When I run the solver, it says it finds a solution but nothing changes. Is this because of the index formulas? Is there a way for this to work or is there perhaps a better way of approaching it?
Thank you!
