Hi, Can someoneplease check my answer to this Microsoft excel question that deals with portfolio avg returns, stdev's and correlations and requires the use of 'Analysis' tools? Specifically Data Solver and Data Analysis (available in Excel Add ins). Please solve ifyou are very confident (can be sure you are correct) in answering the question.

Thanks

Unit: FNCE2004 - Introductory Business Financial Modelling Semester 1 2016 Locations: Bentley (Fully Online) Assessment: Individual assignment Assessment value: 20% Due date: To be submitted by 5pm on Friday 20th of May 2016 (week 12 as per unit outline) IMPORTANT INSTRUCTIONS: This assignment is due at the date and time stated above. Show all workings to demonstrate you have understood how to solve each problem. Please present your answers in at least 2 decimal places. Students are to submit their assignments onto Blackboard. The assignment must be submitted as a MS Excel document. Answer all sections. Your assignment should meet the following requirements A copy of the assignment has been retained Declaration below is complete Declaration Except where I have indicated, the work I am submitting in this assignment is my own work and has not been submitted for assessment in another unit or course. I admit that the work contributed among the group members is adequately fair. I warrant that any diskettes and/or computer files submitted as part of this assignment have been checked for viru and reported clean. Student ID Family Name 17686964 Siano Given Name Gaetano Signature of student Name of your tutor Online All form of plagiarism, cheating and unauthorised collusion are regarded seriously by the University and could result in penalties including failure in the course and possible exclusion from the University. If you are in dou please contact your lecturer, unit coordinator or your course coordinator. at the or viruses e in doubt, Question 2 (35 marks) You are planning to invest in the following stocks: Rows 8 to 78 show the monthly closing prices of the stocks between 2010 and 2015. Rio Tinto Ltd (RIO.AX) Date Jan-2010 68.00 Feb-2010 70.50 Mar-2010 78.40 Apr-2010 72.10 May-2010 67.14 Jun-2010 66.66 Jul-2010 70.61 Aug-2010 70.09 Sep-2010 76.77 Oct-2010 82.69 Nov-2010 82.21 Dec-2010 85.47 Jan-2011 84.05 Feb-2011 84.93 Mar-2011 84.75 Apr-2011 82.21 May-2011 81.50 Jun-2011 82.99 Jul-2011 80.00 Aug-2011 72.52 Sep-2011 61.80 Oct-2011 69.21 Nov-2011 62.95 Dec-2011 60.30 Jan-2012 69.16 Feb-2012 67.45 Mar-2012 65.40 Apr-2012 66.35 May-2012 56.86 Jun-2012 56.50 Jul-2012 53.20 Aug-2012 49.24 Sep-2012 53.36 Oct-2012 56.94 Nov-2012 58.75 Dec-2012 66.01 Jan-2013 66.36 Feb-2013 67.05 Mar-2013 57.20 Apr-2013 55.80 May-2013 55.18 Jun-2013 52.37 Jul-2013 57.51 Aug-2013 58.30 Sep-2013 61.74 Oct-2013 63.99 Nov-2013 66.06 Dec-2013 68.18 Jan-2014 65.64 Feb-2014 66.84 Mar-2014 63.55 Apr-2014 61.70 May-2014 59.30 Jun-2014 59.31 Jul-2014 66.38 Aug-2014 62.63 Sep-2014 59.58 Oct-2014 60.41 Nov-2014 59.10 Dec-2014 58.00 Jan-2015 57.56 Feb-2015 64.41 Mar-2015 57.23 Apr-2015 57.15 May-2015 58.20 Jun-2015 53.75 Jul-2015 52.86 Aug-2015 50.29 Sep-2015 48.60 Oct-2015 50.65 Nov-2015 45.91 a. (5 marks) Apple Inc. (AAPL) 192.06 204.62 235.00 261.09 256.88 251.53 257.25 243.10 283.75 300.98 311.15 322.56 339.32 353.21 348.51 350.13 347.83 335.67 390.48 384.83 381.32 404.78 382.20 405.00 456.48 542.44 599.55 583.98 577.73 584.00 610.76 665.24 667.10 595.32 585.28 532.17 455.49 441.40 442.66 442.78 449.73 396.53 452.53 487.22 476.75 522.70 556.07 561.02 500.60 526.24 536.74 590.09 633.00 92.93 95.60 102.50 100.75 108.00 118.93 110.38 117.16 128.46 124.43 125.15 130.28 125.43 121.30 112.76 110.30 119.50 118.30 Cathay Pacific Airlines (0293.HK) 12.80 14.42 16.36 16.44 15.42 15.50 17.30 19.18 21.05 20.85 22.65 21.45 19.64 18.10 18.64 19.38 18.64 18.02 18.06 15.68 12.80 14.30 13.56 13.32 15.36 15.40 14.38 13.16 11.98 12.46 12.84 12.64 12.62 14.04 13.62 14.22 15.06 14.48 13.28 13.64 14.48 13.56 14.36 13.30 15.20 15.38 16.42 16.40 16.10 15.80 14.46 14.64 14.12 14.48 14.72 14.40 14.30 14.54 17.08 16.90 18.10 17.40 17.94 19.98 19.86 19.06 18.34 14.00 14.52 15.44 13.68 The monthly data is collected from Yahoo Finance on 17th of December 2015. Ignore dividends for this questi estion. Students used the prices that ignored dividends from Yahoo Finance Rio Tinto Ltd Date (RIO.AX) Monthly Returns Jan-2010 Feb-2010 Mar-2010 Apr-2010 May-2010 Jun-2010 Jul-2010 Aug-2010 Sep-2010 Oct-2010 Nov-2010 Dec-2010 Jan-2011 Feb-2011 Mar-2011 Apr-2011 May-2011 Jun-2011 Jul-2011 Aug-2011 Sep-2011 Oct-2011 Nov-2011 Dec-2011 Jan-2012 Feb-2012 Mar-2012 Apr-2012 May-2012 Jun-2012 Jul-2012 Aug-2012 Sep-2012 Oct-2012 Nov-2012 Dec-2012 Jan-2013 Feb-2013 Mar-2013 Apr-2013 May-2013 Jun-2013 Jul-2013 Aug-2013 Sep-2013 Oct-2013 Nov-2013 Dec-2013 Jan-2014 Feb-2014 Mar-2014 Apr-2014 May-2014 Jun-2014 Jul-2014 Aug-2014 Sep-2014 Oct-2014 Nov-2014 Dec-2014 Jan-2015 Feb-2015 Mar-2015 Apr-2015 May-2015 Jun-2015 Jul-2015 Aug-2015 Sep-2015 Oct-2015 Nov-2015 3.68% 11.21% -8.04% -6.88% -0.71% 5.93% -0.74% 9.53% 7.71% -0.58% 3.97% -1.66% 1.05% -0.21% -3.00% -0.86% 1.83% -3.60% -9.35% -14.78% 11.99% -9.04% -4.21% 14.69% -2.47% -3.04% 1.45% -14.30% -0.63% -5.84% -7.44% 8.37% 6.71% 3.18% 12.36% 0.53% 1.04% -14.69% -2.45% -1.11% -5.09% 9.81% 1.37% 5.90% 3.64% 3.23% 3.21% -3.73% 1.83% -4.92% -2.91% -3.89% 0.02% 11.92% -5.65% -4.87% 1.39% -2.17% -1.86% -0.76% 11.90% -11.15% -0.14% 1.84% -7.65% -1.66% -4.86% -3.36% 4.22% -9.36% Apple Inc. Cathay Pacific Airlines (AAPL) Monthly (0293.HK) Returns Monthly Returns 6.54% 14.85% 11.10% -1.61% -2.08% 2.27% -5.50% 16.72% 6.07% 3.38% 3.67% 5.20% 4.09% -1.33% 0.46% -0.66% -3.50% 16.33% -1.45% -0.91% 6.15% -5.58% 5.97% 12.71% 18.83% 10.53% -2.60% -1.07% 1.09% 4.58% 8.92% 0.28% -10.76% -1.69% -9.07% -14.41% -3.09% 0.29% 0.03% 1.57% -11.83% 14.12% 7.67% -2.15% 9.64% 6.38% 0.89% -10.77% 5.12% 2.00% 9.94% 7.27% -85.32% 2.87% 7.22% -1.71% 7.20% 10.12% -7.19% 6.14% 9.64% -3.14% 0.58% 4.10% -3.72% -3.29% -7.04% -2.18% 8.34% -1.00% 12.66% 13.45% 0.49% -6.20% 0.52% 11.61% 10.87% 9.75% -0.95% 8.63% -5.30% -8.44% -7.84% 2.98% 3.97% -3.82% -3.33% 0.22% -13.18% -18.37% 11.72% -5.17% -1.77% 15.32% 0.26% -6.62% -8.48% -8.97% 4.01% 3.05% -1.56% -0.16% 11.25% -2.99% 4.41% 5.91% -3.85% -8.29% 2.71% 6.16% -6.35% 5.90% -7.38% 14.29% 1.18% 6.76% -0.12% -1.83% -1.86% -8.48% 1.24% -3.55% 2.55% 1.66% -2.17% -0.69% 1.68% 17.47% -1.05% 7.10% -3.87% 3.10% 11.37% -0.60% -4.03% -3.78% -23.66% 3.71% 6.34% -11.40% p w1212 w22 22 w32 32 2w1w21,2 2w1w31,3 2w2 w3 2,3 2 2 P w12 12 w2 2 2 w1 w2 12 1 2 SR p ( rp rf ) p Based on the responses in part (b), calculate the average monthly stock returns and standard deviation for each stock. Average Return -0.35% 1.09% 0.40% VAR (population) 0.0043 0.0157 0.0061 St.Dev (as Sqrt) 0.0654 0.1252 0.0778 ` St.Dev (population) 0.0654 0.1252 0.0778 Calculate the correlation of monthly stock returns between each pair of stocks. Correlations Correlation (Rio and AAPL) 0.13377 Correlation (Rio and 0293) 0.54919 RIO Correlation (AAPL and 0293) 0.10789 APPL Cathay Covariances Covariances (Rio and AAPL) Covariances (Rio and 0293) Covariances (AAPL and 0293) 0.00109 0.00279 0.00105 RIO APPL Cathay CORRELATION MATRIX RIO APPL Cathay 1 0.1337719557 0.5491905312 0.13377196 1 0.1078882282 0.54919053 0.1078882282 1 COVARIANCE MATRIX RIO APPL Cathay 0.00427 0.00109 0.00279 0.00109 0.01567 0.00105 0.00279 0.00105 0.00605 Suppose that you invest 20% in Apple Inc., 50% in Rio Tinto Ltd and 30% in Cathay Pacific Airlines. Calculate the portfolio expected return, variance and standard deviation. Weight of Rio Tinto Ltd Weight of Apple Weight of Standard Expected Inc. Cathay Pacific Deviation Return Airlines 20.0% 50.0% 30.00% 7.42% 0.60% How does the expected return and standard deviation change if you decide to invest 60% in Rio Tinto Ltd, 30% in Apple Inc. and 10% in Cathay Pacific Airlines? Explain your answers. Weight of Rio Tinto Ltd Weight of Apple Weight of Standard Expected Inc. Cathay Pacific Deviation Return Airlines 60.0% 30.0% 10.00% 6.17% 0.16% Find the weights of Apple Inc., Rio Tinto Ltd and Cathay Pacific Airlines that will provide a monthly return of 0.5% with the lowest level of risk. Weight of Rio Tinto Ltd Weight of Apple Weight of Standard Expected Inc. Cathay Pacific Deviation Return Use Data->Solver Add In Airlines Low Risk = Lowest Standard Deviation 14.8% 30.3% 54.95% 6.53% 0.50% 100.0% Target Expected Return Plot a scatter diagram showing the portfolios from parts (e), (f) and (g), with the X-axis displaying standard deviation and the Y-axis displaying monthly returns. Different Portfolio return and risk 0.70% 0.60% 0.50% Portfolio1 Portfolio2 Portfolio3 0.40% Mont ly Ret urns 0.30% 0.20% 0.10% 0.00% 6% 6% 6% 7% 7% 7% 7% 7% 8% Standard Deviation If the monthly risk-free rate is 0.1%, estimate the Sharpe ratio for the portfolios mentioned in parts (e), (f) and (g) and highlight the highest Sharpe ratio using conditional formatting. Adding a risk-free asset Risk Free Expected return Risk Free St. Dev. 0.10% per month 0.00% by definition Average return Portfolio 1 Portfolio 2 Portfolio 3 Highest Sharpe ratio Which is the best portfolio? 0.60% 0.16% 0.50% 0.07 Portfolio 1 Return standard deviation 7.42% 6.17% 6.53% Sharpe Ratio 0.07 0.01 0.06 0.50% per month