BUSINESS FINANCE BBUS/ELCBUS 350 Stock Portfolio Project Objective The framework for financial decision-making always requires a risk and return tradeoff. The level of risk that an investor is willing to take on should be rewarded with an acceptable level of return. Conversely, a required rate of return is accompanied with a certain degree of risk that limits unreasonable returns. This project seeks to demonstrate that the world of stock investing exhibits equilibrium pricing and well-defined risk and return tradeoff for all participants. Goals Completion of this project should provide participants with an understanding of how equity investing is affected by stock selection, the relative risks of each stock choice, and how risk is defined and controlled through portfolio creations. Upon completion of the project, participants are expected to be familiar with concepts of rates of return along various temporal dimensions, stand-alone risk, portfolio risk, and where to locate stock performance information leading to a cursory ability to conduct equity research. Solution A cross section of publicly traded companies is selected by each participant as their research assignment. Individual securities are analyzed for their respective historical risk-return tradeoff performance. In Part 2 of this project, participants are grouped together to construct a well diversified portfolio of equity securities and are tasked to find an optimal portfolio construction. Project Outline The final set of deliverables for this analysis is a multi-page executive summary along with appendices supporting your assessments as securities analysts. The project consists of finding financial information on selected equity securities. Information about securities must be gleaned from various University of Washington subscription databases and other free internet sources. All collected information is analyzed using a business-accepted electronic spreadsheet for ease of estimation and communication. The results of the analyses are reported, presented, and discussed. EQUITY PORTFOLIO ANALYSIS I— INDIVIDUAL DIRECTIONS 1. Stock selections Select ten (10) different stocks from the universe of NYSE and NASDAQ stocks. The stocks 1 selected must meet all of the following criteria: o Actively traded on either the New York Stock Exchange or NASDAQ o Continuously active for the past 5 years. (Must have a beta measure) o A company profile on Mergent Online o Two (2) stocks must be in the same industry (2-digit SIC code matched) o Four (4) different industries must be represented among the stocks (2-digit SIC code) There are numerous stock exchanges in the United States and many more world-wide. However, for purposes of this project, the analysis is limited to stocks traded on the largest two domestic equity exchanges. 2. Sources of Information You must have an active UW NetId account in order to access certain databases of required company information. Other information may be sourced from various online websites that are not subscription based. Please view the UW Library course guide webpage for a comprehensive list of available websites and databases through the university at http://guides.lib.uw.edu/c.php?g=342001&p=2300131 A. Mergent Online: This database is sourced through the university library system. Access will require an active NetID and may require on-campus connection. B. Mint Global: This database is available through the university library system. C. Google Finance and/or Yahoo!Finance: Both of these websites are great sources for financial information. The information provided by both is secondary information so a good rule of thumb is if you obtain information from one website, avoid mixing with another for comparison purposes as the original sources may be quite different thus leading to errors in comparison. D. Standard Industry Codes: The OSHA website has descriptions of the four-digit industry code. A newer coding methodology called, the North American Industry Classification System (NAICS) is also provided for reference and comparison. 3. Describe the Stocks Equity analysis is more than just identifying which securities will perform the best. It is understanding the interactions among the different investment options. While it is assumed that investments are expected to increase in value, stocks vary by who they are, what they do, and how the market interprets their actions. Analysis of a stock must therefore begin with an understanding of what the underlying asset does and how it operates. STOCK DESCRIPTION: Locate the stocks’ Mergent Online or Mint Global company profiles. Make a note of what the company does, its size, its growth opportunities, its business model, and other important market information about it. PRINT COMPANY PROFILE: Print a single page company profile for each of your stock selections. At a minimum, make sure the print out includes the company name, description and ticker symbol. 2 INDUSTRY: From the various data sources previously listed, determine the company’s industry classification (SIC and/or NAICS). 4. Past performance analysis The purpose of this activity is to assess the market performance of different stocks to develop meaningful forecasts about their future potential. In order to produce understandable decision critical knowledge, an analyst should review past market performance to assess factors that contribute to a stock’s valuation. In this section, individual stock returns are analyzed for stand alone risk-return characteristics as described in the course textbook. PAST STOCK PRICES: Obtain monthly price and dividend data for each of your five stocks from Yahoo!Finance. You may use any other source, but the suggested database has a monthly date selection feature. The analysis time frame is April 1, 2013 through March 31, 2018            Open a web browser to http://finance.yahoo.com In the “Quote Lookup” search box (left hand side), enter the name or ticker symbol of your first stock. From the Company Analysis page, click on “Historical Prices” (left hand column, 5th line down) On Historical Prices page, “Set Date Range” “Start Date” to Apr 1, 2013 and “End Date” to Mar 31, 2018. Check box on “Monthly” and click “Get Prices”. Place cursor over “Download to Spreadsheet” (bottom of stock price table); Save the file as “Stock1.csv” Check box on “Dividends Only” and click “Get Prices”. Place cursor over “Download to Spreadsheet” (bottom of stock price table); Save the file as “Dividend1.csv” Repeat steps 1-8 for the other nine selected stocks. Make sure to label each file correctly as Stock# or Dividend# 2-5. SPREADSHEET DATA SETUP - STOCK PRICES: Create a new workbook using your spreadsheet program of choice. Copy the pricing data from each of the Stock#.csv files.     Open the file “Stock1.csv” from inside the spreadsheet program. A worksheet should open with seven columns with the headings: DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ CLOSE. Delete all columns except for DATE and ADJ CLOSE. (Adjusted Close is a modified end-ofday closing price that takes into account any stock splits over the life of the company.) Label the ADJ CLOSE as the ticker symbol for Stock1. 3      Open “Stock2.csv” from inside the spreadsheet program. A worksheet should open with seven columns with the headings: DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, ADJ CLOSE. Copy the entire column of ADJ CLOSE from Stock2.csv to a column adjacent the closing prices of Stock1. Make sure monthly prices line up correctly. Label ADJ CLOSE as the ticker symbol for Stock2. Repeat steps 4-6 for the three other selected stocks. Make sure to label each column correctly as ticker symbol for the stock. o NOTE: Make sure to line up prices with the correct month. Save the workbook as “PORTFOLIO” .xls, .xlsx, .numbers, etc. The process may differ slightly depending on operating system and spreadsheet program. One of the main objectives with this project is to practice spreadsheet analysis - so figure it out for your particular spreadsheet program! DATA SETUP - DIVIDEND PAYOUTS: Add dividend information to the stock price workbook.  Open the PORTFOLIO workbook.  Next to each stock price column, insert a blank column to the right of the stock prices for each Ticker.  Label the new column, “Tickersymbol_D”. This column is where any dividend payout will be placed.  Manually enter (type) each dividend payout ($) to the cell according to the Date paid. The dividend paid date month should be on the same row as the stock price date month. *If two dividends are paid in the same month for a stock, enter the sum of both dividends for the month.  NOTE: Some stocks do not payout dividends. In those cases, leave the cells blank. DATA ANALYSIS I: Calculate stock returns 1. Create a column to the right of each dividend column. Label the columns “Return”.This column is a where monthly percent change will be calculated based on each stock’s monthly price movements. 2. Each row will reflect the percent change from the beginning of the month stock price to the end of the month stock price (which is also the beginning of the next month’s stock price). 3. Price change, or monthly return, is calculated from the formula: (Pt+1 - Pt + Dt) / Pt where Pt+1 is the month ending price, Pt is the month beginning price, and Dt is any dividends paid during the month. 4. Copy and paste the formula into each cell for the remaining months. 5. Repeat steps 1-4 for each of the remaining nine stocks. 4 DATA ANALYSIS: Calculate stock returns (cont’d)  For ease of reading, format all price data to two (2) decimal points; dividend data to three (3) decimal points; and return data to percentage (%) with one (1) decimal point.  Label a row below the data: “Mean Return”.  In Microsoft Excel, the formula is “=average(range:range)” where range encompasses all the cells you wish to calculate the mean. STAND ALONE RISK: Below each Mean Return cell, create a cell that estimates the standard deviation of each stock’s 59 monthly returns.  Label a row below the Mean Return row: “Std Deviation”.  In Microsoft Excel, the formula is “=STDEV(range:range)” where range encompasses all the cells you wish to calculate standard deviation. COEFFICIENT OF VARIATION: Below each stand alone risk measure, create a cell that estimates the risk per unit of return of each stock’s 59 monthly returns.  Label a row below the Std Deviation row: “Coefficient of Variation”  The formula is Std Deviation / Mean Return for each stock. CORRELATION COEFFICIENT: This step requires some data copying and reorganization. In this step, you are calculating correlation coefficients among your five stocks. In order to do this efficiently, monthly returns need to be lined up and adjacent each other in a worksheet.  Create a new blank worksheet (tab) in your workbook.  Copy the entire spreadsheet of dates, prices, dividends, and returns. (Highlight all the cells and click “Copy” or press “Control + C”).  On the new blank worksheet, paste “VALUES” under Paste Special (Right button mouse click). If you don’t paste the copied cells as values, you will paste the formulas instead and not have the return value columns.  Delete all columns except monthly returns. Keep the first row for Stock Ticker Labels.  You should have 10 adjacent columns of 59 rows representing monthly return for each of your ten stocks.  Activate and select the Data Analysis tab in MS Excel. If you have not used the Data Analysis tab before, contact the UWB IT Helpdesk to have them help you activate it. Each spreadsheet program handles statistical analysis differently. These directions are for MS Excel only.  With the returns only worksheet open, click on “Data”, “Data Analysis”, then “Correlation”. Input range should be all populated cells in the spreadsheet. If you include the label row in the input range, make sure to click the “Labels in the First Row” box.  Make the “Output range” somewhere next to your data. Output will be a six-by-six array of data. Press “OK”.  Correlation coefficients ranges from -1.0 through +1.0. Determine which two (2) of your stocks have the: 1. Greatest, most positive correlation coefficient (closest to +1.0). These are “HI-COR” 5 stocks. 2. Lowest, most negative correlation coefficient (closest to -1.0). These are “LO-COR” stocks. CALCULATE PORTFOLIO RETURNS: In this section, portfolio risk return characteristics are estimated. Three (3) portfolios are retroactively created to validate weighted portfolio returns and diversification through correlation. A. H-COR Portfolio 1. On the data worksheet, create a new column on the far right of the data. Label this column “HI-COR”. 1. Identify which two stocks have the most positive correlation coefficient. Make a synthetic, equally weighted portfolio of these two stocks. For each month of returns take the average of the combined stock returns. So for April 2013, the portfolio return is =average(HCOR1 returns, HCOR2 returns). Do the same for all 59 months of the estimation period. 2. Just like for each individual stock, calculate HI-COR’s average portfolio returns at the bottom of the monthly data. 3. Calculate HI-COR’s standard deviation of portfolio returns at the below average returns. Calculate HI-COR’s coefficient of variation below Std deviation. B. LO-COR Portfolio 1. Repeat Steps 1-3 for L-COR. The portfolio monthly return of L-COR is the monthly average of the two stocks defined with the lowest correlation coefficient. C. All Stock Portfolio 1. The last synthetic portfolio is comprised all ten selected stocks equally weighted. Explained a different way, if you had invested $5,000 in these stocks, you would have purchased $1,000 of each share (no matter what the per stock prices were at the time of purchase). Each stock had the same dollar value to you when you made the purchase. 2. Create a new column and label it “ALL”. 3. The ALL portfolio monthly return is the average of all five stocks monthly returns by month. So the ALL monthly return for July 2013 is the sum of July 2013 returns for Stock1, Stock2, Stock3, Stock4,…, and Stock 10 all divided by ten. 4. Just like for the other portfolios, calculate ALL’s average portfolio returns at the bottom of the monthly data. 5. Calculate ALL’s standard deviation of portfolio returns at the below average returns. 6. Calculate ALL’s coefficient of variation below Std deviation. STOCK BETA: Another metric of risk is beta which is defined as the sensitivity of an asset (stock) to the market. While you do not need to estimate beta for your 10 stocks, you should know its beta value and how it is applied. 1. Look up each of your stock’s beta measure and list it at the bottom of each return column in your data spreadsheet. These values can be found in Yahoo!Finance or 6 Google Finance. 2. All of the listed financial websites provide a beta measure for your firm (unless it has been public for less than 5 years). INDIVIDUAL ANALYSIS INDIVIDUAL STOCK DISCUSSION 1. Which of your ten stocks had the greatest average monthly return? (and value) 2. Which of your ten stocks had the least average monthly return? (and value) 3. Which of your ten stocks had the highest stand-alone risk? (and value) 4. Which of your ten stocks had the least stand-alone risk? (and value) 5. Which of your ten stocks had the greatest beta measure? (and value) 6. Which of your ten stocks had the smallest beta measure? (and value) 7. Order your stocks three different ways: A. Highest average return to lowest average return B. Highest standard deviation to lowest standard deviation C. Highest beta to lowest beta D. Does the theory that “higher returns is accompanied with higher risk” hold for your five individual stocks? Discuss in terms of your stocks. 8. Is your highest stand alone risk stock the same as your greatest beta measure stock? Why do you suppose that is or isn’t? 9. Is your lowest stand alone risk stock the same as your lowest beta measure stock? Does standard deviation and beta always show the same results? Should it? 10. If you are a risk adverse investor and could only choose one stock to invest in, which would it be and why? STOCK PORTFOLIO DISCUSSION 1. Should stocks in the same industry have higher or lower correlation than different industry stocks? Explain. 2. Was your HI-COR portfolio comprised of the two stocks you selected from the same industry? What makes these two company’s market performance similar? 3. Which portfolio had lower stand alone risk, HI-COR or LO-COR? Is this what you would 4. expect? Why or why not? 5. State a definition of “diversification” in terms of correlation (as it is explained by theory, not your results) 6. Indicate the range of standard deviation among your 5 stocks. 7. What is the standard deviation of the ALL portfolio? 8. Is the risk of the ALL portfolio closer the highest stock standard deviation or closer to the lowest stock standard deviation? 9. Discuss the meaning of the following statements: The standard deviation of any portfolio of stocks can never be higher than the highest individual stock standard deviation. However, a portfolio’s standard deviation can be lower than the lowest individual stock standard deviation. [The corollary to this is a portfolio’s stand alone risk 7 could be zero even when individual stocks each have a lot of stand alone risk.] 10. Describe one scenario that you could employ to reduce stand alone risk in your ALL portfolio if you had constructed the portfolio five years ago. That is, if you had to invest in each of the five stocks, how might your portfolio construction and allocation be different to maximize risk reduction? 11. If given a choice a choice to invest in a single mutual fund (a diverse group of assets) or invest in your own stock/bonds/asset selections, which would you rather do and why? 12. After answering this question, read this article by Money Magazine: http://money.us/XcZIco DELIVERABLES A. Excel Workbook (.xls, .xlsx) of ten (10) stock analysis. B. Word Document (.doc, .docx, .pdf) of Individual Stock and Stock Portfolio discussions. To be submitted on Canvas by Thursday, May 31 at 11:59pm. 8
Purchase answer to see full attachment