OBJECTIVE Relationships between Security Return, Beta, Risks and Financial Ratio of a company assigned to your group Refer to the Excel Example and Word Explanation in Moodle Part 1A - Return and Risk (30%) Step 1 Collect 10 years (1-1-2010 to 31-12-2019) of DAILY closing price data for your company from https://au.finance.yahoo.com Step 2 Collect 10 years of All Ordinaries Index (^AORD) ) DAILY closing index from the same site. Step 3 For the one-year data series for year 2010: 3a) Calculate DAILY security return Ri. Ri = ln (Price t + 1 / Price t ) 3b) Calculate DAILY market return Rm. Rm = ln (AORD t + 1 /AORD t) 3c) Calculate MONTHLY Return, Security Risk and Market Risk 3d) Compile THREE Tables similar to that shown in the "Combined" sheet in the Excel example: Table 1: Monthly Return, Monthly Beta, Monthly Beta (Lag 1) Table 2: Monthly Return, Monthly Security Risk, Monthly Security Risk (Lag 1) Table 3: Monthly Return, Monthly Market Risk, Monthly Market Risk (Lag 1) Step 4 For the one-year data series for year 2010 (refer to the "2010" sheet in the Excel example. The graphs must use the right scales for comparison. Use Primary and Secondary axes if necessary) Plot SIX time series graphs of: Graph 1: Return and Monthly Beta vs time Graph 2: Return and Monthly Beta (Lag 1) vs time Graph 3: Return and Monthly Security Risk vs time Graph 4: Return and Monthly Security Risk (Lag 1) vs time Graph 5: Return and Monthly Market Risk vs time Graph 6: Return and Monthly Market Risk (Lag 1) vs time Step 5 For the one-year data series for year 2010: Carry out SIX Regressions of: Regression 1: Return (Y-axis) vs Monthly Beta Regression 2: Return (Y-axis) vs Monthly Beta (Lag 1) Regression 3: Return (Y-axis) vs Monthly Security Risk Regression 4: Return (Y-axis) vs Monthly Security Risk (Lag 1) Regression 5: Return (Y-axis) vs Monthly Market Risk Regression 6: Return (Y-axis) vs Monthly Market Risk (Lag 1) Step 6 Compile Table 4 - Summary of Regression (2020), showing R-squared, p-value, Slope and signs of the Relationship Step 7 Summarise Steps 4, 5 and 6 in ONE worksheet similar to the "2020" sheet in the Excel example. Step 8 From Table 4, select the best regression in terms of R-Squared. State the name of the Y and X variables, and the R-Squared value. R-squared is between 0 and 100%, the higher the better. Step 9 From Table 4, select the best regression in terms of p-value. State the name of the Y and X variables, and the p-value. Note that p-value is compared to the level of significance . Test at = 5%, where the confidence level is 95%. e.g. a) if > p, the regression model is significant at = 5%. b) if < p, the regression model is insignificant at = 5%. Step 10 Repeat Steps 3 to 9 for every year from 2011 to 2019. Create a NEW worksheet for each year. A total of 10 years. Step 11 Compile Table 5 - Choosing the best model as per the Excel example in the worksheet "Summary 2010 to 2019", and draw a conclusion of the BEST and the three WORST models. Part 1B - Return and Financial Ratio (30%) Step 12 For the one-year data series for year 2010: Calculate YEARLY security return Ri. Ri = ln (Price last trading day/ Price first trading day ) Step 13 Repeat Step 12 for each of the years 2011 to 2019. Step 14 Go to the website of your assigned company, and find the balance sheet and profit and loss statements from 2010 to 2019. Step 15 Calculate the following ratios for each of the years from 2010 to 2019 (you must show details of your calculation in Excel): 1. P E = Price per share Earnings per share 2. Debt Equity = Total Debt (BV) Total Equity (BV) 3. Times Interest Earned (TIE) = EBIT Interest 4. Market Value/Book Value = Market Value per share Book Value per share (Source: Chapter 3 Essentials of Corporate Finance 5e by Ross, Trayler, Koh, Hambusch, Westerfield and Jordan, 2019) Step 16 Present your results in Table 6 as follows: Year Return P/E (Lag 1) D/E (Lag 1) TIE (Lag 1) MV/BV (Lag 1) 2010 2011 Ret2011 P/E2010 D/E2010 TIE2010 (MV/BV)2010 2012 Similarly from 2012 to 2019 Similarly from 2012 to 2019 Similarly from 2012 to 2019 Similarly from 2012 to 2019 Similarly from 2012 to 2019 All the way to... 2018 2019 Ret2019 P/E2018 D/E2018 TIE2018 (MV/BV)2018 Step 17 Regress Return against P/E (Lag 1), where Return is the Y-variable, and P/E (Lag 1) the X-variable. Step 18 Repeat Step 17 for D/E (Lag 1), TIE (Lag 1) and MV/BV (Lag 1), one ratio at a time. Step 19 Draw a time series graph, where Return and P/E (Lag 1) are the Y values, and year the X values. Similarly for the other three ratios and name the graphs from Graph 7 to 10. Step 20 Find "Correlation" under Data/Data Analysis in Excel, and compute the correlation matrix of Return and the four ratios. Part 2 (40%) In a Business Report format, answer the following questions. 1. What is the meaning of Beta? What type of beta have you found in Step 3- asset beta, equity beta or debt beta? Explain. (2%) 2. What is the meaning of Security Risk and Market Risk? (2%) 3. a) Observe the time series graphs 1 to 6 for each year, and comment on the relationship between the two Y-variables over time. (3%) b) Is the relationship consistent with the sign of the slope in Table 4? (1%) c) Describe the relationship using the correct names of the Y-variables and Xvariables. (2%) d) Based on your study of Corporate Finance, is the relationship you have observed consistent with the theory? Explain why and why not. (4%) 4. What is the purpose of the Simple Linear Regression? State the regression equation (in Y and X) and the meaning of all the components in the equation. (3%) 5. Explain the meaning of R-Squared for the regression equation. (2%) 6. From each of the regressions in step 17 and 18, record R-Squared, the p-value and the slope in Table 7: (3%) Regression R-Squared p -value Slope Return vs P/E (Lag 1) Return vs D/E (Lag 1) Return vs TIE (Lag 1) Return vs MV/BV (Lag 1) 7. a) Observe the time series graphs 7 to 10, and comment on the relationship between the two Y-variables over time. (2%) b) Is the relationship consistent with the sign of the slope in Table 7? (1%) c) Using R-squared, p-value and the slope in Table7, explain the properties of the relationship using the correct names of the Y-variable and X-variable. (5%) 8. Based on the results in Question 6 and 7, explain whether the security return is related to each of the ratios. (2%) 9. Explain clearly what the four financial ratios in Step 15 are telling you about your company. (You will earn no marks if you simply describe the formula.) (3%) 10. Comment on the correlations in the correlation matrix found in Step 20, include the correlation matrix here as Table 8. (5%)