Portfolio Construction and Company Analysis - Investment Paper

Assignment 1 (Case study)
Hello
Our friend, John has recently inherited $100,000 and he is interested in investing it. thought since you are doing that Investment paper, you should be able to apply some of your knowledge and help him out. He wants to be able to invest by himself later on, so in addition to recommendations on what to do with the money he wants to understand your analysis.

Since it is a small portfolio and he has not got a lot of time, and with transaction costs it is not worth him taking on too many stocks. I have suggested a two-stock portfolio with the risk-free asset. So please just focus on one company from the S&P 500, and pick two companies from the NASDAQ1

Please choose one of the two NASDAQ stocks (remember, explain your choice) to construct the optimal risky portfolio with the S&P 500 company you selected. Then use John’s answers (your own answers) to the utility questionnaire to determine how he should allocate his money (forming the complete portfolio using the right optimal risky portfolio and the risk-free asset). The annual risk free rate is 2% and the sample period is from 1 June 2012 to 4 June 2019 (Please use these exact days for sample period selection).

When you choose your S&P 500 company, choose one which pays dividends, as he wants some income from his investments. Also make sure all three companies have been listed for a while, so you can get adequate data for analysis. For the data, just check Don’t worry too much if the companies you choose do not look ideal. You are not trying to sell him a particular company, although a good choice of companies will make your assignment much easier (some tips are given below). Understanding how to construct an optimal portfolio and a complete portfolio and how to evaluate a company are the key tasks here.

Tips of choosing your companies:
First, check the stock returns and standard deviation (S.D.) of the company. A company with an average annual return below the annual risk free rate is obviously not a good
 1 Some companies are cross-listed on both markets and that is fine.
choice. In general, a company with high return (in comparison with returns of other stocks) and low S.D. (in comparison with S.D. of other stocks) is a good choice.
Please prepare a written report (3-5 pages, font size 12, 1.5 line space) on your portfolio construction and company analysis, after you finish the following calculations in all the steps in Excel.

Portfolio Construction and Company Analysis

The written report is worth 58 marks, and it should include the following parts:
1. Basic information of your three chosen companies (This part should not be more than one page): (Please do not just copy and paste the information from the company website. The analysis of the public information is required.)
a. Basic information such as the firm’s age, which industry it belongs to, its business life stage (3 marks);
b. Any strategies of these firms for developing in the international markets and any business ethical challenges of these firms (6 marks); and
c. Why you choose these three companies (3 marks);

2. Your three companies’ basic statistical analysis from your own calculations
(return, risk and correlations of your three companies; alpha, beta and R-squared of your companies and whether your companies are overpriced or underpriced; and relevant discussion); (14 marks)

3. The summary of the portfolio construction and selection (with portfolio weights, return, risk of portfolios and which portfolio is better and why; and any relevant information and analysis); (11 marks)

4. Evaluating your three companies by comparing each chosen firm with any two competitors in the same industry using the relative valuation method. You should compare some ratios such as PE, PB, price to cash flow ratio between your chosen firm and its competitors. In addition, you need to consider the market prices of your chosen firm and its competitors. (15 marks)

5. The future prospects of your three companies based on your analysis and the discussion of the complete portfolio (including any borrowing or lending situation if any). (6 marks)

The following tables and calculation should be included in an Excel file, 42 marks.
(I will show you an example of how to do steps 1, 2 and 4 in Excel on Stream—Air NZ example. As for how to collect data, please see “help and hint of assignment” at the end of this file. I will also go through all the steps with you during my internal class in Week Five.)
Step 1 - Table of monthly adjusted closing prices for the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies for the sample period (from 1 June 2012 to 4 June 2019); (2.5 marks)

Step 2 - Table of monthly returns for the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies for the sample period; (2.5 marks)

Choosing Companies for Optimal Portfolio

When calculating monthly returns, please note the following:
You can use either one of the following two methods to calculate returns (based on different assumptions). I will explain the difference in Air New Zealand example posted on Stream.

Step 3 - Two graphs on the monthly returns for the sample period; one with monthly returns of the S&P 500 index and your S&P 500 company, and the other with monthly returns of the NASDAQ index and the two NASDAQ companies; (2 marks)

Step 4 - Summary table of the average monthly returns and standard deviations (S.D.) and the average annual returns and S.D. of the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies; (5 marks)

Step 5 - Correlation matrix among the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies; (1 mark)
(To calculate the correlation matrix, use Excel – data — data analysis — correlation. The input is the 5 time series of monthly returns of the S&P 500 index, the NASDAQ index, your S&P 500 company and two NASDAQ companies that you get in Step 2.)

Step 6 - Using the regression method to find and highlight three companies’ (your S&P 500 company and two NASDAQ companies) betas (against the proper market index) and R-squared of the regressions. Show the input data of the regressions and the three regression result tables; (7 marks)

Step 7 - Using Jensen’s alpha equation, calculate the alpha of your S&P 500 stock and two NASDAQ stocks3  (3 marks)

Step 8 - Formation of two optimal risky portfolios (Portfolio one consists of your S&P 500 stock and NASDAQ stock 1; and portfolio two consists of your S&P 500 stock and NASDAQ stock 2) (The optimal risky portfolio is different from the global minimum variance portfolio and you need to consider the existence of the risk-free asset whencalculating the weights of your stocks): calculating the weights of two stocks in each optimal risky portfolio; calculating the expected return & risk of the two optimal risky portfolios; and calculating the Sharpe ratios of two optimal risky portfolios (10 marks)

Step 9 – Answer the utility questionnaire on Pages 163 (11th edition) or Pages 174-175 (10th edition) in the textbook and get your “A”--the degree of risk aversion (1 mark)

Step 10 – Two graphs of the optimal portfolios and CALs: provide two input tables for the investment opportunities curves and show two graphs for each efficient frontier and CALs (4 marks)

Step 11 - Determination of the complete portfolio along the CAL according to John’s degree of risk aversion (your own risk aversion level): calculating the weights between the optimal risky portfolio and the risk-free asset; calculating the return and risk of the complete portfolio; (4 marks)