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

Returns Data for Problems 4-6
Date	S&P500	AAPL	CAT	LLY
20130131	0.050428	-0.144094	0.097999	0.088605
20130228	0.011061	-0.025116	-0.061185	0.027193
20130328	0.035988	0.002855	-0.058461	0.038968
20130430	0.018086	0.000271	-0.020467	-0.024828
20130531	0.020763	0.022596	0.013346	-0.031239
20130628	-0.014999	-0.118303	-0.038578	-0.075997
20130731	0.049462	0.141225	0.012365	0.081230
20130830	-0.031298	0.083389	-0.004463	-0.022971
20130930	0.029749	-0.021481	0.010419	-0.020817
20131031	0.044596	0.096386	0.006715	-0.010133
20131129	0.028049	0.069673	0.014875	0.017864
20131231	0.023563	0.008902	0.073404	0.015532
20140131	-0.035583	-0.107697	0.040744	0.059020
20140228	0.043117	0.057311	0.032584	0.112757
20140331	0.006932	0.019953	0.024750	-0.012582
20140430	0.006201	0.099396	0.066720	0.004077
20140530	0.021030	0.078293	-0.030076	0.021151
20140630	0.019058	0.027662	0.062995	0.038590
20140731	-0.015080	0.028731	-0.066440	-0.017854
20140829	0.037655	0.077092	0.082581	0.048968
20140930	-0.015514	-0.017073	-0.092051	0.020296
20141031	0.023201	0.071960	0.031102	0.022822
20141128	0.024534	0.105556	-0.007987	0.034374
20141231	-0.004189	-0.071891	-0.090159	0.012772
20150130	-0.031041	0.061424	-0.118650	0.043630
20150227	0.054893	0.100461	0.036639	-0.018472
20150331	-0.017396	-0.031372	-0.034620	0.035343
20150430	0.008521	0.005786	0.094340	-0.010736
20150529	0.010491	0.045146	-0.017956	0.104772
20150630	-0.021012	-0.037266	-0.005860	0.058175
20150731	0.019742	-0.032888	-0.063900	0.012217
20150831	-0.062581	-0.066117	-0.027852	-0.019643
20150930	-0.026443	-0.021816	-0.144950	0.016272
20151030	0.082983	0.083409	0.128519	-0.025332
20151130	0.000505	-0.005690	-0.004658	0.011892
20151231	-0.017530	-0.110228	-0.064556	0.027060
20160129	-0.050735	-0.075242	-0.072837	-0.061239
20160229	-0.004128	-0.001335	0.087725	-0.083312
20160331	0.065991	0.127211	0.130576	0.000139
20160429	0.002699	-0.139921	0.025477	0.048882
20160531	0.015329	0.071368	-0.067035	0.000132
20160630	0.000906	-0.042660	0.045511	0.049580
20160729	0.035610	0.090063	0.101834	0.052571
20160831	-0.001219	0.023606	-0.009787	-0.055857
20160930	-0.001234	0.065504	0.083221	0.032283
20161031	-0.019426	0.004334	-0.051143	-0.079990
20161130	0.034174	-0.021578	0.144980	-0.084101
20161230	0.018201	0.047955	-0.029510	0.095799