MN M024 Financial Modelling
MN-M024 – Financial Modelling
Individual Coursework Assignment
The coursework assignment for this module is an individual assignment worth 100% of the overall module mark.
Coursework Brief
This assignment consists of two parts. In Part A, you are required to build models in an Excel worksheet, whereas in Part B, you are required to write a report based on Part A to explain source of information, modelling method and calculation of results. The total word count of the report, excluding tables and references, should be no more than 3,750 words. A word count must be provided on the cover page.
Part A – Creating the Excel Workbook
A.1 – Financial Statement Analysis
You are an analyst at the corporate banking department of a consulting firm. You are required to build a valuation forecast model and arrive at a valuation calculation for one of the following four companies: Persimmon plc, BAE System plc, Tesco plc, NEXT plc.
The Data
Collect the most current financial data required from the company’s annual reports or alternative (reliable!) online sources.
Tasks
In an Excel worksheet, complete the following tasks:
a) Clearly indicate your assumptions of Capex for the fade period.
b) Complete an income statement forecasting model.
c) Complete a balance sheet forecasting model.
d) Complete a cash flow statement forecasting model.
e) Calculate key ratios, such as WACC.
f) Perform cash flow valuation.
g) Perform sensitivity analysis
A.2 – Modelling the Yield Curve
You are a corporate finance analyst in an investment bank. One of the bank’s clients is interested in raising $5m of debt financing through the issue of a bond. The bond would have a face value of $1000, a maturity of 15 years, and a coupon rate of 5.5%. The company has a credit rating of A. Your manager wants you to estimate the number of bonds that the company would have to issue to fulfil the financing requirement.
The Data
Relevant data is available on Canvas. The data file contains the adjusted close prices for the seven indices. Assume that the currency risk is perfectly hedged so you can ignore exchange rate fluctuations.
Tasks
In an Excel worksheet, complete the following tasks:
a) Use the return index data to compute monthly simple returns.
b) Use the return data to compute the covariance matrix for the seven markets.
c) A significant problem with applying portfolio optimisation in practice is that sample estimates of expected returns are very noisy, and hence not reliable. Using another approach, the fund company has estimated the following expected monthly returns for the G7 markets:
| US | UK | Japan | Germany | France | Canada | Italy | |
| Expected Return | 0.48 % | 0.39% | 0.55% | 0.51% | 0.43% | 0.45% | 0.42% |
Using the above information, compute the efficient set. Graphically estimate the efficient frontier of the seven equity markets both with and without a risk-free asset. Obtain the US risk-free rate from Yahoo! Finance for a three-month holding period.
Part B – Creating the Report
Write a report summarizing and discussing the computations and results from Parts A.1, A.2, and A.3. Provide a detailed description of how you carried out the tasks in Part A and comprehensively discuss the results you obtained. Your report should also include a critical analysis of the potential limitations of the financial statement forecasting model, the yield curve model, and the portfolio optimisation model as well as possible improvements. The report must include figures and tables from your Excel Workbook to support your analysis. References at the end should also be included.
Key Marking criteria will include:
• Initiative: originality, innovativeness of answer
• Assignment Structure: clarity of aims, objective, structure, and presentation
• Quality of Writing: readability and ability to convey the key message(s) concisely
• Suitability of Literature: use of suitable, high-quality sources to address the key aims
• Literature Analysis: Quality/level of analytical skill demonstrated
• Insightfulness of Analysis: interest and usefulness of findings, appropriate conclusions drawn
• Understanding: the assignment demonstrates students have understood key topics
• Overall Quality of Assignment