This assignment requires you to build a user-defined Excel Function to compute the standard

deviation for a portfolio.

Additionally, you are required to complete several spreadsheet

exercises involving estimation of systematic risk, factor exposure, covariance of returns, and

residual standard deviations for Target (

TGT

), Wal-Mart (

WMT

), Cisco Systems (

CSCO

) and

Huntsman Corporation (

HUN

).

You should download a time series of

Weekly Adjusted Closing

Prices

from

www.yahoofinance.com

for each stock, beginning with the Adjusted Closing Price

for December 27, 2013 (if you input this date as the Start Date Yahoo will select the adjusted

closing price for Friday December 27, 2013 although the closing price will be labeled as

December 23, 2013), and ending with the Adjusted Closing Price for Friday December 29, 2017

(which is designated by Yahoo as the closing price for the week beginning Monday December

25, 2017).

The respective time series of Adjusted Closing Prices should be placed in a

spreadsheet and then used to construct a time series of 210 weekly returns.

These weekly return

series should then be merged with the returns for the factors from the Fama-French 3-factor

model (Market, Firm-Size and Book-to-Market) stored in the text file Fama_French_18.xlsx

available in the Data folder on the eLearning for our course. You may also directly download the

data from Professor Ken French's Data Library:

https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html

and use

Fama/French 3 Factors [Weekly]

.

TXT

CSV

The spreadsheet file Fama_French_18.xlsx is an abridged version of the weekly returns file

for the Fama-French 3-factor model.

The first column of the Fama-French data contains the

Friday end date for each weekly return period (which differs from the Yahoo convention

matching Friday Adjusted closing price with the date for the Monday that begins that week).

The

R

R

second column contains weekly excess returns (

-

) for a value-weighted portfolio including

M

F

all

NYSE, AMEX

, and

NASDAQ

firms.

Columns three and four respectively contain weekly

returns for the Fama-French Firm-Size and Book-to-Market factors.

The weekly return to the

Firm-Size factor reflects the difference in weekly returns for a value-weighted portfolio of stocks

having market-cap less than the median NYSE stock and the weekly returns for a value-weighted

portfolio of stocks having market-cap greater than the median NYSE stock.

The Book-to-Market

factor is the difference between weekly returns for value-weighted portfolios of stocks having the

highest book-to-market ratios, including the top three firm-deciles ranked by the NYSE

breakpoints for book-to-market, and the weekly returns for stocks having the lowest book-to-

market ratios, from the bottom three ranked firm-deciles.

Once you have finished constructing a spreadsheet matching the weekly returns for the

Fama-French 3-factor model with the weekly returns for

TGT, WMT, CSCO

and

HUN

, you may

begin answering the questions that begin on page 9

.

Your answers should be submitted in a

"

" spreadsheet, using sequential work sheet labels to identify the worksheets that

well-annotated

View answer and explanation

include your answers.

When your answers require a

concise

verbal explanation

, please provide

an explanation using a text box within your worksheet, merging worksheet cells into a cell large

enough to contain your answer.

The assignment should be submitted using the Assignments

Link for Spreadsheet Assignment 1 included in the Spreadsheet Assignments Folder linked to the

menu in the left-hand column on the eLearning Homepage for the course.

2