Learning Objectives: Calculate market betas using monthly returns

1. You need to show your working notes for credit. You must submit your work using 1 Excel file (.xls or .xlsx).

1. Retrieve monthly data for the period of 01/01/2012 - 12/31/2017 from Yahoo Finance for

1. S&P 500 Index (ticker: ^GSPC)
2. Apple Inc. (ticker: AAPL)
3. Procter & Gamble
4. Disney (ticker: DIS)
5. Ford (ticker: F)

Instructions

1. Yahoo Finance address: https://finance.yahoo.com
2. To obtain the monthly data for each company, on Yahoo! Finance website, enter the ticker symbol in the companies search bar. Then, click on "Historical Data". Enter "Time Period" as given above. For "Frequency", make sure "Monthly" is selected and then click on "Apply". Click on "Download Data". Your computer will download a .csv file with 7 columns:

1. Keep only "Date" and "Adj Close" columns for each company. FYI, "Adj Close" accounts for dividend income, which is needed to calculate percentage return later.
2. Put the data for 5 assets in one excel file to do further analysis and save your final file as a .xls or .xlsx file before you start the computations. Comma delimited (.csv) files do not retain the formulae and cell references after closing the file. You will receive a grade of zero if your file does not contain cell references and formulae that show how you arrived at the various answers.

2. Calculate Percentage Returns by calculating the percentage change in "Adj Close" for each asset.

3. Calculate the average monthly return and standard deviation for each asset. Highlight your final answers.

4. Calculate the market beta for AAPL, PNG, DIS and F. Highlight your final answers.

5. Suppose you form a portfolio that invests 40% in AAPL, 30% in PNG, 20% in DIS and 10% in F. Calculate the average return, standard deviation and beta for your portfolio. Highlight your final answers.