Chapter 4 Describing Your Data 177
companies is more similar in terms of
size: a company totaling $50,000,000
in annual sales and a company with
$10,000,000, or a company with
$450,000,000 in sales and a company
with $400,000,000 in sales? What are
the differences in sales between the
two sets of companies? What are the
ratios? Does the difference or the ratio
better express the similarity of the
companies?
i. Save your workbook and write a report
summarizing your analysis. Explain
how transforming the employee values
using the logarithm function affected
the distribution of the data.
- In the late 1980s, the U.S. Congress held
several joint hearings on discrimination
in lending practices, particularly in the
mortgage industry. Refusal rates from
20 lending institutions were presented
to the committee. Analyze these rates:
a. Open the Mortgage workbook from
the Chapter04 folder and save it as
Mortgage Refusal Rates.
b. Create a table of univariate statistics
for the four data columns. Save the
table in a worksheet named Refusal
Statistics.
c. Create a boxplot of the refusal rates
for the four data columns stored in a
chart sheet named Refusal Boxplots.
Label the chart appropriately.
d. Save your workbook. Including the de-
scriptive statistics and boxplot you’ve
created, write a report detailing your
fi ndings. What conclusion do you draw
from the data? Is there any specifi c in-
formation that this data sample is lack-
ing? Include a discussion of potential
problems in this data set and how you
would go about remedying them.
- Average teacher salary, public school
spending per pupil, and ratio of teacher
salary to pupil spending for 1985 have
been stored in an Excel workbook. The
values are broken down by state and area.
You’ve been asked to calculate statistics
on teacher salaries on the basis of the data.
a. Open the Teacher workbook from
the Chapter04 folder and save it as
Teacher Salaries.
b. Create a table of univariate statistics
except the mode for the teacher sala-
ries broken down by area and overall.
Save the table on the worksheet
Salary Statistics.
c. Create a boxplot of the teacher sala-
ries broken down by area on a chart
sheet named Salary Boxplots.
d. Discuss the distribution of the teacher
salaries for each area. There is an ex-
treme outlier in the west area. Which
state is this? Discuss why salaries for
teachers in this state might be so high.
e. Create a table of univariate statis-
tics except the mode for the ratio of
teacher salary to spending per pupil
broken down by area and overall.
Save the table on a worksheet named
Salary Pupil Ratio Statistics.
f. Create, on a chart sheet named Salary
Pupil Ratio Boxplots, a boxplot of the
ratio values broken down by area.
g. For the state that was an outlier in
the west area in terms of teacher
salary, check to see if it is also
an outlier in terms of the ratio of
teacher salary to public spending
per pupil. Estimate the percentile of
this state’s salary/pupil ratio within
the west area. How does that com-
pare to its percentile for teacher’s
salary alone? If the cost of educa-
tion per pupil is indicative of the
cost of living in a state, are teachers
in this particular state overpaid or
underpaid relative to other states in
the west area?
h. Save your changes to the workbook
and write a report summarizing your
observations and calculations.
