a) Determine the NPV of undertaking the certification program.

7FNCE020W.1 Contemporary Finance Group Coursework

Assessment weighting: 70%

Submission date:

Provisional marks & feedback:)

General instructions:

Coursework must be undertaken in groups of two (pairs). One party should submit the assignment on behalf of the team. Group contract must be signed by both group members and submitted on Blackboard. This coursework has four parts, and students must answer all four parts to qualify for the full mark. All questions carry equal weights. The assignment should be produced in Word/PDF files and Excel spreadsheet (calculations).

The assignment will be graded to reflect your understanding of several concepts in corporate finance, your ability to gather relevant data and carry out in-depth analysis. All quantitative analyses must present a clear set of assumptions and a complete audit trail in an Excel spreadsheet. The word limit is 2,000 words (excluding appendices and tables)

[START OF THE COURSEWORK]

PART 1/4

Today is 6^th March 2020. Mia Matthews is 30 years old and holds BSc in Data Science. Currently, she is working as a Tier 2 field service representative for a telephone corporation and earns $38,000 a year after tax. She anticipates her salary will grow by 3% per year. Mia hopes to retire at 65.

Mia has $75,000 worth of savings in 10-year Treasury bonds. Mia is considering whether she should advance her education and use her savings to fund it.

Mia is considering studying for a certification in network design, which would promote her to a Tier 3 field service representative. The base salary for this position is $9,000 higher than what Mia makes now. Mia expects that this salary differential will grow at a 3.5% rate, as long as she remains employed. To obtain the certificate, Mia needs to complete several courses, which would take one year. The program’s cost is $7,000, which is payable on enrolment. As she intends to do the work for the certification in her own time, no loss of income during this time is expected.

Mia is also considering doing an MBA degree, which would help promote her to a managerial position, which pays $20,000 a year more than her current one. Mia expects this salary differential to grow at a rate of 4% per year for as long as she keeps working. The evening program will take three years to complete and costs $30,000 per year, payable at the beginning of each year. Mia doesn’t expect to lose any income while earning her degree as she will attend evening classes.

To determine the interest rate Mia is currently earning on her savings, go to the US Treasury Department Website (www.treasury.gov) and select “data” on the main menu. Then select “Daily Treasury Yield Curve Rates” under the interest rate heading and enter the appropriate year, 2020, and then search down the list for 6^th March to obtain the closing yield or interest rate that she is earning. Use this interest rate as the discount rate for the remainder of this question.

Create a timeline for Mia’s current situation, as well as the certification program and MBA degree options, using the following assumptions:

Salaries for the year are paid only once, at the end of the year

The salary increase becomes effective immediately upon graduating from the MBA program or being certified. Because the increases become effective immediately, but salaries are paid at the end of the year, the first salary increase will be paid precisely one year after graduation or certification.

Required:

a) Determine the NPV of undertaking the certification program. [10 marks]

b) Determine the NPV of undertaking the MBA. [10 marks]

c) Based on your answers in parts a) and b), what advice would you give to Mia? What if the two programs are mutually exclusive? Would your direction be different? [5 marks]

[TOTAL: 25 MARKS]

PART 2/4

Today is 30th March 2019, and you have just started your new job with a financial planning firm. Your first task is to review a portion of the client`s equity portfolio to determine the risk- return profiles of twelve stocks. Specifically, you need to determine the twelve stocks` monthly average returns and standard deviations for the past five years. a) Determine the NPV of undertaking the certification program.The stocks are:

Required:

a) Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the monthly statistics to annual statistics for easier interpretation. [5 marks]

b) Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Convert these monthly statistics to annual statistics for interpretation. [5 marks]

c) What do you notice about the average of the volatilities of the individual stocks, compared to the volatility of the equally weighted portfolio? [5 marks]

d) Rebalance the portfolio with the optimum weights that will provide the best risk and return combinations for the new 12-stock portfolio. Comment on your findings. [10 marks] [TOTAL: 25 MARKS]

PART 3/4

You have been asked to analyse changes to Home Depot’s capital structure. Specifically, you would like to consider two scenarios: the firm issues $1 billion in new debt to repurchase stock, and the firm issues $1 billion in new stock to repurchase debt. Suppose that capital markets are perfect and that 𝑘𝑒𝑢 = 10%.

You need to obtain all the necessary financial information for Home Depot to answer the following questions. All data used must be presented in your Excel spreadsheet, together with the assumptions you make.

Required:

a)   Calculate^𝑉𝐵

𝑉𝑆

b) Calculate 𝑘𝑒𝑔

c) Calculate company’s current WACC.

[5 marks]

[5 marks]

[5 marks]

d)   Repeat steps b) and c) for the two scenarios you would like to analyse. What is the^𝑉𝐵 ratio in each of these cases? [5 marks]

e)   What implicit assumptions generate the results found in d)? How might your results differ in the “real world”? [5 marks]

[TOTAL: 25 MARKS]

PART 4/4

Go to www.ft.com and note down Tesla’s Inc (NASDAQ: TSLA) current level of systematic risk. Then, obtain Tesla’s daily prices for the period between 07/12/2016 to 07/12/2021.

Required: