In this special project you will be gathering
data on any company that you choose and creating a regression model using
Excel. You will be graded both on the correctness of your model and on your
comments about the Regression output.
Step 1: Gathering the data…
1. Navigate to Yahoo!
Finance and enter the ticker symbol for the stock of your choice.
2. Select “Historical
Prices” from the left navigation menu
3. The “End Date” should
be the current date, and the “Begin Date” should be exactly 5 years and 1 month
before your end date. You want 61 months of data to end up with 60 data points
(n=60)
4. Select “Monthly” on
the right and then select “Get Prices”
5. The right-most column
in the output is labeled “Adj Close*.” This is the adjusted closing price…but
adjusted for what. If you scroll down to the bottom of the table you will see
that the adjustment is for both dividends and stock splits. This is the data
you are looking for.
6. Select “Download to
Spreadsheet” just below the table of data.
7. All that you need to
retain is the date column and the adjusted closing
price data…save this to a file on your computer.
8. Navigate back to the
Yahoo! Finance homepage and click on the blue link for the S&P 500 Index.
9. Follow the same
process as steps 2-7 to gather the data on the benchmark.
10. Now enter the ticker
symbol “^TNX” which is the yield on the 10-year Treasury.
11. Follow the same
process as steps 2-7 to gather the data on Treasuries.
Step 2: Compile all of the relevant data in one
Excel Spreadsheet. (TIP:It works well to have all of the data on
Worksheet 1 and eventually your regression results on Worksheet 2).
1. Column A should be the
date.
2. Column B should be
your chosen stock’s adjusted monthly closing price.
3. Column C should be the
Holding Period Return for your chosen stock (TIP #3: HPR =
(Ending-Beginning)/Beginning.
4. Column D should be the
Excess Return of your chosen stock (HPR of stock – 10-Year Treasury Yield
[Column C – Column I]).
5. Column E should be
S&P 500 adjusted monthly closing data (this is ^GSPC).
6. Column F should be the
Holding Period Return for the S&P 500.
7. Column G should be the
Excess Return of the S&P.
8. Column H should be the
10-year Treasury Yield data (this is ^TNX)
9. Column I should be the
Holding Period Return for the 10-year Treasury Yield
Step 3: Regression Analysis
1. In Excel, look under
the “Data” menu in the “Analysis” section, on the far right, for “Data
Analysis.”
2. If you do not see this
then you will need to google how to add the “Analysis Tool Pak” for Excel.
3. If you are using a
Mac, there is a similar product, but I think that you need to buy it, so it
might be easier to simply use a PC for the regression portion of this project.
4. Once you are able to
select “Data Analysis”, select this feature and then choose “Regression” from
the pull down menu.
5. First, establish your
output range as cell A1 on your first empty Workbook (1 or 2).
6. Second, set your
“input Y” range as your stock’s excess return (Column D). Each column should
have a label in row 1. Include row 1 in the data range for each input.
7. Third, set your “input
X” range as the S&P 500 Index’s Excess Return (Column G). Again include row
1 in the range, which has your column label.
8. Check the box for
labels.
9. Run the regression
You should discuss what the specific numbers
you found through regression communicate about your data. Be sure to discuss:
1. The R2
2. The alpha
3. The beta
4. The relevant t-value
and p-value statistics.
Be specific. Also include at the top of your
paper (1) the SCL equation inferred by your regression, (2) the ticker symbol
for your stock, and (3) a screen shot of your regression results (the snipping
tool works very well).
Points awarded as follows: 2
points for properly identifying and discussing #1, 3, and 4 from the list
above; 1 point for properly identifying and discussing #2 from the list above;
1 point for proper specification of the regression formula; and 2 points for
conducting the regression process correctly.
Required Formatting:
You need to write a 1-2 page,
single-spacedpaper detailing what you have discovered. Your document
should be in Times New Roman, 12 point font with 1-inch margins. Type your name
and the assignment’s name in the Header (“Insert” tab, then select “Header” in
MS Word). Do not include a heading in the body of your text to fill
space…I will remove it when determining the length requirement.You
need to include 1 image (google “snipping tool” if you do not know how to use
it) showing your regression results. This image should be on the last
page of your paper and does not count towards the length requirement. Discuss
both what you have learned from this process and the
regression output itself.
Rubric
Revised Regression
Project
Revised
Regression Project
Criteria
Ratings
Pts
Discussion of R^2
Detailed
discussion of R^2 including actual regression value
2.0 pts
Generic
discussion of R^2
1.0 pts
Did
not discuss
0.0 pts
2.0 pts
Discussion of alpha coefficient
Detailed
discussion of the alpha coefficient including actual regression value
2.0 pts
Generic
discussion of the alpha coefficient
1.0 pts
Did
not discuss
0.0 pts
2.0 pts
Discussion of beta coefficient
Detailed
discussion of beta coefficient including actual regression value
2.0 pts
Generic
discussion of the beta coefficient
1.0 pts
Did
not discuss
0.0 pts
2.0 pts
Discussion of statistical
significance of alpha coefficient
Detailed
discussion of both p-value and t-stat for the alpha coefficient
1.0 pts
Only
discussed either the p-value or the t-stat for the alpha coefficient
0.5 pts
Generic
discussion / Did not discuss
0.0 pts
1.0 pts
Discussion of both p-value and
t-stat for the beta coefficient
Detailed
discussion of both p-value and t-stat for the beta coefficient
1.0 pts
Only
discussed either the p-value or the t-stat for the beta coefficient
0.5 pts
Generic
discussion / Did not discuss
0.0 pts
1.0 pts
Specification of the SCL
Correct
specification of the SCL including the error term.
2.0 pts
Correct
specification of the SCL WITHOUT the error term
1.5 pts
Missing
2 or more variables from the SCL
0.5 pts
Did
not specify the SCL
0.0 pts
2.0 pts
Total Points: 10.0
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more