1

Please build an Excel spreadsheet that uses formulas to create entries in all the black cells in the table

I will be providing some help in class, but you should first attempt to create the table without help.

Please take the GE Stock Price and the S&P 500 Index as "givens". Just enter the numbers. Columns 5-10 results should be created with formulas. (This means that you must use a formula to calculate the 49.28% in the row for year 2 for column 5).

Note that Column 5 is the annaul percent change in the stock price for GE. For example, when GE stock increased from $14.63 in 2011 to $21.84 in 2012, the % change was 49.28%.

Column 6 (GE Factor) is 1+column 5.

Columns 7 & 8 refer to the S&P 500.

Column 9 shows what would happen if you invested $1000 in GE Stock in 2011.

Note you would only have $432 in 2020. Note that the first entry of columns 9 and 10 is $1000,

which is entered and not calculated as a formula. The other entries in columns 9 and 10 are calculated with formulas.

Column 10 shows what would happen with a $1000 investment using the S&P 500.

2

Create the graph in section 1 next to the table. Display the years (2011 through 2020) on the horizontal axis.

3

Take the average of the entries in column 5 and confirm that the result is -4.5618%

4

Find the annualized return and confirm that it is -8.9045%

Show me the Excel formula.

Go to column 3. Create a fraction (End/Beginning). Raise this fraction to the 1/9 power. Subtract 1.

5

Use Excel to calculate the 9th root of 78815.63867 (There's no hidden meaning here).

6

Calculate the correlation between GE and the S&P 500.

You can use Data Analysis in Excel. Or you can go online and find a correlation calculator.

7

Go to Investopedia.com.

Look for "What does a negative correlation coefficient mean?"

Based on information on this webpage, state whether the correlation result is positive or negative, and the strength of the correlation. Interpret this in plain English. (or as plain as possible).

8

Using Excel formula =geomean, calculate the geometric mean of the factors in column 6.

Subtract 1 from the geometric mean. How does the result compare to the annualized return in Deliverable 4.

9

Find a stock that interests you and record its price on or around July 1 for 2011 through 2020. You can be close (within a month) of 2020. Get the data from a stock price chart. You can go online to Yahoo Finance and find specific stock prices. Or feel free to find another site. Record the results in a table showing price by year. You will have 10 entries.

10

Very briefly, what is the S&P 500? What's the DJIA (Dow Jones Industrial Average)?

Section 1 Col 1 Col 2 Col 3 Col 4 Col 5 Col 6 Col 7 Col 8 Col 9 Col 10 S&P 500 Annual % Start w $1000 Start w $1000 Annual % change Year # Year GE Index change GE GE Factor S&P S&P Factor GE S&P DO YOUTAWNY 2011 $ 14.63 1292 1,000 $ 1,000 2012 $ 21.84 1379 49.28% 1.493 6.73% 1.067 $ 1,493 1,067 2013 $ 22.97 1686 5.17% 1.052 22.26% 1.223 $ 1,570 $ 1,305 2014 $ 24.63 1931 7.23% 1.072 14.53% 1.145 $ 1,684 1,495 2015 $ 24.25 2103 -1.54% 0.985 8.91% 1.089 $ 1,658 1,628 2016 $ 28.48 2173 17.44% 1.174 3.33% 1.033 $ 1,947 1,682 2017 23.25 2470 -18.36% 0.816 13.67% 1.137 $ 1,589 S 1,912 2018 $ 10.86 2816 -53.29% 0.467 14.01% 1.140 $ 742 2,180 2019 $ 8.94 2980 -17.68% 0.823 5.82% 1.058 $ 611 10 2,307 2020 $ 6.32 3271 -29.31% 0.707 9.77% 1.098 $ 432 S 2,532\f