FINA 6207 FINANCIAL MODELING

Java Python FINA 6207

FINANCIAL MODELING

GROUP PROJECT

Guidelines：

l This group project constitutes 10% of your final grade. Please read the instructions carefully and organize your response in a professional report.

l You can form. a team of 5-8 people and work with others on the project. Each team should have a team leader who’s responsible for submitting your group’s work.

l The team leader should submit your work as a single PDF file by the end of Sunday 12/08 to receive full consideration. You may include tables/codes/charts/screenshots etc. that can support your analysis in the report document. If you wish to submit additional scripts/spreadsheets, please also attach them on Canvas . The report should indicate full names of all your team members on the frontpage.

l Your grade will depend on several factors such as how well your report is organized, how comprehensive and thorough your responses are. Try to keep it professional and easy to read even for audiences that do not have business background.

l Please do NOT use ChatGPT or any AI tools in generating your report. It can and will be detected.

PART 1

Suppose you area bond portfolio manager atBVS bank. Your portfolio currently has the following components:

Government Bond A: $600,000 with a 20-year maturity and 2.5% coupon. Current YTM 5%. Corporate Bond B: $400,000 with a 5-year maturity and 3.8% coupon. Current YTM 4%.

Municipal Bond C: $500,000 with a 10-year maturity and 4.2% coupon. Current YTM 3%.

Conduct a comprehensive analysis of the portfolio with respect to its risk. Identify some types of risk the portfolio might be exposed to and then quantify them using the tools we discussed in class.

Hint: This is an open-end question. Just like what we deal with in the real world, when a problem is not well-defined and lacking some information. You might need to make reasonable assumptions in addition to what we learnt it class to solve it. Feel free to use market data and make sure to cite it properly. To get you started, you can calculate some key measures for bonds. You may conduct a sensitivity analysis to see the effect on portfolio value: simulate several scenarios that might help illustrate its risk exposure, e.g. a 1% increase in interest rates or 0.5% decrease in interest rates. How does the current term  structure affect the valuation process?  Is there an immunization strategy?

PART 2

Stock Valuation and building optimal portfolio:

a)  Use what you’ve learned in this course so far to val FINA 6207 FINANCIAL MODELING ue any 2 stocks from the following S&P

500 top firms list:

1.    Apple (AAPL)

2.    Microsoft (MSFT)

3.    Amazon (AMZN)

4.   Nvidia Corp (NVDA)

5.    Alphabet Inc. Class A (GOOGL)

6.    Meta. Platforms, Inc Class A (META)

7.    Tesla, Inc. (TSLA)

8.    Berkshire Hathaway Class B (BRK.B)

9.    Unitedhealth Group Incorporated (UNH)

10.  Eli Lilly & Co. (LLY)

With one of the following methods

1)   Dividend Discount Model method (with the help of CAPM)

2)   The multiple method

3)   FCF valuation model

b)  Choose 5 stocks out of the above list and construct an efficient frontier. Draw the efficient frontier on a graph and locate

I.      the GMVP

II.       the maximum Sharpe-ratio portfolio (tangent portfolio) given the current risk-free rate. Does it make a difference when short selling is prohibited? Back testing this portfolio  against S&P 500, which gives a higher return? Explain in detail.

In your report, please show the following

l Explain in detail why and why not you choose one valuation model over the others. Not all methods are applicable under certain circumstances e.g. when firms have negative required returns, EPS and FCF.

l If you need any data, please restrict your data range to be within the most recent five years and your data frequency to be monthly. Please refer to our slides as to how to obtain raw data. Audited financial statements can generally be found on firms 10-K forms filed with SEC. You        can         access        them         via         SEC’s        EDGAR         system         online.

https://www.sec.gov/edgar/searchedgar/companysearch

l Appropriate  assumptions  are  acceptable when key  information  is  missing. E.g. You  can somewhat assume a firm’s dividend growth rate is similar to its main competitor’s or the industry average when it’s not possible to obtain accurate data on it. However, do NOT abuse it when the raw data are available.

l Include key  steps of your  analysis or calculation with detailed explanation in between to ensure  readability.  Always  assume  your  audience  to  be  people  with  only  basic  or  no business/finance background.

l Try to limit your report on Part 2 under 10 pages.

l This  is  an open-ended project, and you  are  encouraged to do your  own research before finishing the report. Showing your thought process is more important than the results.

l R is the preferred tool.

l Please let me know if you have any questions