finance homework for DENNISWRIGHT

FIN3014—PRINCIPLES OF BUSINESS FINANCE LAB

Beta Calculation Assignment

Due Date: Nov 17th or Nov 19 in Lab

No late assignments will be accepted. This assignment will build toward a larger assignment at the end of the semester. Turning in this assignment gives you the opportunity to receive valuable feedback for completing future assignments. Be sure to save your work.

Goals for this assignment:

· Gather historical return data

· Use excel to plot a scatter graph

· Use excel to calculate beta for your stock (the slope of the regression line)

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 January 2012 through December 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 your mouse. 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.

Using the beta you calculated, explain how risky your stock is relative to the S&P500. Compare the beta that you calculate with the beta that is provided on the http://finance.yahoo.com website (or some alternative source) for your stock. If they differ, why do you think this might be the case?