BEA3026 Financial Modelling

Individual Assignment

(100% of Total Module Assessment)

Released: 12:00 noon on 1st November 2024

Submission Deadline: 12:00 noon on 9th December 2024

(Late Submission Penalties Will Apply)

Length: 3,500 words

with a permissible deviation of +/- 5%

Excluding Title Page, Tables, Figures, Equations and References,

BEA3026 Financial Modelling Individual Assignment

(100% of the course mark)

Overview

•    This is an assessed piece of work that will account for 100% of the total marks of this course.

•    The  assignment is in the form of a written report, to be submitted on the course ELE submission folder.

•    This is an individual assignment, length of 3,500 words with a permissible deviation of +/- 5%, excluding title page, tables, figures, equations, and references.

•    A word count must be placed on the front cover; 2 marks will be deducted if there is no word count, and 5 marks will be deducted if the examiner feels that there has been a deliberate attempt to give misleading information about the length of the assignment.

•    Penalties will apply if the coursework is submitted late. and details can be found here.

Assignment Task

You should choose ONE of the following two assignment tasks.

Assignment Task Option 1: Financial Statement Modelling

You area corporate finance analyst in the mergers and acquisitions department of an investment bank. The bank’s client is considering making abid for Caterpillar Inc (ticker CAT). You are tasked with providing a preliminary estimate of the fair market value of equity that will provide the starting point for the client’s bid for CAT. You should assume that the company will maintain its current capital structure and dividend pay-out ratio. You should assume that the company has an annual stock repurchase policy.

Note: a spreadsheet template with CAT's financial statement data for 2023 is available in the Assessment Information folder on ELE. The template also provides the values for the sales growth rate, long term growth rate, WACC, and stock repurchase policy to be used in the valuation.

Assignment Task Option 2: Risk Measurement

You area risk analyst for an investment bank and have been tasked with analysing the risk of an investment in options on Caterpillar Inc (ticker CAT) stock. Compute the VaR and CVaR for a range of confidence level from 50% to 99% for a position in a call option with a maturity that is as close as possible to three months and a strike price of 350. The VaR horizon is the maturity of the option. You should also estimate the corresponding VaR for a position in the underlying stock. You should incorporate the expected return of the stock in the simulation using the CAPM.

Note: A spreadsheet template containing two years of historical daily adjusted close price data  for CAT, five years of historical monthly adjusted close price data for both CAT and S&P500, and the market risk premium and risk free rate for use in the CAPM is available in the Assessment Information folder on ELE.

Assignment Structure Guide

You should use the following assignment structure for your report:

1. Title page: this should include the assignment title, your student number and the word count.

2.    Introduction: this should summarise the objective of the assignment and motivate it in the context of the existing literature (either from the course contents, or from your further reading), define the assignment objective, summarise the methodology and the key findings, and outline the structure of your report.

3.    Method: this should precisely and succinctly define the methods that you use in the analysis, making use of mathematical formulas where appropriate.

4.    Data: this should define the data items used in the analysis, the sources of the data and, where appropriate, the sample period and frequency. You should also report summary statistics of your data.

5.    Results: this should present the results of the analysis, including any sensitivity analyses. You should provide a critical  discussion of your results relating them to the objective of the assignment.

6.    Conclusion: this should summarise the  main findings of the model, critically evaluate any shortcomings of the data and method, its practical implications and offer some suggestions for future improvement.

7.    References: it is important that you include references for all the papers cited and that you use APA reference system. Further information about referencing other people’s work can be found here: https://ele.exeter.ac.uk/course/view.php?id=6748§ion=3#module-2714576

8. Assignment cover sheet: to comply with the UEBS GENAI policy, you must submit the

assignment cover sheet (see from the Appendix 1) together with the assignment report.

Guidance on Completion of the Assignment

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, and you should take this opportunity to develop your skills in this area. Some useful guidance on academic writing can be found from here: https://ele.exeter.ac.uk/course/view.php?id=6748

•    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 published paper in the Assessment Information section on the course website to give you some examples of how to present the assignment results.

•    You should use Equation Editor in Microsoft Word to format any equations; You should define any variables that are used in equations and explain/discuss the assumptions that are made for any of the input variables.

•    You should pay attention to the formatting of your report, particularly with respect to line spacing, paragraphs and section titles.

•    Note that there is no word limit for individual sections, only for the overall report.

•    You should not include appendices in your report.

•    Note that you can use Generative AI tools such as ChatGPT to support your work without using these tools to try to  replace or substitute for your own ideas and  perspective.  If you do  use generative AI tools in assisting the completion of this academic work, you must reference and acknowledge AI tools used in your academic work following  the  guidance  from  below:

https://libguides.exeter.ac.uk/referencing/generativeai.

•    Your report should follow the Business School’s guidelines on referencing, citation and avoiding

plagiarism, which can be found here:

https://vle.exeter.ac.uk/course/view.php?id=6748§ion=2

Assignment Submission

Deadline: Monday, 09 December 2024, 12:00 noon GMT

Written Report – will be marked

Please submit your written assignment in PDF format via the submission link on the modules ELE2 page, which will be available up to three weeks before the above deadline.

Supporting Data /Calculation /Programme Files – will be checked

A separate Excel file(s) containing your modelling data and calculations should be submitted on ELE  2 to the support document submission folder. Without this, your assignment mark will be capped at 50. The supporting Excel file(s) will not be marked but they are required and will be used to check the originality of your work.

Please name your supporting documents in following format: Student Number xx Support File. If submitting more than one file, please put them in a zipped folder to submit.

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: https://as.exeter.ac.uk/academic-policy-standards/tqa-

manual/aph/settingandsubmission/#late