BBA2163 Investment Analysis Group Assignment Questions | UCSI

g) Repeat these steps for the remaining stocks, pasting each closing price right next to the other stocks, again making sure that the correct prices on the correct dates all appear on the same rows.

2. Convert these prices to monthly returns as the percentage change in the monthly prices. (Hint : Create a separate worksheet within the Excel file). Note that to compute a return for each month, you need a beginning and ending price, so you will not be able to compute the return for the first month.
3. Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation (multiply the mean monthly return by 12, and multiply the monthly standard deviation by (12)^0.5.
4. Add a column in your Excel worksheet with the average return across stocks for each month. This is the monthly return to an equally weighted portfolio (EWP) of these stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Double check that the average return on this equally weighted portfolio is equal to the average return of all the individual stocks. Convert these monthly statistics to annual statistics for interpretation.
5. Using the annual statistics, create an Excel plot with standard deviation (volatility) on the x-axis and average return on the y-axis as follows :

   a) Create 3 columns on your spreadsheet with the statistics you created in Questions 3 and 4 for each of the individual stocks and the equally weighted portfolio. The first column will have the ticker, the second will have annual standard deviation, and the third will have the annual mean return, followed by equally weighted portfolio return & STD.

   b) Highlight the data in the last 2 columns (standard deviation and mean), choose Insert > Chart > XY Scatter Plot. Complete the chart wizard to finish the plot. (Display of scatter plot is a MUST, make sure EWP is also shown in the plot)

6. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio? (Avoid explaining returns and STD of each of the stocks one-by-one, unless there is something other than what the plot already has shown that you like to add. I am interested to read your overall justification for any potential difference between individual stock vs EWP properties using your knowledge from the subject)

Question 2

Repeat steps from question 1 and calculate semi-standard deviation for stocks and portfolio (Do your research on this question to see which average return you should use for calculation of deviation).

1. What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio?
2. Does the result from question 1 is in line with results of question 2? If there is any contradictory results, how do you justify it?

Question 3

For each company in the sample, calculate the beta using single-index CAPM model with respect to FTSE Bursa Malaysia KLCI (^KLSE) for the duration mentioned above. (Use Malaysian T-Bills for risk free measure in MS Excel file I attached for your r_f. You need to get the last few months from BNM website yourself. Make sure figures in Excel and your results are almost equal to each other for the months stated in Excel file for the sake of compatibility).

Required:

• Interpret, compare, and contrast systematic risks of different companies.
• Calculate and comment on their firm-specific risk.
• Compare the performance of single index model in your analysis in terms of each stock (single index model has done the best prediction on which stock?).