from others in this chapter in that each state has its own sheet with the same data in the same format. Each state sheet breaks down all mortgage applications by loan purpose, applicant race, loan type, outcome, and denial reason (for those that were denied). The question is how a single data set for all states can be created for analysis. The Typical Data Set sheet indicates a simple way of doing this, using the powerful but little-known INDIRECT function. This sheet is basically a template for bringing in any pieces of data from the state sheets you would like to examine.
a. Do whatever it takes to populate the Typical Data
Set sheet with information in the range B7:D11 and B14:D14 (18 variables in all) of each state sheet. Add appropriate labels in row 3, such as Asian Dollar Amount Applied For.
b. Create a table of correlations between these
variables. Color yellow all correlations between a given applicant race, such as those between Asian Mortgage Application, Asian Dollar Amount Applied For, and Asian Average Income. Comment on the magnitudes of these. Are there any surprises?
c. Create scatterplots of White Dollar Amount
Applied For (X axis) versus the similar variable for each of the other five applicant races. Comment on any patterns in these scatterplots, and identify any obvious outliers.
C A S E
B
ank98 operates a main location and three branch locations in a medium-size city. All four locations perform similar services, and customers typically do business at the location nearest them.The bank has recently had more congestion—long waiting lines—than it (or its customers) would like. As part of a study to learn the causes of these long lines and to suggest possible solutions, all locations have kept track of customer arrivals during one-hour intervals for the past 10 weeks. All branches are open Monday through Friday from 9 A.M. until 5 P.M. and on Saturday from 9 A.M. until noon. For each location, the file Bank98 Arrivals.xlsxcontains the number of customer arrivals during each hour of a10-week period. The manager of Bank98 has hired you to make some sense of these data. Specifically, your task is to present charts and/or tables that indicate how customer traffic into the bank locations varies by day of week and hour of day. There is also interest in whether any daily or hourly patterns you observe are stable across weeks. Although you don’t have full information about the way the bank currently runs its operations—you know only its customer arrival pattern and the fact that it is currently experiencing long lines—you are encour- aged to append any suggestions for improving operations, based on your analysis of the data. ■
3.1 CUSTOMER
ARRIVALS AT
BANK98
C A S E
T
he best-selling book The Millionaire Next Door by Thomas J. Stanley and William D. Danko (Longstreet Press, 1996) presents some veryinteresting data on the characteristics of millionaires. We tend to believe that people with expensive houses, expensive cars, expensive clothes, country club memberships, and other outward indications of wealth are the millionaires.The authors define wealth, however, in terms of savings and investments, not consumer items. In this sense, they argue that people with a lot of expensive things and even large incomes often have surprisingly little wealth.These people tend to spend much of what they make on consumer items, often trying to keep up with, or impress, their peers.
In contrast, the real millionaires, in terms of savings and investments, frequently come from “unglamorous”
professions (particularly teaching), own unpretentious homes and cars, dress in inexpensive clothes, and otherwise lead rather ordinary lives.
Consider the (fictional) data in the file Social Climbers.xlsx. For several hundred couples, it lists their education level, their annual combined salary, the market value of their home and cars, the amount of savings they have accumulated (in savings accounts, stocks, retirement accounts, and so on), and a self- reported “social climber index” on a scale of 1 to 10 (with 1 being very unconcerned about social status and material items and 10 being very concerned about these). Prepare a report based on these data, supported by relevant charts and/or tables, that could be used in a book such as The Millionaire Next Door. Your conclusions can either support or contradict those of Stanley and Danko. ■
C A S E
T
he term “churn” is very important to managers in the cellular phone business. Churning occurs when a customer stops using one company’s service and switches to another company’s service. Obviously, managers try to keep churning to a minimum, not only by offering the best possible service, but by trying to identify conditions that lead to churning and taking steps to stop churning before it occurs. For example, if a company learns that customers tend to churn at the end of their two-year contract, they could offer customers an incentive to stay a month or two before the end of their two-year contract. The file Churn.xlsxcontains data on over 2000 customers of a particular cellular phone company. Each row contains the activity of a particular customer for a given time period, and the last column indicates whether the customer churned during this time period. Use the tools in this chapter (and possibly the previous chapter) to learn (1) how these variables are distributed, (2) how the variables in columns B–R are related to each other, and (3) how the variables in columns B–R are related to the Churn variable in column S. Write a short report of your findings, including any recommendations you would make to the company to reduce churn. ■