Looking for a hand with this assignment. I don't need a hand to write the report. i just need some guidance with the data provided on what variables to use in the regression to help calculate these two models: Stone's two-index model and Fama and French three factor model.

I need to analyse the two bank stocks separately using both models.

I know it mentions the variables in the question but i'm just not sure how to get to those variable figures from the data provided.

If you can help me out in the excel data file provided that would be greatly appreciated.

ACFI3190 Financial Institutions Risk Management Group Assignment Semester 2, 2017 Course Coordinator: Md Safiullah Weight: 50% Total Marks: 60 Due time and date: 1pm, Friday, 20 October, 2017 Assignment Topic You are a research analyst in a financial institution. You need to write a research report on the sensitivity of the stock return of the financial institution to the change in the longterm interest rate using the Stone (1974) two-index model, and an extension of its using the Fama-French (1993) three-factor model. The following models can be used to estimate the empirical duration (that is, the sensitivity of the stock return of the financial institution/s to the change in the long-term interest rate). Stone (1974) two-index model: (1) ,t = 0 + 1 , + + , where , is stock return on the individual financial institution; , is return on the S&P/ASX All ordinaries Index; represents change in 10-year commonwealth government securities yield; and , is the random error term at month t. Extension of Stone (1974) two-index model using the Fama-French (1993) three-factor model: SMB + HML + + , (2) ,t = 0 + , + is the the difference between the returns on global portfolios of small Where stocks and large stocks; is the spread in returns between global portfolios of value stocks and growth stocks. Preparing the report The table below outlines the structure of the report and the marks allocation for each section. Section Cover sheet Title page ACFI3190 S2 2017 Contents Marks allocation Each group member has to fill in a cover sheet. All the cover sheets have to be attached to the assignment The title of the report 3 Group members' names and ID numbers Course code and title Class time Word count (includes Introduction, Body and Conclusion): 2000 maximum. Page 1 of 3 Executive summary This is a short summary of the whole report. It summarises the report's purpose, findings, conclusions and recommendations. It is like an abstract or a synopsis and should be no more than one page. Table of contents This lists the main sections of the report as well as the second and third level headings with the page numbers on which each occurs. 1. Introduction The introduction usually includes: Brief background information Purpose Scope Outline of the report 2. Body 1. Literature review [5 marks] 2. Methodology (Hints: you should discuss the model, sources of data, variables etc.) [5 marks] 5 2 10 30 3. Findings (Hints: you should discuss the results from the model) [20 marks] 3. Conclusion 4. References Report format Total marks (Hints: you should discuss the implications) Please ensure to properly reference other people's work in your assignment. This includes in-text referencing and a reference list. The suggested reference style is APA 5th. Penalty marks apply for poorly formatted reports. [eg, missing the cover sheets, missing any section of the report, no headings, no page number etc.] 5 5 [in-text reference and reference list] Up to 10 60 Additional information To assist you in preparing the report, I have provided all relevant data in excel sheets (Data sources : Datastream and French Data Library) and a number of relevant research articles on Blackboard. You are encouraged to also search for additional articles besides the ones provided. You will also need to read the relevant textbook chapters (discussing interest rate risk, credit risk, market risk and liquidity risk) to form the background knowledge for the assignment. Each group should work on the assigned two firms for comparison purpose. ACFI3190 S2 2017 Page 2 of 3 Submission Instructions This assignment is marked out of 60 marks. It counts toward 30% of the total course mark. For every day of late submission, 10 marks will be deducted unless an extension is granted. Word limit: 2,000 words, excluding tables, figures and appendices. Submission: A soft copy in Lecture 11 and Online through Turnitin. Assignments submitted by other means will not be marked. Please ensure only one submission per group (have only one group member submit the assignment). ACFI3190 S2 2017 Page 3 of 3 Frequency Name INVERLOCH &LOGAN CMTY.FINL.SVS. Code NX:IAX(P) NX:LCX(P) 9/28/2007 0.7 10/31/2007 0.55 11/30/2007 0.9 0.55 12/31/2007 0.9 0.7 1/31/2008 0.9 0.7 2/29/2008 0.9 0.55 3/31/2008 0.9 0.55 4/30/2008 0.9 0.55 5/30/2008 0.9 0.55 6/30/2008 0.9 0.55 7/31/2008 0.9 0.65 8/29/2008 0.9 0.65 9/30/2008 0.9 0.7 10/31/2008 0.9 0.7 11/28/2008 0.9 0.7 12/31/2008 0.9 0.7 1/30/2009 0.9 0.6 2/27/2009 0.9 0.59 3/31/2009 0.9 0.59 4/30/2009 0.9 0.65 5/29/2009 0.9 0.65 6/30/2009 0.9 0.65 7/31/2009 0.9 0.65 8/31/2009 0.9 0.5 9/30/2009 0.9 0.65 10/30/2009 0.9 0.7 11/30/2009 0.9 0.7 12/31/2009 0.7 0.7 1/29/2010 0.7 0.7 2/26/2010 0.7 0.7 3/31/2010 0.7 0.5 4/30/2010 0.7 0.5 5/31/2010 0.7 0.5 6/30/2010 0.7 0.5 7/30/2010 0.7 0.5 8/31/2010 0.7 0.5 9/30/2010 0.7 0.5 10/29/2010 0.7 0.6 11/30/2010 0.7 0.65 12/31/2010 0.7 0.65 1/31/2011 0.7 0.55 2/28/2011 0.7 0.55 3/31/2011 4/29/2011 5/31/2011 6/30/2011 7/29/2011 8/31/2011 9/30/2011 10/31/2011 11/30/2011 12/30/2011 1/31/2012 2/29/2012 3/30/2012 4/30/2012 5/31/2012 6/29/2012 7/31/2012 8/31/2012 9/28/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/29/2013 4/30/2013 5/31/2013 6/28/2013 7/31/2013 8/30/2013 9/30/2013 10/31/2013 11/29/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/30/2014 6/30/2014 7/31/2014 8/29/2014 9/30/2014 10/31/2014 11/28/2014 0.7 0.7 0.7 0.7 0.7 0.7 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.95 0.95 0.95 0.95 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.55 0.5 0.5 0.5 0.5 0.5 0.6 0.6 0.6 0.6 0.5 0.5 0.5 0.5 0.5 1 1 1 1 0.55 0.55 0.55 0.55 0.6 0.6 0.6 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 0.75 12/31/2014 1/30/2015 2/27/2015 3/31/2015 4/30/2015 5/29/2015 6/30/2015 7/31/2015 8/31/2015 9/30/2015 10/30/2015 11/30/2015 12/31/2015 1/29/2016 2/29/2016 3/31/2016 4/29/2016 5/31/2016 6/30/2016 7/29/2016 8/31/2016 9/30/2016 10/31/2016 11/30/2016 12/30/2016 1/31/2017 2/28/2017 3/31/2017 4/28/2017 5/31/2017 6/30/2017 7/31/2017 8/31/2017 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.9 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.7 0.6 0.75 0.55 0.55 0.55 0.55 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.4 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.5 0.4 Frequency Name Code 9/28/2007 10/31/2007 11/30/2007 12/31/2007 1/31/2008 2/29/2008 3/31/2008 4/30/2008 5/30/2008 6/30/2008 7/31/2008 8/29/2008 9/30/2008 10/31/2008 11/28/2008 12/31/2008 1/30/2009 2/27/2009 3/31/2009 4/30/2009 5/29/2009 6/30/2009 7/31/2009 8/31/2009 9/30/2009 10/30/2009 11/30/2009 12/31/2009 1/29/2010 2/26/2010 3/31/2010 4/30/2010 5/31/2010 6/30/2010 7/30/2010 8/31/2010 9/30/2010 10/29/2010 11/30/2010 12/31/2010 1/31/2011 2/28/2011 M ASX ALL ORDINARY DS-CALC. - PRICE INDEX ASXAORZ(PI) 2152.38 2212.79 2158.15 2073.6 1893.3 1881.49 1798.53 1872.39 1905.09 1764.13 1676.63 1729.02 1542.01 1331.05 1237.34 1243.9 1195.79 1135.55 1212.6 1269.04 1299.64 1335.67 1432.69 1505.23 1546.07 1515.07 1543.54 1595.96 1503.97 1520.39 1599.11 1583.26 1469.28 1429.99 1482.36 1462.78 1522.53 1551.19 1534.11 1585.54 1585.42 1607.39 3/31/2011 4/29/2011 5/31/2011 6/30/2011 7/29/2011 8/31/2011 9/30/2011 10/31/2011 11/30/2011 12/30/2011 1/31/2012 2/29/2012 3/30/2012 4/30/2012 5/31/2012 6/29/2012 7/31/2012 8/31/2012 9/28/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/29/2013 4/30/2013 5/31/2013 6/28/2013 7/31/2013 8/30/2013 9/30/2013 10/31/2013 11/29/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/30/2014 6/30/2014 7/31/2014 8/29/2014 9/30/2014 10/31/2014 11/28/2014 1607.59 1599.84 1563.75 1517.15 1471.68 1431.86 1340.95 1428.06 1370.69 1344.1 1410.21 1430 1438.87 1452.89 1351.16 1352.62 1401.78 1415.65 1437.84 1477.33 1472.68 1517.43 1593.68 1660.67 1617.68 1680.72 1602.38 1561.34 1646.66 1671.98 1703.29 1767.91 1731.6 1742.99 1696.02 1764.44 1760.08 1782.36 1783.43 1754.42 1831.32 1831.16 1725.9 1792.69 1726.38 12/31/2014 1/30/2015 2/27/2015 3/31/2015 4/30/2015 5/29/2015 6/30/2015 7/31/2015 8/31/2015 9/30/2015 10/30/2015 11/30/2015 12/31/2015 1/29/2016 2/29/2016 3/31/2016 4/29/2016 5/31/2016 6/30/2016 7/29/2016 8/31/2016 9/30/2016 10/31/2016 11/30/2016 12/30/2016 1/31/2017 2/28/2017 3/31/2017 4/28/2017 5/31/2017 6/30/2017 7/31/2017 8/31/2017 1757.84 1811.59 1924.82 1912.69 1882.86 1884.03 1777.89 1854.12 1705.29 1653.75 1729.14 1706.27 1745.65 1655.36 1623.31 1690.74 1746.84 1795.61 1752.69 1862.83 1826.53 1819.26 1775.27 1806.03 1875.71 1863.37 1889.39 1932.17 1948.38 1890.97 1892.37 1893.96 1879.32 Frequency Name Code 9/28/2007 10/31/2007 11/30/2007 12/31/2007 1/31/2008 2/29/2008 3/31/2008 4/30/2008 5/30/2008 6/30/2008 7/31/2008 8/29/2008 9/30/2008 10/31/2008 11/28/2008 12/31/2008 1/30/2009 2/27/2009 3/31/2009 4/30/2009 5/29/2009 6/30/2009 7/31/2009 8/31/2009 9/30/2009 10/30/2009 11/30/2009 12/31/2009 1/29/2010 2/26/2010 3/31/2010 4/30/2010 5/31/2010 6/30/2010 7/30/2010 8/31/2010 9/30/2010 10/29/2010 11/30/2010 12/31/2010 1/31/2011 2/28/2011 M AUSTRALIA BOND YIELD 10 Y - MIDDLE RATE ABND10Y 6.155 6.18 6.00 6.33 6.09 6.21 6.04 6.29 6.53 6.45 6.23 5.75 5.40 5.18 4.58 3.99 4.10 4.40 4.41 4.57 5.28 5.52 5.60 5.41 5.36 5.54 5.23 5.65 5.38 5.43 5.76 5.71 5.38 5.10 5.20 4.77 4.96 5.21 5.41 5.51 5.49 5.47 3/31/2011 4/29/2011 5/31/2011 6/30/2011 7/29/2011 8/31/2011 9/30/2011 10/31/2011 11/30/2011 12/30/2011 1/31/2012 2/29/2012 3/30/2012 4/30/2012 5/31/2012 6/29/2012 7/31/2012 8/31/2012 9/28/2012 10/31/2012 11/30/2012 12/31/2012 1/31/2013 2/28/2013 3/29/2013 4/30/2013 5/31/2013 6/28/2013 7/31/2013 8/30/2013 9/30/2013 10/31/2013 11/29/2013 12/31/2013 1/31/2014 2/28/2014 3/31/2014 4/30/2014 5/30/2014 6/30/2014 7/31/2014 8/29/2014 9/30/2014 10/31/2014 11/28/2014 5.45 5.40 5.21 5.21 4.81 4.37 4.22 4.51 3.93 3.67 3.72 3.97 4.08 3.67 2.92 3.04 3.01 3.01 2.91 3.06 3.09 3.27 3.45 3.35 3.42 3.09 3.36 3.76 3.72 3.90 3.81 3.94 4.13 4.23 4.00 4.02 4.08 3.95 3.66 3.54 3.51 3.30 3.48 3.29 3.03 12/31/2014 1/30/2015 2/27/2015 3/31/2015 4/30/2015 5/29/2015 6/30/2015 7/31/2015 8/31/2015 9/30/2015 10/30/2015 11/30/2015 12/31/2015 1/29/2016 2/29/2016 3/31/2016 4/29/2016 5/31/2016 6/30/2016 7/29/2016 8/31/2016 9/30/2016 10/31/2016 11/30/2016 12/30/2016 1/31/2017 2/28/2017 3/31/2017 4/28/2017 5/31/2017 6/30/2017 7/31/2017 8/31/2017 2.81 2.44 2.46 2.32 2.65 2.73 3.01 2.76 2.66 2.61 2.61 2.86 2.88 2.64 2.40 2.49 2.52 2.30 1.98 1.88 1.83 1.91 2.31 2.67 2.76 2.72 2.72 2.70 2.58 2.39 2.60 2.63 2.63 SMB 200709 200710 200711 200712 200801 200802 200803 200804 200805 200806 200807 200808 200809 200810 200811 200812 200901 200902 200903 200904 200905 200906 200907 200908 200909 200910 200911 200912 201001 201002 201003 201004 201005 201006 201007 201008 201009 201010 201011 201012 201101 201102 201103 201104 HML -2.14 0.85 -3.49 -0.72 -1.15 2.17 -0.82 -2.78 1.7 1.29 -0.41 -0.46 -1.26 -2.78 -0.46 2.22 2.27 1.17 -1.36 1.68 1.64 2.78 -1.39 0.87 1.68 -0.39 -2.43 0.65 2.64 -0.02 -0.18 3.9 0.07 0.3 -1.51 -0.15 1.59 -0.04 2.1 1.97 -1.59 0.19 1.45 -0.64 -1.49 -2.63 -0.65 0.56 2.67 -0.86 1.58 -0.38 -1.28 -1.12 2.02 0.18 2.34 0.01 -1.69 0.49 -4.6 -4.19 -0.04 2.68 -0.09 -1.63 4 4.79 0.39 -2.19 -1.44 0.79 -0.1 0.14 3 0.77 -2.45 -2.76 1.4 -1.99 -1.63 -1.2 -0.67 1.92 2.72 0.46 -1.66 -1.83 201105 201106 201107 201108 201109 201110 201111 201112 201201 201202 201203 201204 201205 201206 201207 201208 201209 201210 201211 201212 201301 201302 201303 201304 201305 201306 201307 201308 201309 201310 201311 201312 201401 201402 201403 201404 201405 201406 201407 201408 201409 201410 201411 201412 201501 -0.52 -0.46 1.18 -0.66 -1.34 -1.97 -1.12 -0.49 2.13 -0.85 -0.37 0.23 -0.54 -1.59 -1.64 -0.21 1.11 -0.7 -1.19 1.29 0.03 0.32 0.69 -1.32 -0.63 0.44 0.01 1.29 2.04 -1.65 -0.21 -0.22 2.72 -0.71 -0.46 -2.65 -1.12 2.09 -1.17 -0.47 -2.68 -0.17 -2.09 1.76 -0.03 -1.34 0.58 -2.17 -1.78 0.86 -1.92 -0.72 1.94 -1.09 -0.21 -0.03 -1.75 0.18 1.53 -1.05 0.74 1.26 2.18 0.03 3.52 2.11 -0.72 -2.01 1.05 1.01 -0.24 0.43 -0.69 -0.34 1.9 -0.17 -0.15 -0.04 0.03 2.72 1.07 -0.32 -0.11 0.21 -0.8 -0.86 -3.04 -2.14 -0.12 -2.44 201502 201503 201504 201505 201506 201507 201508 201509 201510 201511 201512 201601 201602 201603 201604 201605 201606 201607 201608 201609 201610 201611 201612 201701 201702 201703 201704 201705 201706 201707 -0.17 1.4 0.8 1.26 2.09 -3.07 1.38 -0.26 -2.32 1.77 0.97 -2.1 0.84 1.29 0.99 0 -0.48 1.34 0.34 2.25 -1.59 0.3 -0.45 0.33 -0.64 0.41 0.56 -0.46 1.64 -0.05 -0.45 -0.86 2.15 -1.25 -0.68 -3.21 0.98 -0.93 0.27 -1.92 -1.68 0.79 -0.17 0.56 2.99 -2.04 -0.72 0.3 2.23 -1.26 4.26 4.32 2.74 -0.67 -1.77 -1.76 -1.49 -2.59 1.61 1