The Portfolio
Exercise
In this exercise
students will demonstrate the following principles: efficient market
hypothesis, market line, and Excel skill. [For BADM 321 students, you will have
more weeks of data as you have a longer class]
Step 1. Select a portfolio of at least five stocks
chosen from the “Big Board”, the NYSE.
These may be a subset of the DJIA, the S&P 500 or just any other
stocks you choose. You may want to try
to select stocks you believe will be “winners”, that is, outperform the market.
[In addition to stocks listed on the NYSE, you may also include non-NYSE stocks
included in the Dow Jones Industrial Average; currently these include NASDAQ’s
Microsoft and Intel]
Step 2. Select a random sample of at least five
stocks from the NYSE using the “dartboard” method. Literally, this means pasting the NYSE pages
on the wall and throwing darts at them.
You may use any “random” method of your choice.
Step 3. Open an Excel spreadsheet and enter the names
of both portfolios as well as DJIA and S&P 500 in column a of the
sheet. You will also need the indices
for each stock in each of the two portfolios (see Step 4). This guarantees that all stocks have equal
influence on the portfolio average. In effect you will start with $100 of each
stock. Thus the number of shares will be
given by 100/Po, where Po is the price on week 1.
Step 4. Look in the Wall Street Journal or any of the
many free stock quote services available on the internet to find the current
prices of each of the stocks picked as well as the S&P 500 and DJIA
averages. Enter these into column c of
your sheet. The original values of the
stocks I used can be seen HERE. [I
have also calculated the mean, standard deviation and coefficient of variation
for each stock and index. This is done
once all the data are input]
Step 5. In
column b you will calculate the factor to multiply each period’s stock price
for that stock such that the index is 100 in the first period. This is given by 100/price in period 1. The
subsequent prices will be entered in columns d through h (assuming seven weeks
of data). See my example by clicking HERE. [I had more
weeks, as I tracked from the week after 9/11 through
Step 6. Plot the four indices on an Excel chart. The graph is seen be clicking HERE. Also calculate the percent change over the
six or seven weeks. You can also have
Excel produce descriptive statistics of each stock, and the four indices using
the Data Analysis function under Tools. [If Data Analysis is not found here, go
to Add Ins to include it] The
coefficient of variation (cv) is the standard deviation divided by the mean and
is a measure of the riskiness of a stock; the higher the cv, the riskier the
stock.
Step 7. Write up a short, two paragraph summary of
your observations on the co-movements of the various indices in your sheet; in particular,
the relative performances of the averages.
In your case, do these support or call into question the efficient
market hypothesis? Were variances and averages directly related?