Instruction: This project is individually assigned. You are not allowed to work in groups. Submit a report with a written summary for each part of the project. The report should be complete but concise. You are required to submit your report (Word or PDF) and your Excel spreadsheet in iCollege in the Assignment submission tab. Please name your submissions using the following convention to ensure proper credit F14000_Project1_LastName.xlsx F14000_Project1_LastName.pdf This project is due one week after the first midterm. No late submission will be accepted Your project submission will be graded based on accuracy and the extent to which it is professionally executed. You should imagine yourself as an analyst who will submit a report to your director or client. In addition to getting the answer right, you want to make a good impression. Be concise, straight to the point yet thorough and professional in your report, and have the firepower in your Excel model to back it up Part 1 I Returns, Risk, and Correlations Go to finance.yahoo.com and obtain the daily adjusted closing prices for MSFT. AAPL, TGT, and WMT over the year MM/DD/2019 to MM/DD/2020, where MM/DD corresponds to your birthday. Compute daily return for each stock for the entire sample period. Merge the retums for these firms into a single excel workbook with the returns for each company properly aligned. 1. Using the Excel functions for Average and Standard Deviation, calculate the average return and standard deviation for each of the stocks. 2. Using the Correlation function construct the correlation matrix for the firms using the daily returns for the entire period. 3. Which pair of firms has the highest correlation coefficient? The lowest? 4. If you have to choose two stocks for your portfolio, which pair will give you the greatest benefit with regard to diversification? Explain. MacBook Pro Part 2: Minimum Variance Portfolios Monthly price data can be obtained for securities at a number of online sources. A good source is finance.yahoo.com. (Look for the "Historical Prices" tab once you enter ticker symbol of the firm you choose.) 1. Download 10 years of monthly price data for two different stocks. 2. Calculate the annualized mean return and annualized standard deviation of the monthly returns and the correlation coefficient of the returns on the two stocks. 3. Calculate the investment opportunity set composed of these two stocks. Plot the investment opportunity set. 4. What are the weights of each of these stocks in the minimum-variance portfolio? 5. Compute the expected return and standard deviation of the minimum-variance portfolio 6. Describe the minimum-variance portfolio versus another one you might suggest for your client, why do you suggest another portfolio, or not? Hints: Annual Mean Retum = (1 + Monthly Mean Return)2 - 1 Standard Deviation of Annual Return = Standard Deviation of Monthly Return x 12 Instruction: This project is individually assigned. You are not allowed to work in groups. Submit a report with a written summary for each part of the project. The report should be complete but concise. You are required to submit your report (Word or PDF) and your Excel spreadsheet in iCollege in the Assignment submission tab. Please name your submissions using the following convention to ensure proper credit F14000_Project1_LastName.xlsx F14000_Project1_LastName.pdf This project is due one week after the first midterm. No late submission will be accepted Your project submission will be graded based on accuracy and the extent to which it is professionally executed. You should imagine yourself as an analyst who will submit a report to your director or client. In addition to getting the answer right, you want to make a good impression. Be concise, straight to the point yet thorough and professional in your report, and have the firepower in your Excel model to back it up Part 1 I Returns, Risk, and Correlations Go to finance.yahoo.com and obtain the daily adjusted closing prices for MSFT. AAPL, TGT, and WMT over the year MM/DD/2019 to MM/DD/2020, where MM/DD corresponds to your birthday. Compute daily return for each stock for the entire sample period. Merge the retums for these firms into a single excel workbook with the returns for each company properly aligned. 1. Using the Excel functions for Average and Standard Deviation, calculate the average return and standard deviation for each of the stocks. 2. Using the Correlation function construct the correlation matrix for the firms using the daily returns for the entire period. 3. Which pair of firms has the highest correlation coefficient? The lowest? 4. If you have to choose two stocks for your portfolio, which pair will give you the greatest benefit with regard to diversification? Explain. MacBook Pro Part 2: Minimum Variance Portfolios Monthly price data can be obtained for securities at a number of online sources. A good source is finance.yahoo.com. (Look for the "Historical Prices" tab once you enter ticker symbol of the firm you choose.) 1. Download 10 years of monthly price data for two different stocks. 2. Calculate the annualized mean return and annualized standard deviation of the monthly returns and the correlation coefficient of the returns on the two stocks. 3. Calculate the investment opportunity set composed of these two stocks. Plot the investment opportunity set. 4. What are the weights of each of these stocks in the minimum-variance portfolio? 5. Compute the expected return and standard deviation of the minimum-variance portfolio 6. Describe the minimum-variance portfolio versus another one you might suggest for your client, why do you suggest another portfolio, or not? Hints: Annual Mean Retum = (1 + Monthly Mean Return)2 - 1 Standard Deviation of Annual Return = Standard Deviation of Monthly Return x 12
