I have a team assignment due and my teammates didn’t do their part.

Abstract

This document outlines what you need to do to complete Assignment 1 successfully.  Please read it carefully and thoroughly. Ask your professor if you have questions.

Assignment 1: Chapters 5 & 6 Team Assignment

The objectives of this assignment are for you to “Model and consider risk and return in the context of modern portfolio and capital market theory and practice” and “Understand how to approach and solve financial problems” (Syllabus, 2008). You will also work in teams to complete this project, so before you begin, you should download the Peer Evaluation Workbook (2008) to understand how you will grade your peers and vice versa.

Work problems 5-16 and 5-17 on pp. 187-189 of Corporate Finance (Smart, Megginson & Gitman, 2010, 3^dedn).  Refer to Chapter 6 and class discussion as needed to understand beta.

Do all of the work for this assignment within one Excel workbook per team. Make it very clear where your answers are. DO NOT put everything in one worksheet within the workbook, but instead organize you work so that I can find your answers and check your inputs easily.  Present your work in a manner befitting your effort, that is, make it right but also make it easy for me to find what I need to find to check your work.

5-16

Your professor can require that you use different companies from those listed in the book, otherwise use PMC Sierra and Broadcom. Also, use the start date of the first trading day in December 2002 and the end date of the first trading day in December 2007 for 61 months of price data (they use only 37 in the book, this needs to be changed). Follow the instructions in the problem to calculate 60 months of return data.

You should use IBM and Coca-Cola (KO) for this assignment.

A. Using the series of adjusted closing priced calculate the return on IBM and Coke for each month.

B. Using the AVG excel function calculate the average monthly return

C. What is the Geometric average monthly return

D. Using the functions var and stdev in excel calculate the variance and standard deviation of monthly returns

E. Calculate the covariance and correlation coefficient between the two stocks. Is this surprising or expected

F. Imagine you constructed a portfolio equally invested I the two stocks calculate the return for this portfolio would have earned using this equation Monthly return = .50 IBM return+.50 Coke return. Then use excel to calculate the average value of the monthly return series constructed. What is the average monthly portfolio return?

G. Calculate the portfolios return using the following formula E (R_p)= W₁ E (R₁)+ W₂E (R₂) let w1=w2=.50 Use your average monthly returns from B and C then verify this matches the answer for F

H. Use stdev to calculate the std devation of the monthly returns on the 50/50 portfolio. Next calculate the portfolios std deviation using the general formula for two asset portfolio variance

5-17

Get 61 months of data as in the previous question, even though the book requires a shorter dataset. Answer all parts in the workbook. Provide explanations on a clearly market sheet. Do not submit a word doc. Be sure to answer all questions thoroughly and thoughtfully.  You may answer them separately or you may tie them together in one essay. You do not have to conform to APA standards to answer these questions since you are writing them within a workbook. Do not insert a Microsoft Word document into Excel for your answers. Doing this makes it difficult to grade, so just start typing your answers in Excel. You can format your answers to make it easy to read and grade, however, so please do so. Please show all answers only for the different parts of the two questions on the first sheet of Excel, with links to their detailed calculations in other sheets.

You should use HPQ and CSCO for this part.

A.    Calculate the avg monthly return, variance, and std deviation for each of the 2 stocks

B.     Calculate the covariance and correlation coefficient between the two stocks. Are the two stocks strongly or weakly correlated.

C.     Calculate the return that a portfolio split 50/50 would do and the monthly average return and standard deviation

D.    How does the avg portfolio return compare to the avg return of the individual stocks

E.     How does the portfolio std deviation compare to the std deviation on the individual stocks how is this related to the correlation of the 2 stocks

You should be sure to include ALL references you used in completing this assignment in a references section of your workbook and refer to them appropriately in the body of your answers.

Additional part b2 for Q5-17 only

Estimate the betas of the two companies using the data you have collected for each of the two companies. Before you do that, however, you will have to repeat the exercise of retrieving prices for the S&P500. Its ticker symbol is ^GSPC. Calculate the 60 months of returns for this stock market index.

The return data you calculated for the S&P500 is the x-variable in each of the two regressions.  You do not have to do a full regression.  Instead, you may use the SLOPE or REGRESSION function in Excel.  The return data for the two companies is the y-variable for each of the two regressions.  Think about this logically: the S&P500 is a market indicator series and the whole idea behind beta is to consider how the returns of the S&P500 (the x- or independent variable) affect the returns of the individual companies (the y- or dependent variable).

Beta is just the slope of a regression of individual company stock returns versus those of the market.

Compare your betas to betas you can find online. Tell me why you think they differ if they do.

Grading

See Table 1 at the end of this document for the grading rubric for this assignment. Although the writing assignment portion is somewhat small, please refer to the Holistic Scoring Guide for Written Work from theSyllabus (2010).  Please read it before, during and after you complete this assignment and before you turn it in.

  Your team will receive a zero with no chance for resubmitting this assignment if you turn in sloppy or inadequately referenced work or incorrectly formatted work.

Each team member will turn in a peer evaluation separately according to your professor’s instructions.  If you do not turn in your evaluation of your peers on time or if you do not use the template provided, you will lose the 5% peer evaluation portion of your grade.  Use the Peer Evaluation Workbook provided for you in Assignments in Blackboard or follow your professor’s instructions if different.  By the way, you must evaluate yourself as well as a teammate in the peer evaluation workbook and if you neglect to do so, you will lose the 5% peer evaluation portion of your grade. Finally, not everyone is a perfect teammate. You should rank your teammates and give the best teammate the best scores, not necessarily perfect scores, by the way. You are providing your teammates with valuable information through your aggregated evaluations of your teammates (no one sees the score that you give them but they do see an aggregated value from all teammates).  Team members turning in scores of perfect for everyone will lose half of their peer evaluation grade automatically.

You must also include the following honor pledge within the workbook that your team submits at the bottom:

By submitting this paper for a grade either hard copy or via BB, we are affirming that we have neither given nor received inappropriate assistance on this paper nor do we know of anyone else having done so.

Deadline

This is a team assignment, so each team should turn in one Excel workbook by the deadline in the tentative schedule posted in the Course Information area of Blackboard according to your professor’s instructions. Each team member will turn in a peer evaluation separately by the same deadline. 

References

American Psychological Association.  Publication Manual of the American Psychological Association (5^th ed.).  (2001). Washington, D.C.: American Psychological Association.

Peer Evaluation Workbook (2010). At http://blackboard.stedwards.edu in Assignments.

Graham, J.R., Smart, S. B., & Megginson, W. L.. (2010).  Corporate Finance. ^3d edn., Mason, OH: South-Western, a division of Thomson Learning.

Syllabus (2010).  At http://blackboard.stedwards.edu in Course Information.

Tentative Schedule (2010).  At http://blackboard.stedwards.edu in Course Information.

White, E.M. (1988).  Teaching and Assessing Writing.  San Francisco: Jossey-Bass.

Writing Resources. (n.d.)  http://www.stedwards.edu/business/graduate/current/apa.htm .