Please answer excel questions.

2. Enter a formula in cells B88 to D88 to calculate the average return rate on each investment type.

3. In cell F5, create a drop down list of value choices. Do not use a comma-delimited list. Instead, use the cell range A5:A86 for the valid choices.

4. Display an input message when a user clicks on cell F5: Choose a year from the list.

5. Display an error message if the user types a year that is not on the list: Invalid year. Please re-enter.

6. In cell F7, enter a formula to display the stock return percentage for the year entered in cell F5.

7. In cell F9, enter a formula to display the TBill return percentage for the year entered in cell F5.

8. In cell F11, enter a formula to display the TBond return percentage for the year entered in cell F5.

9. In cell F13, enter a formula to set the cell to Stocks, TBills, or TBonds, whichever had the highest return in the year entered in cell F5.

10. The Federal Reserve Bank has stated that the inflation rate is one factor that affects the interest rate on TBills in any given year In cell F17, calculate the correlation coefficient between the annual inflation rates and the annual return rates for TBills. Include a small scatter plot to determine whether this pair of variables has a linear relationship. Place the graph in Row 67.

11. Select the data in the cell range A4:D86, and create a Scatter Plot of the data, with the years on the x axis, and the return percentage on the y axis. Color the scatter dots by investment type. Extend the chart so that it fits in cells F3:R19. Change the Chart Title to Investment Returns by Year.

12. Answer these questions in a Word document:

A. What does the correlation coefficient tell you about the correlation between inflation and the interest rate on TBills? Explain in detail.

B. Use the scatter plot (not the spreadsheet data) to answer each of the questions below.

1. In which year were stock returns the lowest? In which year were TBond returns the lowest? Explain why the scatter plot was or was not a good tool for answering these questions.

2. Which investment type was most volatile (had the biggest fluctuations)? Explain why the scatter plot was or was not well-suited to answering this question.

3. What do you notice about T-Bill return rates before and after the mid-point of the data (1968)? Explain why the scatter plot was or was not well-suited to answering this question.

