Finance for Managers Project Description Project Introduction: Th...

Transcribed Text

Finance for Managers Project Description Project Introduction: This project will help you build an understanding of risk and return characteristics of common stocks and portfolios and using the CAPM. You will use actual historical data and statistical tools in MS Excel to calculate returns and measures of risk for single assets and portfolios and then draw conclusions from the results. In this project, you will use historical stock price and stock index data to calculate monthly returns and standard deviations for five common stocks and the S&P 500 stock index. Then, you will use MS Excel to calculate beta estimates for each stock and compare them with the beta estimates available on financial websites such as http://finance.yahoo.com. Next, you will use the beta estimates and portfolio weightings to construct two model hypothetical portfolios with different beta estimates. Finally, you will compare the average returns and standard deviations of the two portfolios to the returns on the stock index and discuss the following: • Systematic risk and unsystematic risk measurements of the portfolios • Performance of the portfolios vs. the stock index Course Objectives Tested: 4. Analyze risk and return and relate these concepts to a firm's cost of capital. 4.1: Calculate measures of risk and return for a single asset. 4.2: Apply the calculation of risk and return to portfolios and estimate a measure of correlation. 4.3: Differentiate between systematic and unsystematic risks. 4.4: Use the capital asset pricing model (CAPM) and security market line (SML) to estimate required returns. Project Submission Plan: Project Part Description/Requirements of Project Part Project Part 1 Tasks: 1. In MS Excel template that you downloaded previously, copy and paste the "Date" column from Closing Price Data_DJIA Companies.xls. Then, copy and paste the adjusted closing price data for each stock and the S&P index in Columns B to G, and label the columns appropriately. Tip: Ensure that the closing price for each stock aligns with the appropriate date in Column A. This is critical for the accuracy of this exercise. 2. In Columns H to M, calculate the monthly returns for each stock and the S&P Index by using the formula (Pn – Pn-1) / Pn-1. For example, if Row 2 shows the closing price data for 1/3/2011 and Row 3 shows the closing price data for 12/1/2010, then in Column H Row 2, the following formula will be used to calculate return for 1/3/2011: (B2 – B3) / B3 Copy and paste—or use "Fill Down"—this formula into each cell in the column to calculate the return for each of the 60 periods, noting that the return for 1/3/2006 cannot be calculated because there is no beginning price. Label each column in the first row to show each stock or the index returns in that column. 3. At the bottom of each column of returns, enter a formula to calculate the average return and the standard deviation of returns, using the following MS Excel functions: =Average(Col,Row2:Col,Row62) =StDev(Col,Row2:Col,Row62) 4. In the row below the standard deviation calculation, look up the beta estimate for each common stock on Yahoo! Finance by performing the following steps: i. Go to http://finance.yahoo.com and enter the ticker symbol or name of the company; for example, Alcoa has the ticker symbol "AA." ii. On the left-hand side of the company page, under Company, click Key Statistics, and locate the beta estimate at the top of the column located on the right-hand column. You may refer to Closing Price Data_DJIA Companies.xls to view an example of how beta estimates need to be populated. Note: Beta estimates are available on other websites as well, such as MSN Money at http://money.msn.com/investing/. 5. Submit your spreadsheet to your instructor for evaluation and feedback. Note: Do not proceed with Parts 2 and 3 of the project until your instructor reviews and approves Part 1. Your spreadsheet at this point should include: S&P index and five companies' average returns, standard deviations, and beta estimates from online sources.

Solution Preview

These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice. Unethical use is strictly forbidden.

By purchasing this solution you'll be able to access the following files:
Solution.xlsx.

\$60.00
for this solution

PayPal, G Pay, ApplePay, Amazon Pay, and all major credit cards accepted.

Find A Tutor

View available Finance Tutors

Get College Homework Help.

Are you sure you don't want to upload any files?

Fast tutor response requires as much info as possible.