Homework related to finance and finance management.Need to be completed in strict accordance with the requirements.Please read the operation requirements carefully。It needs to be completed by March 31.
1
Finance & Financial Management 2019/2020
Group-based assignment
This document describes the coursework component for Finance & Financial Management which
counts for 40% of the final mark for the course. You are required to complete this coursework in groups.
Details of the questions are given below.
Assignment
Each coursework group is required to choose two real-life companies (Company A & Company B) that
are publicly traded on the main market of the London stock exchange. The two companies can be
selected from the FTSE 100 Index. The list of companies that constitute the FTSE 100 Index can be
downloaded from the following website:
http://www.morningstar.co.uk/uk/equities/indexstockprices.aspx?index=FTSE_100
Please note that the list can also be downloaded from, for example, Yahoo Finance, Bloomberg, or
Datastream. One of the companies (Company A) you select must not be from the financial sector (e.g.,
cannot be a bank or an insurance company). Further, neither company (A or B) can be an investment
fund (e.g., you should not use Aberdeen Asset Management Plc as one of the two companies).
* It is the responsibility of each team (and all team members) to collect all necessary data required
to complete the assignment.
Part 1
To complete this part, you will require data on your two companies’ monthly (or weekly) common-
stock returns from January 2009 to December 2018. Or weekly common-stock returns for the period
January 2014 to December 2018. If weekly returns are used then the answers to the questions below
should be based on weekly data.
Required:
1. Suppose you are advising an investor who is considering investing all his/her wealth in the
stock of just one of the two companies chosen by your coursework group (Company A or
Company B).
a. Provide brief descriptions of Company A and of Company B.
http://www.morningstar.co.uk/uk/equities/indexstockprices.aspx?index=FTSE_100
2
b. Next, compare and contrast the stock return performance of the two companies’
common stocks over the calendar period using monthly (or weekly) return data from
January 2009 to December 2018. Specifically, calculate the mean, variance and
standard deviation of the monthly returns of the two stocks separately.
c. Briefly comment on your results and make a stock recommendation.
2. Now suppose you are advising an investor who is considering investing all his/her wealth in a
portfolio consisting of the two companies’ common stock held together.
a. Calculate the mean, variance and standard deviation of the returns of portfolio
comprising the two stocks with equal weights (i.e. 50:50). Next repeat the calculations
for alternative portfolio weights, including 10:90, 20:80, 40:60, 60:40, 80:20, and 90:10.
You may choose to construct other additional portfolios (but remember the portfolio
weights need to add to 100%). Report your results in a table. Compare and contrast your
findings with those of the single-stock portfolios in 1(b).
b. Illustrate your results in 2(a), along with the single-stock results in 1(b), in a graph
plotting the trade-off between the mean return and standard deviation of the portfolio
returns.
c. In the trade-off graph in 2(b), indicate the efficient frontier (assuming the stocks of
Company A and B are the only available assets).
d. Finally, try to identify the minimum variance portfolio in the trade-off graph. To do so,
you can use trial and error, or the method outlined in the notes that you can find in
MyPlace. Report the portfolio weights of the minimum-variance portfolio, and the mean,
variance and standard deviation of returns of the minimum-variance portfolio.
e. Based on your findings in the previous parts, briefly explain to the investor how to
choose his/her optimal portfolio assuming the two stocks are the only assets available
to him/her. Also briefly indicate how your advice would change if other assets (e.g.,
risk-free asset) became available to the investor.
Part 2
The senior management of Company A have asked you to advise them on the cost of capital the
company should use to calculate the net present value and decide whether or not to undertake a new
investment project. You may assume that the new project is comparable to the average of the company’s
existing projects in all respects.
It is worth stressing that that Company A cannot be from the financial sector and also cannot be an
investment fund.
Required:
1. Calculate investors’ required rate of return on Company A’s equity.
3
Remember, there are many ways of estimating investors’ required returns. You should use two
alternative methods of calculating the required returns to check how sensitive your result is to
using different methods; i.e. to check the robustness of your result. For example, you could use
the Capital Asset Pricing Model (CAPM) which uses a single factor (beta) and the Dividend
Constant Growth Model.
2. Calculate Company A’s debt cost of capital.
The bond yield (cost of debt) can be calculated as Yield = risk-free rate + credit spread. If you
cannot find data on the approximate credit spread for a given credit rating of your chosen
company then you can simply use the latest rate that your company is paying on its newly issued
debt (you can use the latest published financial Statements of your chosen company to find the
latest rate the company is paying on its debt). For simplicity, you may assume that the only
securities outstanding of your chosen company are common stock (equity) and long-term debt.
Note that the after-tax cost of debt is lower than the pre-tax cost of debt if there is a tax advantage
of debt relative to equity (interest tax shield).
3. Calculate the cost of capital (that is, the appropriate discount rate to calculate the net present
value) of Company A’s new investment project.
4. Clearly explain your calculations and methods used in (1) to (3). Among other things, note
explicitly whether your results are in terms of monthly, weekly or yearly returns (either is
acceptable as long as clearly stated). Briefly describe and justify the data and (proxy) measures
you are using. State and discuss any assumptions you are making (including assumptions about
the financing of the project).
5. Briefly discuss any limitations of your analysis and how (given more time and information) you
might improve your analysis in the future.
4
Collecting data
To complete the assignment, your group will need to collect various sorts of data which can be
downloaded from different sources (e.g., Yahoo Finance, the website of the company, Datastream).
Please e-mail me if your group cannot find the required data.
1. Equity data
Download the equity return data from Yahoo Finance, Bloomberg, or Datastream. You will need to
calculate the monthly (or weekly) holding period returns for your chosen company and the FTSE 100
Index. Holding period returns are defined as follows:
𝑟𝑖𝑡 = (
𝑝𝑖𝑡 + 𝑑𝑖𝑡
𝑝𝑖(𝑡−1)
− 1)
where 𝑟𝑖𝑡 is the holding period return for company i for month t, 𝑝𝑖𝑡 is the price of company i at the end
of month t, 𝑑𝑖𝑡 is any dividend declared ex div during month t adjusted to an end-of-month basis, and
𝑝𝑖(𝑡−1) is the price of company i at the start of month t (adjusted if necessary for any changes in
capitalizations to make it comparable with 𝑝𝑖𝑡).
If you want to express returns in percent (%) you have to multiply the equation for the (decimal) holding
return above by 100. Make sure you convert returns collected from different data sources to the same
units (decimals or percent).
In your baseline calculations use monthly return data from January 2009 to December 2018 or from
January 2014 to December 2018 (if weekly returns are used).
The total market value of the equity (market capitalization) can be calculated by multiplying the number
of shares outstanding by the market price of the shares. You need to find the total number of outstanding
shares of the company and the current market share price of your chosen company.
2. Debt data
The data on debt can be extracted from the company’s financial statements, Yahoo Finance, Bloomberg,
or Datastream. You need to extract the data relating to the Long-term debt.
Credit Rating: (If your company has credit rating)
Ratings: Select an appropriate credit rating for your company. For simplicity, you may assume that
your company only has long-term debt. Note that there may not be any data on the time-series of bond
returns for individual companies. In fact, you do not need (to construct) the time-series of bond returns
5
to complete the assignment – you only need to download one observation of the credit rating (if rating
data is available) and one observation of the debt value.
The bond yield (cost of debt) can be calculated as Yield = risk-free rate + credit spread. Data on the
approximate credit spreads for companies with a given credit rating can be found in various places on
the internet. Ideally you should use spreads at the end of 2018, but current values can be used instead
(bearing in mind any changes in spreads since the last financial year of your chosen company). In the
event that you cannot find spreads for the specific rating of your company, you can use the spread for
the next lower or higher rating. Please explain, discuss and justify your data and methods on spreads
(and on all other data/methods) in your report in Part 2(5).
3. Estimating equity betas
You can estimate the CAPM beta of Company A’s equity using the following regression:
𝑟𝑖𝑡 − 𝑟𝑓𝑡 = 𝛼 + 𝛽𝑖(𝑟𝑚𝑡 − 𝑟𝑓𝑡) + 𝜀𝑖𝑡
where 𝑟𝑖𝑡 is the monthly return on the company’s stock, 𝑟𝑚𝑡 the monthly return on the market (FTSE
100 Index), 𝑟𝑓𝑡 the monthly return on a “risk-free” asset, and 𝜀𝑖𝑡 is the error term.
In your baseline calculations use monthly return data from January 2009 to December 2018.
Calculations can be done in Excel. You will need to ensure that the Analysis ToolPak has been added
in. You can easily check this by clicking on Tools in the menu at the top of the screen in Excel. If it
has been added, “Data Analysis” will appear in the list. If “Data Analysis” is not there, click on “Add-
Ins” and check (i.e., tick) the “Analysis ToolPak” box.
The Regression function in Excel can be found in the Data Analysis… part of the Tools menu.
When you click on Regression, you will be asked to input a Y range and an X range. In the SML
equation (of the CAPM), the Y range is your company’s excess return (𝑟𝑖𝑡 − 𝑟𝑓𝑡) while the X range is
the excess return on the market (𝑟𝑚𝑡 − 𝑟𝑓𝑡). Please remember to use the Help menu in Excel.
Submission
Please upload the following documents to Myplace until the deadline (27th March, Friday):
1. Excel files including the raw data and analysis
2. Report (words or pdf)