Question

W72A) I am learning EXCEL Functions. Please answer in EXCEL Functions in detail

Stock Returns (relationship between hypothesis testing and confidence intervals)

Suppose you as an investor with a stock portfolio of hundreds of thousands of dollars decide to sue your broker because of low returns due to lack of portfolio diversification, i.e., too many holdings with similar return prospects. The 39 monthly returns, expressed as percentages, are shown in the table below and reproduced in your Excel answer template.

-8.36	1.63	-2.27	-2.93	-2.70	-2.93	-9.14	-2.64
6.82	-2.35	-3.58	6.13	7.0	-15.25	-8.66	-1.03
-9.16	-1.25	-1.22	-10.27	-5.11	-0.80	-1.44	1.28
-0.65	4.34	12.22	-7.21	-.09	7.34	5.04	-7.24
-2.14	-1.01	-1.41	12.03	-2.56	4.33	2.35

If we graph these data in a histogram, we can reasonably infer that the data are distributed normally.

Suppose you’re on an arbitration panel reviewing this case and decide to compare these returns with the Standard & Poor’s (S&P’s) stock index over the same period and find that the S&P mean, which we can interpret as the population mean (μ), equals .95%.

1.        Test (two-sided) at the 5% level that the mean return you compute   from the investor’s 39-month sample differs from the S&P (population) mean. Use the P-value method, rounding to 4 digits. Make sure to state the null and alternate hypotheses, claim, and your conclusion.   

Remember: since we must compute the sample standard deviation, use the t-statistic to perform the test.)

2.       Now use your P-value calculated in (a) to test at the 1% level whether the sample mean differs from the S&P population main, making sure to state your conclusion. (Do not restate the hypotheses. Simply state the conclusion.

3.        Compute a 95% confidence interval for the population mean,

using the data given in the Table above for α = .05. (Express the results with three decimal places, one more than given in the data.) Does the S&P mean lie in the interval you’ve computed?

4.        Now compute a 99% confidence interval for the population mean, expressing the results with three decimal places. Does the S&P mean lie in the interval you’ve computed?

Answer 1