Excel #$4-$ Confidence Intervals $-$ Three Bell $-$Shaped Curves.

Unit III:

Confidence Intervals $($Excel $2010$ & $2007)$

$1.$ Open a new Excel worksheet $($which will be saved as CONFIDENCE.xlsx$).$ In

cell A$1$ type your name. In cell A$2$ type the course and section number $($i$.$e$.$

ECON$225-01).$ In cell A$3$ type the date. Leave cell A$4$ blank. In cell A$5$ type

$$Assignment: Confidence Intervals$$ In cell A$6$ type $$File: CONFIDENCE.xlsx$.$

$2.$ Enter the following $25$ data values beginning in cell A$11$ and continue down the A

column to cell A$35$:

$42,30,53,50,52,30,55,49,61,74,26,58,40,$

$40,28,36,30,33,31,37,32,37,30,32,23$

$3.$ Select the following cells and type the headings indicated below:

In cell A$10$ type Sales

In cell D$11$ type Mean

In cell D$12$ type Standard Deviation

In cell D$13$ type # Observations

In cell D$15$ type Confidence Interval for Alpha $=\mathrm{.}10$

In cells D$16,$ D$19,$ and D$22$ type Min.

In cell D$18$ type Confidence Interval for Alpha $=\mathrm{.}05$

In cell D$21$ type Confidence Interval for Alpha $=\mathrm{.}01$

In cells F$16,$ F$19,$ and F$22$ type Max.

$4.$ Select cell C$11.$ Then click on the Formulas tab in the toolbar, then select More

Functions. Under the Function category select Statistical. Under the Function

name select AVERAGE. In the dialog box Number $1$ type A$11$:A$35$ then click

on OK$.$ The mean of the $25$ data values should appear.

$5.$ Select cell C$12.$ Then click on the Formulas tab in the toolbar, then select More

Functions. Under the Function category select Statistical. Under the Function

name select STDEV.P$.$ In the dialog box Number $1$ type A$11$:A$35$ then click on

OK$.$ The standard deviation of the $25$ data values should appear.

$6.$ Select cell C$13.$ Then click on the Formulas tab in the toolbar, then select More

Functions. Under the Function category select Statistical. Under the Function

name select COUNT. In the dialog box Value $1$ type A$11$:A$35$ then click on OK$.$

The number of observations $(25)$ for the data values should appear.

$7.$ Select cell C$15.$ Then click on the Formulas tab in the toolbar, then select More

Functions. Under the Function category select Statistical. Under the Function

name select CONFIDENCE.NORM $($or CONFIDENCE for Excel $2007).$ In the

dialog box Alpha type $\mathrm{.}10,$ in the dialog box Standard deviation type C$12,$ in the

dialog box Size type C$13.$ Then click on OK$.$ The confidence interval $($ value$)$

should appear.

$($Instructions continue on the next page$)$

$40$

$8.$ Select cell C$18.$ Repeat Step $7$ above from the second sentence, except type $\mathrm{.}05$

in the Alpha dialog box. Then click OK$.$ The confidence interval $($ value$)$

should appear.

$9.$ Select cell C$21.$ Repeat Step $7$ above from the second sentence, except type $\mathrm{.}01$

in the Alpha dialog box. Then click OK$.$ The confidence interval $($ value$)$

should appear.

$10.$ Select cell C$16$ and type $=$C$11-$C$15.$ Then hit Enter. Next select cell E$16$ and

type $=$C$11+$C$15,$ and hit Enter. The minimum and maximum values for the

confidence interval should appear.

$11.$ Select cell C$19$ and type $=$C$11-$C$18.$ Then hit Enter. Next select cell E$19$ and

type $=$C$11+$C$18,$ and hit Enter. The minimum and maximum values for the

confidence interval should appear.

$12.$ Select cell C$22$ and type $=$C$11-$C$21.$ Then hit Enter. Next select cell E$22$ and

type $=$C$11+$C$21,$ and hit Enter. The minimum and maximum values for the

confidence interval should appear.

$13.$ Save the worksheet on a disk as CONFIDENCE.xlsx and print$-$out the worksheet

to submit to the instructor.

$14.$ In addition to submitting a print$-$out of the worksheet, also complete and submit

the following, referencing the data in your print$-$out and your Notes.

$($a$)$ Draw three separate graphs $($one for each alpha value $\mathrm{.}10,\mathrm{.}05,$ and $\mathrm{.}01)$

illustrating the alpha values, confidence levels, the mean, and the

minimum and maximum values for the confidence level.

$($b$)$ What is the Maximum Tolerable Error value at an Alpha $=\mathrm{.}10,$ Alpha $=$

$\mathrm{.}05,$ and an Alpha $=\mathrm{.}01?$ Write your answers next to each graph.

$($c$)$ Now, suppose the data represents a population and you are performing

hypothesis tests.

$(1)$ Indicate and show on the graphs whether you would accept or reject

the H$0$ for each of the three alpha values $(\mathrm{.}10,\mathrm{.}05,\mathrm{.}01)$ if x$-$bar $=$

$45\mathrm{.}9$ and the type of error that could possibly be made. Write your

accept$/$reject decision and type of error next to each graph.

$(2)$ Indicate and show on the graphs whether you would accept or

reject the H$0$ for each of the three alpha values $(\mathrm{.}10,\mathrm{.}05,\mathrm{.}01)$ if x$-$

bar $=35\mathrm{.}9$ and the type of error that could possibly be made.

Write your accept$/$reject decision and type of error next to each

graph.