Assignment Winter 2021Financial ModellingStudents must use the excel features to answer the...

Question

Assignment Winter 2021Financial ModellingStudents 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 5Use 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 XXXXXXXXXXUse 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 risketurn relationship of your portfolio versus the S&P XXXXXXXXXXmarks)Name                                                         Symbol               Twitter, Inc.                                               TWTR-US           eBay Inc.                                                    EBAY-US             Procter & Gamble Company                    PG-US                 Marathon Petroleum Corporation          MPC-US              Allstate Corporation                                 ALL-US                Aott 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 4Part AWhat 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 4Part BUsing 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)

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...

Solution

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment