Purpose:
1. To attain further understanding of concepts and processes learned in class through practical application.
2. To increase skill with MS Excel®.
3. To become familiar with some of the fundamentals of financial research.
Requirement:
Choose eighteen stocks listed on the NYSE and analyze the stand-alone risk and return characteristics of each stock. Build portfolios comprised of these stocks and analyze the risk and return characteristics of the portfolios.
Specific Tasks:
1. Choose 18 companies traded on the NYSE.
2. For each stock, find the monthly closing prices for 61 months. The time frame you will use is 4 Feb 2015 through 4 Feb 2020. These data are easily found at Yahoo! Finance (http://finance.yahoo.com) [For reference go to Risk and Return Calculation Videos Posted on Learn, both explanation and excel example]. In Yahoo Finance enter a company’s stock symbol in the search field. Once you arrive at the company’s “Summary” page, click on the “Historical Data” link. Enter the required date range (4 Feb 2015 through 4 Feb 2020.) and ensure that you have selected “monthly” then click on “Apply”. Click on “Download Data”. Copy and paste information in the “Date” and “Adj. Close” columns into the appropriate columns on the worksheet in the workbook I have provided you (see
Format
below). When you paste, chose the “123” option. The other data columns are not needed]. Sort these data from “oldest to newest”. Ensure that you provide the name of the company and its stock symbol in the appropriate cells at the top of the worksheet.
3.
- Find the average realized rate of rate of return (rs) over 60 months for each stock. Do this by computing the realized return for each month then finding the average of all 60 monthly returns. The return for each month is found by using the equation: (Ending Price – Beginning Price) / Beginning Price.
- Translate the average monthly realized return to an annualized realized return by multiplying the number you arrived at in part a. by 12.
4.
- Find the standard deviation (s) for the 60 monthly realized returns. Use the standard deviation function to do this.
- Translate this number into an annualized standard deviation by multiplying it by the square root of 12 (use the SQRT function).
5. Compute the coefficient of variation for each stock using average annualized realized returns from 3b. and the annualized s’s from 4b. above.
6. Establish a portfolio comprised of four of your stocks. The share allocation is: 300 shares, 350 shares, 400 shares and 450 shares. You decide which stocks receive a specific share allocation.
- Compute the expected portfolio ROR, E(rp). Use the annualized realized returns computed in 3b as the expected/required RORs for each stock.
- Repeat 6a. for a portfolio with all 18 of your stocks. The share allocation is 100 shares for each stock. You may choose to simply add the table for the 18 stock portfolio to the provided 4-stock portfolio worksheet or copy this worksheet and expand it for the 18-stock portfolio.
7.
a. Plot the price history of your four stocks from Task 6 for the 5 year period and the price history of your 4-stock portfolio for the same period. The portfolio value is the weighted average of the individual stock prices. You will use weight by share, not weight by value (pay attention to the note on the
Task 7
Data
worksheet). Ensure that the names of the companies appear at the top of each column instead of the generic labels (Stock1,Stock 2, etc.)
b. Repeat 7a. for all 18 stocks. You may have to copy the worksheets for the 4-stock portfolio and expand them. You may have to use multiple colors and line formats on your chart for clarity.
8. Find a published b (Beta) for each of your stocks. These can be found at MSN Money (http://moneycentral.msn.com/home.asp) or Yahoo! Finance (http://finance.yahoo.com). Compute rs
for each stock using the CAPM formula. Use 1.3500% for rRF
and 11.20% for rM. These are the average returns for 30-day T-bills and the S&P 500 (NYSE) respectively, over the period.
9. Compute the Beta(bp) of both your 4-stock portfolio and your 18-stock portfolio. Compute rp
of both portfolios using the CAPM formula, incorporating your bp. Use weights by value as in Task 6.
10.
Report
and discuss the observations and conclusions you should make from performing the above tasks. They should be aligned with concepts presented in class from Chapters 11 and 12. Discussion topics should include but are not limited to:
- Why the rs’s you computed in Task 3b and rs’s you computed in Task 8 are different. If they are not very different, discuss why you think that may be so.
- Compare the expected rp’s you computed in Task 6 and the rp’s you computed in Task 9. You should see that the 18-stock portfolio rp
you computed in Task 6 and the 18-stock portfolio rp
you computed in Task 9 are nearer in value than is the case for the 4-stock portfolio. Discuss why this may be so. If they are not similar, discuss why this may be so
- The risk/return characteristics of your stock portfolios (both the 4-stock and 18-stock portfolios) relative to the individual risk/return characteristics of your stocks. The CV from Task 5 and the charts you created in Task 7 should help you do this.
Format:
1. All work must be produced and presented in Excel®
using a file entitled
Project1 Workbook – Student(s) Name and Last Name(s).xls
that I have provided. This file is a template for you to fill-in and adjust accordingly. Ensure the title page includes your name(s),
your MGT 326 section(s)
and the names of the companies you have analyzed. This file also has some minor additional guidance on how to complete some of the tasks.
2. Order of presentation: Title Page, all Tasks 2-5 worksheets, all Task 6 worksheets; Task 7 Chart (4 Stock), Task 7 Chart (18 Stock), Task 7 Data (4 Stock), Task 7 Data (18 Stock), Task 8 & 9 worksheet, Task 10.
3. Do not submit your work in binders or folders. Simply staple your pages together.
Grading:
1. 70% of your grade will be based on completeness, correctness and professional appearance of all tasks. Incomplete tasks will receive substantial point penalties and may even cause your project to receive a score of zero.
2. The remaining 30% will be based on the scope, depth and accuracy of your observations and conclusions from Task 10.
3. Substantial penalties will be imposed for missing or incomplete work.
4. Ensure that your project is professional in appearance. Ensure that all worksheets are labeled correctly.
5. This assignment is due 11:59 pm, Wednesday, April 11. Use the drop box provided on Learn in the Week 13 Folder under Stock Project
Additional Guidance:
1. You may work individually or with a (one) partner. If you choose to do the latter, submit your work jointly as a single document. Make sure to include both your last names on the Excel File name.
2. A good source for identifying which industry a particular firm is in can be found at http://siccode.com. Industries are classified by the Standard Industrial Code (SIC). To find a list of companies listed on the S&P 500 and their associated industry, search “standard and poor’s company listing” and click on the Wikipedia link.
3. Do not choose firms that have overall negative stock growth for the years in question.
4. Do not choose stocks that are priced excessively high (i.e. above $250) or excessively low (i.e. below $15) consistently through the period in question.
Note on Academic Integrity:
I expect each student or pair of students to do original work and to document all sources. Any incidence of copying the work of others, plagiarism from published works, or any other violation as outlined in the
Student Guide
concerning academic integrity, will result in your dismissal from this course with a failing grade and referral to the ASM Associate Dean for further disciplinary action. This includes using project files from previous semesters, i.e. do not use a file from a student who took this course in a previous semester.