FIN205 Business Finance
Semester 2, 2018 Dr. D Chakrabarty
Maximum Marks: 30
Due date: Sunday, 23rd September 2018, 23:59 hours, Darwin time
- This assignment has two questions. Attempt both of them.
- Download and save the file 2018 FIN205.xlsx to answer this assignment. The data required for Questions 1 & 2 are provided on separate worksheets titled “Data Q1” and “Data Q2” respectively.
- Graphs and calculations should be done using EXCEL.
- Your assignment should be written in MSWord format. You have to copy and paste EXCEL outputs (for instance, graphs, tables etc.) into your main assignment which is the MSWord document. Any answer not in the main document will not be marked.
- The completed the assignment (MSWord document) needs to be submitted online via “Assignment Submission Point: Word Document Only”, under Assessment Task 2 folder. Make sure your name and student number are on every page of your submission.
- The EXCEL file used for your calculations must be submitted via “Excel Workbook Submission Point”, under the Assessment Task 2 folder.
- Explain your answers. Do not simply state them. State the formula used to derive an answer. Keep a copy of your assignment in case the original submission is lost due to any technical issues.
PDF or paper copies will not be accepted.
This is an individual assignment (not a group assignment). If portions of your assignment are copied or very close to copying, all parties will be penalized for copying. Copying would be considered plagiarism and CDU has strict policies. It is up to you to keep your assessment confidential.
Question 1 (Total Marks: 15)
This question has seven parts (a) – (g). Attempt all of them.
Refer to worksheet “Data Q1” in the file 2018 FIN205.xlsx to answer this question. This worksheet contains monthly data on yields of Australian Government Bonds and New South Wales (NSW) Treasury Bonds from January 2008 – December 2009. You may use the worksheet “Yield curves” as a template to answer part “a” of the question.
For questions a – e, consider Australian Government Bonds only.
- Pick the month you were born. Draw the yield curve for that month in 2008 and 2009. Also, draw the yield curve for the month falling exactly in the middle. (For instance, if you were born in April, draw the yield curves for April 2008, October 2008 and April 2009.)
- What should be the shape of the yield curve under normal circumstances? Why?
- Compare the shapes of the three yield curves that you have drawn. From the yield curves, can you infer anything about the market’s expectation about the future of Australian economy?
- What is the yield on 2-year bond in January 2008? Under the expectations theory, what interest rate do investors expect on a 1-year bond next year (i.e., January 2009)? Using the same method calculate the expected interest rates on 1-year bonds for all the months of 2009.
- How do the expected interest rates compare with the actual interest rates in 2009? Do your calculations validate/not validate the expectations theory? List some possible reasons why the expectations hypothesis may not hold.
For questions f – g, consider both Australian Government Bonds and NSW Treasury Bonds for the period January 2008 – December 2009.
- For 3, 5 and 10 year bonds what was the average spread between Australian Government Bonds and NSW Treasury bonds?
- Draw a graph plotting the spread between Australian Government Bonds and NSW Treasury Bonds. Comment on the pattern of the spread. Can you provide an explanation for this pattern?
Question 2 (Total Marks: 15)
This question has six parts (a) – (f). Attempt all of them.
Refer to worksheet “Data Q2” in the file 2018 FIN205.xlsx to answer this question. This worksheet contains data on the adjusted end of month share price from January 2015 – June 2018 of two companies: AMP Limited (AMP.AX) and Qantas Airways Limited (QAN.AX). You will also find the value of S&P/ASX200 market index over this period. The basic data is provided in columns A – D. You will need to manipulate the data to answer the questions below. Some excel formulas are provided in the worksheet.
Pick the month you were born. For you the relevant data will be month 2015 – June 2018.
- Suppose you are a financial analyst working for Star Bank. Use an appropriate graph to plot the AMP and Qantas share prices from month 2015 – June 2018. Provide a brief comment on the pattern you observe.
- Compute the monthly return on AMP, Qantas shares and the ASX200 index by using the following formula:
where, is the share price (or the value of ASX200 index) in time period . (Please note that you lose the first observation when calculating returns. For instance, if you were born in June, you will only be able to calculate the returns only from July 2015.)
Compute the mean, variance and standard deviation of all three returns. Based on your calculations what can you infer about the performance of AMP, Qantas and ASX200 index over the last three years? Provide a brief explanation.
- Calculate the beta of AMP and Qantas. For this you will have to first calculate the covariance of AMP and Qantas returns with the ASX index. Use the following formula in excel:
Beta’s can then be calculated by
- Briefly explain why AMP and Qantas have high/low values of beta.
- Assume the monthly risk-free rate to be 0.14%. According to the Capital Asset Pricing Model (CAPM) are the AMP and Qantas shares are underpriced? Explain.
- Write a short report for the management of your company evaluating the past performance and future prospects of AMP and Qantas shares.
Instructions for part (f): For this question you are expected to do some research on AMP and Qantas.
You may find the following websites useful in your research:
Australian Financial Review: https://www.afr.com/
Yahoo Finance: https://au.finance.yahoo.com/
Sydney Morning Herald: https://www.smh.com.au/
In addition to these websites you are free to use any other appropriate website/article as reference for your report. Make sure that you cite all your references.
You can find the financial statements of AMP and Qantas by doing a search on the Yahoo finance website. Also, search for news coverage and announcements related to these companies. Classify the financial situation and the news/announcements as “positive” or “negative”. Summarize how the share prices have reacted to “positive/negative” developments in the past. Use your findings to provide some predictions for future prospects of these companies.