FIN 340 Module Two Activity Guidelines and Rubric

Overview

This activity will help you gather and analyze data about returns and standard deviations.

Prompt

Use Morningstar and the Module Two Activity Instructions to complete this activity.

Specifically, the following critical elements must be addressed:

1. Calculate the standard deviation for the 10-year period for the S&P 500 ETF SPY, Coca-Cola, and Netflix. Support each calculation by showing the work involved.
2. Calculate the annualized return for the 10-year period. Support each calculation by showing the work involved.
3. Compare the differences in returns and standard deviations of the three sets of data. Based on your analysis, explain which one of the three investment products you would choose to invest in and why.

What to Submit

You must submit a completed Excel spreadsheet that fulfills the requirements outlined in the Module Two Activity Instructions document.

FIN 340 Module Two Activity Instructions Instructions 1. Open a new Excel workbook in which you can aggregate all of the data and make the necessary calculations.

2. Go to Morningstar (not investor.morningstar.com). Complete the following steps for KO, NFLX, and SPY: A. At the top left of the screen, enter the company stock ticker. i. For Coca-Cola, search KO. Click the linked title Coca-Cola Co. Then click on Price vs Fair Value. ii. For Netflix, search NFLX. Click the linked title Netflix Inc. Then click on Price vs Fair Value. iii. For SPY, search SPY. Then click the linked title SPDR S&P 500 ETF Trust. Click on Quote.

3. Open an Excel spreadsheet.

4. Create a table. A. Title the table Annual Total Return. B. Create four columns. From left to right, the columns should be titled Year, KO, NFLX, SPY. C. In the Year column, enter the past 10 years in ascending order.

5. Below the table, skip one row. Then add a new table with two rows with the following titles: A. AVG (for average annual return) B. SD (for standard deviation)

6. Copy data from Morningstar to the Excel table. A. For KO, NFLX, and SPY, enter the return data for each of the past 10 years next to the corresponding year in the table. B. Calculate the following: i. The average annual return for the past 10 years ii. The standard deviation for the past 10 years C. Enter the average annual return for each stock into the row of the lower table titled AVG. D. Enter the standard deviation for each stock into the row of the lower table titled SD.

Statistic Excel Functions

Average Annual Return: AVERAGE Annual Standard Deviation: STDEV.S