Please help me answer question 4, 5 & 6.

Background Information This assessment task requires your team to develop a written presentation based on information that your lecturer has collected for this assignment. The information is considered rather rudimentary, but the difficulty of the task is in the analysis and clarity of your explanations of the results. Each team is required to review the information provided and break it down into its simplest components and figure out a way to show how risk is treated in the financial markets and when making investment decisions. The presentation should be as complex as the team feels is necessary There are two ways to understand risk: risk could be measured in conjunction with an investor's portfolio, or risk could be measured as the potential for variation of returns from a single investment. It is stressed that each team should work out these two different meanings of risk in the presentation and make sense of each. In addition, it is concerned that the techniques used in finance are complicated and tedious, and this presentation allows you to discuss and demonstrate how these techniques can make sense when considered carefully. The Numbers You are provided several sets of numbers for the team to use in the presentation (Exhibit 1). The first set is 36 observations of historical monthly share prices for two different stocks and the historical monthly share market index over the same period. These representative numbers have been extracted from the Internet. It also indicates to each team that the proper measure of risk for a portfolio would have to be calculated using this kind of data series. Exhibit 1 - Monthly Values for the Market Index and the Two Traded Shares. Month Market index NCM Ltd ($) CCP Ltd ($) (points) 1 5675 21.53 17.52 2. 5761 22.14 16.46 3 5903 23.27 17.29 4 5948 21.16 18.20 5 5761 21.14 17.92 6 5764 20.20 17.71 7 5774 22.85 16.96 8 5776 21.02 17.95 9 5745 22.41 19.22 10 5976 23.92 20.06 11 6024 22.82 22.60 12 6167 23.67 22.31 13 6147 21.25 21.15 14 6117 19.56 21.14 15 5869 21.15 19.29 16 6072 20.76 17.96 17 6124 21.80 18.47 18 6290 23.55 18.07 19 6366 24.15 20.58 20 6428 24.57 23.08 21 6325 20.61 22.26 22 5913 20.75 19.05 23 5749 21.80 19.24 24 5709 24.45 18.76 25 5937 24.32 21.98 26 6252 27.58 21.68 27 6262 25.01 22.19 28 6418 28.49 23.85 29 6492 31.95 24.90 30 6699 35.58 26.52 31 6477 37.00 25.15 32 6605 34.75 28.81 33 6595 31.30 30.14 34 6744 30.76 31.38 35 6700 30.25 33.07 36 6789 29.53 30.84 Report Your team is also required to produce an approximately 800-900-word (4-5 pages of report excluding references & appendix) report that comments on the results calculated above. The report will need to refer to each statistic calculated and interpret (not simply summarise your results) the result and make comparisons amongst the market index and the two companies. Your team is required to specifically address what each statistic is measuring and its implications from a risk and return viewpoint. You will also need to focus on the impact of creating a portfolio of two shares and the implications for risk reduction (You will need to provide some quantitative evidence of risk reduction). Furthermore, the Beta of each company needs to be assessed and interpreted in the context of asset pricing. That is, what is the implication for risk and required return and what impact will this have on the asset's price? To answer this assessment, you must thoroughly understand Lecture 4 (Risk & Return) 1. Using the information given in Exhibit 1, calculate the historical returns for each company and the market index. With the use of the excel functions calculate the average monthly return and standard deviation of returns for each company and the market index. What do your results suggest about the relationship between risk and return? 2. Using your answers to Question 1, above, and if investors can only invest in one of the two alternative shares in Exhibit 1, use the average return and standard deviation to determine which share would be the most appropriate for a risk-averse investor. Provide numerical justification for your selection based on the coefficient of variation. 3. Calculate the correlation coefficients between both shares. Provide an explanation of your results and the implications for diversification 4. Determine the standard deviation of a two-asset portfolios comprised of NCM Ltd and CCP Ltd; Assume equal weightings of each share within the portfolio. Interpret your results and comment and illustrate the impact on risk when combining shares into a portfolio will need to quantify (calculate) the level of risk reduction resulting from the creation of the portfolio. 5. Determine the systematic risk (Beta) for both shares. Interpret your answers and make specific reference to how each asset's beta will affect the required rate of return and each assets price. The use of excel statistical slope functions should be used to calculate Beta. What limitations are associated with the use of beta for asset pricing? 6. Assuming a risk-free rate of 3% and market risk premium of 7%, calculate the required return for both shares. If the future growth in dividends is expected to continuously average 5% for NCM Ltd and 1.5% for CCP Ltd, what value would you place on each share (use the dividend growth model). What concerns do you have, if any, regarding your valuations? NOTE: The last dividend paid for NCM Ltd was $1.45 and $0.30 by CCP Ltd. Background Information This assessment task requires your team to develop a written presentation based on information that your lecturer has collected for this assignment. The information is considered rather rudimentary, but the difficulty of the task is in the analysis and clarity of your explanations of the results. Each team is required to review the information provided and break it down into its simplest components and figure out a way to show how risk is treated in the financial markets and when making investment decisions. The presentation should be as complex as the team feels is necessary There are two ways to understand risk: risk could be measured in conjunction with an investor's portfolio, or risk could be measured as the potential for variation of returns from a single investment. It is stressed that each team should work out these two different meanings of risk in the presentation and make sense of each. In addition, it is concerned that the techniques used in finance are complicated and tedious, and this presentation allows you to discuss and demonstrate how these techniques can make sense when considered carefully. The Numbers You are provided several sets of numbers for the team to use in the presentation (Exhibit 1). The first set is 36 observations of historical monthly share prices for two different stocks and the historical monthly share market index over the same period. These representative numbers have been extracted from the Internet. It also indicates to each team that the proper measure of risk for a portfolio would have to be calculated using this kind of data series. Exhibit 1 - Monthly Values for the Market Index and the Two Traded Shares. Month Market index NCM Ltd ($) CCP Ltd ($) (points) 1 5675 21.53 17.52 2. 5761 22.14 16.46 3 5903 23.27 17.29 4 5948 21.16 18.20 5 5761 21.14 17.92 6 5764 20.20 17.71 7 5774 22.85 16.96 8 5776 21.02 17.95 9 5745 22.41 19.22 10 5976 23.92 20.06 11 6024 22.82 22.60 12 6167 23.67 22.31 13 6147 21.25 21.15 14 6117 19.56 21.14 15 5869 21.15 19.29 16 6072 20.76 17.96 17 6124 21.80 18.47 18 6290 23.55 18.07 19 6366 24.15 20.58 20 6428 24.57 23.08 21 6325 20.61 22.26 22 5913 20.75 19.05 23 5749 21.80 19.24 24 5709 24.45 18.76 25 5937 24.32 21.98 26 6252 27.58 21.68 27 6262 25.01 22.19 28 6418 28.49 23.85 29 6492 31.95 24.90 30 6699 35.58 26.52 31 6477 37.00 25.15 32 6605 34.75 28.81 33 6595 31.30 30.14 34 6744 30.76 31.38 35 6700 30.25 33.07 36 6789 29.53 30.84 Report Your team is also required to produce an approximately 800-900-word (4-5 pages of report excluding references & appendix) report that comments on the results calculated above. The report will need to refer to each statistic calculated and interpret (not simply summarise your results) the result and make comparisons amongst the market index and the two companies. Your team is required to specifically address what each statistic is measuring and its implications from a risk and return viewpoint. You will also need to focus on the impact of creating a portfolio of two shares and the implications for risk reduction (You will need to provide some quantitative evidence of risk reduction). Furthermore, the Beta of each company needs to be assessed and interpreted in the context of asset pricing. That is, what is the implication for risk and required return and what impact will this have on the asset's price? To answer this assessment, you must thoroughly understand Lecture 4 (Risk & Return) 1. Using the information given in Exhibit 1, calculate the historical returns for each company and the market index. With the use of the excel functions calculate the average monthly return and standard deviation of returns for each company and the market index. What do your results suggest about the relationship between risk and return? 2. Using your answers to Question 1, above, and if investors can only invest in one of the two alternative shares in Exhibit 1, use the average return and standard deviation to determine which share would be the most appropriate for a risk-averse investor. Provide numerical justification for your selection based on the coefficient of variation. 3. Calculate the correlation coefficients between both shares. Provide an explanation of your results and the implications for diversification 4. Determine the standard deviation of a two-asset portfolios comprised of NCM Ltd and CCP Ltd; Assume equal weightings of each share within the portfolio. Interpret your results and comment and illustrate the impact on risk when combining shares into a portfolio will need to quantify (calculate) the level of risk reduction resulting from the creation of the portfolio. 5. Determine the systematic risk (Beta) for both shares. Interpret your answers and make specific reference to how each asset's beta will affect the required rate of return and each assets price. The use of excel statistical slope functions should be used to calculate Beta. What limitations are associated with the use of beta for asset pricing? 6. Assuming a risk-free rate of 3% and market risk premium of 7%, calculate the required return for both shares. If the future growth in dividends is expected to continuously average 5% for NCM Ltd and 1.5% for CCP Ltd, what value would you place on each share (use the dividend growth model). What concerns do you have, if any, regarding your valuations? NOTE: The last dividend paid for NCM Ltd was $1.45 and $0.30 by CCP Ltd