Solved: Creating a company portfolio

Portfolio Project #1
Create a portfolio by selecting one stock for each sector (see attached for sector guidelines). Please make sure the company meets the following criteria:

1. Clearly belongs in the sector.
2. At start, Market Capitalization over $1.0 billion
3. 5 Years of pricing history (starting Dec 2001).
   Gather five years of historical pricing for each issue (monthly observations*). Calculate the following for each issue. Ignore dividends. See attached format.
4. Monthly and annual geometric average return.
5. Total return for the holding period.
6. Total risk, (standard deviation) of monthly and annual returns.
7. Coefficient of variation
8. Correlation with the market
   Obtain the monthly prices and calculate the same summary statistics for the S&P 500.
   Also, ignore dividends.
   Equally weight the ten positions. Calculate total portfolio summary statistics including portfolio beta and alpha. Create a correlation matrix. Include all holdings, the market and portfolio returns.
   Additionally, calculate tracking error (TE), tracking error risk (TEσ) for annual and monthly frequency. Calculate IR, which we define as TE/TEσ. Note you will need to apply an array function to your calculation of TE and TSσ.
   Answer the following questions.
9. How does your portfolio compare to your individual holdings? Was
   diversification helpful? Successful?
10. What does Tracking Error and Risk imply and how can it be useful for a
    manager or a fund sponsor. Likewise does the IR add any additional value in terms of what TE or TE Risk explain?
11. Knowing what you now know, i.e. returns, risks, how would you change your allocation to improve your portfolio's risk and return? Calculate summary and portfolio statistics with your changes incorporated.
12. Did your changes increase return? Reduce risk? Are you comfortable with the results?
13. If you expected the economy to slow what sectors would you emphasize and why? If you expected a robust economy what sectors would you emphasize and why?
14. Does the correlation matrix help form any opinion on how to weight your stocks or how you might want to change weights going forward?

Portfolio Project #1 - continued
Furthermore,

7. Download prices for the period 05/31/2007 to 06/30/2010. Calculate
   portfolio statistics. Apply the weights from the changes you recommended from 2 above. Are these the results you were expecting? Examine the correlation matrix. Does the matrix provide guidance as to weightings?
   Deliverables:
   It is essential that the following be delivered, exactly as described and demonstrated in the attached document.
8. For Scenario #1, 10 pages of documentation. This includes summary sheet, prices, returns, performance graph, and correlation matrix.
9. For Scenario #2, only a summary sheet and performance graph is required. Additional documents are discouraged and tend to make me emotional - no additional documentation.
10. For Scenario #3, only a summary sheet, performance graph and correlation matrix is required. No additional documentation.
11. Seven questions will be answered on a separate sheet(s). A maximum of five (5) pages will be used to answer the questions.
12. Therefore, there will be 15 sheets of data and graphs for the three scenarios, plus up to a maximum of 5 pages for narrative.
13. Please add a cover sheet for a maximum of 21 pages.
14. Any deviation from the above will result in an immediate reduction points from your grade. The number of points reduced will depend on my emotional state.
    Grading:
15. Understanding and application of theory as demonstrated in your narratives. (70%)
16. Correctness in calculations (20%)
17. Management, organization and clarity of your model (10%)
18. Performance is not an issue. This is not an exam to test your stock selection skills.