NBS- 6907B FINANCIAL MODELLING 2021-2022
Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit
FINANCIAL MODELLING (NBS- 6907B)
2021-2022
Objective: This assignment asks you to prepare an analytical report for a real U.S. firm using data from Yahoo! Finance and the financial models presented in the module. The main purpose of the assignment is to apply the knowledge and skills obtained in the module to a real-world financial modelling problem.
Scenario: Suppose you work as a quantitative analyst for a large company, and you have been asked to prepare an analytical report. To prepare the report, you need to collect data on monthly dividend-adjusted closing prices from January 2000 to December 2021 and calculate the returns for the firm assigned to you. You can find your firm in the “Assigned Firms CW.xlsx” file under the “Summative Assessment” section on the Blackboard page of the module. You should perform the analysis under the assumption that the risk-free rate of return is 1.6%. In addition, you will have to use monthly data for the return on the S&P 500 index for the same period as above. You can find the data for this variable in the Excel file ‘SP500.xslx’ on Blackboard (under the “Summative Assessment” section). You should include the following items in your report:
I) Financial News
Mention two recent financial news relevant to your company and then discuss how this news is expected to affect its shareholders. (Hints: Avoid lengthy discussions and be to the point; you can use Yahoo! Finance (https://finance.yahoo.com/) or any other news source, such as Reuters (https://www.reuters.com/), Wall Street
Journal (https://www.wsj.com/), etc; please mention this source in your report.)
(10 marks) [Total: 10 marks]
II) Regression Analysis
Perform the following tasks using the “Data Analysis Tool” in Microsoft Excel:
A. Using regression analysis and the return on the S&P 500 index as a proxy for the return on the overall market, estimate the stock’s beta. Write down the regression model that you must estimate using algebraic notation. Clearly specify the assumptions that we usually make about the unobservable error terms in the classical linear regression model . Why are these assumptions made?
(10 marks)
B. Report and discuss the results of your regression analysis. Use a 5% level of significance to test the hypothesis that the stock whose return you analyse is market neutral, that is, the true value of the stock’s beta is zero . (Hint: Specify clearly the null (0) and the alternative (1) hypothesis using the notation that you introduced in the answer to question I.A).
(5 marks) [Total: 15 marks]
III) Cost of Capital and Firm Valuation
For the calculations in this section, use data from the annual financial statements for 2021 available on Yahoo! Finance.
A. Compute the cost of equity of your firm using the Capital Asset Pricing Model, assuming a market risk premium of 5.6% (Hint: Use the estimate of beta that you obtained in the answer to question I.A).
(3 marks)
B. Compute the cost of debt of your firm. Use Table 1 provided in the appendix at the end of this document to identify the default spread of your firm.
(5 marks)
C. Compute the Weighted Average Cost of Capital (WACC) of your firm. Use a tax rate of 28%. (Hint: To calculate the market value of equity, use the dividend-adjusted closing price from Yahoo! Finance for 1st of December
2021 as the current stock price.)
(7 marks)
D. Calculate the Free Cash Flow to Firm (FCF) of your company. Table 2 in the appendix at the end of this document, contains the analysts’ forecasts of your firm’s FCF growth for the next 5 years. Based on this growth rate project the FCFs for a period of 5 years. Then, assuming a long-term FCF growth rate (after year 5) equal to the risk-free rate minus 0.2%, estimate the overall firm value as well as the value per share. What would you recommend to a potential investor?
(10 marks)
E. Discuss the relationship between the value of the firm and the WACC. Provide the relevant plot.
(5 marks) [Total: 30 marks]
IV) Optimal Capital Structure
Calculate the unlevered beta of your firm. Determine the optimal capital structure
for the firm based on the cost of capital approach under the assumption that the cost of debt changes with the Debt/Capital ratio as shown in the table below (Use the information on the tax rate and market premium provided in Section III above).
Debt/Capital
ratio
0% 0.00%
10% 1.00%
20% 2.00%
30% 4.00%
40% 8.00%
50% 13.00%
60% 18.00%
70% 24.00%
A. What is the value of the WACC that maximizes the value of the firm? Show this on a plot of the WACC (y-axis) vs. the Debt/Capital ratio (x-axis).
(15 marks)
B. Briefly discuss the main benefits of debt financing .
(5 marks) [Total: 20 marks]
V) Forecasting
Suppose you want to build a model to predict the returns of your firm. To do this, you need to perform the following tasks:
A. Apply an exponential smoothing to the series of monthly returns of your firm’s stock. Use a smoothing constant of 0.7. Then, change the smoothing constant to 0.3. Plot the two smoothed series on the same graph. Why are they different?
(10 marks)
B. Estimate a regression to examine if the monthly returns of your firm can be predicted by the current and 1 month lagged returns of the S&P 500 index . Discuss the overall predictive performance of the model.
(5 marks)
C. Calculate the mean squared error of the above regression model and the two exponential smoothing models. Discuss which model has the best predictive performance and why.
(10 marks) [Total: 25 marks]
Important details:
➢ The file “Assigned Firms CW.xls” provided on the Blackboard site of the module contains information about the firm that has been assigned to each student ID.
You should prepare the report for the firm that has been assigned to you, otherwise your work may not be marked.
➢ To find the data you need, you should use Yahoo! Finance. A link to the firm’s webpage on Yahoo! Finance is provided in the above file.
➢ The questions are related to the material covered in the lectures and seminars.
➢ 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 questions should be answered using Microsoft Excel in a single workbook with multiple worksheets. In the workbook, you should include all the data you obtained from Yahoo! Finance.
➢ Your submission should include a copy (screenshots) of your Excel workbook in an appendix to your report. These screenshots should not include any time series of prices or returns. Instead, what I would like to see is the structure of the model that you built. 25% of the coursework mark will be subtracted from submitted assignments that do not include a copy of the Excel workbook.
➢ You should present your formulas and models in Word and Excel in a clear manner. You should also use an appropriate format for the Excel cells. All graphs should be included in your report (Word file). For more details on successful writing please refer to:
https://my.uea.ac.uk/divisions/student-and-academic-services/student-
services/learning-enhancement/help-with-your-studies/resources/academic-
➢ All citations to articles and textbooks should be explicitly mentioned in the main text. Use only reliable sources and make sure that you refer to at least 4 academic references.
➢ More details about coursework submission can be found at
https://my.uea.ac.uk/divisions/student-and-academic-services/learning-and-
teaching/students/academic-cycle/coursework/submission-guide
➢ Any questions related to the coursework should be submitted to the “Assessment Questions” forum on the Blackboard page of the module (in the “Summative Assessment” folder). 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. It is a good practice to visit this forum regularly.
Submission deadline: Wednesday 01/06/2022 at 15:00 (3 PM)
Word limit: The report should not exceed 3,000 words. This limit excludes the Appendix with the Excel workbook and the bibliography.
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:
Criterion |
Weight |
Identification and application of knowledge to the question asked |
20% |
Evidence of reading and research |
10% |
Capacity to find data from online resources |
10% |
Ability to construct financial models related to the question asked |
15% |
Ability to correctly implement these models using spreadsheet software |
15% |
Demonstration of ability to structure and present work in a clear and coherent way |
20% |
Clear and accurate expression |
10% |
• Please preserve anonymity by using your university number, not your name.
• This coursework counts for 100% 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:
https://my.uea.ac.uk/documents/20142/490081/Senate+Scale+Undergraduat
Referencing
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. To read University policy concerning plagiarism, please visit the
https://my.uea.ac.uk/divisions/student-and-academic-services/learning-and-
teaching/students/academic-cycle/regulations-and-discipline/plagiarism-
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.
General Advice
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 available at:
https://www.uea.ac.uk/about/university-information/university-
governance/academic-calendar/section-3/general-regulations/submission-of-
work-for-assessment-taught-programmes-
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 material in the appendix and the bibliography (or reference list) shall be excluded from the word count.
Word Count Penalties
Up to 10% over word limit |
No Penalty |
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 |
2022-05-12