Purpose: The purpose of this lab is to use Excel to test a hypothesis. There is no lab 4, everyone gets full credit for that lab.

Data: We will use the same data on the S & P 500 that was used in lab 3. So if you saved your spreadsheet from the last lab, you are in luck. If you did not save it, remember the data is NOT on your CD, instead, you will need to download it from the web page for this class. Please do this part of the assignment as soon as you can just in case there are problems with accessing the data.

Get the data into an Excel Workbook. It should look like something like this:

1. First, treat the S & P stocks as a population and calculate two important population parameters: the mean and the standard deviation. You do not need to print these out for this assignment.
2. Here is a new investing theory: Someone believes that investors are basically lazy and so they are more likely to buy stocks whose ticker symbols begin with the letter "A", so for example, from above, Abbott Laboratories (ABT), Adobe (ADBE) and others will have more buyers than say, stocks whose ticker symbols begin with the letter "N" (it near the middle of the alphabet and far from either end of the spectrum). As a result, stocks with ticker symbols beginning with an "A" do better on average than other stocks in the S & P 500.
3. Test the theory. I will get you started. First, state the null hypothesis. From page 455 in your book, the null is a statement of "no difference" or "no effect" or a statement about a population.

A statement of no difference or no effect would look something like this:

H0: m = 11.0476 (this is the overall average, the population mean, for the S & P 500 stocks)

In other words, the null suggests that one could treat the stocks with ticker symbols which begin with "A" as a random sample. That is, there should be no relationship between 52 week percentage returns and ticker sign.

The alternative hypothesis is, as stated on p. 455 of your text "a name to the statement we hope is true instead of the null"

For this assignment:

Ha: m > 11.0476

4. The appropriate test statistic is Z. To calculate it, you will need to find the sample average x-bar. To do this, sort the ticker symbols alphabetically using the DATA and then SORT commands. You should see a screen like this pop-up:



 

Then calculate the average for the "A stocks". This will serve as your sample average. There are 52 stocks which have tickers beginning with the letter "A".

Perform your test by calculating Z. What did you get for Z, what is the P-value associated with Z and how would you interpret that P-value (review pp. 456-457 in your text)? Is there evidence to support the theory of "A stocks"?

5. The most extreme observation in the S & P 500 data just happens to be America Online (AOL). Let's suppose AOL never existed. Make a copy of your data by using the EDIT then MOVE OR COPY command:

1. A restatement of the null and alternative given above and results from a test (i.e. Z and P-values) using the original S & P 500 data (America Online included) and your interpretation.
2. The null, alternative, and results from a test using an S & P 500 without America Online and your interpretation.