Question

Use the Excel Template and the table of prices and dividends below to answer the next seven questions:

	Kellogg Ticker = K		S&P 500
Month	Dividend	Price	Price
Jan-17		72.71	2278.87
Feb-17		74.07	2363.64
Mar-17	0.52	72.61	2362.72
Apr-17		71	2384.2
May-17	0.52	71.6	2411.8
Jun-17		69.46	2423.41
Jul-17		68	2470.3
Aug-17	0.54	65.46	2471.65
Sep-17		62.37	2519.36
Oct-17		62.53	2575.26
Nov-17	0.54	66.16	2584.84
Dec-17		67.98	2673.61
Jan-18		68.11	2823.81
Feb-18		66.2	2713.83
Mar-18	0.54	65.01	2640.87
Apr-18		58.9	2648.05
May-18	0.54	64.39	2705.27
Jun-18		69.87	2718.37
Jul-18		71.03	2816.29
Aug-18	0.56	71.79	2901.52
Sep-18		70.02	2913.98
Oct-18		65.48	2711.74
Nov-18	0.56	63.65	2760.17
Dec-18		59.23	2545.94

1. (EXCEL TEMPLATE) Calculate monthly returns for Kellogg (K) and the S&P 500. Be sure to include the dividends for Kellogg in the return.

2. (EXCEL TEMPLATE) Calculate the average, standard deviation, and variance of returns for Kellogg (K) and the S&P 500.

3. (EXCEL TEMPLATE) Use the covariance equation to find the covariance between Kellogg (K) and the S&P 500.

4. (EXCEL TEMPLATE) Use the correlation coefficient equation to find the correlation coefficient between Kellogg (K and the S&P 500.

5. (EXCEL TEMPLATE) Use the beta equation to find the beta of Kellogg (KY).

6. (EXCEL TEMPLATE) Use Regression Analysis to find the beta for Kellogg (you should get the same answer as in question 5).

7. (EXCEL TEMPLATE)

      a. What is the probability that the true beta for Kellogg is equal to zero?

b. If the market risk premium is 6% and the risk-free rate is 3%, what return would you expect to earn on Kellogg given YOUR beta calculation?

c. Go to the website for Yahoo Finance and to the Profile tab. List two products the company makes.

d. Go to the website for Yahoo Finance and to the Summary tab. What beta is listed?

e. Give a possible reason for why your calculated beta is different than the Yahoo beta.

Answer 1

A	B	C	D	E	F	G	H	I	J	K	L
2
3
4
5			Kellogg		S&P 500	Kellog	S&P 500
6		Ticker = K
7		Month	Dividend	Price	Price	Return	Return
8		17-Jan		72.71	2278.87
9		17-Feb		74.07	2363.64	1.87%	3.72%	=(F9-F8)/F8
10		17-Mar	0.52	72.61	2362.72	-1.27%	-0.04%
11		17-Apr		71	2384.2	-2.22%	0.91%
12		17-May	0.52	71.6	2411.8	1.58%	1.16%
13		17-Jun		69.46	2423.41	-2.99%	0.48%
14		17-Jul		68	2470.3	-2.10%	1.93%
15		17-Aug	0.54	65.46	2471.65	-2.94%	0.05%
16		17-Sep		62.37	2519.36	-4.72%	1.93%
17		17-Oct		62.53	2575.26	0.26%	2.22%
18		17-Nov	0.54	66.16	2584.84	6.67%	0.37%
19		17-Dec		67.98	2673.61	2.75%	3.43%
20		18-Jan		68.11	2823.81	0.19%	5.62%
21		18-Feb		66.2	2713.83	-2.80%	-3.89%
22		18-Mar	0.54	65.01	2640.87	-0.98%	-2.69%
23		18-Apr		58.9	2648.05	-9.40%	0.27%
24		18-May	0.54	64.39	2705.27	10.24%	2.16%
25		18-Jun		69.87	2718.37	8.51%	0.48%
26		18-Jul		71.03	2816.29	1.66%	3.60%
27		18-Aug	0.56	71.79	2901.52	1.86%	3.03%
28		18-Sep		70.02	2913.98	-2.47%	0.43%
29		18-Oct		65.48	2711.74	-6.48%	-6.94%
30		18-Nov	0.56	63.65	2760.17	-1.94%	1.79%
31		18-Dec		59.23	2545.94	-6.94%	-7.76%
32					Average Return	-0.51%	0.53%	=AVERAGE(H9:H31)
33					St. Dev.	4.69%	3.21%	=STDEV.S(H9:H31)
34					Variance	0.2199%	0.1031%	=H33^2
35
36		3)
37
38		Cov(Kellog, S&P 500)	0.000705	=COVARIANCE.S(G9:G31,H9:H31)
39
40		4)
41
42		Correlation (Kellog, S&P 500)	0.468084	=CORREL(G9:G31,H9:H31)
43
44		5)
45
46		Beta can be calculated using following formula:
47		Beta of stock i, βi	=Cov(Rm,Ri)/σm2
48		Where Cov(Rm,Ri) is the covariance of market return (Rm) and stock return (Ri) and σm2 is the variance of the market.
49		Cov(Kellog, S&P 500)	0.000705
50		Variance for S&P 500	0.0010
51
52		Beta for Kellog	=Cov(Kellog, S&P 500)/ Var (S&P 500)
53			0.68	=D49/D50
54
55		Hence Beta for Kellog is	0.68
56