Questions from file "Financial Modelling Assignment" to be done using excel template...

Question

Questions from file "Financial Modelling Assignment" to be done using excel template provided

Question 1, Version 1 Use the Assignment Question 1 Template to answer this question. It is located in the Assignment Module on Canvas. Gather adjusted month end prices for a 10-stock portfolio consisting of the stocks listed below for the past 60 months (ending Feb. 2021). Use this data to construct a value weighted portfolio. (You can get the market Capitalization data for each company from Yahoo.com). Calculate monthly returns of your portfolio and the S&P 500. (Use VFINX as the proxy for the S&P 500 returns). Assume the monthly risk free rate for the period was .2%. Calculate the portfolio statistics (listed on the template). Use the portfolio statistics, and any other portfolio statistics you wish to calculate, to comment on the risk/return relationship of your portfolio versus the S&P500. (10 marks) Name Symbol Verizon Communications Inc. VZ-US Ford Motor Company F-US Coca-Cola Company KO-US Exxon Mobil Corporation XOM-US Wells Fargo & Company WFC-US AbbVie, Inc. ABBV-US General Dynamics Corporation GD-US Cisco Systems, Inc. CSCO-US Air Products and Chemicals, Inc. APD-US PPL Corporation PPL-US Question 2, Version 4 Part A What will be the weights, the expected return, the variance, and the standard deviation of the minimum variance portfolio combining the stocks below. (Refer the template for Chapter 10, exercise 13 for guidance) (5 marks) Stock X Stock Y Mean Return 22.0% 14.00% Variance .12 .065 Sigma ? ? Covariance of returns .03 Question 2, Version 4 Part B Using 10% increments (0% X, 100% Y, 10% X, 90% Y, etc.) calculate and graph the efficient frontier of the stock portfolios composed of stocks X and Y. (5 marks)

financial-modeling-assignment-e3sbswiu.docx assignment-question-1-template-xi13wumx.xlsx

Shakeel · Accepted Answer

