Amanda Nguyen is considering investing in four different two-factor portfolios. She can obtain the weekly returns of securities A, B, C and D for the period from August 2012 to August 2017 (The attached file (Stock returns Aug 2012-Aug 2017) gives 260 weekly returns for four securities along with the returns of the NZX all share index for the period from August 2012 to August 2017).In any of the possible two-factor portfolios, the weight of each security in the portfolio will be 50%. The four possible portfolio combinations are A&B; B&C; A&C; C&D; A&D and B&D.

Required:

a)Determine, using the appropriate Excel function (see f_x) the average weekly return, the standard deviation, and variance for each of the companies. (Use the 260 weekly returns data in the calculations and use the Excel functions identified as "Variance P" and "Standard Deviation P.")

(4 marks)

b)Determine, using the appropriate Excel function the covariance between securities A&B; B&C; A&C; C&D; A&D and B&D.(Use the 260 weekly returns data in the calculations and use the Excel function "COVARIANCE P.")(4 marks)

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

a.A and B

b.B and C

c.A and C

d.C and D

e.A and D

f.B and D(6 marks)

d)By computing the ratio of () select the best investment that Amanda should undertake, assuming she is risk averse. Explain the rationale that you have used in making this choice.(4 marks)

e)Draw a portfolio graph (showing Risk on the X-axis and Return on the Y-axis) for the investment portfolio that you have recommended in (d) above for a range of investment weights that Amanda could choose from (i.e., Amanda could invest 0% in one company and 100% in the other or 5% in one company and 95% in the other and so on). Determine from the portfolio graph, the minimum risk that Amanda could obtain for this portfolio and the respective weightings that should be invested in each of the securities in the portfolio. (6 marks)

f)Determine the betas for Securities A, B, C and D by regressing the returns of each of the companies on the returns for the NZX ALL Share Index (the first column in the spreadsheet). Security A is in construction industry, securities B and D are in Health care sector, and security C is in entertainment sector,

