FIN 5130
Instructor: Dr. Palkar
Mini-Case 1
Late assignments will not be accepted so please plan
accordingly. You need to show your working notes for credit. You must submit
your work using excel files (with
.xls or .xlsx for credit). You must upload your files on
Blackboard under the Assignment Dropbox for credit.
This assignment will require you to analyze time series of
monthly returns. Start by retrieving MONTHLY data for the period of 09/01/2013
– 08/31/2017 from Yahoo website for
? S&P 500 Index
(ticker: ^GSPC)
? Verizon Communications Inc. (ticker: VZ) ? Chevron
Corporation (ticker: CVX)
? Pfizer Inc.
(ticker: PFE)
Instructions for downloading the data from Yahoo! Website
(https://finance.yahoo.com/): To obtain the monthly data for each company, on
Yahoo! Finance website, enter the ticker symbol under Quote Lookup. Then, click
on “Historical Data”. Enter “Time Period” as given above. For “Frequency”, make
sure Monthly is selected and then click on “Apply”. Click on “Download Data”.
Saving your final file:
Keep only Date and Adj Close columns for each company. Put
all three sets of data in one excel file to do further analysis.
Very important: Save your final file as a .xls or .xlsx file
before you start the computations. Comma delimited (.csv) files do not retain
the formulae and cell references after closing the file. You will receive a
grade of zero if your file does not contain cell references and formulae to
show how you arrived at the various answers.
Calculating Returns:
Use the ‘Adj Close’ column to obtain returns for each
period. Remember that the Adjusted Close column has already adjusted the prices
for dividends and stock splits so you do not have to adjust for it again. Just
use the adjusted close column to obtain the return for each month t as follows:
Rt = Adjusted Closet
?1
Adjusted Closet?1
Solve for the following:
A. What is the
average return and standard deviation of returns for (i) S&P 500, (ii)
Verizon, (iii) Chevron, and (iv) Pfizer. Comment on what you find.
B. Calculate the
covariance and the correlation coefficient of returns between (i) Verizon and
Chevron, (ii) Verizon and Pfizer, and (iii) Chevron and Pfizer. Comment on the
statistics.
Page 1 of 2
FIN 5130
Instructor: Dr. Palkar
Mini-Case 1
C. Calculate the
stock betas: (i) Calculate Verizon’s beta for the 09/01/2013 – 08/31/2017
period, (ii) Calculate Chevron’s beta for the 09/01/2013 – 08/31/2017 period,
and (iii) Calculate Pfizer’s beta for the 09/01/2013 – 08/31/2017 period.
Comment on the betas.
D. If you were to
form a portfolio that had 34% of S&P, 33% of Verizon, and 33% of Chevron,
what would be the average return and the standard deviation of returns for your
portfolio? (Ignore the fact that both Verizon and Chevron may already be
included in the S&P 500)
E. If you were to
add Pfizer to your portfolio so that you now had 25% S&P, 25% Verizon, 25%
Chevron, and 25% Pfizer, what would be the new average return and standard
deviation of returns for your portfolio? Is Pfizer a good addition to your
portfolio? Why do you think so? (Ignore the fact that Verizon, Chevron, and
Pfizer may already be included in the S&P 500)
ScholarMatic: Explanation & Answer
Your ready answer from a verified tutor is just a click away for as little as $14.99
Click Order Now to get 100% Original Answer Customized to your instructions!
