Using the Excel file, you will analyze the risk-return characteristics of the following companies:
Exxon Mobil (XOM)
The Walt Disney Company (DIS)
Microsoft Corporation (MSFT)
Using the price data in this Excel file, please convert these prices into monthly returns. Please note that you will have 31 months of returns even though you have 32 months of prices. This is because in order 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 of the sample.
Next, please complete the table below using the Descriptive Statistics feature in Excel (to save time1 ):
|
|
XOM
|
|
WS
|
|
MSFT
|
|
|
Mean
|
%
|
Mean
|
%
|
Mean
|
%
|
|
|
|
|
|
|
|
Volatility
|
%
|
Volatility
|
%
|
Volatility
|
%
|
|
|
|
|
|
|
|
Range
|
Range
|
Range
|
|
|
%
|
%
|
%
|
|
|
Minimum
|
Minimum
|
Minimum
|
|
|
%
|
%
|
%
|
|
|
Maximum
|
Maximum
|
Maximum
|
|
Based on your volatility and range data points for these three stocks, which stock appears to be the riskiest? Briefly discuss your response.
Which stock had the lowest monthly return and which stock had the largest monthly return? What month and year did these low and high returns occur?
Please create a correlation matrix for these three companies and complete the table below. (i) Which stocks are the most correlated and which stocks are the least correlated? (ii) For diversification purposes, do we look for low correlation coefficients or high correlation coefficients? (iii) Lastly, why are there ones down the diagonal of a correlation matrix?