Please answer all parts of question 3:Portfolio analysis attached below. To answer the question use the excel spread sheet return data attachment and complete all the answers on the excel spreadsheet. MAKE sure to follow the questions instructions word by word and use the EXACT excel function that they tell you to use in the question.

No plagiarism and please reference any citations

Thanks

QUESTION 3: PORTFOLIO ANALYSIS (43 Marks)

Jane is considering investing in three different securities or creating three distinctive two-factor portfolios. She is able to obtain the monthly returns of securities A, B & C for the years 2007 to 2014(The attached file (Stock returns 2007-14) gives 96 monthly returns for three securities along with the returns of the NZX all share index for the period January 2007 and December 2014).In any of the possible two factor portfolios, the weight of each security in the portfolio will be 50%.The three possible portfolio combinations are AB, AC and BC.Required:

a) Determine, using the appropriate Excel function (see fx) the average monthly return, the standard deviation and variance for each of the companies. (Use the 96 months returns data in the calculations and use the Excel functions identified as ?Variance P? and ?Standard Deviation P?.) (3 Marks)

b) Determine, using the appropriate Excel function the covariances between securities A&B;B&C; A&C. (Use the 96 months returns data in the calculations and use the Excel function?COVARIANCE P?.) (3 Marks)

c) Calculate using the two-factor portfolio equations, the portfolio returns and risks (standard deviation and variance) for the following portfolios:

a. A and B

b.B and C

c.C and A (12 Marks)

d) Would you recommend that Jane invest in the single securities of A, B or C or the portfolios consisting of securities A&B or A&C or B&C? Explain your answer from a risk-return viewpoint. (10 Marks)

e) Determine the betas Security A, a utility company, security B, a construction company, and security C, a manufacturing company by regressing the returns for each of the companies on the returns for the NZX ALL Share Index (the first column in the spread-sheet).

(Regression Calculation: Go to Data Analysis - far right under Data- and choose regression. If Data Analysis does not appear it must be added, it will be available in Excel. Go to Options under File and choose Add-ins and then Data Analysis the company returns constitute the Y input and the index returns the X input. Alternatively, the ?slope? found in f(x) also represents Beta).

a. Explain what the values of the betas (the slope coefficients in the regression)indicate and discuss the factors that might explain the differences in the values of the betas of the three companies.

b. Comment on the implications of the estimated value of beta for investors and the cost of capital for the two companies (15 Marks)

