Please create a separate excel document per question.Thank you.Assignment Winter 2021 Financial...

Question

Please create a separate excel document per question.Thank you.

Assignment Winter 2021 Financial Modelling Students must use the excel features to answer the questions and submit one excel file for each question. There are a total of 2 questions. Your excel files MUST clearly identify the Question number in Cell A1. Example Q1 Version 1, or Q1 Version 2, etc. BE SURE TO CREATE A SEPARATE EXCEL FILE FOR EACH QUESTION. Question 1, Version 5 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 October 2020). Use this data to construct a value weighted portfolio. (You can get the market Capitalization data for each company from Yahoo.com).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 Twitter, Inc. TWTR-US eBay Inc. EBAY-US Procter & Gamble Company PG-US Marathon Petroleum Corporation MPC-US Allstate Corporation ALL-US Abbott Laboratories ABT-US Waste Management, Inc. WM-US Intel Corporation INTC-US Freeport-McMoRan, Inc. FCX-US American Water Works Company, Inc. AWK-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-modelling-assignment-1civietc-41e05atb.docx

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		52.862	40.643	315.222	35.05	34.297	213.026	51.443	259.057	51.085	25.104		Alpha	0.0093	0.0010	0.0083	-0.0195	0.0001	0.0099	0.0065	0.0014	0.0089	0.0155	 	 
	Portfolio weight		0.049	0.038	0.292	0.033	0.032	0.198	0.048	0.240	0.047	0.023		Beta	0.81	1.16	0.41	2.18	0.82	0.72	0.76	0.68	2.25	0.21
														R-squared	0.0685	0.3666	0.1542	0.4977	0.3682	0.4012	0.4843	0.1590	0.2443	0.0276
														Regression of value-weighted portfolio
														Portfolio alpha	0.0057
														Portfolio beta	0.75
														Portfolio r-squared	0.2430
															VFINX	Portfolio
														Average Return	1.02%	1.39%
														Beta	1	0.75
														 
														Portfolio Statistics
														Sharpe Ratio	0.259
														Treynor Ratio	0.016
														Jenson's Alpha	0.006
	Stock prices													Stock returns
		Stock Symbol	TWTR	EBAY	PG	MPC	ALL	ABT	WM	INTC	FCX	AWK		Stock Symbol
	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
	11/1/15	174.73	25.40	28.65	64.04	47.73	56.26	40.56	47.73	30.00	7.93	52.05		 
	12/1/15	170.98	23.14	26.60	67.95	42.62	55.93	40.55	47.38	29.94	6.56	54.17		11/1/15	-2.14%	-8.90%	-7.13%	6.11%	-10.72%	-0.59%	-0.02%	-0.74%	-0.22%	-17.24%	4.06%	-0.10%	 
	1/1/16	163.37	16.80	22.71	69.90	34.35	54.58	34.18	47.34	26.95	4.46	58.84		12/1/15	-4.45%	-27.40%	-14.63%	2.87%	-19.39%	-2.40%	-15.72%	-0.07%	-9.96%	-32.05%	8.64%	-8.58%
	2/1/16	163.13	18.12	23.04	69.30	28.16	57.16	35.20	49.94	25.71	7.39	58.76		1/1/16	-0.15%	7.86%	1.45%	-0.86%	-18.04%	4.72%	3.00%	5.48%	-4.61%	65.87%	-0.14%	2.62%
	3/1/16	173.31	16.55	23.10	71.04	30.89	61.00	38.01	52.75	28.36	10.02	62.81		2/1/16	6.23%	-8.66%	0.25%	2.52%	9.69%	6.71%	7.98%	5.64%	10.29%	35.52%	6.89%	7.01%
	4/1/16	174.84	14.62	23.65	69.15	32.46	58.90	35.35	52.95	26.54	13.57	66.30		3/1/16	0.89%	-11.66%	2.39%	-2.66%	5.11%	-3.44%	-7.00%	0.36%	-6.40%	35.40%	5.56%	-2.30%
	5/1/16	177.96	15.22	23.68	70.52	28.93	61.12	36.23	54.89	27.69	10.74	67.52		4/1/16	1.78%	4.10%	0.12%	1.98%	-10.88%	3.78%	2.50%	3.67%	4.33%	-20.86%	1.84%	1.31%
	6/1/16	177.57	16.91	22.66	73.68	31.82	63.33	35.94	59.68	29.00	10.80	77.40		5/1/16	-0.22%	11.10%	-4.29%	4.48%	9.98%	3.61%	-0.81%	8.73%	4.73%	0.54%	14.63%	3.89%
	7/1/16	184.97	16.64	30.17	74.48	33.02	62.17	40.91	59.95	30.82	12.56	75.63		6/1/16	4.17%	-1.60%	33.11%	1.09%	3.77%	-1.84%	13.84%	0.45%	6.28%	16.34%	-2.28%	6.54%
	8/1/16	185.21	19.21	31.13	76.57	35.63	62.74	38.65	57.97	31.73	9.97	67.76		7/1/16	0.13%	15.44%	3.21%	2.81%	7.92%	0.92%	-5.52%	-3.30%	2.95%	-20.60%	-10.40%	0.23%
	9/1/16	184.43	23.05	31.85	78.71	34.32	63.25	38.90	57.81	33.63	10.53	68.86		8/1/16	-0.42%	19.99%	2.30%	2.79%	-3.69%	0.81%	0.64%	-0.28%	5.98%	5.54%	1.62%	3.64%
	10/1/16	181.84	17.95	27.60	76.12	36.86	62.08	36.09	59.91	31.06	10.84	68.12		9/1/16	-1.41%	-22.13%	-13.34%	-3.29%	7.39%	-1.85%	-7.21%	3.64%	-7.63%	2.95%	-1.07%	-5.34%
	11/1/16	188.57	18.49	26.92	72.87	39.76	63.93	35.24	63.43	30.91	14.88	66.68		10/1/16	3.70%	3.01%	-2.46%	-4.27%	7.87%	2.97%	-2.36%	5.88%	-0.49%	37.30%	-2.12%	0.57%
	12/1/16	191.12	16.30	28.74	74.31	42.93	68.08	35.56	65.08	32.56	12.78	66.93		11/1/16	1.35%	-11.84%	6.76%	1.96%	7.97%	6.49%	0.89%	2.60%	5.32%	-14.07%	0.38%	1.63%
	1/1/17	195.88	17.62	30.81	77.42	40.96	69.08	38.67	63.79	33.05	16.14	67.93		12/1/16	2.49%	8.10%	7.21%	4.19%	-4.57%	1.47%	8.75%	-1.99%	1.52%	26.23%	1.49%	5.07%
	2/1/17	203.65	15.77	32.82	81.12	42.29	75.46	42.00	67.29	32.49	12.99	72.15		1/1/17	3.96%	-10.50%	6.50%	4.79%	3.23%	9.24%	8.62%	5.50%	-1.68%	-19.52%	6.21%	2.31%
	3/1/17	202.95	14.95	32.50	80.03	43.40	75.19	41.38	66.93	32.61	12.95	72.31		2/1/17	-0.34%	-5.20%	-0.97%	-1.34%	2.63%	-0.36%	-1.49%	-0.55%	0.35%	-0.30%	0.22%	-0.85%
	4/1/17	205.91	16.48	32.34	77.79	43.74	75.01	40.66	67.19	32.68	12.36	74.16		3/1/17	1.46%	10.23%	-0.48%	-2.80%	0.79%	-0.25%	-1.73%	0.39%	0.22%	-4.57%	2.56%	-0.75%
	5/1/17	208.78	18.32	33.21	79.07	44.69	79.67	42.80	67.31	32.64	11.14	72.69		4/1/17	1.39%	11.17%	2.66%	1.64%	2.16%	6.21%	5.27%	0.18%	-0.11%	-9.88%	-1.98%	1.91%
	6/1/17	209.17	17.87	33.81	78.22	45.24	81.60	45.57	67.72	30.73	11.64	72.86		5/1/17	0.18%	-2.46%	1.81%	-1.07%	1.25%	2.43%	6.46%	0.60%	-5.87%	4.53%	0.23%	-0.13%
	7/1/17	214.35	16.09	34.59	81.52	48.41	84.33	46.10	69.78	32.30	14.17	75.80		6/1/17	2.48%	-9.96%	2.32%	4.21%	6.99%	3.34%	1.17%	3.05%	5.13%	21.73%	4.04%	3.90%
	8/1/17	214.98	16.91	34.98	83.47	45.35	83.86	48.01	71.60	31.94	14.32	75.62		7/1/17	0.29%	5.

Assignment Winter 2021 Financial Modelling Students must use the excel features to answer the questions and submit one excel file for each question. There are a total of 2 questions. Your excel...

Answer To: Assignment Winter 2021 Financial Modelling Students must use the excel features to answer the...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment