3. You first need to download the spreadsheet with stock price and return data from the Assignments tab in Canvas and access the spreadsheet tab titled "Stock Price Data". This tab contains monthly stock prices, dividends, and stock-split information for Visa (V), IBM (IBM), and Tesla (TSLA) for the period spanning the end of December 2013 through the end of December 2016. For each firm, youll see 37 end-of-month closing prices, from which you will be able to calculate 36 monthly stock returns. (Each part is worth 2 points.) a. Calculate the arithmetic average monthly return for each stock. b. Calculate the geometric average monthly return for each stock. c. Calculate the standard deviation of the monthly returns across this four-year horizon for each stock. (NOTE: Contrary to any variance or standard-devia- tion equation for a sample of observations that might be in the textbook (with N1 as part of the math), please use the equation for standard deviation for a population of observations. That is, use the equa- tions with N, not with N1. These sets of returns are the populations; theyre not samples.) d. Cal- culate the total percentage return (referred to in the text as the holding-period return) for each stock, as- suming that you bought the stock at the end of December 2013 and held it through the end of December 2016. In your calculations assume that any dividends are reinvested immediately in the stock rather than being stuffed under a mattress where they would earn no further returns. e. If you constructed a portfolio at the end of December 2013 consisting of 100 shares of each stock and held this portfolio through the end of December 2016 (again, reinvesting all dividends), what would be the total percentage return on the portfolio? What would the portfolio's geometric average monthly return be?

To answer questions 4-6, you will need to access the tab labeled "Stock-Return Data for Q4-Q6" in the previously downloaded spreadsheet. The tab contains monthly stock returns for Apple (AAPL), Caterpil- lar (CAT), and Eli Lilly (LLY) for the months from January 2013 through December 2016 (48 observa- tions, total), along with monthly stock returns for the S&P 500 Composite Index over the same interval.

4. Using the returns on the S&P 500 Composite Index as the proxy for the overall stock-market return, estimate a beta for each stock listed above in Excel. Report the betas and comment on your level of con- fidence in each of the beta estimates given the significance level (p-values) of the t-statistics for the beta estimates in the regression models. Clearly demonstrate your understanding of beta calculations and sta- tistical estimates. (6 points)

5. Which of the three stocks (AAPL, CAT, LLY) has the most total return variability if held in isolation (i.e., not as part of a well-diversified portfolio)? Clearly convey what measure you used to identify the amount of risk of a stock held in isolation. (1.5 points)

6. Which of the three stocks (AAPL, CAT, LLY) has the most systematic risk? Clearly convey what measure you used to identify the amount of systematic risk. (1.5 points)

in order, you'll find data for V, IBM, and TSLA
please be alert to the facts that 2 of 3 stocks paid dividends and 1 of 3 stocks had splits
Firm	Date	Close	Split	Dividend
V	Dec. '13	222.68
V	Jan. '14	215.43
V	Feb. '14	225.94		0.400
V	Mar. '14	215.86
V	Apr. '14	202.61
V	May '14	214.83		0.400
V	June '14	210.71
V	July '14	211.01
V	Aug. '14	212.52		0.400
V	Sep. '14	213.37
V	Oct. '14	241.43
V	Nov. '14	258.19		0.480
V	Dec. '14	262.20
V	Jan. '15	254.91
V	Feb. '15	271.31		0.480
V	Mar. '15	65.41	4-for-1
V	Apr. '15	66.05
V	May '15	68.68		0.120
V	June '15	67.15
V	July '15	75.34
V	Aug. '15	71.30		0.120
V	Sep. '15	69.66
V	Oct. '15	77.58
V	Nov. '15	79.01		0.140
V	Dec. '15	77.55
V	Jan. '16	74.49
V	Feb. '16	72.39		0.140
V	Mar. '16	76.48
V	Apr. '16	77.24
V	May '16	78.94		0.140
V	June '16	74.17
V	July '16	78.05
V	Aug. '16	80.90		0.140
V	Sep. '16	82.70
V	Oct. '16	82.51
V	Nov. '16	77.32		0.165
V	Dec. '16	78.02
Firm	Date	Close	Split	Dividend
IBM	Dec. '13	187.57
IBM	Jan. '14	176.68
IBM	Feb. '14	185.17		0.950
IBM	Mar. '14	192.49
IBM	Apr. '14	196.47
IBM	May '14	184.36		1.100
IBM	June '14	181.27
IBM	July '14	191.67
IBM	Aug. '14	192.30		1.100
IBM	Sep. '14	189.83
IBM	Oct. '14	164.40
IBM	Nov. '14	162.17		1.100
IBM	Dec. '14	160.44
IBM	Jan. '15	153.31
IBM	Feb. '15	161.94		1.100
IBM	Mar. '15	160.50
IBM	Apr. '15	171.29
IBM	May '15	169.65		1.300
IBM	June '15	162.66
IBM	July '15	161.99
IBM	Aug. '15	147.89		1.300
IBM	Sep. '15	144.97
IBM	Oct. '15	140.08
IBM	Nov. '15	139.42		1.300
IBM	Dec. '15	137.62
IBM	Jan. '16	124.79
IBM	Feb. '16	131.03		1.300
IBM	Mar. '16	151.45
IBM	Apr. '16	145.94
IBM	May '16	153.74		1.400
IBM	June '16	151.78
IBM	July '16	160.62
IBM	Aug. '16	158.88		1.400
IBM	Sep. '16	158.85
IBM	Oct. '16	153.69
IBM	Nov. '16	162.22		1.400
IBM	Dec. '16	165.99
Firm	Date	Close	Split	Dividend
TSLA	Dec. '13	150.43
TSLA	Jan. '14	181.41
TSLA	Feb. '14	244.81
TSLA	Mar. '14	208.45
TSLA	Apr. '14	207.89
TSLA	May '14	207.77
TSLA	June '14	240.06
TSLA	July '14	223.30
TSLA	Aug. '14	269.70
TSLA	Sep. '14	242.68
TSLA	Oct. '14	241.70
TSLA	Nov. '14	244.52
TSLA	Dec. '14	222.41
TSLA	Jan. '15	203.60
TSLA	Feb. '15	203.34
TSLA	Mar. '15	188.77
TSLA	Apr. '15	226.05
TSLA	May '15	250.80
TSLA	June '15	268.26
TSLA	July '15	266.15
TSLA	Aug. '15	249.06
TSLA	Sep. '15	248.40
TSLA	Oct. '15	206.93
TSLA	Nov. '15	230.26
TSLA	Dec. '15	240.01
TSLA	Jan. '16	191.20
TSLA	Feb. '16	191.93
TSLA	Mar. '16	229.77
TSLA	Apr. '16	240.76
TSLA	May '16	223.23
TSLA	June '16	212.28
TSLA	July '16	234.79
TSLA	Aug. '16	212.01
TSLA	Sep. '16	204.03
TSLA	Oct. '16	197.73
TSLA	Nov. '16	189.40
TSLA	Dec. '16	213.69