Chicago State University Confidence Interval Excel Task

Confidence IntervalsAlbright, S. C., & Winston, W. L. (2017). Business analytics: Data analysis and decision
making (6th ed.). Retrieved from https://redshelf.com/
In this week’s reading, we discussed different types of samples, their distributions,
sampling errors, probabilities, and confidence intervals. Using Excel or StatTools,
complete the following Problem. This is similar to Problem 33 in Chapter 8 on page 351.
Problem – You have been assigned to determine whether more people prefer Coke or
Pepsi. Assume that roughly half the population prefers Coke and half prefers Pepsi.
How large a sample do you need to take to ensure that you can estimate, with 90%
confidence, the proportion of people preferring Coke to within 5% of the actual value?
Show all of your work.
8-3 CONFIDENCE INTERVAL FOR A MEAN
We now come to the main topic of this chapter: using properties of sampling distributions to
construct confidence intervals. We assume that data have been generated by some random
mechanism, either by observing a random sample from some population or by performing
8-3 Confidence Interval for a Mean 317
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203
Activate Windows
8-3 CONFIDENCE INTERVAL FOR A MEAN
We now come to the main topic of this chapter: using properties of sampling distributions to
construct confidence intervals. We assume that data have been generated by some random
mechanism, either by observing a random sample from some population or by performing
8-3 Confidence Interval for a Mean 317
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203
Activate Windows
a
a randomized experiment. The goal is to infer the values of one or more population param-
eters such as the mean, the standard deviation, or a proportion from sample data. For each
such parameter, you use the data to calculate a point estimate, which can be considered a
best
guess for the unknown parameter. You then calculate a confidence interval around the
point estimate to measure its accuracy.
We begin by deriving a confidence interval for a population mean u, and we discuss its
interpretation. Although the particular details pertain to a specific parameter, the mean, the
same ideas carry over to other parameters as well, as will be described in later sections. As
usual, the sample X is used as the point estimate of u.
To obtain a confidence interval for y, you first specify a confidence level, usually
90%, 95%, or 99%. You then use the sampling distribution of the point estimate to deter-
mine the multiple of the standard error (SE) to go out on either side of the point esti-
mate to achieve the given confidence level. If the confidence level is 95%, the value used
most frequently in applications, the multiple is approximately 2. More precisely, it is a
t-value. That is, a typical confidence interval for u is of the form in Equation (8.4), where
SE(X) = s/Vn.
=
To obtain the correct t-multiple, let a be one minus the confidence level (expressed as
a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-
priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution
with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%,
cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%
confidence interval for u is then
X + 2.045(s/Vn)
n =
If the confidence level is instead 90%, the appropriate t-value is 1.699 (change the proba-
bility in cell B24 to 0.10 to see this), and the resulting 90% confidence interval is
X + 1.699(s/Vn)
If the confidence level is 99%, the appropriate t-value is 2.756 (change the probability in
cell B24 to 0.01 to see this), and the resulting 99% confidence interval is
Activate Windows
X + 2.756(s/Vn)
Go to Settings to activate W
Excel-only method at first and then move to StatTools when you are more comfortable with
the procedure, but this is totally up to you. These comments apply to the other confidence
intervals in this chapter as well.
EXAMPLE
8.1 CUSTOMER RESPONSE TO A NEW SANDWICH
A
fast-food restaurant recently added a new sandwich to its menu. To estimate the popu-
wich were surveyed. Each of these customers was asked to rate the sandwich on a scale
of 1 to 10, 10 being the best. The results of this survey appear in column B of Figure 8.3
(with several rows hidden). (See the file Satisfaction Ratings.xlsx.) The manager wants to
estimate the mean satisfaction rating over the entire population of customers by finding a
95% confidence interval. How should she proceed?
Figure 8.3
Analysis of New Sandwich Data
A
B
с
D
E
F
G
1
Customer
Satisfaction
2
1
7
M[ در
Excel-only confidence interval for mean
Sample size
40
Sample mean
6.25
Sample Std Dev
1.597
3
2
5
=COUNT(B2:B41)
=AVERAGE( B2:B41)
=STDEV.S(B2:B41)
=E4/SQRT(E2)
4
3
5
5
4
6
Std Error of mean
0.253
6
5
8
95%
7
6
7
39
8
7
6
Confidence level
Degrees of freedom
t multiple
Lower limit
Upper limit
2.023
=E2-1
=T.INV.2T(1-E6,E7)
=E3-E8*E5
9
8
7
5.739
10
9
10
6.761
=E3+E8*E5
11
10
7
12
11
9
39
38
9
© Cengage Learning
40
39
5
41
40
4
Activate Windows
Objective To obtain a 95% confidence interval for the mean satisfaction rating of the
new sandwich.
Solution
The Excel-only method is spelled out in Figure 8.3 by the formulas shown in column G.
The calculations follow directly from Equation (8.4). As in Figure 8.2, the T.INV.2T func-
tion is used to find the correct multiple. Its arguments are one minus the confidence level
and the degrees of freedom. The result is that the 95% confidence interval extends from
5.739 to 6.761.
Alternatively, to use StatTools, make sure a StatTools data set has been designated,
select Confidence Interval from the StatTools Statistical Inference dropdown list, and
select the Mean/Std. Deviation option. Then fill in the resulting dialog box as shown in
Figure 8.4. In particular, select One-Sample Analysis as the Analysis type. (Other types
will be used later in the chapter.) You should obtain the output shown in the figure. It
Figure 8.4 StatTools Confidence Interval Method
G
H
K
StatTools – Confidence interval for Mean/Std. Deviation
X
Analysis Type
One-Sample Analyss
Eormat
D
E
12 StatTools confidence interval for mean
13
Satisfaction
14 Conf. Intervals (One-Sample) Ratings Data 1
15 Sample Size
40
16 Sample Mean
6.250
17 Sample Std Dev
1.597
18 Confidence Level (Mean) 95.0%
19 Degrees of Freedom
39
20 Lower Limit
5.739
21 Upper Limit
6.761
22
23
Variables (Select one or more)
Data Set
Rotings Data
Name
Customer
A2:A41
Satisfaction
B2:841
Address
NNNNN
Confidence Intervals to Calculate
For the Mean
For the Standard Deviation
95%
Confidence Level
Confidence level
09
24
© Cengage Learning
25
OK
Cancel
26
Activate Windows
doesn’t show the “ingredients” shown in the Excel-only method, but it arrives at exactly
the same confidence interval. (Note: If you want to place the output next to the data, as
shown here, select Settings from the StatTools ribbon, and, in the Report group, select
either of the last two Placement options.)
From either method, the principal results are that (1) the best guess for the population
mean rating is 6.250, and (2) a 95% confidence interval for the population mean rating
extends from 5.739 to 6.761. The manager can be 95% confident that the true mean rating
over all customers who might try the sandwich is within this confidence interval.
We stated previously that as the confidence level increases, the length of the confi-
dence interval increases. You can convince yourself of this by entering different confidence
levels such as 90% or 99%. The lower and upper limits of the confidence interval will
change automatically, getting closer together for the 90% level and farther apart for the
99% level. Just remember that you, the analyst, can choose the confidence level, but 95%
is the level most commonly chosen.
Before leaving this example, we discuss the assumptions that lead to the confidence
interval. First, you might question whether the sample is really a random sample—or
whether it matters. Perhaps the manager used some random mechanism to select the
customers to be surveyed. More likely, however, she simply surveyed 40 consecutive
customers who tried the sandwich on a given day. This is called a convenience sample and
is not really a random sample. However, unless there is some reason to believe that these
40 customers differ in some relevant aspect from the entire population of customers, citlijndows
probably safe to treat them as a random sample.
Go to Settings to activate
A second assumption is that the population distribution is normal. We made this
assumption when we introduced the t distribution. Obviously, the population distribution
cannot be exactly normal because it is concentrated on the 10 possible satisfaction rat-
ings, and the normal distribution describes a continuum. However, this is probably not a
problem for two reasons. First, confidence intervals based on the t distribution are robust
FUNDAMENTAL INSIGHT
True Meaning of a 95% Confidence
Interval
Given the data in a particular sample, a 95% confidence
interval for the mean will either include the (unknown)
population mean or it won’t. The true meaning of a 95%
confidence interval is that if the same procedure is used
on many different random samples, about 95% of the
resulting confidence intervals will include the popula-
tion mean, and only about 5% won’t. Therefore, you can
be 95% confident that any particular confidence inter-
val you happen to get is a “good one.
PROBLEMS
Level A
32. Elected officials in a California city are preparing the
annual budget for their community. They would like to
estimate how much their constituents living in this city
are typically paying each year in real estate taxes. Given
that there are over 100,000 homeowners in this city, the
officials have decided to sample a representative subset
of taxpayers and study their tax payments.
a. What sample size is required to generate a 95%
confidence interval for the mean annual real estate
tax payment with a half-length of $100? Assume
that the best estimate of the population standard
deviation o is $535.
b. If a random sample of the size from part a is
selected and a 95% confidence interval for the
mean is calculated from this sample, will the half-
length of the confidence interval be equal to $100?
Explain why or why not.
c. Now suppose that the officials want to construct a
95% confidence interval with a half-length of $75.
Again, assume that the best estimate of the population
standard deviation o is $535. Explain the difference
between this result and the result from part a.
33. You have been assigned to determine whether more
people prefer Coke or Pepsi. Assume that roughly half
the population prefers Coke and half prefers Pepsi. How
large a sample do you need to take to ensure that you
can estimate, with 95% confidence, the proportion of
people preferring Coke within 2% of the actual value?
34. You are trying to estimate the average amount a family
spends on food during a year. In the past the standard
deviation of the amount a family has spent on food
during a year has been approximately $1000. If
want to be 99% sure that you have estimated aver-
age family food expenditures within $50, how many
families do you need to survey?
35. In past years, approximately 20% of all U.S. families
purchased potato chips at least once a month. You
are interested in determining the fraction of all U.S.
families that currently purchase potato chips at leastows
you
W11.
Latiu?
familia
i
8-3 CONFIDENCE INTERVAL FOR A MEAN
We now come to the main topic of this chapter: using properties of sampling distributions to
construct confidence intervals. We assume that data have been generated by some random
mechanism, either by observing a random sample from some population or by performing
8-3 Confidence Interval for a Mean 317
Copyright 2017 Cengage Learning. All Rights Reserved. May not be copied, scanned, or duplicated, in whole or in part. WCN 02-200-203
a randomized experiment. The goal is to infer the values of one or more population param-
eters such as the mean, the standard deviation, or a proportion from sample data. For each
such parameter, you use the data to calculate a point estimate, which can be considered a
best guess for the unknown parameter. You then calculate a confidence interval around the
point estimate to measure its accuracy.
We begin by deriving a confidence interval for a population mean u, and we discuss its
interpretation. Although the particular details pertain to a specific parameter, the mean, the
same ideas carry over to other parameters as well, as will be described in later sections. As
usual, the sample X is used as the point estimate of u.
To obtain a confidence interval for y, you first specify a confidence level, usually
90%, 95%, or 99%. You then use the sampling distribution of the point estimate to deter-
mine the multiple of the standard error (SE) to go out on either side of the point esti-
mate to achieve the given confidence level. If the confidence level is 95%, the value used
most frequently in applications, the multiple is approximately 2. More precisely, it is a
t-value. That is, a typical confidence interval for u is of the form in Equation (8.4), where
SE(X) = s/Vn.
=
Confidence Interval for Population Mean
X + t-multiple SE (X)
(8.4)
To obtain the correct t-multiple, let a be one minus the confidence level (expressed as
a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-
priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution
with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%indows
cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%-o activate |
=
=
To obtain the correct t-multiple, let a be one minus the confidence level (expressed as
a decimal). For example, if the confidence level is 90%, then a = 0.10. Then the appro-
priate t-multiple is the value that cuts off probability a/2 in each tail of the t distribution
with n – 1 degrees of freedom. For example, if n = 30 and the confidence level is 95%,
cell B25 of Figure 8.2 indicates that the correct t-value is 2.045. The corresponding 95%
confidence interval for u is then
X + 2.045(s/Vn)
If the confidence level is instead 90%, the appropriate t-value is 1.699 (change the proba-
bility in cell B24 to 0.10 to see this), and the resulting 90% confidence interval is
X + 1.699(s/Vn)
If the confidence level is 99%, the appropriate t-value is 2.756 (change the probability in
cell B24 to 0.01 to see this), and the resulting 99% confidence interval is
X + 2.756(s/Vn)
tinto AG
Note that as the confidence level increases, the length of the confidence interval also
increases. Because narrow confidence intervals are desirable, this presents a trade-off. You
can either have less confidence and a narrow interval, or you can have more confidence and
a wide interval. However, you can also take a larger sample. As n increases, the standard
error s/Vn tends to decrease, so the length of the confidence interval tends to decrease for
any confidence level. (Why won’t it decrease for sure? The larger sample might result in a
larger value of s that could offset the increase in n.)
Example 8.1 illustrates confidence interval estimation for a population mean. Starting
in this edition of the book, we illustrate this in two ways: with Excel-only formulas and
with StatTools. The advantages of the Excel-only method are that no add-in is required,
and it shows exactly how the calculations are performed. The advantage of StatTools is
that it is much faster, requiring only that you fill in a dialog box. You might want to try the