Question 1 Today is April 30, 2012, and you have just started your new job with a financial planning firm. In addition t

[answerhappygod]

Question 1
Today is April 30, 2012, and you have just started your new jobwith a financial planning firm. In addition to studying for allyour license exams, you have been asked to review a portion of aclient’s stock portfolio to determine the risk/return profiles of12 stocks in the portfolio. Unfortunately, your small firm cannotafford the expensive databases that would provide all thisinformation with a few simple keystrokes, but that’s why they hiredyou. Specifically, you have been asked to determine the monthlyaverage returns and standard deviations for the 12 stocks for thepast 5 years.
The stocks (with their symbols in parentheses) are :
Archer Daniels Midland (ADM)
International Business Machines Corporation (IBM)
Boeing (BA)
JP Morgan Chase & Co. (JPM)
Caterpillar (CAT)
Microsoft (MSFT)
Deere & Co. (DE)
Procter and Gamble (PG)
General Mills, Inc. (GIS)
Wal-Mart (WMT)
Google Inc. (GOOG)
Hershey (HSY)
1. Collect price information for each stock from Yahoo! Finance(finance.yahoo.com) as follows :
2. Convert these prices to monthly returns as the percentagechange in the monthly prices. (Hint : Create a separate worksheetwithin the Excel file). Note that to compute a return for eachmonth, you need a beginning and ending price, so you will not beable to compute the return for the first month.
3. Compute the mean monthly returns and standard deviations forthe monthly returns of each of the stocks. Convert the monthlystatistics to annual statistics for easier interpretation (multiplythe mean monthly return by 12, and multiply the monthly standarddeviation by (12)0.5.
4. Add a column in your Excel worksheet with the average returnacross stocks for each month. This is the monthly return to anequally weighted portfolio of these stocks. Compute the mean andstandard deviation of monthly returns for the equally weightedportfolio. Double check that the average return on this equallyweighted portfolio is equal to the average return of all theindividual stocks. Convert these monthly statistics to annualstatistics for interpretation.
5. Using the annual statistics, create an Excel plot withstandard deviation (volatility) on the x-axis and average return onthe y-axis as follows :
6. What do you notice about the average of thevolatilities of the individual stocks, compared to the volatilityof the equally weighted portfolio?
Question 2
Repeat steps from question 1 and calculate semi-standarddeviation for stocks and portfolio.
1. What do you notice about the average of the volatilities ofthe individual stocks, compared to the volatility of the equallyweighted portfolio?
2. Does the result from question 1 is in line with results ofquestion 2? If there is any contradictory results, how do youjustify it?