Assignment Information
Module Name: Software Applications in Finance
Module Code: 5019FIN
Assignment Title: Individual Coursework
Assignment Due: 04/04/2024
Assignment Credit: 15 credits
Word Count (or equivalent): 3,000 words
Assignment Type: Percentage Grade (Applied Core Assessment). You will be provided with an overall grade between 0% and 100%. You have one opportunity to pass the assignment at or above 40%.
Assignment Task
Assume that you are working as a financial analyst for an investment bank in the UK.
As a financial analyst at your bank, you have been tasked by your manager with selecting a company from the list of FTSE-All Share companies. Your job is to conduct research, write a financial analysis report on the chosen company, and provide a recommendation on whether your bank should invest in it.
Required:
Your goal is to select a company from Real Estate Investment and Services sector listed on the FTSE All-Share, conduct research, collect data, perform financial analysis of the company's financial statements, create data visualizations in Microsoft Excel, and write a financial report in Microsoft Word about the selected company. The ultimate objective is to provide a recommendation to your manager regarding whether to invest in this company's stocks.
Specifically, you are required to perform. the followings:
PART I: COLLECT DATA AND PRESENT ANALYSES ON MS EXCEL
1. Select company and collect data:
a. Select company: Choose one of the companies which is in the FTSE All-Share list and working in Real Estate Investment and Services sector categorized by London Stock Exchange (LSE). You can find the list of companies and sectors in the following link:
https://www.londonstockexchange.com/live-markets/market-data-dashboard/price-explorer?markets=MAINMARKET&categories=EQUITY&indices=ASX§ors=351010
b. Data collection: Using available data Sources to collect financial data of the selected company for the last 5 years (2019-2023). The data include: Balance Sheets, Income Statements (Profit and Loss Statements), Statements of Cash Flows and Monthly Stock pricing series of the company. [10 marks]
2. Using your Excel skills, gather the collected data and present the analyses in an
Excel file.
a. Gather the data that you have collected in part (1b) in different labelled worksheets of an Excel file. Name the file as “ Data file.xlsx”
b. Calculate the financial ratios of the firm and present them in a new worksheet – “Ratios ” using your financial analysis, Excel, aesthetic design, and presentation skills. The sets of financial ratios include:
Profitability Ratios: Gross Margin, Net Profit Margin and Return on Equity Ratio (ROE)
Liquidity Ratios: Current ratio, Quick ratio, and Cash ratio
Leverage Ratios: Debt-Equity ratio and Debt-Capital ratio Valuation Ratios (P/E): Price-Earnings Ratio
(Note: Please ensure that all the calculations must be entered in function or formula forms).
c. Create a new worksheet – “ Forecast Ratios”, perform historical forecasts and trend analyses of the ratios calculated in part (2b) and create dynamic graphs of the trend lines.
d. Collect data on Income Statements (Profit and Loss Statements) and Monthly stock pricing series for the last 5 years of any other FIVE firms in the same industry.
- Calculate P/E ratios and ROE of those firms.
- Create appropriate types of charts showing the changes over the last 5 years and
make comparisons between your chosen company and these five firms. [35 marks]
PART II: WRITE A REPORT (3000 words including tables and numbers)
Using all the financial ratios, forecast ratios, dynamic graphs, and other analyses that you have done in PART I, please write a complete report, give comments on the overall financial health of the chosen company, and give recommendation to your manager on whether to invest stock in this firm. The report contents should include:
- Introduction: Introduction of the report and a brief description of the data collection process (e.g. which data sources, functions and financial statement templates used?) (300-500 words)
- Overviews of the company and the industry that this company is working in (300 - 500 words).
- Financial Analysis: Explain and analyze the last year financial ratios; Discuss on the changes in firm’s performance (ROE) and value (P/E ratio) over the last 5 years and make the comparisons with the five peer companies.
(Note: The tables and charts that you have created in part 2 must be presented in the report).
(1500 - 1800 words including numbers)
- Conclusion and recommendation on whether to invest in the company’s stocks. (300
- 500 words)
[55 marks] Total 100 marks
Note: Please include the report and Excel file in your submission.
Criteria for Assessment
Your coursework (essay) should be written in an essay form using the following formatting:
Font: Times New Roman (Body), Size 12
Spacing: 1.5 with justified text
Referencing style. APA
Submission Instructions:
The assessment must be submitted by 18:00 – 04/04/2024. No paper copies are required. You can access the submission link through the module web.
• Your coursework will be given a zero mark if you do not submit a copy through Turnitin. Please take care to ensure that you have fully submitted your work.
• Please ensure that you have submitted your work using the correct file format, unreadable files will receive a mark of zero. The faculty accepts Microsoft Office and PDF documents, unless otherwise advised by the module leader.
• All work submitted after the submission deadline without a valid and approved reason (see below) will be given a mark of zero.
• The University wants you to do your best. However, we know that sometimes events happen which mean that you can’t submit your coursework by the deadline – these events should be beyond your
control and not easy to predict. If this happens, you can apply for an extension to your deadline for up to 5 working days, or if you need longer, you can apply for a deferral, which takes you to the next
assessment period (for example, to the resit period following the main Assessment Boards). You must
apply before the deadline. You will find information about the process and what is or is not considered to be an event beyond your control athttps://share.coventry.ac.uk/students/Registry/Pages/Deferrals- and-Extension.aspx
• Students MUST keep a copy and/or an electronic file of their assignment.
• Checks will be made on your work using anti-plagiarism software and approved plagiarism checking websites.
Marking and Feedback
How will my assignment be marked?
Your assignment will be marked by module team.
This document is intended for Coventry University Group students for their own use in completing their assessed work for this module. It must not be passed to third parties or posted on any website.
How will I receive my grades and feedback?
Provisional marks will be released once internally moderated.
Feedback will be provided by the module team alongside grades release.
Your provisional marks and feedback should be available within 15 working days.
What will I be marked against?
Details of the marking criteria for this task can be found at thebottom of this assignment brief.
Assessed Module Learning Outcomes
The Learning Outcomes for this module align to themarking criteriawhich can be found at the end of
this brief. Ensure you understand the marking criteria to ensure successful achievement of the assessment task. The following module learning outcomes are assessed in this task:
This assignment is designed to assess learning outcomes:
1. Effectively utilize the Refinitiv Workspace database for financial investment.
2. Display mastery in the use of technical analysis techniques to appraise stock picking and other financial asset investment decisions.
3. Display mastery in the use of elements of fundamental analysis techniques to appraise stock picking and other financial asset investment decisions.
4. Examine and scrutinise the theoretical basis of technical and fundamental analysis.
Assignment Support and Academic Integrity
If you have any questions about this assignment please see theStudent Guidance on Courseworkfor more information.
Spelling, Punctuation, and Grammar:
You are expected to use effective, accurate, and appropriate language within this assessment task.
Academic Integrity:
The work you submit must be your own, or in the case of groupwork, that of your group. All sources of
information need to be acknowledged and attributed; therefore, you must provide references for all sources of information and acknowledge any tools used in the production of your work, including
Artificial Intelligence (AI). We use detection software and make routine checks for evidence of academic misconduct.
This document is intended for Coventry University Group students for their own use in completing their assessed work for this module. It must not be passed to third parties or posted on any website.
Definitions of academic misconduct, including plagiarism, self-plagiarism, and collusion can be
foundon the Student Portal. All cases of suspected academic misconduct are referred for
investigation, the outcomes of which can have profound consequences to your studies. For more information on academic integrity please visit theAcademic and Research Integritysection of the Student Portal.
Support for Students with Disabilities or Additional Needs:
If you have a disability, long-term health condition, specific learning difference, mental health
diagnosis or symptoms and have discussed your support needs with health and wellbeing you may be able to access support that will help with your studies.
If you feel you may benefit from additional support, but have not disclosed a disability to the
University, or have disclosed but are yet to discuss your support needs it is important to let us know so we can provide the right support for your circumstances. Visitthe Student Portalto find out more.
Unable to Submit on Time?
The University wants you to do your best. However, we know that sometimes events happen which mean that you cannot submit your assessment by the deadline or sit a scheduled exam. If you think this might be the case, guidance on understanding what counts as an extenuating circumstance, and how to apply isavailable on the Student Portal.
Administration of Assessment
Assignment Category: Written
Attempt Type: Standard
Component Code: Coursework 2
MARKING CRITERIA
The following criteria will be used for marking the essay.
Marking Criteria for PART I: COLLECT DATA AND PRESENT ANALYSES ON MS EXCEL
1. Data collection:
(10 marks)
In this section, students are expected to select a correct company as required; to collect correct and sufficient data for the coursework; to clearly provide sources of collected data; and to show excellent data organization in the worksheets.
2. Gather the collected data and present the analysis in an Excel
(35 marks)
In this section, students are expected to:
a. Gather the financial data of the firm that you have collected in an Excel file named “Data file.xlsx” ; Fill colors and correctly label each worksheet. [5 marks]
b. - Use correct formulas with links to the data worksheets to calculate required financial ratios in a new worksheet named “ Ratios”. (All the calculated ratios must be clearly entered in function forms and correctly linked to the data worksheets).
- Design and presentation: Use your Excel skills to design and present a summary table of the calculated financial ratios. [10 marks]
c. Use correct functions to calculate historical forecasts of the ratios calculated in part (2b); Perform. trend analyses of the ratios using dynamic graphs of trend lines of the ratios. [10 marks]
d. Collect correct and sufficient data of other five firms in the same industry as required; Create a new worksheet – “Comparisons”, calculate P/E ratios and ROE ratios and use appropriate charts showing comparisons these ratios between your and other five companies; and present them in that worksheet. [10 marks]