Stock Portfolio Project (Completed on 5/1/21) -- 2

Project You have $500,000 to create a stock portfolio. You must pick five stocks and maintain them in an Excel spreadsheet staring from 1/19/21 – 5/1/21. The stocks should be from the manufacturing sector if possible. You should have two tabs open in Excel: one for calculations and one for financial ratios. The calculations in Excel should include the following statistics: 1. Daily rate of return of the individual stocks. For example, if you are looking at stock XYZ (made-up) prices for April 1 of $100 and April 2 prices are 101, then the rate of return would be (101-100)/100. The formula is todays prices minus yesterday’s prices divided by todays prices. An example is shown on Blackboard. Also see Section 2-6 A, “Stock Market Reporting” and examine Figure 2.3. Noter the book uses Yahoo Finance. 2. Rate of return of the portfolio. See section 8-3 A “Expected Portfolio Returns” and look at Table 8.4. An example is also in the Excel spreadsheet on Blackboard. 3. Standard deviation of each stock. See Section 8-2B “Measuring Stand-Alone Risk: The Standard Deviation. There is an Excel function that can calculate this for you. 4. Stand deviation of the portfolio. See Section 8-2 B again and Table 8-2 shows how to take a portfolio standard deviation. Note, the Excel standard deviation function is also useful. 5. Correlation correlation matrix between the individual stocks. You will need to have the statistics package loaded. You can do this on the primary tab where you save and print files. At the bottom of the tab you have way to install new options. Section 8-3 “Portfolio Risk discusses correlation coefficients. These are important to portfolio diversification. Correlation coefficients range from -1 to 1. A value of 1 means that your stock moves reacts exactly as the other stock that you are comparing. This means that there is no benefit to portfolio diversification. A value of -1 means that the two stocks move in opposite directions. When one is increasing in price, the other stock loses value by the same amount. This means that the value of your portfolio will not change in value due to individual changes in the stocks. Only the general trend of the market determines the overall value of your portfolio. This is complete diversification. Do not get too excited, in normal times, it is extremely rate to have a value of -1, so you will be dealing with partial diversification and have to make decisions about the amount of risk that you can tolerate and the rate of return in the market. Financial Ratios 1. You should create at least three years of financial ratios and include them in a separate tab in Excel. Five years would be optimal. You may find other ratios and should include the ones you think are important. You should discuss how the ratios have changed over time. Are they getting better or worse. 2. Section 4-9 discusses the importance of benchmarking the financial ratios of your firms to the industry average. Try to compare your firm to other firms in the industry for the latest year available. Paper Write-Up. 1. Write and introduction telling me what you did and why it is important. Two paragraphs. 2. Discuss what happened to your individual stocks. The differences in individual performance should be interesting. This is the average rate of return of your stock. 3. How did your portfolio perform? 4. How risky were the stocks that you picked? This is the standard deviation of each stock. 5. How risky was your portfolio? Were you able to achieve portfolio diversification? In other words, is the standard deviation of your portfolio lower or higher than the individual stocks. 6. Discuss the financial ratios. Is the risk and return of your stocks related to the financial ratios? Can your firms pay their bills? Are there opportunities of your stocks fro the future? 7. Write a conclusion. Two paragraphs.