Return Data for a sample of companies drawn from NZX from January 2007 to December 2014. Month All Share index Security A Security B Security C 1/31/2007 -0.00253 -0.05098 -0.04292 0.01445 2/28/2007 -0.00169 0.02200 -0.00338 0.08828 3/30/2007 0.03343 0.12628 -0.05250 0.10917 4/30/2007 0.02449 -0.00298 -0.01719 -0.00354 5/31/2007 0.02820 0.13863 0.00460 0.08947 6/29/2007 -0.00757 -0.03093 -0.08983 0.13008 7/31/2007 -0.03326 -0.04255 -0.05841 0.05970 8/31/2007 -0.00270 -0.01387 -0.00695 -0.00950 9/28/2007 0.01893 0.15647 -0.19440 0.21041 10/31/2007 0.04347 0.00760 -0.09519 0.04629 11/30/2007 -0.04747 -0.00845 -0.28697 -0.12233 12/31/2007 0.00260 -0.06269 -0.02096 -0.03796 1/31/2008 -0.08666 -0.10714 -0.07355 -0.04463 2/29/2008 0.00769 0.17600 -0.02844 0.11327 3/31/2008 -0.02054 -0.05114 0.00854 -0.08283 4/30/2008 0.06257 0.07959 -0.30724 0.20402 5/30/2008 -0.00199 0.04588 -0.33937 0.06334 6/30/2008 -0.07065 0.03130 -0.68219 0.00313 7/31/2008 -0.03601 -0.17158 0.63793 -0.11823 8/29/2008 0.04995 0.01238 0.54737 0.01477 9/30/2008 -0.13238 -0.36732 -0.28741 -0.25610 10/31/2008 -0.11897 -0.17413 -0.26492 -0.16362 11/28/2008 -0.01669 0.00130 -0.39935 0.12916 12/31/2008 0.03673 0.00782 0.51351 0.08831 1/30/2009 -0.05829 -0.18111 -0.00714 -0.08733 2/27/2009 -0.06520 -0.20931 0.23022 -0.03940 3/31/2009 0.03279 0.18481 0.03801 0.25226 4/30/2009 0.09944 0.25125 0.57746 0.02816 5/29/2009 0.04191 0.19744 0.13214 0.03652 6/30/2009 -0.03207 -0.00759 -0.06940 -0.07588 7/31/2009 0.08503 0.09441 0.34576 0.14589 8/31/2009 0.07701 0.04716 0.23829 0.04159 9/30/2009 0.04722 -0.01386 -0.00163 0.06613 10/30/2009 -0.01821 0.11139 -0.16045 -0.03776 11/30/2009 0.02960 0.17517 -0.12509 0.13022 12/31/2009 0.04342 0.04149 0.04907 0.07402 1/29/2010 -0.03572 -0.14349 -0.03468 -0.06466 2/26/2010 0.03373 0.02928 -0.04929 0.07557 3/31/2010 0.06758 0.20251 0.08963 0.14067 4/30/2010 -0.01389 -0.02383 0.00565 -0.10377 5/31/2010 -0.06225 -0.05150 -0.13392 -0.05554 6/30/2010 -0.04625 -0.11668 -0.12222 -0.08285 7/30/2010 0.06895 0.07402 0.01530 0.11228 8/31/2010 -0.00225 -0.06695 -0.00208 -0.06149 9/30/2010 0.06541 0.07928 0.02811 0.12265 10/29/2010 0.02534 0.15146 -0.20911 0.09308 11/30/2010 12/31/2010 1/31/2011 2/28/2011 3/31/2011 4/29/2011 5/31/2011 6/30/2011 7/29/2011 8/31/2011 9/30/2011 10/31/2011 11/30/2011 12/30/2011 1/31/2012 2/29/2012 3/30/2012 4/30/2012 5/31/2012 6/29/2012 7/31/2012 8/31/2012 9/28/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/29/2013 4/30/2013 5/31/2013 6/28/2013 7/31/2013 8/30/2013 9/30/2013 10/31/2013 11/29/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/30/2014 6/30/2014 7/31/2014 8/29/2014 9/30/2014 10/31/2014 -0.02256 0.07141 -0.00528 0.02398 -0.00811 0.03111 -0.00716 -0.00450 -0.02203 -0.06887 -0.05007 0.07891 -0.00373 0.00840 0.02715 0.04289 -0.00949 -0.00328 -0.06797 0.04819 0.01331 0.02212 0.01086 0.01034 0.01754 0.00988 0.06372 0.02278 0.01405 0.00579 0.02912 -0.04990 0.06783 -0.02213 0.01116 0.04286 -0.00700 0.01836 -0.03057 0.05242 -0.02605 0.02162 0.01367 -0.01277 -0.00299 0.02152 -0.02769 -0.00686 -0.03009 0.18260 -0.08185 0.08849 -0.03052 -0.02697 -0.02900 0.01897 -0.06073 -0.10860 -0.13184 0.02893 0.05516 -0.01694 0.10299 0.00972 -0.10704 0.01327 -0.16765 0.05961 -0.09026 -0.06927 0.03828 0.04734 -0.08960 0.09321 -0.00395 0.01882 -0.10228 -0.07506 -0.02460 -0.17066 0.11217 0.06406 0.02672 -0.02174 -0.09158 -0.02150 0.08788 0.06616 0.01995 0.03636 -0.07869 -0.01888 0.11818 -0.03157 -0.09542 -0.04877 -0.10307 0.26553 0.03948 0.16549 0.02514 0.01544 0.01431 0.00705 -0.13748 -0.16497 -0.04620 0.13193 0.11993 -0.06586 0.17546 0.34799 -0.04212 -0.05177 -0.10322 0.16013 -0.04960 0.13464 0.13000 0.11858 0.02901 0.06407 0.04817 0.10340 0.14161 0.13535 0.02764 -0.03190 0.05297 -0.06902 0.01680 0.09387 -0.01761 0.06015 0.08481 0.16218 -0.06250 -0.09639 -0.03329 0.04619 -0.06556 0.05928 0.07083 0.07563 0.03230 0.11641 -0.06703 0.02248 0.02293 0.02622 -0.04813 0.02039 -0.07300 -0.07347 -0.16076 0.13205 -0.00941 -0.03668 0.13076 -0.02406 -0.06380 0.03513 -0.14181 0.06580 0.03488 -0.01766 0.06960 0.03143 -0.01184 0.08537 0.01291 -0.03153 -0.06659 -0.06475 0.06952 -0.12191 0.11713 -0.00053 -0.01165 0.06071 -0.03704 0.00538 -0.03932 0.07407 -0.02591 0.04067 -0.02658 0.01150 0.07383 -0.05988 -0.08300 -0.06093 11/28/2014 12/31/2014 0.02918 -0.01600 0.00380 -0.09157 0.09909 0.02325 -0.05806 -0.08470