FIN 201 Financial Management Spring 2017 Risk Return Project

This Project is due in class on Thursday, April 6. Please submit the Excel file and submit a paper copy
of the first page of the Excel file and copies of your Value Line reports in class. A template for
completing this assignment is available on Blackboard.
This project was designed to give you some practical experience finding and using financial data as well
as giving you some additional insights into the topic of risk and return. This is an individual project.
However, you are free to discuss the project with other class members; which is to say, you may talk
about how you did your analysis as well as give and receive advice on how to complete the project. You
may not, however, exchange spreadsheets or written materials. The analysis and the write-up must be
your own work product.
Step 1: Select 5 publicly traded firms from the list of firms included in the S&P 500 Index.
IMPORTANT: Each class member must choose different firms. The list will be available in
class for you to designate your selections.
Step 2: Research each of your firms and write a short paragraph explaining each firm’s business and
associated risks. Based only on its business and its stand-alone risk, would you consider each firm to be
highly risky, moderately risky or low risk? Why?
Step 3: Using Yahoo Finance (or some other financial website), download the last five years of weekly
(not daily) stock prices for each of your firms. For your analysis, you should use the “adjusted close”
prices as these include the effect of dividend payments. Use the “download to spreadsheet” function
at the bottom of the page to capture the information in a spreadsheet.
Step 4: For the same five year period, download the values of the S&P 500. Copy this to your excel file.
The ticker for the S&P 500 is ^GSPC.
Step 5: Complete the following table:
High over last 5
years
Firm 1 (name)
Firm 2
Firm 3
Firm 4
Firm 5
S&P 500 Low over past 5
years Return over past 5
years Standard Deviation
of Returns Step 6: Convert the adjusted prices into weekly returns. Do this for each of your five firms and for the
S&P 500 Index. Remember to use the adjusted close data.
ReturnWeek t= ( Adjusted Close Week t ? Adjusted Close Week t ?1)
Adjusted CloseWeek t?1 Step 7: Use the =SLOPE and =INTERCEPT formulas in Excel to estimate the parameters of the market model.
That is, estimate beta (?) for each company using the following equation where R M is the weekly return on the
S&P 500: R=? + ? R M + ?
IMPORTANT: Be very sure that the dates of your firms’ returns line up with the dates of the S&P 500
returns. Step 8: Download the Value Line Investment Survey Reports for each of your five firms and record
Value Line’s estimate of each firm’s beta. Also, research and record Yahoo Finance’s estimate of each
firm’s beta. Discuss any differences in the three estimates. Which of the three estimates do you feel is
most representative of the risk of each firm?
Step 9: Determine the current risk free rate (i.e., the return on 30 year US Treasury Securities).
Step 10: Complete the following table using the CAPM equation (Assume MRP = 5%): Calculated
Beta Value Line
Beta Yahoo
Finance
Beta Risk Free
Rate Required
Return
based on
Calculated
Beta Required
Return
based on
Value Line
Beta Required
Return
based on
Yahoo
Finance
Beta Firm 1
(Name)
Firm 2
Firm 3
Firm 4
Firm 5
Step 11: For each of the three sources of Beta, estimate the beta of a portfolio composed of your five
companies, equally weighted (i.e., 20% each). Estimate the required return of this portfolio.
Step 12: Conclude with a brief discussion about the appropriateness of your companies as potential
investments. Based on your quantitative analysis and considering each firm’s risk in a well-diversified
portfolio, would you now conclude that the firms were high risk, moderate risk or low risk investments,
compared to investing in the S&P 500 index?