UG Coursework 2014-2015
MODULE: NBS- 3F3Y COURSEWORK (Assignment 2)
Objective: This assignment asks you to prepare a professional financial report using data from Yahoo! Finance and the models covered in the module.
Question: You work for an investment bank and you are asked to prepare a financial report. In order to prepare your report, you need to download the weekly dividend-adjusted closing prices for: i) S&P500 (GSPC), ii) Unilever (UL), and iii) another firm (see “Firm-Student ID.xlsx” for your firm) for the period 1/1/2012-1/1/2015. Using this data, you should then compute the corresponding weekly returns. In the report, you should include the following:
Report the mean, the median, the range, the variance and the standard deviation of the weekly returns for each of the three assets. Explain both the statistical and financial meaning of these statistics. Explain which one of these three assets you would recommend to a potential investor based on their risk-adjusted returns. (10 marks)
Suppose you build a portfolio that allocates 60% of your total funds to Unilever and the remaining 40% to the stock of your firm. Discuss the relationship between the weekly returns of the two companies using the appropriate XY plot. Calculate the covariance and correlation between the weekly returns of your firm and Unilever. Also compute the average return and the standard deviation of your portfolio. (10 marks)
The Capital Asset Pricing Model
Create an XY plot of the excess returns of the S&P500 (X-axis) vs. the excess returns of your firm (Y-axis), assuming a constant weekly risk-free rate of 0.05%. Fit the Capital Asset Pricing Model (CAPM) regression line, and display the regression equation and the R-squared on the plot. Discuss the explanatory power of the model. (10 marks)
Estimate the CAPM regression using the “Data Analysis Tool” of Excel. Discuss the interpretation of the two regression coefficients (α and β) both from a statistical and from a financial viewpoint. Examine the significance of the above regression coefficients at the 5% and 10% significance levels.
Also report the 90% and 95% confidence intervals for the two coefficients and discuss their meaning. (10 marks)
Discuss whether the stock of your firm offers significant abnormal returns using Jensen’s alpha. Using the Sharpe ratio and the Treynor measure and assuming the risk-free rate given above, which one of the following two stocks would you recommend to potential investors, Unilever or the stock of your firm? Also, discuss the following: i) under what circumstances the Treynor measure is preferred over the Sharpe ratio and ii) the relationship between Sharpe ratio and the security market line. (15 marks)
Using the lagged regression model, discussed in Lecture 17, examine if the returns for the previous four weeks on Unilever’s stock can predict the current week’s return on your firm. Plot the actual and predicted values of your firm’s returns in the same graph. Discuss what the distance between the two lines represents. (15 marks)
Suppose your company decides to issue new bonds. Compute the fair value of your firm’s bond knowing that it pays semi-annual coupons with an annual coupon rate of 7%, it has a par value of $1,000 and a maturity of 12 years. The required return for the bond is 5%. Using the appropriate graph show how the value of this bond changes as a function of the “yield to maturity”. Based on the above graph, discuss how the bond value is affected by changes in the interest rates. (15 marks)
Consider the bond of your company as described above. Discuss the following: i) Calculate the percentage of the bond price that arises from coupon payments and the corresponding percentage of the price that comes from the face value, ii) If the bond is callable at $1,100 after the first 5 years of its life compute the yield-to-call. (15 marks)
The file “Firm-Student ID.xlsx” provided in the Assignment 2 folder at the Blackboard site of the module assigns a real U.S. firm to each student id. Therefore, you should prepare the report for the firm assigned to you.
To find the data you need, you should use Yahoo! Finance. A link to each firm’s webpage is provided in the above file as well.
The question is strongly related to the material covered in Lectures 10-18 and Labs 5-7 of the module and the associated chapters from the textbooks.
You should prepare your report in Microsoft Word or in another word processing software.
In each section of the report, you should also include details about the data you used and the formulas you applied for your computations. However, try to be brief and clear in your answers.
The question should be answered using Microsoft Excel in a single workbook with multiple worksheets. In the first worksheet, you should include all the data you got from Yahoo! Finance.
You should submit a hard copy of your report to the HUB including a hard copy of your Excel workbook as an appendix to your report.
30% of the total mark will be subtracted from submitted assignments that do not include a printed copy of the Excel workbook.
You should present your formulas and models in Word and Excel in a helpful and clear way. You should also use an appropriate format for the Excel cells. All graphs should be included in your report as well (Word file).
More details about coursework submission can be found at http://www.uea.ac.uk/calendar/section3/regs(gen)/submission-of-work-for-assessment-(taught-programmes)
Any questions related to the coursework should be submitted to the “Assignment 2 Questions” forum within the Q&A forum on the Blackboard Site of the module. The link to the Q&A forum is below the link to Module Documents. To ask a question, click on Create a New thread and post your question. You should also click on “Subscribe” to receive an email when a new question is posted. You should visit this forum regularly.
I will also be available in my office (TPSC 0.13) on Friday 17/4 between 12.30 and 16.30 for any coursework related issues.
Submission deadline: 23/04/2015 at 15:00 (3 PM)
Word limit: The report should not exceed 2,500 words. This word limit excludes the Appendix with the Excel workbook.
UG Coursework Assessment Criteria
In preparing your coursework, you should be aware that you will be assessed by reference to the extent to which your answers meet the following criteria:
Identification and application of knowledge to the question asked
Evidence of reading and research
Capacity to find data from online resources
Ability to construct financial models related to the question asked
Ability to correctly implement these models using spreadsheet software
Demonstration of ability to structure and present work in a clear and coherent way
Clear and accurate expression
Please preserve anonymity by using your university number, not your name.
This coursework counts for 50% of the assessment in this module.
You should be aware of the standards required for the award of marks for coursework. These are available at
Your coursework should clearly distinguish between your original words and ideas, and those of others. When referring to the work of others, from books, journals or any other source (including the internet), it is essential that you make this clear by acknowledging your source and referencing correctly. Failure to reference correctly will lose you marks and may constitute plagiarism or collusion. Unless specified otherwise by the module organiser, Norwich Business School uses the Harvard system of referencing.
University Policy on Plagiarism and Collusion
It is important that all students familiarise themselves with the rules and regulations regarding plagiarism and collusion. Understanding these rules will help you to avoid plagiarism, and to maintain the quality of your academic work.
You should ensure that you are familiar and up to date with the Universities rule on Plagiarism and Collusion, available at
You should also make sure that you are familiar with the Business School’s policy on Harvard referencing which is available in your student handbooks.
Individual study skills support and advice on referencing is also provided by the Learning Enhancement Service at the Dean of Students Office.
Where work has been submitted in word-processed format, you may be requested to provide a digital copy of your coursework (after an initial examination of the hard copy by the coursework assessors) for use with plagiarism detection software. Instances where plagiarism is suspected will be investigated by the NBS Plagiarism Officer. Students who are found to have plagiarised will be penalised.
In proven cases offenders will be punished and the punishment may extend to degree failure, temporary suspension or expulsion from further study if the case comes before a Discipline Committee of the University.Work for assessment should be submitted to the relevant Learning and Teaching Service Hub. Please ensure that you know the specific location of the submission box in advance of your first deadline.
Before you submit any coursework you should make sure that you are clear and up to date in your knowledge of university policy and practice.
The University’s policy on Submission of Work for Assessment (Taught Programmes): Submission of Anonymised Work for Assessment, Word Limits and Penalties, Extensions and Penalties for Unauthorised Late Submission, Provisional Marks and Feedback, and Retention of Coursework is published in the Calendar, and is available at:
A penalty will be imposed for exceeding the word limit, which will be clearly stated in the assignment. Students must include their word count on the coursework coversheet when they submit their work. The word count for coursework, written assignments, projects, reports and dissertations shall include: Footnotes and endnotes (irrespective of whether they have been used for the purpose of referencing or not, references (in the main text), tables and illustrations and if applicable the abstract, title page and contents page. Any appendicised material and the bibliography or reference list shall be excluded from the word count.
Word Count Penalties
Up to 10% over word limit
10% or more over the word limit
Deduction of 10 marks off original mark
Failure to provide an electronic copy when requested
Mark capped to the pass mark
Intentional misrepresentation of the word count on the coversheet
Mark capped to the pass mark
Penalties for late submission
The deadline for submission of coursework is 15:00 (3pm) on the submission/due day. This is for both electronic submission and paper copy submissions.
If you submit coursework after the published deadline, without an approved extension, the following penalties will apply:
After 15:00 on the due date and before 15:00 on the day following the due date
After 15:00 on the second day after the due date and before 15:00 on the third day after the due date
After 15:00 on the third day after the due date and before 15:00 on the 20th day after the due date.
All the marks the work merits if submitted on time (i.e. no marks awarded)
After 20 working days
Work will not be marked and a mark of zero will be entered
Saturdays and Sundays will not be taken into account for the purposes of calculation of marks deducted.
For more information on coursework submission, see: