TERM PROJECT
Choose TWO publicly trade stocks in S&P 500 in the same industry (e.g. Home Depot
vs. Lowes) then estimate their market risk (i.e. Beta coefficient, the slope of the
characteristic line by simple linear regression), and calculate their interval estimations for
return. Please submit the Excel file by following the steps:
1. Gather the data from Yahoo:
a. Go to Yahoo Finance Get Quotes
(https://finance.yahoo.com/q/?bypass=true&ltr=1).
b. Enter the symbol of the stock (e.g. IBM) then click Look Up.
c. On the vertical bar, click on Historical Data.
d. Fill up the Time Period (1 year up to today), show Historical Prices, and choose Weekly
under Frequency, then click Apply. You will see weekly stock prices for the company.
Click Download Data then save it as Excel file to your computer. Please trim the data
(e.g. eliminate the other columns and only use Close Prices as the stock prices). Then
change the ticker symbol ^GSPC (for S&P 500 index) and download the one year weekly
data for S&P 500. Again, use Close Prices as ^GSC prices for your calculations. 2. In the same Excel file, list the stock prices (with label on the top cell) in column A and
S&P 500 prices in column B. Using weekly data in one year, you would have 53 rows in
column A and B.
3. Estimate the return of the stock under consideration (IBM) and the return of the S&P500
by creating the following formula in column C and column D:
Return = (Pt - Pt-1)/ Pt-1
Remember, when you create formula in Excel, you have to define the cell clearly. For
example, if the price in column A is descending in date, then the formula of return
will be = ($A2$A3)/$A3, which is created in cell $C2. Scroll it down by holding at
the right-bottom corner to create the return for the entire column C.
4. Then, run a simple linear regression. The dependent variable (Y) would be returns of
stock (column C) and the independent variable (X) would be the returns of the SP500
(column D).
5. Show the scatter diagram with clear titles of both X and Y and show the trend line.
6. The slope of the regression line is the Beta () coefficient of the stock. Show it clearly
in answer and comment on the simple linear regression given the testing output. (i.e.
whether it is a good fit; how the independent variable is related to the dependent variable;
significant or not in t-test). The minimum R2 for each stock is at least 0.20 (20%). If the company you choose cannot reach the minimum standard,
please try another company.
7. Calculate the interval estimate of return for the company at a 95% confidence level. You
need to run “descriptive statistics” of column C and show the descriptive statistics output.
Then apply the return’s sample mean and sample standard deviation to construct the 95%
confidence level estimation. (Remember, the unit of rate of return should be percentage.)
8. Repeat step 1-7 for another stock in the same industry you choose.
9. Compare the two stocks’ beta () coefficients then conclude which one is riskier in
market [i.e. By definition, the market (S&P 500) itself has an underlying beta of 1.0, and
individual stocks are ranked according to how much they deviate from the macro market.
A stock that swings more than the market (i.e. more volatile) over time has a beta above
1.0. If a stock moves less than the market, the stock's beta is less than 1.0.].
10. Compare the two stock’s interval estimation of returns. Which stock has a wider range of
rate of return?
11. As an investor, which stock will you choose to buy and hold, given your answers in 9 and
10? Briefly explain why. Grading Target
Retrieved Data Format
Rate of Return Calculation
Scatter Diagram
Beta ()
Required R2 and Comment on Testing
Interval Estimate
Comment on Comparison
Reason for Preferred Stock Choice
Total Score
10
10
20
10
10
20
10
10
100 Limit your term project within 10 pages including the data spreadsheet.

• 1493340205-Project.docx

SuperTutor

(15)

Status NEW Posted 28 Apr 2017 12:04 AM My Price 13.00

-----------

Attachments

file 1493340209-Solutions file.docx preview (56 words )

S-----------olu-----------tio-----------ns -----------fil-----------e -----------Hel-----------lo -----------Sir-----------/Ma-----------dam----------- T-----------han-----------k y-----------ou -----------for----------- yo-----------ur -----------int-----------ere-----------st -----------and----------- bu-----------yin-----------g m-----------y p-----------ost-----------ed -----------sol-----------uti-----------on.----------- Pl-----------eas-----------e p-----------ing----------- me----------- on----------- ch-----------at -----------I a-----------m o-----------nli-----------ne -----------or -----------inb-----------ox -----------me -----------a m-----------ess-----------age----------- I -----------wil-----------l b-----------e q-----------uic-----------kly----------- on-----------lin-----------e a-----------nd -----------giv-----------e y-----------ou -----------exa-----------ct -----------fil-----------e a-----------nd -----------the----------- sa-----------me -----------fil-----------e i-----------s a-----------lso----------- se-----------nt -----------to -----------you-----------r e-----------mai-----------l t-----------hat----------- is----------- re-----------gis-----------ter-----------ed -----------on�-----------�th-----------is -----------web-----------sit-----------e. ----------- H-----------YPE-----------RLI-----------NK -----------&qu-----------ot;-----------htt-----------p:/-----------/wo-----------rkb-----------ank-----------247-----------.co-----------m/&-----------quo-----------t; -----------\t -----------&qu-----------ot;-----------_bl-----------ank-----------&qu-----------ot;----------- -----------Tha-----------nk -----------you----------- -----------

Not Rated(0)

• Buy Answer