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

 Assignment 1: Chapters 5 & 6 Team Assignment St. Edward’s University FINC 6301

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, 3d edn).  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 (Rp)= W1 E (R1)+ W2E (R2) 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

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.

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 the Syllabus (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.

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.

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 (5th ed.).  (2001). Washington, D.C.: American Psychological Association.

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.