(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. (8 marks)

b.Should Amanda decide to invest in all the four companies in equal proportions? Compute the return and risk of the portfolio? (3 marks)

Return Data (weekly )for a sample of companies drawn from NZX from 26th August 2012 to 13th August 2017 Period All Share index 8/13/2017 0.0055 8/6/2017 -0.0035 7/30/2017 0.0140 7/23/2017 -0.0041 7/16/2017 0.0028 7/9/2017 0.0036 7/2/2017 0.0014 6/25/2017 0.0077 6/18/2017 0.0001 6/11/2017 0.0157 6/4/2017 -0.0085 5/28/2017 0.0078 5/21/2017 0.0067 5/14/2017 -0.0081 5/7/2017 0.0118 4/30/2017 -0.0018 4/23/2017 0.0252 4/16/2017 -0.0045 4/9/2017 -0.0019 4/2/2017 0.0065 3/26/2017 0.0174 3/19/2017 -0.0118 3/12/2017 -0.0027 3/5/2017 0.0023 2/26/2017 0.0145 2/19/2017 -0.0049 2/12/2017 -0.0015 2/5/2017 0.0014 1/29/2017 -0.0056 1/22/2017 0.0122 1/15/2017 0.0002 1/8/2017 0.0109 1/1/2017 0.0130 12/25/2016 0.0006 12/18/2016 0.0173 12/11/2016 -0.0193 12/4/2016 -0.0017 11/27/2016 0.0008 11/20/2016 0.0061 11/13/2016 0.0239 11/6/2016 -0.0016 10/30/2016 -0.0318 10/23/2016 -0.0043 10/16/2016 -0.0245 10/9/2016 -0.0048 Security A 0.0304 0.0084 -0.0010 0.0611 -0.0455 -0.0250 0.0013 0.0310 -0.0252 0.0379 -0.0091 -0.0128 -0.0381 -0.0228 -0.0257 0.0000 0.0809 -0.0100 -0.0025 -0.0373 0.0452 -0.1281 -0.0172 -0.0393 -0.0031 -0.0387 0.0030 -0.0252 -0.0264 0.0212 0.0078 -0.0265 -0.0019 -0.0140 0.0417 -0.0382 0.0458 -0.0518 0.0169 0.0503 0.0422 -0.0517 0.0364 -0.0626 0.0273 Security B 0.0000 0.0187 0.0388 -0.0310 -0.0022 0.0572 0.0338 -0.0012 -0.0119 -0.0083 0.0000 -0.0047 0.0047 -0.0140 0.0130 -0.0174 -0.0046 0.0023 -0.0012 0.0297 0.0024 -0.0036 -0.0322 -0.0214 0.0102 0.0092 -0.0113 0.0000 0.0173 0.0224 0.0107 0.0207 0.0136 -0.0217 0.0235 -0.0426 -0.0309 0.0331 -0.0163 0.0190 -0.0059 -0.0253 0.0093 -0.0369 -0.0251 Security C 0.0028 -0.0626 0.0123 -0.0401 0.0139 0.0150 0.0098 -0.0556 -0.0182 -0.0045 -0.0178 0.0227 0.0138 -0.0269 0.0276 -0.0046 -0.0091 -0.0112 0.0160 0.0554 0.0533 -0.0199 -0.0195 0.0123 -0.0098 0.0124 0.0228 0.0618 -0.0388 -0.0178 -0.0101 0.0179 -0.0051 0.0155 0.0238 -0.0526 0.0153 -0.0224 0.0720 0.0388 -0.0028 -0.0742 0.0399 -0.1897 0.0022 10/2/2016 9/25/2016 9/18/2016 9/11/2016 9/4/2016 8/28/2016 8/21/2016 8/14/2016 8/7/2016 7/31/2016 7/24/2016 7/17/2016 7/10/2016 7/3/2016 6/26/2016 6/19/2016 6/12/2016 6/5/2016 5/29/2016 5/22/2016 5/15/2016 5/8/2016 5/1/2016 4/24/2016 4/17/2016 4/10/2016 4/3/2016 3/27/2016 3/20/2016 3/13/2016 3/6/2016 2/28/2016 2/21/2016 2/14/2016 2/7/2016 1/31/2016 1/24/2016 1/17/2016 1/10/2016 1/3/2016 12/27/2015 12/20/2015 12/13/2015 12/6/2015 11/29/2015 11/22/2015 11/15/2015 11/8/2015 -0.0263 0.0088 0.0064 -0.0292 0.0057 0.0047 -0.0019 0.0057 0.0075 -0.0054 0.0169 0.0217 0.0104 0.0107 0.0388 -0.0262 -0.0179 -0.0075 0.0046 0.0120 -0.0010 0.0027 0.0114 -0.0066 0.0031 0.0170 0.0033 0.0068 0.0059 0.0166 0.0152 0.0310 0.0136 0.0350 -0.0357 -0.0027 0.0079 -0.0077 0.0018 -0.0263 0.0159 0.0193 0.0062 -0.0041 -0.0010 0.0154 0.0033 -0.0133 -0.0429 0.0229 -0.0085 -0.0468 0.0403 0.0172 0.0116 0.0391 0.0396 -0.0093 0.0533 0.0143 0.0571 -0.0092 0.0705 -0.0206 -0.0271 -0.0297 -0.0034 0.0209 0.0023 0.0226 0.0060 -0.0024 0.0245 0.0515 0.0184 -0.0078 -0.0013 0.0159 0.0257 0.0543 0.0654 -0.0150 -0.0592 0.0290 0.0162 -0.0216 -0.0156 -0.0422 0.0280 0.0214 0.0000 -0.0264 0.0000 0.0141 -0.0509 -0.0223 -0.0467 0.0355 -0.0117 -0.0279 0.0126 0.0116 0.0053 0.0097 0.0032 -0.0262 -0.0021 0.0084 -0.0084 0.0324 0.0511 -0.0168 -0.0677 -0.0021 0.0105 -0.0114 0.0467 0.0143 0.0157 0.0125 0.0244 0.0311 -0.0071 0.0133 -0.0258 0.0327 0.0073 0.0225 -0.0025 0.0295 -0.0347 0.0050 0.0050 -0.0256 -0.0296 -0.0047 0.0012 0.0168 0.0133 0.0185 0.0280 0.0288 0.0053 -0.0318 0.0109 -0.0022 0.0022 -0.0255 -0.0147 -0.0383 -0.0060 0.0081 -0.0275 0.0039 0.0411 0.0451 0.0175 -0.0129 0.0450 -0.0327 -0.0234 0.0173 -0.0149 0.0043 -0.0574 -0.0079 0.0327 -0.0355 -0.0117 0.1152 -0.0800 -0.0099 0.0264 0.0490 0.0330 0.0067 0.0273 0.0023 -0.0201 -0.0428 0.0332 0.0711 0.0120 -0.0587 0.0302 -0.0023 0.0141 -0.0116 0.0263 -0.0233 0.0118 0.0367 11/1/2015 10/25/2015 10/18/2015 10/11/2015 10/4/2015 9/27/2015 9/20/2015 9/13/2015 9/6/2015 8/30/2015 8/23/2015 8/16/2015 8/9/2015 8/2/2015 7/26/2015 7/19/2015 7/12/2015 7/5/2015 6/28/2015 6/21/2015 6/14/2015 6/7/2015 5/31/2015 5/24/2015 5/17/2015 5/10/2015 5/3/2015 4/26/2015 4/19/2015 4/12/2015 4/5/2015 3/29/2015 3/22/2015 3/15/2015 3/8/2015 3/1/2015 2/22/2015 2/15/2015 2/8/2015 2/1/2015 1/25/2015 1/18/2015 1/11/2015 1/4/2015 12/28/2014 12/21/2014 12/14/2014 12/7/2014 0.0139 0.0026 0.0259 0.0321 0.0081 -0.0165 -0.0043 0.0113 0.0183 -0.0218 -0.0140 0.0096 -0.0293 -0.0088 0.0045 0.0069 0.0224 -0.0198 0.0148 -0.0046 -0.0112 -0.0036 0.0039 0.0119 0.0027 0.0044 -0.0107 0.0056 -0.0164 0.0024 0.0027 -0.0039 -0.0029 -0.0063 0.0009 0.0042 0.0225 -0.0065 -0.0019 0.0093 0.0121 0.0104 0.0057 0.0030 0.0020 0.0054 0.0023 -0.0013 0.0241 0.0095 0.0207 0.0112 0.0243 0.0116 -0.0823 0.0301 0.0518 -0.0414 -0.0372 -0.0131 -0.0218 -0.0152 -0.0161 -0.0266 0.0535 -0.0273 -0.0098 -0.0216 -0.0234 0.0107 -0.0365 0.0270 -0.0081 0.0130 0.0229 0.0172 -0.0228 0.0121 -0.0214 -0.0117 -0.0448 0.0113 0.0256 -0.0035 0.0225 -0.0343 0.0331 0.0107 0.0084 0.0272 -0.0146 -0.0108 -0.0048 0.0233 0.0062 -0.0471 -0.0038 -0.0113 0.0668 0.0476 -0.0179 -0.0109 -0.0041 -0.0277 0.0243 -0.0439 -0.0373 -0.0062 -0.0380 0.0024 -0.0012 0.0244 0.0327 -0.0087 0.0038 -0.0012 -0.0123 -0.0158 0.0300 0.0013 -0.0025 -0.0232 0.0099 0.0062 -0.0347 0.0133 0.0249 0.0216 0.0077 -0.0225 -0.0012 -0.0160 0.0163 -0.0136 -0.0134 -0.0036 0.0160 -0.0299 -0.0130 -0.0047 0.0083 0.0242 0.0086 -0.0024 0.0200 0.0178 -0.0051 0.0102 0.0316 -0.0130 -0.0077 0.0104 0.0105 -0.0404 0.0000 -0.0435 -0.0350 -0.0294 0.0524 -0.0071 0.0095 -0.0434 0.0429 0.0024 -0.0499 0.0092 -0.0135 0.0091 0.0378 0.0368 -0.0239 0.0000 0.0170 0.0000 -0.0048 0.0098 0.0074 -0.0122 -0.0024 0.0326 0.0000 0.0205 0.0236 -0.0280 -0.0296 0.0332 -0.0076 0.0180 0.0265 -0.0131 -0.0328 -0.0174 11/30/2014 11/23/2014 11/16/2014 11/9/2014 11/2/2014 10/26/2014 10/19/2014 10/12/2014 10/5/2014 9/28/2014 9/21/2014 9/14/2014 9/7/2014 8/31/2014 8/24/2014 8/17/2014 8/10/2014 8/3/2014 7/27/2014 7/20/2014 7/13/2014 7/6/2014 6/29/2014 6/22/2014 6/15/2014 6/8/2014 6/1/2014 5/25/2014 5/18/2014 5/11/2014 5/4/2014 4/27/2014 4/20/2014 4/13/2014 4/6/2014 3/30/2014 3/23/2014 3/16/2014 3/9/2014 3/2/2014 2/23/2014 2/16/2014 2/9/2014 2/2/2014 1/26/2014 1/19/2014 1/12/2014 1/5/2014 0.0180 -0.0130 0.0022 0.0120 0.0058 0.0101 0.0363 -0.0150 -0.0023 -0.0031 0.0139 -0.0082 -0.0057 0.0059 0.0109 0.0175 0.0045 -0.0107 -0.0162 0.0167 0.0016 -0.0170 0.0087 -0.0002 -0.0049 -0.0023 0.0008 0.0053 -0.0067 0.0065 -0.0153 0.0153 0.0099 0.0023 -0.0063 -0.0037 0.0035 0.0090 -0.0090 0.0272 0.0127 0.0080 0.0098 -0.0069 0.0002 -0.0041 0.0061 0.0200 0.0379 -0.0250 -0.0071 0.0120 -0.0279 0.0082 0.0059 -0.0275 0.0069 -0.0103 -0.0079 -0.0167 -0.0303 0.0054 0.0033 0.0155 0.0308 -0.0168 -0.0368 0.0405 0.0045 -0.0134 0.0205 -0.0034 -0.0167 -0.0239 0.0268 -0.0229 0.0033 -0.0204 -0.0461 0.0010 0.0146 -0.0082 0.0115 -0.0042 -0.0143 0.0198 -0.0323 0.0510 -0.0338 0.0285 0.0533 -0.0088 0.0225 -0.0100 0.0136 0.0267 0.0237 -0.0220 0.0636 0.0000 0.0145 0.0134 0.0374 -0.0309 -0.0274 -0.0353 0.0245 -0.0190 -0.0063 -0.0136 0.0075 0.0323 -0.0397 0.0050 -0.0037 -0.0416 -0.0106 -0.0116 0.0058 0.0094 -0.0012 0.0012 0.0180 -0.0083 -0.0301 -0.0035 -0.0236 0.0711 0.0000 -0.0095 -0.0048 -0.0198 0.0476 -0.0215 0.0036 0.0570 0.0313 0.0013 -0.0129 -0.0190 -0.0186 -0.0359 0.0283 0.0384 0.0254 -0.0296 -0.0146 0.0049 0.0460 0.0103 0.0840 0.0000 -0.0165 -0.0216 0.0109 -0.0134 -0.0211 0.0053 -0.0079 0.0053 0.0412 -0.0319 -0.0208 0.0323 0.0000 -0.0606 -0.0050 -0.0221 0.0074 0.0100 0.0000 0.0025 -0.0292 -0.0096 0.0122 -0.0073 0.0299 0.0126 0.0154 0.0000 -0.0201 0.0102 0.0025 0.0000 0.0342 0.0188 0.0027 -0.0388 0.0104 0.0132 -0.0053 0.0000 12/29/2013 12/22/2013 12/15/2013 12/8/2013 12/1/2013 11/24/2013 11/17/2013 11/10/2013 11/3/2013 10/27/2013 10/20/2013 10/13/2013 10/6/2013 9/29/2013 9/22/2013 9/15/2013 9/8/2013 9/1/2013 8/25/2013 8/18/2013 8/11/2013 8/4/2013 7/28/2013 7/21/2013 7/14/2013 7/7/2013 6/30/2013 6/23/2013 6/16/2013 6/9/2013 6/2/2013 5/26/2013 5/19/2013 5/12/2013 5/5/2013 4/28/2013 4/21/2013 4/14/2013 4/7/2013 3/31/2013 3/24/2013 3/17/2013 3/10/2013 3/3/2013 2/24/2013 2/17/2013 2/10/2013 2/3/2013 0.0004 0.0184 -0.0076 0.0008 -0.0170 -0.0048 -0.0196 -0.0075 0.0076 0.0104 0.0220 0.0038 -0.0039 -0.0049 0.0111 0.0171 0.0117 0.0124 0.0037 0.0023 -0.0044 -0.0107 0.0002 0.0096 -0.0066 0.0175 0.0112 0.0177 -0.0131 -0.0043 -0.0158 -0.0033 -0.0156 -0.0118 0.0239 -0.0010 0.0234 0.0020 0.0006 0.0023 0.0184 -0.0101 0.0076 0.0083 0.0245 0.0043 -0.0069 -0.0048 0.0117 0.0143 -0.0422 -0.0223 -0.0175 -0.0214 -0.0210 -0.0104 -0.0203 0.0240 0.0105 0.0042 0.0000 -0.0187 0.0063 0.0138 0.0183 0.0594 0.0186 0.0337 -0.0072 -0.0036 0.0120 -0.0200 -0.0070 -0.0093 0.0225 0.0407 -0.0098 -0.0133 -0.0096 -0.0012 -0.0071 -0.0254 0.0785 -0.0727 0.0297 -0.0289 -0.0114 0.0222 -0.0115 -0.0525 -0.0161 0.0186 0.0518 -0.0214 -0.0100 -0.0468 -0.0026 -0.0013 0.0103 0.0224 -0.0194 0.0265 -0.0443 0.0000 0.0436 0.0120 0.0686 0.0101 -0.0086 0.0043 -0.0043 0.0130 0.0058 0.0163 -0.0088 -0.0187 0.0221 -0.0490 0.0056 0.0320 -0.0029 0.0392 0.0424 0.0176 0.0032 -0.0250 -0.0288 0.0015 0.0346 0.0291 -0.0064 0.0855 0.0035 0.0981 0.0117 0.0198 0.0500 0.0042 0.0482 -0.0108 -0.0022 -0.0065 0.0220 -0.0173 -0.0206 0.0237 0.0355 -0.0567 0.0458 -0.0339 -0.0052 -0.0026 -0.0051 0.0078 -0.0203 -0.0319 -0.0073 0.0149 0.0359 -0.0076 0.0103 0.0000 -0.0152 0.0102 -0.0668 -0.0095 0.0048 -0.0094 -0.0252 0.0093 -0.0092 0.0307 -0.0208 0.0410 -0.0095 -0.0730 0.0343 -0.0068 0.0162 -0.0023 0.0188 0.0000 0.0143 -0.0476 0.0115 -0.0023 0.0307 -0.0093 0.0516 0.0124 -0.0171 0.0381 1/27/2013 1/20/2013 1/13/2013 1/6/2013 12/30/2012 12/23/2012 12/16/2012 12/9/2012 12/2/2012 11/25/2012 11/18/2012 11/11/2012 11/4/2012 10/28/2012 10/21/2012 10/14/2012 10/7/2012 9/30/2012 9/23/2012 9/16/2012 9/9/2012 9/2/2012 8/26/2012 0.0110 0.0086 0.0078 0.0139 -0.0014 0.0065 0.0190 -0.0154 -0.0021 0.0104 0.0153 -0.0025 0.0112 -0.0175 -0.0011 0.0235 -0.0021 0.0184 0.0065 0.0045 0.0188 0.0151 0.0122 0.0217 0.0055 0.0493 0.0283 0.0095 0.0182 -0.0012 0.0024 0.0404 -0.0088 0.0725 0.0290 0.0358 -0.0183 -0.0339 0.0166 -0.0295 0.0717 -0.0071 0.0324 0.0462 -0.0226 0.0278 0.0065 -0.0108 -0.0211 0.0193 0.0333 0.0181 0.0091 0.0528 -0.0024 0.0097 0.0024 0.0299 0.0050 -0.0124 -0.0146 -0.0024 -0.0048 0.0073 0.0123 0.0000 0.0227 0.0366 0.0700 -0.0100 0.0258 0.0104 0.0240 -0.0053 -0.0053 0.0528 -0.0191 -0.0187 -0.0132 0.0026 -0.0332 0.0209 -0.0154 -0.0251 0.0556 -0.0053 0.0053 -0.0258 -0.0127 0.0000 0.0369 0.0026 August 2012 to 13th August 2017. Security D 0.0000 0.0194 -0.0190 0.0234 -0.0087 -0.0254 0.0009 -0.0221 0.0150 0.0056 0.0153 0.0019 0.0398 0.0657 -0.0105 0.0325 0.0000 0.0406 -0.0166 0.0067 0.0136 0.0045 -0.0079 0.0045 0.0220 0.0537 -0.0180 -0.0200 -0.0047 0.0035 0.0107 0.0120 0.0024 0.0159 0.0161 0.0241 -0.0051 -0.0294 0.0422 -0.0624 -0.0458 0.0594 -0.0225 -0.0152 -0.0562 -0.0532 0.0021 0.0192 -0.0310 0.0464 -0.0253 0.0337 -0.0555 -0.0357 0.0202 -0.0040 0.0122 0.0370 -0.0227 -0.0051 0.0104 -0.0331 -0.0139 0.0151 0.0365 0.0224 0.0492 0.0229 0.0492 -0.0071 -0.0551 0.0114 -0.0011 0.0209 0.0153 0.0417 -0.0157 0.0122 -0.0012 0.0238 -0.0123 0.0279 -0.0260 0.0293 -0.0013 -0.0586 0.0609 -0.0162 0.0349 0.0144 0.0242 0.0150 -0.0041 0.0068 -0.0135 0.0178 0.0581 0.0782 -0.0139 -0.0499 0.0059 -0.0073 0.0333 -0.0045 0.0061 -0.0164 -0.0132 0.0029 -0.0102 0.0804 0.0000 0.0000 0.0226 0.0032 -0.0463 0.0031 0.0556 0.0338 0.0017 -0.0280 -0.0485 0.0016 -0.0700 0.0118 0.0544 0.0110 -0.0124 0.0206 -0.0277 -0.0313 0.0585 0.0259 0.0283 0.0204 0.0334 0.0071 -0.0407 -0.0034 -0.0051 -0.0067 0.0364 0.0434 0.0843 -0.0154 0.0402 0.0205 -0.0201 0.0101 0.0335 0.0214 0.0000 0.0086 0.0243 -0.0362 0.0539 -0.0241 0.0556 -0.0046 -0.0046 0.0307 0.0048 -0.0141 -0.0206 -0.0136 0.0045 0.0329 -0.0427 0.0422 0.0167 0.0660 -0.0051 0.0421 -0.0026 0.0133 0.0108 -0.0236 0.0106 -0.0407 0.0000 -0.0051 0.0154 0.0401 -0.0260 0.0213 0.0053 0.0081 -0.0288 0.0324 0.0423 0.0000 -0.0139 0.0141 0.0201 0.0029 0.0327 0.0275 -0.0466 0.0394 0.0185 0.0351 0.0195 -0.0254 -0.0337 0.0093 0.0000 -0.0182 0.0613 -0.0064 0.0230 -0.0644 0.0284 -0.0155 0.0421 0.0439 -0.0807 0.0733 0.0417 -0.0069 0.0394 0.0410 0.0268 -0.0369 0.1245 0.0711 0.0465 -0.0183 0.0330 0.0443 -0.0193 -0.0143 0.0244 -0.0284 0.0144 0.0048 0.0147 0.0515 0.0157 -0.0205 0.0000 0.0052 -0.0051 0.0209 -0.0354 0.0102 -0.0025 -0.0553 0.0532 0.0180 0.0104 0.0105 0.0160 0.0081 -0.0185 0.0442 0.0000 -0.0163 0.0514 0.0870 -0.0585 0.0301