only for accountguru

FIN3014—PRINCIPLES OF BUSINESS FINANCE LAB

Beta Calculation Assignment

Due Date: November 14

No late assignments will be accepted for credit. Be sure to save your work. We will be using this project as part of our final project at the end of the semester. Completing this assignment gives you the opportunity to receive valuable feedback for completing future assignments.

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)

· Interpret and present results

· Construct a professional memo

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 October 1, 2009 and ending September 31, 2014 (from project 3).

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?

· Compare the beta that you calculate with the beta that is provided on the http://finance.yahoo.com website for your stock.

· Why might the two estimates differ?

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.

Grading

Format: 20 points – Professional memo format. Your name, company name, and indication of content contained are to be presented in the header. Analysis should be in paragraph form and the figure should be below the analysis, included in-line (not printed separately from excel and stapled).

Figure: 60 points – All graphs should have all axes labeled appropriately, a title, and should be presented neatly (without overlapping components). This also includes correct data, type of graph/chart, and all required additional information (such as legends or trendlines, when necessary).

Analysis: 20 points – See “Make Observations” section above. Analysis should be written in complete sentences and include, at a minimum, answers to the questions asked above. This is one of the most important parts of the final project.