BEAM046J Financial Modelling

BEAM046J Financial Modelling
Individual Assignment
(70% of Total Module Assessment)
Released: 12:00 on 12 March 2021
Submission Deadline: 12:00 on 27 April 2021
(Late Submission Penalties Will Apply)
Maximum Length: 2,000 words (this is a strict limit)
(Excluding Title Page, Tables, Figures, Equations and
The assessment for the course is split into two parts: Part A Proposal (a group
assignment that counts for 30%) and Part B Financial Model (an individual
assignment that counts for 70%). Together, the two parts of the assignment
involve the design and implementation of a financial model. In Part A, you were
required to write a proposal for the financial model, while in Part B (i.e. this
assignment), you are required to implement a revised version the financial
The overall task (i.e. Part A and Part B of the assignment) is summarized as
follows: You are a fund manager in an investment company that operates a US
equity fund. You have decided to focus on the following stocks: Caterpillar (CAT),
Johnson and Johnson (JNJ), Walmart (WMT), Amgen (AMGN) and Proctor and
Gamble (PG). Analysts within your company have provided you with views about
the future performance of the five stocks. Using these views, you will construct
the optimal active portfolio of the five stocks assuming that the date is 31
December 2019. You will compare the performance of the optimal active
portfolio with that of the market-weighted portfolio of the five stocks and the
tangency portfolio of the five stocks over the following 12 months.
Requirements of the Part B Individual Assignment
For this assignment, you should undertake the following tasks:
Task 1: Construct the optimal active portfolio of the five stocks using the
following parameters:
View for CAT: +0.20% per month relative to market-implied expected return
View for WMT: +0.10% per month relative to market-implied expected return
Risk free rate: 0.00% per month
Expected market return: 0.45% per month
Estimation sample: monthly returns over five years
Evaluation sample: monthly returns over one year
Market capitalization weights: most recent values
Task 2: Undertake one or more sensitivity analyses to explore the robustness of
your results to the assumptions made.
You should write up the completed financial model in a report of no more than
2,000 words, excluding title page, tables and figures, formulas and references.
Note that this is a strict word limit.
In writing up your report, you should adhere to the following guidelines:
• Your report should be professionally presented. You could assume that it will
be read by the senior management of the company and so it needs to be neat,
properly structured and clearly and concisely written. This is an important
skill in practice.
• You should use the following structure for your report:
Title page: this should include your student number and word count
Executive summary (no more than 100 words): this should briefly
summarise the analysis and its main findings.
Method and data: this should concisely describe your calculations and the
data on which they are based
Results: this presents the results of your financial model, including the
sensitivity analysis. You should provide a critical discussion of your results.
Conclusion: this should summarise the main findings of the model, critically
evaluate any shortcomings of the data and method, and offer some
suggestions for future work
References: these should be alphabetically listed and properly formatted
(see below)
• You should NOT include appendices in your report
• You should make creative use of properly presented tables and charts. Tables
and charts should be accompanied by detailed explanatory notes. Look at any
paper in a good finance journal to see how to present a table of results (I have
put a sample of a Journal of Financial Economics paper in the Assessment
Information section on ELE, but feel free to look at others also).
• Do NOT include Excel screen prints. Also, you do NOT need to describe the
specific steps you took in Excel or the functions used.
• You should pay attention to the formatting of your reports, particular with
respect to line spacing, paragraphs and section titles
• You should use Equation Editor in Microsoft Word to format any equations
• You should define any variables that are used in equations
• Your report should also follow the School’s guidelines on referencing, citation
and avoiding plagiarism, which can be found at
• You do NOT need to submit the Excel workbook with your calculations, only
the report itself
• Penalties for late submission: You will be penalised if you submit your
assignment after the deadline. Details of the penalties for late submission can
be found here:

Buy plagiarism free, original and professional custom paper online now at a cheaper price. Submit your order proudly with us

Essay Hope