Select historical prices from the left navigation menu

Reference no: EM13970000

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 reliability of the 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 before your end date.
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). You will have 60 months worth of data and 59 HPRs.
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 Adjusted R2
2. The standard error of the regression
3. Both coefficients
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).

Watch the video below for help with this project.

https://notorious-prod.s3.amazonaws.com/originals/o-5wixKXC7yPTnhunue8JQPTyt8hy7buFD/transcodings/t-5xk2vSgBqejWz9zW9jjDob9SYvKvZMVu.mp4?AWSAccessKeyId=AKIAIEH4LE5KKJ5TP42A&Signature=jzOL3kLIFKWhqXvCedTpoQtad3M%3D&Expires=1455156331

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-spaced paper 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 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.

Reference no: EM13970000

Questions Cloud

Analyze the key types of policies required for the delivery : Defend or critique the primary alternative sources of healthcare funding in the United States - Analyze the key types of policies required for the delivery of a public health insurance program, and hypothesize their main effects on the achievement ..

What problems do you see with shared decision making : What will it mean for health care organizations and its employees? Good or bad?? Why? What is shared decision-making? Is this good for all concerned? What problems do you see with shared decision making?

Evaluate efficacy of major types of health clinical outcomes : Evaluate the efficacy of major types of health clinical outcomes one can use in economic evaluation analysis - determine the primary ways in which an established market for health insurance can be efficient, especially when individuals carry less th..

Identify possible target markets : Identify possible target markets and Discuss the variables of the marketing mix (product with pictures, price, promotion, and distribution) for the following products: Jonson & Jonson Baby Oil

Select historical prices from the left navigation menu : 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 reliability of the model and on your comments about the Regression output.

Brief description of the product and service : Project Proposal: The project proposal is a two-page document, and will outline the new product, service or concept you plan to launch. List of group members and Brief description of the product / service / concept you plan to launch

Different roles of law enforcement in american society : Define the different roles of law enforcement in American society - examine the field as it pertains to job opportunities and movement within the field.

Major aspects of industrialization between 1865 and 1920. : Summarize how industrialization affected the life of the average working American during this period. Use specific examples and details.

What is the us olympic committee largest expense : What is the U.S. Olympic Committee's largest expense? Currently, how many states do not permit paid daily fantasy games

User Account

All Pages