BFC 5935W1-Monthly Data Year Manapon Limkriangkrai: ML: You need to create Year and Month Columns...

Question

BFC 5935W1-Monthly Data 	Year Manapon Limkriangkrai: ML: You need to create Year and Month Columns based on the Date. 	Month	Date User: ML: These shaded 4 columns are the raw data from Yahoo Finance. You will need to create Year & Month Columns 	RIO	BHP	MQG W2-Calculations 	Year	Month	RIO	BHP	Portfolio 1								Covariance and Variance Calculation 			Return	Return	Equal-Weighted Return								RIO			BHP			Product 									RIO	BHP	Portfolio 1		Return	Return-mean	(Return-mean)^2	Return	Return-mean	(Return-mean)^2 							Monthly mean 							Monthly variance 							Monthly standard deviation 									RIO	BHP	Portfolio 1 							Annual mean  							Annual variance 							Annual standard deviation Manapon Limkriangkrai: ML: This is the square root of the Variance. 							Covariance 							Correlation 							Optimal Weight Calculations 							RIO Weight 							BHP Weight 										MVP 1 							Expected Return [E(Rp)] based on optimal weights 							Risk [σp] based on optimal weights 										Coefficient of Variations 							RIO 							BHP 							Portfolio 1 							MVP 1 W3-Calculations 	Year	Month	RIO	MQG	Portfolio 2								Covariance and Variance Calculation 			Return	Return	Equal-Weighted Return								RIO			MQG			Product 									RIO	MQG	Portfolio 2		Return	Return-mean	(Return-mean)^2	Return	Return-mean	(Return-mean)^2 							Monthly mean 							Monthly variance 							Monthly standard deviation 									RIO	MQG	Portfolio 2 							Annual mean  							Annual variance 							Annual standard deviation 							Covariance 							Correlation 							Optimal Weight Calculations 							RIO Weight 							MQG Weight 										MVP 2 							Expected Return [E(Rp)] based on optimal weights 							Risk [σp] based on optimal weights 										Coefficient of Variations 							RIO 							MQG 							Portfolio 2 							MVP 2 W4-Beta 	Year	Month	RIO	MQG	Rm User: ML: Both MKT and Rf are provided in a separate file. You will need to transfer the relevant period here. 	Rf	? Manapon Limkriangkrai: ML: You can create another Variable in this column for the regression. 	? Manapon Limkriangkrai: ML: You can create another Variable in this column for the regression. 	? Manapon Limkriangkrai: ML: You can create another Variable in this column for the regression. 		Output regression result to this column from Cell K3 onwards (Format the numbers to 2 decimals) W5-Analysis 	[10A] With reference to W2 & W3 - Calculations, compare and contrast the Return and Risk of Portfolio 1 with Portfolio 2. Which is considered to be more efficient? Explain why this is the case. 	[10B] With reference to W3 - Calculations, compare the performances of different assets (RIO, MQG, Portfolio 2 and MVP 2) based on coefficient of variations. Explain why this is expected. 	[10C] With reference to W4 - Beta, provide the interpretations of the regression output, specifically the levels of Coefficients and significance (p-values) of the Intercept and Beta (X Variable 1) in W4-Beta. [Note] If the p-value is less than 0.05 (0.01), then it is significant at the 5% (1%) level. The null hypothesis is that the coefficient is equal to zero.  	[10D] With reference to W4 - Beta, are the regression results consistent with the CAPM predictions? Explain. And ‘If’ the results are inconsistent, also explain the potential reason(s), based on the concepts covered in BFC5935. W6-Return Distribution 	Year	Month	RIO	MQG		[11] Output Summary Statsitics from Cell F3 onwards below. 			Return	Return 						[12] Compare and contrast the Return Distribution (based on materials covered in class) of RIO and MQG.    Sheet1 	Year	Month	MKT	RF 	2000	1	-0.0365	0.0044 	2000	2	-0.0017	0.0046 	2000	3	0.0113	0.0046 	2000	4	-0.0267	0.0047 	2000	5	-0.0251	0.0049 	2000	6	0.0532	0.0048 	2000	7	-0.0160	0.0048 	2000	8	0.0023	0.0052 	2000	9	-0.0285	0.0052 	2000	10	-0.0062	0.0051 	2000	11	0.0075	0.0050 	2000	12	-0.0121	0.0049 	2001	1	0.0259	0.0047 	2001	2	-0.0026	0.0045 	2001	3	-0.0405	0.0042 	2001	4	0.0417	0.0039 	2001	5	0.0113	0.0039 	2001	6	0.0169	0.0039 	2001	7	-0.0426	0.0040 	2001	8	-0.0106	0.0039 	2001	9	-0.0677	0.0036 	2001	10	0.0546	0.0035 	2001	11	0.0262	0.0034 	2001	12	0.0169	0.0034 	2002	1	0.0153	0.0034 	2002	2	-0.0112	0.0034 	2002	3	-0.0006	0.0035 	2002	4	-0.0225	0.0037 	2002	5	0.0033	0.0038 	2002	6	-0.0434	0.0040 	2002	7	-0.0497	0.0039 	2002	8	0.0114	0.0039 	2002	9	-0.0484	0.0039 	2002	10	0.0097	0.0039 	2002	11	-0.0020	0.0038 	2002	12	-0.0130	0.0038 	2003	1	-0.0208	0.0038 	2003	2	-0.0573	0.0038 	2003	3	0.0233	0.0038 	2003	4	0.0302	0.0038 	2003	5	-0.0016	0.0038 	2003	6	0.0125	0.0037 	2003	7	0.0286	0.0038 	2003	8	0.0282	0.0039 	2003	9	-0.0040	0.0039 	2003	10	0.0277	0.0040 	2003	11	-0.0174	0.0042 	2003	12	0.0261	0.0043 	2004	1	-0.0157	0.0044 	2004	2	0.0177	0.0044 	2004	3	0.0093	0.0043 	2004	4	-0.0191	0.0043 	2004	5	0.0065	0.0043 	2004	6	0.0176	0.0043 	2004	7	0.0006	0.0043 	2004	8	0.0057	0.0043 	2004	9	0.0227	0.0043 	2004	10	0.0218	0.0043 	2004	11	0.0376	0.0043 	2004	12	0.0162	0.0043 	2005	1	0.0076	0.0043 	2005	2	0.0109	0.0045 	2005	3	-0.0202	0.0046 	2005	4	-0.0445	0.0046 	2005	5	0.0190	0.0045 	2005	6	0.0372	0.0045 	2005	7	0.0193	0.0045 	2005	8	0.0157	0.0045 	2005	9	0.0384	0.0045 	2005	10	-0.0469	0.0045 	2005	11	0.0379	0.0045 	2005	12	0.0228	0.0045 	2006	1	0.0248	0.0045 	2006	2	-0.0011	0.0045 	2006	3	0.0384	0.0045 	2006	4	0.0137	0.0046 	2006	5	-0.0478	0.0047 	2006	6	0.0053	0.0048 	2006	7	-0.0228	0.0048 	2006	8	0.0205	0.0049 	2006	9	0.0046	0.0049 	2006	10	0.0373	0.0050 	2006	11	0.0147	0.0051 	2006	12	0.0273	0.0051 	2007	1	0.0134	0.0052 	2007	2	0.0018	0.0051 	2007	3	0.0206	0.0052 	2007	4	0.0208	0.0052 	2007	5	0.0230	0.0051 	2007	6	-0.0113	0.0052 	2007	7	-0.0276	0.0052 	2007	8	-0.0047	0.0053 	2007	9	0.0445	0.0053 	2007	10	0.0188	0.0054 	2007	11	-0.0360	0.0055 	2007	12	-0.0306	0.0055 	2008	1	-0.1232	0.0056 	2008	2	-0.0125	0.0058 	2008	3	-0.0510	0.0059 	2008	4	0.0332	0.0059 	2008	5	0.0112	0.0059 	2008	6	-0.0900	0.0059 	2008	7	-0.0602	0.0059 	2008	8	0.0281	0.0056 	2008	9	-0.1139	0.0054 	2008	10	-0.1397	0.0043 	2008	11	-0.0783	0.0035 	2008	12	-0.0048	0.0030 	2009	1	-0.0481	0.0026 	2009	2	-0.0540	0.0022 	2009	3	0.0691	0.0023 	2009	4	0.0601	0.0023 	2009	5	0.0179	0.0024 	2009	6	0.0276	0.0024 	2009	7	0.0711	0.0024 	2009	8	0.0560	0.0025 	2009	9	0.0499	0.0026 	2009	10	-0.0252	0.0030 	2009	11	0.0115	0.0030 	2009	12	0.0301	0.0031 	2010	1	-0.0598	0.0033 	2010	2	0.0018	0.0032 	2010	3	0.0543	0.0034 	2010	4	-0.0299	0.0036 	2010	5	-0.0790	0.0037 	2010	6	-0.0360	0.0037 	2010	7	0.0315	0.0037 	2010	8	-0.0169	0.0037 	2010	9	0.0411	0.0038 	2010	10	0.0158	0.0038 	2010	11	-0.0099	0.0039 	2010	12	0.0284	0.0039 	2011	1	-0.0037	0.0039 	2011	2	0.0121	0.0039 	2011	3	-0.0030	0.0039 	2011	4	-0.0135	0.0039 	2011	5	-0.0261	0.0039 	2011	6	-0.0344	0.0039 	2011	7	-0.0380	0.0038 	2011	8	-0.0337	0.0036 	2011	9	-0.0647	0.0036 	2011	10	0.0578	0.0036 	2011	11	-0.0414	0.0035 	2011	12	-0.0182	0.0033 	2012	1	0.0430	0.0033 	2012	2	0.0189	0.0033 	2012	3	0.0077	0.0034 	2012	4	0.0010	0.0032 	2012	5	-0.0710	0.0028 	2012	6	-0.0296	0.0026 	2012	7	0.0522	0.0027 	2012	8	0.0168	0.0028 	2012	9	0.0187	0.0027 	2012	10	0.0218	0.0025 	2012	11	-0.0027	0.0025 	2012	12	0.0274	0.0024 	2013	1	0.0468	0.0024 	2013	2	0.0415	0.0024 	2013	3	-0.0246	0.0024 	2013	4	0.0312	0.0024 	2013	5	-0.0348	0.0022 	2013	6	-0.0278	0.0022 	2013	7	0.0528	0.0021 	2013	8	0.0191	0.0020 	2013	9	0.0197	0.0020 	2013	10	0.0326	0.0020 	2013	11	-0.0106	0.0020 	2013	12	0.0075	0.0020 	2014	1	-0.0274	0.0020 	2014	2	0.0396	0.0021 	2014	3	-0.0038	0.0021 	2014	4	0.0064	0.0021 	2014	5	0.0031	0.0021 	2014	6	-0.0156	0.0021 	2014	7	0.0371	0.0020 	2014	8	0.0027	0.0021 	2014	9	-0.0474	0.0021 	2014	10	0.0300	0.0021 	2014	11	-0.0258	0.0021 	2014	12	0.0161	0.0020 	2015	1	0.0285	0.0020 	2015	2	0.0624	0.0017 	2015	3	-0.0005	0.0017 	2015	4	-0.0191	0.0017 	2015	5	0.0118	0.0016 	2015	6	-0.0532	0.0016 	2015	7	0.0395	0.0016 	2015	8	-0.0701	0.0016 	2015	9	-0.0264	0.0016 	2015	10	0.0498	0.0016 	2015	11	-0.0121	0.0016 	2015	12	0.0125	0.0016 	2016	1	-0.0572	0.0016 	2016	2	-0.0135	0.0016 	2016	3	0.0354	0.0016 	2016	4	0.0261	0.0016 	2016	5	0.0336	0.0014 	2016	6	-0.0268	0.0014 	2016	7	0.0572	0.0013 	2016	8	-0.0094	0.0012 	2016	9	-0.0024	0.0012 	2016	10	-0.0232	0.0012 	2016	11	0.0237	0.0012 	2016	12	0.0390	0.0012 	2017	1	-0.0112	0.0012 	2017	2	0.0165	0.0012 	2017	3	0.0263	0.0012 	2017	4	0.0051	0.0012 	2017	5	-0.0256	0.0012 	2017	6	0.0010	0.0012 	2017	7	-0.0000	0.0012 	2017	8	0.0048	0.0012 	2017	9	-0.0004	0.0012 	2017	10	0.0368	0.0012 	2017	11	0.0169	0.0012 	2017	12	0.0181	0.0012 	2018	1	-0.0040	0.0012 	2018	2	-0.0025	0.0012 	2018	3	-0.0348	0.0012 	2018	4	0.0299	0.0012 	2018	5	0.0110	0.0012 	2018	6	0.0256	0.0012 	2018	7	0.0101	0.0012 	2018	8	0.0145	0.0012 	2018	9	-0.0119	0.0012 	2018	10	-0.0665	0.0012 	2018	11	-0.0229	0.0012 	2018	12	-0.0070	0.0012   1 Monash University  Department of Banking and Finance  BFC5935: Portfolio Management Theory  2021 Semester 1    INDIVIDUAL ASSIGNMENT01     Objectives: This assignment is designed to show how the topics covered so far – namely, Topic 1 Asset  & Investments, Topic 2 Portfolio Theory and Topic 3 Asset Pricing Models – can be applied using real  financial data and Excel. It is also to further develop your technical and analytical skills, which will help  with your employability.  Important Note: The sample period and correlation applicable depends on the allocated tutorial with  specific tutor, and it is the student’s responsibility to know the tutorial allocated to (e.g. Tutorial 01 –  Mon 11am), as well as the tutor’s name.  Plagiarism & Collusion: Note that this is an individual assignment and that it is not a group work –  please read all the relevant documents related to Plagiarism & Collusion on the Monash page and on  Moodle:  https://www.monash.edu/students/admin/policies/academic-integrity  Any violations are officially misconduct and will be dealt with seriously, and with the case potentially  escalated to the Faculty.  Total Marks: 36 Marks [12% of Total Assessment]       Submission:  You will need to use the Excel Spreadsheet Template posted on Moodle and submit via  Moodle by the due date & time as stated in the Unit Guide. Late submission will result in 10% penalty  each day (including weekend & public holiday).     You are also required to submit the ‘signed’ assignment cover sheet confirming that this is your own  work. The assignment without the signed cover sheet will be deemed incomplete and the late penalty  will apply.    [IMPORTANT] After uploading the files (it will appear as draft, but the files are actually not submitted  yet), you will need to click 'submit' and the ‘Status’ will be indicated as ‘Submitted for grading’.  Please note that you are only allowed one submission – so it is your responsibility to ensure that all the  submitted files are correct.  Also, we would advise that, it is best to not leave until the last minute to submit (especially given that  the ASM does not take that long to complete), please feel free to submit at least 1-2 days before the  deadline.  For the cover sheet, you can submit as a PDF file, or alternatively, you can have your signature as a  picture and paste in the word document. If your signature is in Chinese (or another language), it is  completely fine.     https://www.monash.edu/students/admin/policies/academic-integrity 2 Tasks: You are required to construct portfolios using monthly data of Rio Tinto Group (RIO), and BHP  Group (BHP), and Macquarie Group Limited (MQG) stocks listed on the ASX (Tip: put ‘.ax’ after the  stock ticker and check that the company’s full name is correct), over the following sample period based  on your Allocated Tutorial:  TUTOR  Tutorial Day & Time Sample Period  Tracy Mon 12pm, 2pm & 3pm 2004 – 2006  Kate / Farhan Tue 12pm, 1pm, 2pm & 6pm 2007 – 2009  Taher / Kate Wed 2pm & 3pm 2010 – 2012  Anh Thur 1pm & 2pm 2013 – 2015  Taher / Farhan Fri 12pm & 1pm 2016 – 2018  [Note 1]: Using incorrect stocks and/or sample period will incur immediate penalty of 30% [or possibly  100% if the entire assignment is then found to have copied/plagiarised another assignment].   [Note 2]: As stated, you are required to use the template provided – do not modify or add worksheets –  This will also incur penalty. All results required can be calculated based on the template provide.  [Note 3]: You are required to show workings in the form of using formulae (i.e. do not copy and paste  special as ‘value’) as the marker will check how the answers are calculated. Answers are required to  have 2 decimal places.   PART 1 [7 Marks]  [1]     Download monthly RIO, BHP & MQG (Adj Close) price data from Yahoo Finance:    https://au.finance.yahoo.com/  [Note  that although this is Yahoo AU and that it shows the required date format to be 'dd/mm/yyyy'  - when you actually input the date, it will be based on the US format with month first - i.e.  'mm/dd/yyyy'. You will have to select 'Monthly' for Frequency, and by default

W1-Monthly Data Year Manapon Limkriangkrai: ML: You need to create Year and Month Columns based on the Date. MonthDate User: ML: These shaded 4 columns are the raw data from Yahoo Finance. You will...

Get Answer To This Question

Related Questions & Answers

Submit New Assignment