Reference no: EM133716003

Directions:

You just started your new position as a Portfolio Manager at a wealth management company. You have been asked to create a stock portfolio to offer clients. Specifically, they would like you to create a 10 stock portfolio that follows a goal or objective. Given your role and the size of the firm, they have asked you to select the investments and run risk vs. reward analysis. You must classify your portfolio as one of the following strategies:

Conservative: This strategy has a focus on preserving capital. This would include companies that have been around for a long time, been through market fluctuations, and likely offer income to shareholders through dividends.

Moderate: This strategy has a focus on balancing risk and reward. Designed to preserve capital, but also take on some risk to have higher returns than inflation. This would include a combination of medium to large capitalization companies that have proven consistent growth.

Aggressive: This strategy is to maximize returns by taking higher risk (relative to other investments). Designed for clients to stay in over long periods of time to withstand market fluctuations. The companies in this strategy are likely small to medium in capitalization, relatively volatile in price, and reinvest earnings to keep up with growth.

The firm has suggested that you adhere to two rules:

Select ten stocks that you believe as a portfolio represent the strategy (risk/reward)

Always diversify! This can be done by geography, industry, etc.

After selecting your strategy and deciding on your portfolio:

Collect price information for a recent 5 year time horizon from Yahoo! Finance (finance.yahoo.com) as follows:

Enter the stock symbol. On that page click "Historical Data"

For the time period, enter the "start date" and the "end date" as a recent five year period. For example: Jan 1, 2018 - Jan 1, 2023. Choose the frequency as monthly.

After hitting "Apply" click "Download Data".

Open the downloaded data in an Excel spreadsheet. Delete all the columns except the date and the adjusted close. This takes into account any stock splits and dividends paid. Label the adjusted close to the name of the stock selected.

Enter the next stock symbol in the main search box and search for the next stock. Do this for all 10 stocks. In addition, do this for the symbol "SPY", the Exchange Traded Fund (ETF) for the S&P 500. Use this data to estimate the overall market. Repeat the same steps above for each stock, maintaining the same time frame. Make sure the first and last prices are in the same rows and lined up correctly.
Convert these stock prices to monthly percent change (hint: create a separate worksheet within the Excel file).

Compute the mean monthly returns and standard deviations for the monthly returns of each of the stocks. Convert the statistics to annual for easier interpretation (multiply the mean return by 12, and the standard deviation by square root of 12: √12). You should now have an annual risk (standard deviation) vs. return for each stock selected.

Add a column in your Excel worksheet with the average return across stocks for each month (not including SPY). As a heading (title), label it "Portfolio". This is the monthly return of an equally weighted portfolio of these 10 stocks. Compute the mean and standard deviation of monthly returns for the equally weighted portfolio. Convert these monthly statistics to annual (see step 3).

In addition to the 10 stocks, portfolio, and SPY calculated statistics, look up the symbol "^TNX". The current price of the 10-year treasury can be used as a risk-free rate.

With all of the statistics gathered, create an Excel plot with the annual standard deviation (volatility) on the x-axis and annual average return on the y-axis. You should create the Securities Market Line (SML) with the risk-free rate and market return (SPY). The SML should look similar to page 268 of the textbook (standard deviation on x-axis). All the axis, data points, should be labeled and presented in a professional manner.

Create three columns on your spreadsheet with the statistics you solved. The first column will have the ticker (symbol) and "Portfolio", the second will have annual standard deviation, and the third will have annual mean return. The table should include all 10 stocks selected, the Portfolio, SPY, and ^TNX (standard deviation = 0).

Highlight the data in the last two columns (standard deviation and mean), choose: > Insert > Chart > XY Scatter Plot. Complete the chart wizard with labels, titles, and headings.
Introduction: Identify the strategy, the goal or objective, and what portfolio you selected.
Body/Analysis: Compare and contrast the investments from a risk / reward perspective over the last five years. Describe the graph provided in the spreadsheet created. What do you notice about the average of the volatilities with the individual stocks compared to the volatility of the equally weighted portfolio? What is the advantage of owning the portfolio? What is above, below, and on the SML?
Conclusion: Summarize the findings from your portfolio analysis. Provide any insight about future investment.