Ans 1
	10 Stock Portfolio													Risk-free rate	0.2%
		Stock Symbol												Stock Symbol
		Stock #	1	2	3	4	5	6	7	8	9	10		Stock #	1	2	3	4	5	6	7	8	9	10
	Market value		232.73	51.047	218.956	239.152	163.836	182.627	50.846	206.783	60.94	22.754		Alpha	-0.0008	-0.0086	-0.0031	-0.0179	-0.0139	0.0051	-0.0057	0.0025	0.0044	-0.0090	 	 
	Portfolio weight		0.163	0.036	0.153	0.167	0.115	0.128	0.036	0.145	0.043	0.016		Beta	0.47	1.21	0.62	1.40	1.29	0.81	1.05	0.91	0.80	0.78
														R-squared	0.1516	0.3406	0.3103	0.4945	0.3965	0.1585	0.4915	0.3264	0.4091	0.3393
														Regression of value-weighted portfolio
														Portfolio alpha	-0.0047
														Portfolio beta	0.92
														Portfolio r-squared	0.3203
															VFINX	Portfolio
														Average Return	1.34%	0.78%
														Beta	1	0.92
														 
														Portfolio Statistics
														Sharpe Ratio	0.114
														Treynor Ratio	0.006
														Jenson's Alpha	-0.005
	Stock prices													Stock returns
		Stock Symbol	VZ	F	KO	XOM	WFC	ABBV	GD	CSCO	APD	PPL		Stock Symbol		VZ	F	KO	XOM	WFC	ABBV	GD	CSCO	APD	PPL
	Date	VFINX	1	2	3	4	5	6	7	8	9	10		Date	VFINX	1	2	3	4	5	6	7	8	9	10	Portfolio
	3/1/16	174.84	40.85	10.67	38.01	69.25	42.48	48.85	125.82	23.40	120.33	29.60		3/1/16
	4/1/16	177.96	41.25	10.74	37.84	69.74	43.11	50.88	127.78	24.95	117.64	30.31		4/1/16	1.78%	0.98%	0.58%	-0.45%	0.70%	1.48%	4.16%	1.55%	6.64%	-2.23%	2.39%	1.89%	 
	5/1/16	177.57	45.26	10.00	38.46	74.05	40.53	50.06	125.41	24.64	117.15	29.69		5/1/16	-0.22%	9.71%	-6.82%	1.64%	6.19%	-5.97%	-1.62%	-1.85%	-1.24%	-0.42%	-2.05%	1.44%
	6/1/16	184.97	44.91	10.08	37.30	70.27	41.08	53.55	133.05	26.22	124.02	29.95		6/1/16	4.17%	-0.77%	0.72%	-3.01%	-5.11%	1.35%	6.98%	6.10%	6.41%	5.86%	0.87%	1.04%
	7/1/16	185.21	42.84	10.14	37.13	68.84	43.51	52.28	137.88	27.25	129.16	27.62		7/1/16	0.13%	-4.61%	0.62%	-0.46%	-2.03%	5.90%	-2.37%	3.63%	3.92%	4.15%	-7.77%	-0.02%
	8/1/16	184.43	42.55	9.71	36.18	69.54	38.23	51.44	140.54	27.49	124.78	27.45		8/1/16	-0.42%	-0.67%	-4.21%	-2.56%	1.01%	-12.13%	-1.61%	1.93%	0.89%	-3.39%	-0.60%	-2.03%
	9/1/16	181.84	39.38	9.45	36.54	66.38	39.72	45.50	136.54	26.59	119.71	27.57		9/1/16	-1.41%	-7.46%	-2.73%	1.01%	-4.54%	3.91%	-11.56%	-2.85%	-3.28%	-4.07%	0.42%	-3.69%
	10/1/16	188.57	41.32	9.74	34.78	69.55	45.69	50.05	159.62	26.06	130.43	26.86		10/1/16	3.70%	4.93%	3.14%	-4.83%	4.78%	15.02%	10.00%	16.90%	-2.00%	8.96%	-2.56%	4.62%
	11/1/16	191.12	44.20	9.88	36.04	72.55	47.97	51.55	157.17	26.41	129.85	27.33		11/1/16	1.35%	6.97%	1.42%	3.62%	4.31%	5.01%	2.99%	-1.53%	1.34%	-0.44%	1.76%	3.57%
	12/1/16	195.88	40.58	10.07	36.13	67.43	49.03	50.30	164.84	26.85	126.94	28.29		12/1/16	2.49%	-8.19%	1.90%	0.27%	-7.06%	2.21%	-2.41%	4.88%	1.65%	-2.24%	3.51%	-2.09%
	1/1/17	203.65	41.53	10.37	36.47	65.36	50.38	51.42	173.53	30.13	127.58	29.95		1/1/17	3.96%	2.35%	3.01%	0.94%	-3.06%	2.75%	2.22%	5.28%	12.22%	0.51%	5.86%	2.79%
	2/1/17	202.95	40.80	9.63	36.89	66.52	48.78	54.18	171.15	29.79	122.88	30.36		2/1/17	-0.34%	-1.77%	-7.10%	1.14%	1.77%	-3.18%	5.37%	-1.38%	-1.11%	-3.69%	1.38%	-0.09%
	3/1/17	205.91	38.42	9.49	37.84	66.23	47.19	54.83	177.17	30.03	128.50	31.28		3/1/17	1.46%	-5.83%	-1.46%	2.57%	-0.44%	-3.27%	1.20%	3.52%	0.80%	4.58%	3.03%	-0.42%
	4/1/17	208.78	39.50	9.33	39.87	65.30	44.82	55.44	186.66	28.03	131.76	32.76		4/1/17	1.39%	2.80%	-1.74%	5.38%	-1.41%	-5.01%	1.12%	5.35%	-6.65%	2.53%	4.72%	-0.04%
	5/1/17	209.17	37.82	9.39	39.33	66.10	48.90	60.89	181.93	27.83	130.85	31.74		5/1/17	0.18%	-4.25%	0.63%	-1.36%	1.23%	9.11%	9.83%	-2.53%	-0.73%	-0.69%	-3.13%	1.35%
	6/1/17	214.35	40.99	9.41	40.53	65.54	47.60	58.71	180.30	27.96	130.87	31.78		6/1/17	2.48%	8.37%	0.27%	3.05%	-0.85%	-2.65%	-3.59%	-0.89%	0.48%	0.02%	0.14%	0.98%
	7/1/17	214.98	41.16	9.37	40.27	62.50	45.07	63.80	185.69	28.90	133.84	32.53		7/1/17	0.29%	0.41%	-0.43%	-0.63%	-4.64%	-5.32%	8.68%	2.99%	3.37%	2.27%	2.37%	0.41%
	8/1/17	218.34	42.46	10.17	39.79	67.78	49.03	75.29	189.59	30.18	139.22	31.46		8/1/17	1.56%	3.17%	8.52%	-1.19%	8.44%	8.77%	18.01%	2.10%	4.41%	4.02%	-3.29%	6.19%
	9/1/17	224.49	41.07	10.43	40.97	68.91	49.91	76.47	187.19	30.65	146.78	31.46		9/1/17	2.82%	-3.27%	2.51%	2.97%	1.67%	1.80%	1.56%	-1.26%	1.55%	5.43%	-0.02%	1.11%
	10/1/17	231.36	44.18	10.77	40.79	68.86	50.20	82.69	191.81	33.76	151.04	30.71		10/1/17	3.06%	7.58%	3.30%	-0.46%	-0.07%	0.59%	8.14%	2.47%	10.17%	2.90%	-2.37%	4.02%
	11/1/17	232.85	45.96	10.74	41.22	69.79	54.31	82.52	188.38	34.67	152.00	25.92		11/1/17	0.64%	4.01%	-0.24%	1.06%	1.35%	8.19%	-0.22%	-1.79%	2.68%	0.64%	-15.60%	2.05%
	12/1/17	247.27	46.95	9.44	42.76	72.84	58.

Question 1, Version 1 Use the Assignment Question 1 Template to answer this question. It is located in the Assignment Module on Canvas. Gather adjusted month end prices for a 10-stock portfolio...

Answer To: Question 1, Version 1 Use the Assignment Question 1 Template to answer this question. It is located...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment