Since the S&P/ASX 200 index is not traded, you wish to construct a portfolio consisting of the seven stocks that ‘tracks’ the index as closely as possible (in some sense). Your boss asks you to propose at least two different methods for constructing such a tracking portfolio. After some careful research you come up with two possible methods and to implement these you must perform the following tasks (Hint - you will need to use Solver):

5. (a) Report the weights (in the seven stocks) of the portfolio whose variance is minimised but whose sensitivity to the changes in the market index is exactly one, i.e., that has βP = 1. You should describe in words what you have done in EXCEL and report the value of your portfolio’s (minimised) variance.

(b) Report the weights (in the seven stocks) of the portfolio that minimises the Root Mean Square Error (RMSE) of the difference in weekly returns between the portfolio and the S&P/ASX 200 index. More specifically, let r1, . . . , rT be the vector-valued sample returns of the seven stocks, for t = 1, . . . , T weeks. Similarly, let rI,1, . . . , rI,T denote the sample returns of the market index. Then you want to find the vector of portfolio weights that solves the following minimisation problem:

Again, you should describe in words what you have done in EXCEL as well as report the minimum value of the RMSE achieved.

(c) Report the expected return, variance, beta and R2 for your two tracking portfolios constructed above. Which method do you recommend to your boss and why?

Each member of your group proff-reads the final document before it is presented to your boss. She will ultimately assess the quality of your work and decide whether to implement the tracking portfolio you constructed.

Data Link