76 Excel
Table 2-7 Soft Drink Sales Data
Brand Cases2000 Cases2001 Cases2002 Origin
Coca-Cola 3198.0 3189.6 3288.9 1886
Pepsi 2188.0 2163.9 2156.4 1898
Mountain Dew 810.3 853.7 862.7 1946
Dr Pepper 747.4 740.0 737.4 1885
Sprite 713.9 703.3 687.9 1961
Gatorade 355.8 375.0 422.8 1965
7 Up 276.0 261.6 243.4 1929
Tropicana 301.2 307.7 292.9 1954
Minute Maid 218.0 226.5 285.3 1946
Aquafi na 105.0 151.4 203.0 1994a. Import the Drinks.txt fi le from the
Chapter02 data folder into an Excel
workbook (note that columns are
delimited by tabs).
b. Create range names for each of the
fi ve data columns in the workbook.
c. Create two new columns displaying
the change in sales from 2000 to 2002
and the ratio of the 2000 sales to the
2002 sales. Assign range names to
these two new columns.
Sort the list in descending order of
the difference in sales.
d. Is there any relationship between the
year in which the brand was founded
and the change in sales? (Hint: Are
the older brands showing less growth
than the new brands?)
e. Repeat your analysis using the ratio of
sales.f. Save the workbook in Excel format
to the Chapter02 folder under the
name Soft Drinks Sales Report and
write a report summarizing your
observations.- The NCAA requires schools to submit
information on graduation rates for its
student athletes. Table 2-8 shows the
data for the 11 schools in the Big Ten,
covering the years 1997 through 2000,
indicating the graduation percentage
(within six years.) The overall gradua-
tion percentage for all undergraduates
is shown in the Graduated column and
then is broken down by race and gender
in the remaining four columns of the
table for those who received athletic
scholarships.
Table 2-8 Big Ten Graduation Data
University Graduated White Males Black Males White Females Black Females
ILL 81 70 52 77 83
IND 72 61 45 76 82
IOWA 66 61 51 81 50
MICH 86 79 44 88 67
MSU 72 61 33 87 63
MINN 58 63 39 70 56
NU 93 87 79 94 100
(continued)