Write an assignment in Excel Module Title: Investment
Course :
Module Title : Investment
Lecturer
Assessment : Individual Assignment
Due Date :
Word Length : 1,500 words (+/- 10%)
Weighting within Module :
This is an individual assignment which requires you to assess the investment returns and risk of a selected company against a competitor in the industry for the specified investment time period. The nature of the relationship between the two investments needs to be evaluated to determine whether there is any diversification benefit from investing in both investments instead of one.
Instructions:
1. Select a publicly listed company of your choice. For comparison, identify a competitor and a relevant market index (for example, the S&P500 Index, Straits Times Index (STI), Financial Times Stock Exchange 100 Index (FTSE).
2. Obtain the following data for (i) selected company (ii) competitor (iii) market index:
(a) Monthly closing share price from 1 July 2019 30 Jun 2021 (inclusive of both days).
(b) Dividends paid for 1 July 2019 30 Jun 2021.
You can obtain the data from websites such as https://finance.yahoo.com/ or http://www.nasdaq.com/. Other credible websites can also be used including the companys website or other financial data provider.
3. Using the data gathered in a spreadsheet in Part 2, calculate:
(a) Monthly arithmetic average rate of return (%) for each month (Note: Returns comprise of capital gains and dividends).
(b) Monthly geometric average rate of return (%) for the entire period from 1 Jul 2019 30 Jun 2021.
4. Based on the computation in Part 3:
(a) Present your answer in appropriate tables and/or graphs for (i) selected company (ii) competitor (iii) market index.
(b) Analyse the investment performance of your selected company for the time period being reviewed. Consider to compare with the competitor and market.
5. Determine the investment risk during the period from 1 Jul 2019 30 Jun 2021.
(a) Compute the standard deviation of the respective returns of the 2 companies and market index.
(b) Obtain the beta value from stock data providers (such as Yahoo Finance, Nasdaq or other sources) for (i) selected company (ii) competitor.
6. Based on Part 5:
(a) Present your answer in a table or graph for (i) selected company (ii) competitor (iii) market.
(b) Analyse the investment risk for the time period being reviewed. Consider to compare with the competitor and market.
7. Using Excel, compute the correlation between the selected companys returns and the returns for the competitor. Comment on your findings.
8. Suppose that two different investment portfolio is setup:
Investment Portfolio A: 75% investment in the selected company and 25% investment in the market index.
Investment Portfolio B: 50% investment in the selected company and 50% investment in the market index.
Investment Portfolio C: 20% investment in the selected company and 80% investment in the market index.
Required:
(a) Compute:
(i) Portfolio returns for Investment Portfolio A, Investment Portfolio B and Investment Portfolio C separately, for the time period under review.
(ii) Portfolio risk for Investment Portfolio A, Investment Portfolio B and Investment Portfolio C separately, for the time period under review. Provide your workings to the computations.
(b) Comment on which investment fund performed better and the diversification benefit obtained from the portfolio.
Additional information:
· The assignment paper should contain the following sections:
· Cover page
· Table of contents
· Introduction:
Objective of the paper and brief introduction of the company (about 250 words)
· Content:
Part 1 8: Presentation of the data in tables and graphs and computations with your analysis and comments (about 950 words)
· Conclusion (about 300 words)
· References
· All pages must be clearly numbered and the report must be printed on A4-sized paper (portrait orientation) with one-inch margin and font size 12 using Times New Roman.
· References should follow the Harvard Referencing style.
· This assignment requires you to apply your knowledge and understanding about the investment returns and risks of the company you selected and compare to the competitor and market index. The data and figures computed can aid in justifying your analysis and findings on the investment performance of the company.
· The word count must be displayed on the Cover page. The word count stipulated excludes any references, charts, tables or figures.
· This assignment should be based on public information only (from the companys website or any published information including the annual report).
· Make use of your own comments/conclusions as much as possible. All information extracted from the public domain must be properly acknowledged via proper referencing.
Note:
Please note that MDIS policy on cheating collusion and plagiarism will be applied to this piece of work. MDIS requires that students do not submit work for assessment which contains someone elses work ideas, observations or conclusions without due acknowledgement.
~End~
Marking Scheme
Area
Marks
Introduction:
Clear concise introduction to the company selected, the industry, the market and the overall paper.
5
Content:
Part 1-4:
Accurate extraction of required data presented in a table.
Correct computation of the required measures
Clear, in-depth analysis of the investment performance of company against the competitor and against the market index.
15
20
Part 5-6:
Accurate computation and analysis of the standard deviation and beta for company compared against the competitor and the market index.
20
Part 7-8:
Illustration and comment on the correlation between the company and its competitor.
Accurate computation of the portfolio investment return and risk for portfolios.
Clear explanation of the diversification benefit obtained.
10
10
10
85
Conclusion:
Students should demonstrate ability to conclude about the overall performance of the selected firm.
5
Writing Style and Grammar:
Proper formatting and referencing.
Use of appropriate references to support analysis.
5
Total
100
Recent Comments