Excel代写 | FNCE2004 Introductory Business Financial Modelling

这个作业是用Excel完成金融财务相关数据的统计计算
FNCE2004 Introductory Business Financial Modelling
Final Assignment

1. What is the Yield to Maturity (YTM) of a bond which can be bought today for $10,131.25? The bond has a face value of $10,000. It pays 9.50% coupons semi-annually and it will mature in exactly 7 years. (2 marks)
HINT: First, compute the YTM by multiplying the periodic rate by the coupon frequency. Second, convert this rate to an effective annual rate.

2. The BIG Corporation issued 12-year bonds on 10 Feb 2010. Each of these bonds has a face value of $10,000 and they are currently trading at a yield of 11.50% per annum. The bonds pay quarterly coupons at an annual rate of 7.75% per annum.

(a) Compute the price of each bond of the BIG Corporation on the settlement date (07-Feb-2015). This is the “clean price”, excluding any accrued interest. Please use Actual/Actual as the interest rate basis. (3 marks)

(b) Use Goal Seek to find the YTM that would justify a price of exactly $9,000 for each of these bonds. Outline the steps you have used to obtain the answer. (2 marks)

3. A bond with a face value of $100,000 and coupon interest paid semi-annually at an annual rate of 7.50% per annum was issued on 8 May 2013 for 4 years. Similar bonds are now selling at a yield-to-maturity of 7.41% per annum. Based on the most recent and next coupon dates, work out the accrued interest on the settlement date (20-Jan-2015). Please use Actual/Actual as the interest rate basis and leave the Calc_method as its default. (4 marks)

4. (a) Refer to the table below. Work out the Price Earnings Ratios of the five companies P, Q, R, S and T. Provide a brief interpretation of this ratio. (1.5 marks)
Company P Q R S T
P0 $6.57 $45.46 $4.89 $18.00 $67.85
EPS0 $1.36 $6.78 $0.53 $1.65 $8.04
PE Ratio

(b) Using statistical and/or reference functions (MATCH, INDEX) to display in cells the names of the two companies whose shares represent the best value based solely on their Price Earnings Ratios; i.e. assuming that the companies are otherwise equivalent in potential and risk. Please construct the template based on the picture below: (2.5 marks)

Best value solely by PE ratio
Second best value solely by PE ratio

5. Refer to the table containing a company’s financial statements below:

Year ending 30-Jun-18 Number of shares on issue 4,320,601
Cash $6,105,465 Company tax rate 30.00%
Other assets $87,554,597 Weighted average cost of capital 11.44% per annum
Debt and liabilities $32,190,949 Growth rate in FCFs from 2022 5.48% per annum
Equity (book value) $61,469,113

Year ending 30-Jun-18 Growth rate 30-Jun-19 30-Jun-20 30-Jun-21 30-Jun-22
Sales revenue $78,528,535 3.20% $81,041,448 $83,634,774 $86,311,087 $89,073,042
Operating expenses $45,500,703 2.70% $46,729,222 $47,990,911 $49,286,666 $50,617,406
Depreciation $22,321,855 4.10% $23,237,051 $24,189,770 $25,181,551 $26,213,994
EBIT $10,705,977 $11,075,175 $11,454,093 $11,842,871 $12,241,642
Interest paid $3,908,199 7.30% $4,193,498 $4,499,623 $4,828,095 $5,180,546
Tax payable $2,039,333 $2,064,503 $2,086,341 $2,104,433 $2,118,329
Net income $4,758,445 $4,817,174 $4,868,129 $4,910,343 $4,942,767

Capital expenditure $16,170,094 6.00% $17,140,300 $18,168,718 $19,258,841 $20,414,371
Increase in working capital $2,551,414 2.50% $2,615,199 $2,680,579 $2,747,594 $2,816,284

(a) Work out last year’s (year ending 30 June 2018) free cash flows and the projected free cash flows (30 June 2019 – 30 June 2022) from these extracts from the company’s financial statements. (3 marks)

(b) Work out the Terminal Value of the free cash flows in 30 June 2021, using constant growth rate from 2022. (2 marks)

(c) Work out the enterprise value from the free cash flows and the terminal value. (2 marks)

(d) Display the value per share of the equity in this company. (2 marks)

(e) Recalculate the value per share, based on mid-year discounting (with a nominal conversion of the annual discount rate) of the free cash flows. (1 mark)

6. Refer to the table containing the share price of company T and W below:

Date Share T price Share W price
31-Dec-18 4.09 79.37
31-Jan-19 4.15 81.19
28-Feb-19 4.56 80.24
31-Mar-19 4.43 83.06
30-Apr-19 4.80 82.7
31-May-19 4.81 82.97
30-Jun-19 5.11 85.75
31-Jul-19 4.62 87.82
31-Aug-19 4.25 81.85
30-Sep-19 4.23 83.88
31-Oct-19 4.00 80.45
30-Nov-19 4.21 89.06
31-Dec-19 5.04 85.87

(a) Compute the returns for both companies. Please use the return definition used in the labs. (1 mark)

(b) Compute the average return and standard deviation for each of the returns. (2 marks)

(c) Compute the Covariance between the returns. Provide a brief interpretation of what the Covariance measures. (2 marks)

(d) Assume that you construct a portfolio where you invest 40% in company T and 60% in company W. What is the average return and standard deviation of this portfolio? (2 marks)

(e) Assume that you construct a portfolio where you invest 60% in company T and 40% in company W. What is the average return and standard deviation of this portfolio? (2 marks)

(f) Based on the calculation in parts (d) and (e) above, which portfolio weighting you would like to choose, i.e., 40% in company T and 60% in company W or 60% in company T and 40% in company W? Please provide your reasoning. (1 mark)

7. (a) Refer to the table below. Based on the risk, return and covariance of the RSJ and QLR shares, display the weights of the minimum variance portfolio comprising only these two shares. (2 marks)

Statistics based on monthly returns
RSJ QLR
Average 3.89% 3.92%
Variance 1.37% 1.96%
St. Deviation 11.70% 14.00%
Covariance 0.00661