A ........ ***** . 0.96% . % 0.12% 1 2 Year 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 B D E F Annual Returns on Investments in ... ....... Stocks : T.Bills T.Bonds Inflation Rate : Enter a Year in Cell F5: ............................................................... 1928 ! 3.08% D.01% 0 ... -1.7% ........ 1929 -8.30% 3.16% 4.20% 0.0% : Stock Return Percentage ......... ....... .......... ....... 1930: -25.12%: 4.55%: 4.54% T.UU. -2.3% ..... 1931 -43.84% 2.31% -2.56% -9.0% Tbill Return Percentage ...... ---- ..... ......... ........ 1932 -8.64% 1.07% 8.79% -9.9% ... . ......... -9.97 1933 49.98% 1.86% -5.1% Tbond Return Percentage .........2 ..... ....... ... 1934 - 1.19% 0.30% 7.96% 3.1% ... . .: 170 1935 46.74% 0.23% 4.47% 2.2% Best Investment Instrument ---- ... ... ------ ------ 1936 31.94% 0.15% 5.02% 1.5% . 1937 -35.34% 1.38% 3.6% .. ... 1938 29.28% 0.11% 4.21% -2.1% : Correlation coefficient ... ---- ........ ------ ---- ............... 1939 -1.10% 0.03% 4.41% -1.4% Inflation rate:Tbills InterestRate . --- --- . 1940 -10.67% 0.04% 5.40% 0.7% 1941) -12.77% 0.02% -2.02% 5.0% BV 1942 0707 19.17% 0.33% 2.29% 10.9% ------- .: 1943 25.06% 0.38% 2.49% 6.1% .... STU 2071 1944 : 19.03% 0.38% 2.58% 1.7% 1945 35.82% 0.38% 3.80% 2.3% ......... 1946: -8.43% 0.38% 3.13% 8.3% 07 . 5.20% 1947: 2002 0.38% 0.92% 14.4% ...... 1948 5.70% 0.95% 1.95% 8.1% ..... 1949 18.30% 1.16% 4.66% - 1.2%; .........2 ...... 1950 30.81% 1.10% 0.43% 1.3% ... 1951 23.68% 1.34% -0.30% 7.9% ... ....... 1952 18.15% 1.73% 2.27% 1.9% ....... ..... 1953 -1.21% 2.09% 4.14% 0.8% 1954 52.56% 1.60% 3.29% 0.7% 1955 32.60% 1.15% -1.34% -0.4% 1956 7.44% 2.54% -2.26% 1.5% 1957 -10.46% 3.21% 6.80% 3.3% 1958 43.72% 3.04% -2.10% 2.8% 1959: 12.06% 2.77% -2.65% 0.7% 0107 1960 0.34% 4.49% 11.64% 1.7%; 1961 26.64% 2.25% 2.06% 1.0% 1962: -8.81% ! 2.60% 100 5.69% 1.0% 1963 22.61% 2.87% 1.68% 1.3% 1964: 16.42%: 3.52% 3.73% 1.3% 1965 12.40% 3.84% 0.72% 1.6% 1966 -9.97% 4.38% 2.91% 2.9% 1967 23.80% 4.96% -1.58% 3.1% 07 1968 10.81% 4.97% 3.27% 4.2% 1969 -8.24% 5.96% -5.01% 5.5% 1970 3.56% 7.82% 16.75% 5.7% 1971 14.22% 4.87% 9.79% 4.4% ...... .. 1972 4.01% 2.82% 3.2% ........ ........ ... 1973: -14.31% 5.07% 3.66% 6.2% % 07 ---- ... 18.76% A ........ ***** . 0.96% . % 0.12% 1 2 Year 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 B D E F Annual Returns on Investments in ... ....... Stocks : T.Bills T.Bonds Inflation Rate : Enter a Year in Cell F5: ............................................................... 1928 ! 3.08% D.01% 0 ... -1.7% ........ 1929 -8.30% 3.16% 4.20% 0.0% : Stock Return Percentage ......... ....... .......... ....... 1930: -25.12%: 4.55%: 4.54% T.UU. -2.3% ..... 1931 -43.84% 2.31% -2.56% -9.0% Tbill Return Percentage ...... ---- ..... ......... ........ 1932 -8.64% 1.07% 8.79% -9.9% ... . ......... -9.97 1933 49.98% 1.86% -5.1% Tbond Return Percentage .........2 ..... ....... ... 1934 - 1.19% 0.30% 7.96% 3.1% ... . .: 170 1935 46.74% 0.23% 4.47% 2.2% Best Investment Instrument ---- ... ... ------ ------ 1936 31.94% 0.15% 5.02% 1.5% . 1937 -35.34% 1.38% 3.6% .. ... 1938 29.28% 0.11% 4.21% -2.1% : Correlation coefficient ... ---- ........ ------ ---- ............... 1939 -1.10% 0.03% 4.41% -1.4% Inflation rate:Tbills InterestRate . --- --- . 1940 -10.67% 0.04% 5.40% 0.7% 1941) -12.77% 0.02% -2.02% 5.0% BV 1942 0707 19.17% 0.33% 2.29% 10.9% ------- .: 1943 25.06% 0.38% 2.49% 6.1% .... STU 2071 1944 : 19.03% 0.38% 2.58% 1.7% 1945 35.82% 0.38% 3.80% 2.3% ......... 1946: -8.43% 0.38% 3.13% 8.3% 07 . 5.20% 1947: 2002 0.38% 0.92% 14.4% ...... 1948 5.70% 0.95% 1.95% 8.1% ..... 1949 18.30% 1.16% 4.66% - 1.2%; .........2 ...... 1950 30.81% 1.10% 0.43% 1.3% ... 1951 23.68% 1.34% -0.30% 7.9% ... ....... 1952 18.15% 1.73% 2.27% 1.9% ....... ..... 1953 -1.21% 2.09% 4.14% 0.8% 1954 52.56% 1.60% 3.29% 0.7% 1955 32.60% 1.15% -1.34% -0.4% 1956 7.44% 2.54% -2.26% 1.5% 1957 -10.46% 3.21% 6.80% 3.3% 1958 43.72% 3.04% -2.10% 2.8% 1959: 12.06% 2.77% -2.65% 0.7% 0107 1960 0.34% 4.49% 11.64% 1.7%; 1961 26.64% 2.25% 2.06% 1.0% 1962: -8.81% ! 2.60% 100 5.69% 1.0% 1963 22.61% 2.87% 1.68% 1.3% 1964: 16.42%: 3.52% 3.73% 1.3% 1965 12.40% 3.84% 0.72% 1.6% 1966 -9.97% 4.38% 2.91% 2.9% 1967 23.80% 4.96% -1.58% 3.1% 07 1968 10.81% 4.97% 3.27% 4.2% 1969 -8.24% 5.96% -5.01% 5.5% 1970 3.56% 7.82% 16.75% 5.7% 1971 14.22% 4.87% 9.79% 4.4% ...... .. 1972 4.01% 2.82% 3.2% ........ ........ ... 1973: -14.31% 5.07% 3.66% 6.2% % 07 ---- ... 18.76%