AFIN818 Session 1 2017 1 | P a g e Case Assessment Task 1. Introduction The case assessment task is the Harvard Business School case: Innocents abroad: Currencies and international stock returns (File names - case: 204141-PDF-ENG.pdf and data: 204705XLS-ENG.xls). The case data includes:  Exchange rates for different currencies per USD  Index values for the U.S. and foreign stock markets  Annual returns on the S&P 500 Index and on the EAFE and EM Indices (both local and dollar based returns)  Index values for the S&P500, EAFE and EM indices. Note: 1. The stock market indices are total return indices and therefore dividends are included. 2. Chinese index values are not provided before 31/5/1994. 3. In the Case, Appendix B the PDF and the XLS have some different values. Please use the values in the XLS spreadsheet. The case extends the material covered in class, provide insights into specific topics and allows you to demonstrate your grasp of the material. You can refer to, for example, BKM Ch.25 to learn more about International Diversification and the 'puzzle of home country bias’. The case also provides the opportunity to analyse large data sets using Excel worksheets. This is an individual activity and you must work on your own and your work is to be original content. 2. Task A. In Excel complete the analysis of the data that has been provided as detailed on pp.5-6 of the case – Building the Client Presentation. This will involve you performing the following: 1. Calculate stock market returns for the different countries, in both the local currency and in USD and the standard deviations of the average monthly returns. 2. Calculate the correlation coefficients between the different stock markets 3. Measure the performance of foreign equities relative to U.S. equities (based on EAFE and EM indices and the S&P 500 index 4. Decompose the relative performance of foreign equities into local equity returns and currency returns 5. Construct portfolios with different mixes of foreign and U.S. equities and identify efficient portfolios B. Analyse the data. C. Report your finding AFIN818 Session 1 2017 2 | P a g e Your report should present to the client the continued value of international diversification as detailed in the case p.6. You should consider the following questions as a minimum:  The benefits of international diversification in a pension fund's equity portfolio  Possible arguments against international equity investment and the significant of each  The impact of currency movements on returns. How important are currency considerations and what risks are posed, if any, by exchange rate uncertainties  The drivers and consequences of correlations amongst global equity markets  What are the key assumptions you have made and how sensitive are your results to these assumptions 3. Assessment The case will be marked for the following: 7% Spreadsheet Analysis 9% Interpretation of data 9% Report presentation Please see the marking rubrics for further detail. 4. Submission You are required to submit three files via the three Turnitin links on the unit’s iLearn site under Case Study heading: Create your report and the report table using the templates provided. 1. Report Create your report using the templates provided. Report your finding as detailed in 2.C above. Consider these questions as a minimum. Highlight any key assumptions or data that you require to complete any additional analysis. Create a brief business style report for your client on the benefits of continued international diversification based on the data provided and your analysis of that data. You may think of AFIN818 Session 1 2017 3 | P a g e this as being “Meyer’s” presentation. You should consider the topics that are listed by Sandra Myer for inclusion in the report (e.g., p.1). Report Length The maximum length of your report is 1 page. A second page, the appendix, can contain only figures and tables that can be referenced in page 1 of your report simply by Fig.1 or Table.1. A third page can only be used for references. The report is to be A4 1.15-spaced, size 11 points, font Times New Roman. Use margins of at least 2.54 cm (1 inch). The marker may choose to ignore any content of the report that is non-conforming. Space constraints may mean that not all topics can be fully covered in your response and you may need to prioritise your findings. 2. Report Table Create your report table using the templates provided. Selected results from Spreadsheet Analysis (1 page). Use the table provided in the template unmodified except for the inclusion of your requested figures in the required format that are to be sourced from your spreadsheet analysis of the provided data. 3. Excel Spreadsheet Use the excel spreadsheet provided and add tabs as described below. You are to create an excel spreadsheet with the following analysis (label tabs with Ex1, Ex2…). If you wish to add in additional analysis please label these tabs alphabetically as ExA, ExB). Use the XLS provided as the template and add to this spreadsheet. Exhibit 1 Monthly returns Local currency based Exhibit 2 Conversion of index values to USD by month Exhibit 3 Monthly returns USD based Exhibit 4a Annualised returns and standard deviations, local currency based returns for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 4b Annualised returns and standard deviations, USD currency based returns for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 5 Comparison of local currency based and USD based returns for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 6 NOT USED Exhibit 7a Annualised return, standard deviation and Sharpe ratios by country in local currency (1991-2003) AFIN818 Session 1 2017 4 | P a g e Exhibit 7b Column chart of 7a Exhibit 7c Annualised return, standard deviation and Sharpe ratios by country USD based (1991-2003) Exhibit 7d Column chart of 7c Exhibit 8a Correlations of monthly returns, local currency based returns for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 8b Correlations of monthly returns, USD based for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 8c Comparison of correlations over time both local and USD currency for the full period (1991-2003), and by sub periods (1991-2001), (2002-2013) Exhibit 8d Changes in correlations between (1991-2001) and (2002-2013) by local currency based returns and by USD based returns Exhibit 9a Graph of Index returns for the EAFE, EM and S&P 500 local currency from 19912013 Exhibit 9b Graph of Index returns for the EAFE, EM and S&P 500 USD from 1991-2013 Exhibit 10 EAFE performance relative to S&P and the decomposition of EAFE return into foreign equity and currency contribution by year for the period 1991 to 2012 Exhibit 11 EM performance relative to S&P and the decomposition of EM return into foreign equity and currency contribution by year for the period 1991 to 2012 Exhibit 12 Local Currency EAFE, USD based EAFE, Local currency EM, USD based EM and S&P500 monthly returns Exhibit 13a: Portfolios S&P and EAFE local currency based by month with composition in steps of 10% (e.g., 100% S&P/0%EAFE, 90%S&P/10%EAFE, 80%S&P/20%...) Exhibit 13b: Portfolios S&P and EAFE local currency based (with average monthly returns and standard deviations together with annualised returns and standard deviation and variance) for the full period and the two sub periods with composition in steps of 10% (e.g., 100% S&P/0%EAFE, 90%S&P/10%EAFE, 80%S&P/20%...) Exhibit 13c: Portfolios S&P and EAFE$ by month with composition in steps of 10% (e.g., 100% S&P/0%EAFE$, 90%S&P/10%EAFE$, 80%S&P/20%...) Exhibit 13d: Portfolios S&P and EAFE$ (with average monthly returns and standard deviations together with annualised returns and standard deviation and variance) for the full period and the two sub periods with composition in steps of 10% (e.g., 100% S&P/0%EAFE$, 90%S&P/10%EAFE$, 80%S&P/20%...) Exhibit 14: Portfolio summary data for S&P and EAFE showing Local EAFE and S&P together with $ EAFE and S&P for the full period and the sub periods with composition in steps of 10% (e.g., 100% S&P/0%EAFE, 90%S&P/10%EAFE, 80%S&P/20%...) Exhibit 15: Chart of the efficient portfolio frontier 5. Submission details The written report is due by Sunday 21 May 2017 at 5:00 p.m. Australian EST. No extensions will be granted. There will be a deduction of 10% of the total available marks made from the total awarded mark for each 24-hour period or part thereof that the submission is late (for example, 25 hours late in submission –20% penalty). AFIN818 Session 1 2017 5 | P a g e No submission will be accepted after feedback has been provided in the first class of week 12. This penalty does not apply for cases in which an application for disruption of studies is made and approved. Please make sure you allow time in case you have technical problems. Late submission due to technical problems are not an acceptable excuse. Resubmissions are permitted up to the due date. Similarity scores are generated after the due date. Your case assignment task must be submitted using the Turnitin assignment drop box in iLearn. You will submit three files: Your Report saved in PDF format name as <8 digit studentID>report.pdf e.g., xxxxxxxx.pdf Your Report table in in PDF format name as <8 digit studentID>table.xls e.g., xxxxxxxx.xls Your Excel spreadsheet in Microsoft excel format name as <8 digit studentID>.xls e.g., xxxxxxxx.xls Your assignment will score zero if the spreadsheet does not provide the information used in the Report and/or the Report table. 6. Originality The written report must contain only original work. All information sources used in the preparation of the report must be acknowledged appropriately. The University defines plagiarism in its rules: "Plagiarism involves using the work of another person and presenting it as one's own." Plagiarism is a serious breach of the University's rules and carries significant penalties. You must read the University’s practices and procedures on plagiarism. These can be found in the Handbook of Undergraduate Studies or on the web at: http://mafcstudents.mq.edu.au/student-administration/program-rules/program-rules-onlineversion/plagiarism/ AFIN818 Session 1 2017 6 | P a g e The policies and procedures explain what plagiarism is, how to avoid it, the procedures that will be taken in cases of suspected plagiarism, and the penalties if you are found guilty. Penalties may include a deduction of marks, failure in the unit, and/or referral to the University Discipline Committee.