1 Reply Latest reply: May 2, 2012 8:33 AM by 934263 RSS

    How to link two rows together? Weights should be tied to returns!!

      Hello CB experts! I have a question. It may be simple or it may not; not sure. So, I’m looking for some guidance here. I have historical return on my sheet named ‘Symbols’. In my ‘Returns’ sheet, I have Shares, Return, Volatility, and Weights, along with a Covariance Matrix and a Correlation Matrix on the sheet. I put an Efficient Frontier curve in there and the top performing components of the Dow. I also, have a sheet named ‘Returns-CB’; this is where my CB analysis is. So, from E6:AH6 and E8:AH8, I have defined CB assumptions (they are pretty much all the same). Anyway, my question is this: how do I link the items in row 6 to the items in row 8? If I have 12% return and 30% return for stock AA and stock BA, I should NOT have 0% weight; I want to invest in these stocks. Similarly, if I have a weight of 15% for stock CAT, I should NOT have 0% return. This doesn’t make any sense. Can someone please tell me how to link the returns and weights. It works perfectly fine in the ‘Returns’ sheet; I used MS Solver to get this working. I was trying to do something similar in the ‘Returns-CB’ sheet.

      You can download the file from here:

      Thanks everyone!!
        • 1. Re: How to link two rows together? Weights should be tied to returns!!
          I know this has something to do with constraints. For instance, Cell C8 must = 100%. Cells E8:AH8 will change, depending on the weights of the stocks (this is contingent on risk; defined assumptions and parameters of the distribution curves). Likewise, Cells E6:AH6 must change, depending on the potential future returns of the stocks (this is also contingent on the risk). It seems like, if I define an assumption, it wipes out any formula that I have in the cell. So, I'm not sure how to 'link' the E6:AH6 range and the E8:AH8 range. Does anyone here have any ideas on this?