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:
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?