BA7006- Financial Modelling using Excel : Financial Modelling and Data Analytics, Assignment

Published: 10 Jan, 2025
Category Assignment Subject Financial
University Kingstone University London Module Title BA7006- Financial Modelling using Excel
Assignment Title: Financial Modelling using Excel
Word Count:  3000 words

Assignment Task

The assignment is intended to help you develop skills of implementing financial models in Excel. The assignment provides you with an opportunity to produce spreadsheet applications that solve and analyse corporate finance problems.

You are required to set up a separate worksheet (spreadsheet model) for each of the following four problems:
A. Calculate the Yield to Maturity (YTM) for a $1,000 par bond with a coupon rate of 3% per annum, considering annual, semi-annual, and quarterly coupon payments. The bond has a tenure of 6 years and is currently priced at $850, with a settlement date of January 1, 2020. Discuss how the frequency of coupon payments affects the annual YTM. Additionally, determine the bond's duration and modified duration, and create a data table to show the sensitivity of these metrics to yield changes from 1% to 10% and to changes in coupon frequency. Provide an analysis of what duration and modified duration indicate for the bond, supported by an appropriate table and/or graph. (25%)

B. Choose five companies listed on the S&P 500 index, each from a different Global Industry Classification Standard (GICS) sector, with historical share price data available for the last 10 years. Download the monthly historical share price data from January 1, 2014, to December 31, 2023. Construct an equally weighted portfolio with these stocks and analyse the risk and return dynamics of the portfolio’s monthly returns. Compare the portfolio’s risk and return with those of the individual assets, and critically discuss the value of portfolio diversification using academic literature. Additionally, create multiple portfolios using combinations of these five stocks and calculate the average annual returns and standard deviations. Comment on the risk-return trade-off among the portfolios, graph the average annual return and standard deviation, and discuss the concept of an optimal portfolio using academic literature. Explain why a risk-averse investor might choose to invest on the efficient frontier. (25%)

C. Apply the Capital Asset Pricing Model (CAPM) and regression analysis to estimate the systematic risk and theoretical return for the five stocks and the equally weighted portfolio from Section B. Analyse the results, identify which stock or portfolio has the highest systematic risk, and explain what factors contribute to the systematic risk of an asset. (25%)

D. For one of the five stocks from Section B, find a call option with a one-year expiration. Use the Black-Scholes Option Pricing (BSOP) model to estimate the implied volatility based on the call option inputs and provide a discussion on the estimated implied volatility of the stock. Additionally, calculate the price of a corresponding put option and critically evaluate the usefulness of the BSOP model compared to the binomial option pricing model. (25%)

Are you facing challenges with your BA7006 Financial Modelling and Data Analytics Assignment? At Workingment writers provide high-quality assignment help to make complex topics and critical data easy to understand. hire our proficient experts to take help with report writing for finance and related topics at affordable prices. We also provide reliable finance assignment help to simplify complex topics in a well-structured format with human-written content. Get access to our expert support by following the simple steps of signing up.

5K5Z0009 Financial Reporting and Assurance Assignment Brief | MMU

5K5Z0009: For this assignment, you need to prepare a report on the analysis of the financial reports and assurance implications of JD Sports Fashion Plc for year ended 29th January 2024 (Annual Report and Accounts 2024).

7CI022 Database Systems & Security Coursework Assignment Brief | University Of Wolverhampton

You are required to design and implement a database system for an application area of your choice. For example, it could be a booking type application or an order processing type system (but other suggestions are welcome!).

ACCT11046 Corporate Finance T2 Assignment Brief

ACCT11046 : GreenTech Innovations Ltd. is a small-to-medium enterprise (SME) based in the UK, specializing in renewable energy solutions. The company has been operational for five years and is looking to expand its operations.

6G7V0031: Advanced Network Security Assignment Brief | MMU

6G7V0031: Assignment Tasks: In this assignment, you will identify one of the cybersecurity state-of-the-art topics of your choice. You will create a technical report, critically analysing the latest research articles under the chosen topic.

TLH 219 Meetings, Incentives, Conferences & Exhibitions Assessment Brief

Knowledge of the experiential nature of the MICE sector. Knowledge of the changing nature of the business consumer and marketplace. The ability to situate MICE within the contemporary commercial context. The ability to critically discuss the role of MICE within the wider tourism, events and hospitality industry.

MSB026-6 - Business Sustainability in Practice Assignment 2 Brief | University of Bedfordshire

MSB026-6 - Business Sustainability in Practice, Comprehensive research and enquiry to investigate current sustainability challenges for businesses and organisations, including critical engagement with theories, frameworks and systems in pursuit of effective

SHR016-3 Strategic Management for Business Assignment 01 Brief | University of Bedfordshire

In this assessment task, you will assume the role of a strategic analyst for a real-world company. Your task is to analyze the company's current strategic position and propose recommendations to improve its competitive advantage. This assessment will help you apply the strategic concepts and frameworks covered in the course to a real-world scenario.

SRQ762:Cost Planning Assignment 1 Brief- Deakin University

Solved Questions 1. Describe the objectives and types of cost planning techniques that may be used at the briefing stage of the development of the office building project, and discuss their advantages and disadvantages.

COSC 4364 Numerical Methods Assignment 3 Brief 2025

The working code needs to be submitted together with clear instructions on how to run the code (if necessary) to receive credit. The code must contain comments briefly describing the steps you implement. Write the scripts by hardcoding the inputs according to the problem. DO NOT write scripts that expect user input.

MC6094QA Fashion Marketing Communications in Practice (Fashion Context)Assignment 2 Brief | LMU

Solved: MC6094QA Fashion Marketing Communications in Practice, You are required to develop a report for an Integrated Marketing Communication plan for the fashion brand chosen from your first presentation. The communication plan needs to cover the period January-March and/or April-June 2025.

Online Assignment Help in UK