State of South Carolina
Key Questions
Calculate the mean monthly returns and return standard deviations for each of the 30 stocks in the tab "Tbill, S&P, Dow 30 mo retns" of the Capital Market Returns Data spreadsheet.
A) Create a column of monthly returns for a portfolio that each month was invested equally across the 30 stocks. Calculate the mean and standard deviation of returns of this portfolio and explain how they relate to the mean returns and standard deviations of return of the 30 stocks individually. B) (Optional) Create a column of monthly returns for a portfolio that each month was invested equally across Caterpillar and American Express. Calculate the mean and standard deviation of returns of this portfolio and explain how they relate to the mean returns and standard deviations of return of the Caterpillar and American Express stocks individually.
Create an "xy" graph with Caterpillar monthly returns on the y-axis, and the S&P 500 monthly returns on the x-axis. Plot the regression line (Select "Chart", "Add trendline", "Options" and click "Display equation on chart" as well as "Display R-squared value on chart"). Create another xy graph and trendline for Procter and Gamble (PG) vs. the S&P 500. What is the meaning of the R-squareds and regression coefficients in these graphs?
Key Answers
1. See below for mean monthly returns and Standard Deviation by company Mean Monthly
Company Name Returns St Dev3M Company 1.07% 5.80%
Alcoa Incorporated 0.87% 9.96%
American Express 1.33% 9.33%
AT & T Inc0.56% 7.77%
Bank Of America 1.03% 11.41%
Boeing 1.11% 7.91%
Caterpillar 1.51% 9.05%
Chevron Corp. 1.14% 5.58%
Cisco Systems Inc. 2.64% 11.56%
Coca Cola 1.09% 6.09%
Dupont0.93% 7.15%
Exxon Mobil 1.06% 4.68%
General Electric 1.03% 7.27%
Hewlett-Packard 1.35% 10.46%
Home Depot 1.76% 8.03%
Intel 1.89% 10.85%
IBM 1.16% 7.93%
Johnson & Johnson 1.24% 5.76%
JP Morgan Chase 1.20% 8.69%
McDonalds 1.15% 6.23%
Merck 1.07% 7.43%
Microsoft 2.01% 9.41%
Calculate the mean monthly returns and return standard deviations for each of the 30 stocks in the tab "Tbill, S&P, Dow 30 mo retns" of the Capital Market Returns Data spreadsheet.
A) Create a column of monthly returns for a portfolio that each month was invested equally across the 30 stocks. Calculate the mean and standard deviation of returns of this portfolio and explain how they relate to the mean returns and standard deviations of return of the 30 stocks individually. B) (Optional) Create a column of monthly returns for a portfolio that each month was invested equally across Caterpillar and American Express. Calculate the mean and standard deviation of returns of this portfolio and explain how they relate to the mean returns and standard deviations of return of the Caterpillar and American Express stocks individually.
Create an "xy" graph with Caterpillar monthly returns on the y-axis, and the S&P 500 monthly returns on the x-axis. Plot the regression line (Select "Chart", "Add trendline", "Options" and click "Display equation on chart" as well as "Display R-squared value on chart"). Create another xy graph and trendline for Procter and Gamble (PG) vs. the S&P 500. What is the meaning of the R-squareds and regression coefficients in these graphs?
Key Answers
1. See below for mean monthly returns and Standard Deviation by company Mean Monthly
Company Name Returns St Dev3M Company 1.07% 5.80%
Alcoa Incorporated 0.87% 9.96%
American Express 1.33% 9.33%
AT & T Inc0.56% 7.77%
Bank Of America 1.03% 11.41%
Boeing 1.11% 7.91%
Caterpillar 1.51% 9.05%
Chevron Corp. 1.14% 5.58%
Cisco Systems Inc. 2.64% 11.56%
Coca Cola 1.09% 6.09%
Dupont0.93% 7.15%
Exxon Mobil 1.06% 4.68%
General Electric 1.03% 7.27%
Hewlett-Packard 1.35% 10.46%
Home Depot 1.76% 8.03%
Intel 1.89% 10.85%
IBM 1.16% 7.93%
Johnson & Johnson 1.24% 5.76%
JP Morgan Chase 1.20% 8.69%
McDonalds 1.15% 6.23%
Merck 1.07% 7.43%
Microsoft 2.01% 9.41%