Your task:
Gather data.
In this exercise, you will be using the monthly return data that you calculated for your stock and for the S&P500 in your Stock Prices and Returns assignment. You should have monthly return data for your company and for the S&P500 from June 1, 2009 and ending May 31, 2014.
Manipulate data.
There are several methods for calculating a regression in Excel. For this assignment, we will use the charting capability to calculate beta for our stock. You will begin by choosing the "insert" option above the Excel spreadsheet. You will choose "chart" and then "XY (scatter)" for the type of chart. The chart wizard will guide you through the process.
You will want to choose the "series" option to tell Excel what data you want to use. Be sure to use your S&P500 return column for your "X value" and your stock return column for your "Y value".
This process will give you a scatter graph that plots your stock's return and the S&P500 return. However, it does not give you a regression line or slope. To get a regression line, you will need to click your left mouse key to make several of the data points turn yellow. With your mouse pointing to one of these yellow data points, right click. Choose "add trendline". We will use the default (linear) trend line. However, you will want to choose "options" so that you can place "display equation on chart".
You may find that your regression equation is difficult to read if it is placed over the data points. You can move this equation to the side of the graph (it is in a text box) so that it will be easier to read.
Make sure that you have properly labeled your graph and that your graph has a title.
Make observations.
- Using the beta you calculated, explain how risky your stock is relative to the S&P500.
- Market (aka systematic or non-diversifiable) risk only
- Does your stock exhibit more or less systematic risk than the S&P500?
- What does your beta imply for expected returns?
Formatting.
Results should be presented in memo format. Observations should be written in complete sentences in paragraph form. The assignment will be turned in as a hard copy in your Lab on the assigned due date. Be sure to proofread your work. Your paper should be free of typos and grammatical errors and presented professionally. Your future boss will expect professional quality work and will not waste time trying to decipher your writing.
Looking Forward
This will be part of your company analysis that you will complete later this semester. Be sure to keep your electronic files so that you do not have to recreate the work that you have done.