Solutions Manual For Data Visualization Exploring and Explaining with Data 2nd Edition By Jeffrey Ca by digitaldownload87

Solution and Answer Guide

TABLE OF CONTENTS End of Section Problem Solutions ......................................................................................1

END OF SECTION PROBLEM SOLUTIONS 1.

Types of Analytics. Indicate which type of analytics (descriptive, predictive, or prescriptive analytics) each of the following represents. LO 1 a. a data dashboard b. a model that finds the production schedule that minimizes overtime c. a model that forecasts sales for the next quarter d. a bar chart e. a model that allocates your financial investments to achieve your financial goal Solution: a. descriptive b. prescriptive c. predictive d. descriptive e. prescriptive

2. Transportation Planning. An analytics professional is asked to plan the shipment of a product for the next quarter. She employs the following process: Step 1. For each of the 12 distribution centers, she plots the quarterly demand for the product over the last three years. Step 2. Based on the plot for each distribution center, she develops a forecasting model to forecast demand for next quarter for each distribution center. Step 3. She takes the forecast for next quarter for each distribution center and inputs those forecasts, along with the capacities of the company’s four factories and transportation rates from each factory to each distribution center, into an optimization model. The optimization model suggests a shipping plan that minimizes the cost of how to satisfy the forecasted demand from the company’s four different factories to the distribution centers. Describe the type of analytics being utilized in each of the three steps outlined above. LO 1

Solution: Step 1. Data visualization is descriptive analytics. Step 2. A forecasting model is predictive analytics. Step 3. An optimization mode is prescriptive analytics. 3. Wall Street Journal Subscriber Characteristics. A Wall Street Journal subscriber survey asked a series of questions about subscriber characteristics and interests. State whether each of the following questions provides categorical or quantitative data. LO 2 a. What is your age? b. Are you male or female? c. When did you first start reading the WSJ? High school, college, early career, midcareer, late career, or retirement? d. How long have you been in your present job or position? e. What type of vehicle are you considering for your next purchase? Nine response categories for this question include sedan, sports car, SUV, minivan, and so on. Solution: a. quantitative b. categorical c. categorical d. quantitative e. categorial 4. Comparing Smartwatches. Consumer Reports provides product evaluations for its subscribers. The following table shows data from Consumer Reports for five smartwatches on the following characteristics: Overall Score—a score awarded for a variety of performance factors Price—the retail price Recommended—does Consumer Reports recommend purchasing the smartwatch based on performance and strengths? Best Buy—if Consumer Reports recommends purchasing the smartwatch, does it also consider it a “best buy” based on a blend of performance and value? For each of the four pieces of data, indicate whether the data are quantitative or categorical and whether the data are cross-sectional or time series. LO 2 Make

Overall Score

Recommended

Best Buy

Price

Apple Watch Series 5

Yes

$395

Fitbit Versa 2

Yes

$200

Garmin Venu

Yes

$350

Fitbit Versa Lite

$100

Solution: Overall Score and Price are quantitative. Recommended and Best Buy are categorical (both are yes or no). All of these data are cross-sectional.

5. House Price and Square Footage. Suppose we want to better understand the relationship between house price and square footage of the house, and we have collected house price and square footage for 75 houses in a particular neighborhood of Cincinnati, Ohio, from the Zillow website on January 3, 2021. LO 2, 3 a. Are these data quantitative or categorical? b. Are these data cross-sectional or times series? c. Which of the following type of chart would provide the best display of these data? Explain your answer. i. Bar chart ii. Column chart iii. Scatter chart Solution: a. House price and square footage are quantitative. b. The data were collected at a single point in time, so these data are cross-sectional. c. iii Bar charts and column charts are better for categories. Here we do not have categories, we have pairs of numerical data for each of 75 houses (price, square footage). Therefore, a scatter chart with square footage as the horizontal axis and price as the vertical axis would be appropriate. 6. Netflix Subscribers. The following chart displays the total number of Netflix subscribers from 2010 to 2019. LO 1, 2, 3

a. Are these data quantitative or categorical? b. Are these data cross-sectional or time series? c. What type of chart is this? Solution: a. Number of Netflix subscribers is quantitative. b. Since these data are collected at several points in time, they are time series data. c. This is a column chart. 7. U.S. Netflix Subscribers. Refer to the previous problem. Suppose that in addition to the total number of Netflix subscribers, we have the number of those subscribers by year for the years 2010–2019 who live in the United States. Our message is to emphasize how

much of the growth is coming from the United States. Which of the following types of charts would best display the data? Explain your answer. LO 2, 3 i. Bar chart ii. Clustered column chart iii. Stacked column chart iv. Stock chart Solution: A stacked column chart is most appropriate here as it will show the total number of subscribers (height of the column) but also the same for U.S. subscribers (denoted by color within a column). 8. How Data Scientists Spend Their Day. The Wall Street Journal reported the results of a survey of data scientists. The survey asked the data scientists how they spend their time. The following chart shows the percentage of respondents who answered less than five hours per week or at least five hours per week for the amount of time they spend on exploring data and on presenting analyses. LO 2, 3, 4

a. b. c. d.

Are these data quantitative or categorical? Are these data cross-sectional or time series? What type of chart is this? What conclusions can you make based on this chart?

Solution: a. These data are categorical: exploring data and presenting analysis; less than or equal to four hours per week or at least five hours per week. b. These data are cross sectional since the survey was administered to a person at one point in time. c. The chart is a clustered bar chart (also known as a side-by-side bar chart). d. Data scientists spend more time exploring data than presenting results. 9. Industries in the Dow Jones Industrial Index. Refer to the data on the Dow Jones Industrial Index given in Table 1.3. The following chart displays the number of companies in each industry that make up this index. LO 3

a. What type of chart is this? b. Which industry has the highest number of companies in the Dow Jones Industrial Index? Solution: a. This is a bar chart. b. Financial Services and Technology each have five companies in the Dow Jones Industrial Index. 10. Job Factors. The following chart is based on the same data used to construct Figure 1.3. The data are percentages of respondents to a survey who listed various factors as most important when making a job decision. LO 3, 4

a. What type of chart is this? b. What is the fifth most-cited factor?

Solution: a. This is a column chart. b. The fifth most-cited factor is Industry (Day-to-day Work and Flexible Schedule tie for fourth). 11. Retirement Financial Concerns. The results of the American Institute of Certified Public Accountants’ Personal Financial Planning Trends Survey indicated 48% of the clients had concerns about outliving their money. The top reasons for these concerns and the percentage of respondents who cited the reason were as follows. LO 3, 4

a. What type of chart is this? b. Only 48% of the survey respondents had financial concerns about retirement (outliving their money). What percentage of the total people surveyed had retirement healthcare cost concerns? Solution: a. This is a bar chart. b. 77% of 48% is 0.77x0.48 = 0.3696 or 36.96%. 12. Master’s Degree Program Recruiting. The recruiting process for a full-time master’s program in data science consists of the following steps. The program director obtains email addresses of undergraduate seniors who have taken the Graduate Record Exam (GRE) and expressed an interest in data science. An email inviting the students to an online information session is sent. At the information session, faculty discuss the program and answer questions. Students apply through a web portal. An admissions committee makes an offer of admission (or not) along with any financial aid. If the person is admitted, the person either accepts or rejects the offer. Consider the following chart. LO 3, 4

a. What type of chart is this? b. Which of the following is the correct interpretation of the 21% for Enrolled? i. Of those who were sent an email, 21% enrolled. ii. Of those who were admitted, 21% enrolled. iii. Of those who applied for admission, 21% enrolled. iv. None of the above Solution: a. This is a funnel chart. b. i. 13. Chemical Process Control. The following chart is a quality control chart of the temperature of a chemical manufacturing process. What observations can you make about the process? LO 3

Solution: The process appears to be in control. Only one point is outside the control limits (hour 11), and it barely exceeds the upper limit. However, the last five data points exhibit an upward trend and might be cause for concern. The temperature should be monitored closely moving forward. 14. Buying a Used Car. The following chart shows data for a sample of 18 used cars of the same brand, model, and year. LO 2, 3, 4

a. Are these data quantitative or categorical? b. What type of chart is this? c. How might you use this chart to find a used car to purchase? Solution: a. Price and mileage are both quantitative. b. This is a scatter chart. c. You could identify a potentially good deal by looking for low mileage (to the left on the horizontal axis) and low price (lower on the vertical axis). Two potential good deals are shown in the following chart (circled and in red).

15. Tracking Stock Prices. The following high-low-close stock chart gives the stock price for Exxon Mobile Corporation over a 12-month period. The data are the low, high, and closing price per share on the first trading day of the month. What can you say about the stock price and volatility of the stock price over this 12-month period? LO 3

Solution: As the closing stock price dropped in months 8–10, the price became more volatile as evidenced by the longer bars (the range from low price to high price). The volatility of the price appears to be decreasing in months 11and 12.

Solution and Answer Guide

TABLE OF CONTENTS End of Section Problem Solutions ......................................................................................1 CONCEPTUAL ............................................................................................................................. 1 APPLICATIONS.......................................................................................................................... 15

END OF SECTION PROBLEM SOLUTIONS CONCEPTUAL 1.

Sales by Region. Consider the following data for percentage of sales by region. LO 3 Percentage of Sales Region Total Sales East 28% North 14% South 36% West 22% a. Should a bar chart or a pie chart be used to display these data? Explain. b. List two ways to enhance the formatting of the chart to improve interpretability. Solution: a. These data would be best displayed as a bar chart. Bar charts are preferred over pie charts because we can more easily distinguish differences in length than area or angle. b. The chart should be sorted from high to low based on the percentage of sales. Labels at the end of the bars indicate the exact percentage. Sorting allows the audience to quickly ascertain the rank order of the regions’ contribution to total sales. This is better than a pie chart because it is easier to determine differences in length rather than differences in angle and area.

2. Academic Makeup of Departments. You are conducting an analysis of the makeup of the departments in your firm. Your goal is to compare the departments’ mixes of academic backgrounds. You have defined the following categories for academic background: Business, Engineering, and Other. You have the percentage of employees in each category for each of the four departments as shown in the table below. LO 3

Department

Business

Engineering

Other

84%

16%

45%

43%

12%

48%

20%

32%

17%

68%

15%

What type of chart is best suited to display these data? Solution: A stacked column chart with the horizontal axis representing the departments and the bars giving the composition of each department of the three categories is the best choice. Another possibility is a clustered column chart with separate columns for each department-category combination, but that would be more cluttered. 3. Charts of Gasoline Prices. The following charts both show the average price (in dollars) per gallon of gasoline in the United States for 36 consecutive months. Consider the following charts. The first is a line chart and the second is a column chart. LO 3

Which better displays the data? Why? i. Line chart ii. Column chart Solution: i. Both charts accurately display the data, but the line chart is preferred because it is much less cluttered and allows the audience to easily see the increases and decreases in prices. 4. Pickup Truck Sales. The following charts display the sales of pickup trucks in the United States by manufacturer for one year (source: The Wall Street Journal). You must choose one of these charts to edit to produce the final chart for your presentation to management. LO 3, 5

Which chart is best for displaying these data? Explain your answer. i. Column chart ii. Pie chart iii. Bar chart vi. Funnel chart Solution: iii. For reasons discussed in the chapter, the pie chart is not a good choice. The funnel chart is inappropriate because there is no natural decrease in a process as is intended for that chart type. The bar chart or the column chart are viable options. The bar chart is the best choice as the labels are not split across lines as they are in the column chart. 5. NCAA Women’s Basketball. Since 1994, the NCAA Division I women’s basketball tournament has had a starting field of 64 teams and, over the course of 63 singleelimination games, a champion is determined. The following two charts (a funnel chart and a bar chart) show how the tournament progresses from the starting field of 64 teams. LO 3

Which chart best conveys these data? Why? i. Funnel chart ii. Bar chart

Solution: i. The funnel chart. It is a better choice here as it conveys the natural narrowing of the field as the tournament progresses. 6. Worldwide Robot Supply. The International Federation of Robotics estimates the worldwide supply of industrial robots each year (ifr.org). The charts below show estimates of the worldwide supply of industrial robots for the years 2009 to 2021. The data are shown in two different charts below, first is a line chart and the second a column chart. LO 3

Which chart best displays these data? Why? i. Line chart ii. Column chart

Solution: ii. The column chart is preferred as there is a relatively small number of columns, so it is not too cluttered, and this chart also includes labels of specific values. 7. R&D Project Portfolio. The Ajax Company uses a portfolio approach to manage their research and development (R&D) projects. Ajax wants to keep a mix of projects to balance the expected return and risk profiles of their R&D activities. Consider a situation in which Ajax has six R&D projects as characterized in the following table. Each project is given an expected rate of return and a risk assessment, which is a value between 1 and 10, where 1 is the least risky and 10 is the riskiest. Ajax would like to visualize their current R&D projects to keep track of the overall risk and return of their R&D portfolio. LO 3 Project 1 2 3 4 5 6

Expected Rate of Return (%) 12.6 14.8 9.2 6.1 21.4 7.5

Risk Estimate 6.8 6.2 4.2 6.2 8.2 3.2

Capital Invested ($ Millions) 6.4 45.8 9.2 17.2 34.2 14.8

Which of the following chart types would be the most appropriate for these data? Explain your answer. i. Stacked bar chart ii. Line chart iii. Bubble chart iv. Funnel chart Solution: iii. A bubble chart is most appropriate, as the data are three quantitative variables. 8. E-Marketing Campaign. Gilbert Furniture has initiated a new marketing campaign for its high-end desk lamp. The analyst for e-commerce, Lauren Stevens, has been tracking the progress of the campaign and has collected the following data based on an email sent to the customer list: 68% opened the email, 29% clicked on the web link in the email, 11% added the desk lamp to their cart, and 9% purchased the lamp. LO 3 Which of the following is the most appropriate chart for these data? i. Funnel chart ii. Stacked bar chart iii. Line chart iv. Bubble chart Solution: i. A funnel chart is most appropriate as it conveys the natural progression of the customer reactions from an email advertisement. 9. Choosing the Best Chart Type. Choose the most appropriate chart type (bar chart, bubble chart, choropleth map, line chart) for each data set described below. Use each chart type exactly once. LO 3

a. b. c. d.

Advertising budget, number of salespeople, percent of market share for 10 products Annual demand for potato chips (in tons) in the United States by state Annual sales in millions of dollars for seven regional salespeople Population of the United States for each year 1900–2020

Solution: a. Bubble chart, b. Choropleth map, c. Bar chart, d. Line chart 10. Disney Ticket Prices. The three charts below show the price of a general admission ticket to Walt Disney World for the years 2000 to 2020. The first is a bar chart, the second is an area chart, and the third is a scatter chart. LO 3

Which type of chart best displays these data? Why? i. Bar chart ii. Area chart iii. Scatter chart Solution: iii. Scatter Chart. The bar chart is not natural in that the vertical axis is time when generally we make the horizontal axis time. It is also very cluttered. The area chart is also very cluttered. The scatter chart is simple to read and less cluttered. 11. Exploring Private Colleges. For a sample of 103 private colleges, data have been collected on year founded, tuition and fees (not including room and board), and the percentage of undergraduates who obtained their degree within six years (source: The World Almanac). The following two charts plot tuition versus year founded, and graduation rate versus year founded, respectively. LO 4

a. The two charts are the same type of chart. What type of chart are these? i. Line chart ii. Scatter chart iii. Stock chart iv. Waterfall chart b. Which of the following statements best describes the relationship between tuition and year founded? i. Private colleges founded before 1800 are expensive, but there are greater differences in tuition for private colleges founded after 1800. ii. There is no apparent relationship between year founded and tuition for private colleges. iii. The newer the private college, the higher the tuition. c. Which of the following best describes the relationship between graduation rate and year founded? i. There is no apparent relationship between year founded and graduation for private colleges. ii. The newer the private college, the higher the graduation rate. iii. Private colleges founded before 1800 have high graduation rates, but there are greater differences in graduation rate for private colleges founded after 1800. Solution: a. ii. b. i. c. iii. 12. Vehicle Production Data. The International Organization of Motor Vehicle Manufacturers (officially known as the Organisation Internationale des Constructeurs d’Automobiles, OICA) provides data on worldwide vehicle production by manufacturer. The following three charts, a line chart, a line chart with a table, and a clustered column chart, show vehicle production numbers for four different manufacturers for five recent years. LO 3

Which type of chart best displays these data? Why? i. Line chart ii. Line chart with table iii. Clustered column chart with table Solution: ii. The line chart with table is preferred as it clearly depicts trends over time and provides specific values for comparison.

13. Smartphone Ownership in Emerging Countries. Suppose we have the following survey results regarding smartphone ownership by age in emerging countries. LO 3, 4 Age Category 18-24 25-34 35-44 45-54 55-64 65+

Smartphone (%) 49 58 44 28 22 11

Other Cell Phone (%) 46 35 45 58 59 45

No Cell Phone (%) 5 7 11 14 19 44

a. Which of the following charts best displays these data? i. Stacked column ii. Clustered column b. Is the following statement true or false? Older people are less likely to own a smartphone than a different type of cell phone than younger people. Solution: a. i. The stacked column chart better illustrates the relative percentages as part of the whole. b. True

14. Home Goods Demand. In supply chain planning, demand is often measured in pounds shipped. The following choropleth map shows the demand for home goods, measured in millions of pounds shipped for each state in the United States. LO 4 a. Which five states have the highest demand? b. Compare this map with Figure 2.19, which shows estimates of state population. Does the demand for home goods appear to be related to population? Explain.

Solution: a. California, Illinois, Florida, New York, and Texas have the most demand. b. Demand seems to be correlated with population. The states listed above are also heavily populated as illustrated by the darker shading on both maps in these areas. 15. Coca-Cola Stock Prices. The following stock chart shows stock price performance for Coca-Cola over a two-week period. Note that May 16 and May 17 are a Saturday and a Sunday and are non-trading days. LO 4

a. b. c. d.

What type of chart is this? Which day seems to have the most intra-day price variability? What is the closing price on May 22? If you bought 100 shares at the closing price on May 19 and sold all of those shares at the closing price on May 22, how much did you gain or lose (ignoring any transaction fees)?

Solution: a. High-Low-Close Stock Chart b. May 15 appears to have the longest bar and hence the most intra-day variance. c. $45.03 d. 100(45.03-44.54) = -$49 This is a gain of $49. 16. Day Trading. In addition to the high, low, and closing price, an open-high-low-close stock chart uses the opening price per share to give an indication of the net change in the stock price from open to close on a given day. This is designated by a box inside the high-low range. The range of the box is determined by the opening and closing price per share. A black box indicates a loss and a white box indicates a gain for that day. The length of the box indicates the magnitude of the loss or gain in share price. The following chart is an open-high-low-close chart for a two-week period for Coca-Cola. Note that May 16 and May 17 are a Saturday and a Sunday and are non-trading days. Day trading is the practice of purchasing and then selling stock within the same day. As a novice day trader, your strategy is to buy at the start of the day and sell at the end of the day. LO 4

For which days would you have a gain, and which would you have taken a loss (ignoring transaction costs) for the Coca-Cola data shown in the chart? Solution: Loss on May 11, 12, 13, 15, 19, 21 and 22. Gain on May 14, 18, and 20.

APPLICATIONS 17. Smart Speaker Usage. Futuresource Consulting conducted a survey of owners of smart speakers to better understand how they use these devices (source: The Wall Street Journal). The file smartspeaker contains the percentage of the respondents who use their smart speaker for each of 11 activities. LO 1, 2, 4 Use Category

Percentage of Respondents

Checking sports

27%

Checking the news

40%

Checking weather/traffic

49%

Controlling lighting

26%

Finding local information

30%

Fun stuff (jokes, etc.)

36%

Listening to music

74%

Looking up facts

39%

Making phone calls

24%

Setting reminders/alarms

35%

Using a kitchen assistant

24%

a. Construct a bar chart that shows the percentage of respondents by category. Use “How People Use Their Smart Speakers” for the chart title. Edit the chart to make it easier to interpret. Add data labels. b. Sort the data so percentages are ordered from smallest to largest, and note the differences in the corresponding chart. c. Of the categories in the survey, which is the most popular use of a smart speaker? What is the least popular use? Solution: a.

c. The most popular use is “Listening to music” (74%). The least popular is a tie between “Using a kitchen assistant” and “Making phone calls” (24%). 18. Age and Ridesharing. A Gallup Poll showed that 30% of all Americans regularly use a ridesharing service such as Lyft or Uber. The file rideshare contains the survey results by age category. LO 1, 2, 4 a. Construct a column chart that shows the percentage of respondents who use a ride share by age category. Use “Who Uses Rideshare?” as the chart title and “Age (years)” as the horizontal axis title. Edit the chart to make it easier to interpret. Add data labels. b. Comment on the results. Age

Percentage who use Rideshare

18-29

45%

30-49

36%

50-64

23%

65+

13%

Solution: a.

b. The column chart confirms what our intuition likely suggests. Older age categories use rideshare less than younger age categories. 19. Exploring Private Colleges (Revisited). In this problem, we revisit the charts in Problem 11 showing the relationships between tuition and year founded, and graduation rate and year founded. The two charts are similar. Consider the data in the file colleges. The file contains the following data for the sample of 102 private colleges: year founded, tuition and fees (not including room and board), and the percentage of undergraduates who obtained their degree within six years (source: The World Almanac). LO 1, 2, 4 a. Create a scatter chart to explore the relationship between tuition and percent who graduate. Use “Graduation Rate versus Tuition” as the chart title, “Tuition” as the horizontal axis title, and “Graduation Rate (%)” as the vertical axis title. b. Comment on any apparent relationship.

Solution: a.

b. As the scatter chart reveals, there is a positive correlation between the tuition a college charges and its graduation rate. 20. Top Management. The Drucker Institute ranks corporations for managerial effectiveness based on a composite score derived from the following five factors: customer satisfaction, employee engagement and development, innovation, financial strength, and social responsibility. The file managementtop25 contains the top 25 companies in the Institute’s ranking based on the composite score (source: The Wall Street Journal). For each company, the industry sector, the company name, and the composite score are given. LO 1, 2, 4 a. Create a treemap chart using these data with the sector being the category, company being the subcategory, and the composite score being the quantitative variable. Use “Management Top 25” for the chart title. Hint: Be sure to first sort the data by sector. b. In the sector with the most companies in this top 25, which company has the highest composite score?

Solution: a.

b. Technology is the sector with the most companies in the Top 25 list. Out of these 12 companies, Amazon has the highest composite score. 21. Biodiversity Preservation. Ecologists often measure the biodiversity of a region by the number of distinct species that exist in the region. Nature reserves are lands specifically designated by the government to help maintain biodiversity. Care must be taken when setting up a network of nature reserves so that the maximum number of species can exist in the network. Geography matters as well, as putting reserves too close together might subject the entire network to risks, such as devastation from wildfires. The initial step in this type of planning usually involves mapping the number of species that exist in each region. The file species contains the number of unique species that exist in each of the 50 states in the United States. LO 1, 2, 4 a. Create a choropleth map that displays number of species by state. Use “Number of Species per State” for the chart title. Add data labels. b. Comment on the distribution of species over the United States. Which regions of the United States have relatively many species? Which regions have relatively few species? c. Which two states have the most species?

Solution: a.

b. The southeast and the west regions have many species, the north central and northeast regions have relatively few species. c. California and Texas have the most species. 22. Disney Ticket Prices (Revisited). In this problem, we revisit Problem 10, which displays the price of a general admission ticket to Walt Disney World for the years 2000 to 2020. However, these prices did not factor in inflation over these years. The file disneypricesadjusted gives the general admission price and the general admission price adjusted for inflation for the years 2000 to 2020. LO 1, 2, 4 a. Create a line chart that shows the price of admission and the adjusted price of admission for the years 2000 to 2020. b. Explain what the adjusted ticket price data series shows that the nominal ticket price data series did not.

Solution: a.

b. The prices adjusted for inflation show historical ticket prices in 2020 dollars and therefore allow the audience to observe how much ticket prices increased above the price increases in goods across the U.S. economy. 23. Bubble Chart Labels. The following bubble chart shows TSA wait time (in minutes) on the horizontal axis, cheapest daily parking rate on the vertical axis, and the size of each bubble is the number of enplanements in a year (measured in millions). The file airportbubblechart contains this chart. LO 2, 4

a. Using the following steps, add labels to the bubbles so that the airport codes are easily identifiable with each bubble. Step 1. Click anywhere on the chart Step 2. Click the Chart Elements button

and select Data Labels

This puts the value of enplanements in each bubble Step 3. Click the Chart Elements button

and move the cursor over Data Labels,

and then select the black triangle on the right down menu.

to open a drop-

From this drop-down menu, select Other Options to reveal the Format Data Labels task pane Step 4. When the Format Data Labels task pane appears, under Label Contains, select Value from Cells and click the Select Range…button When the Data Label Range dialog box opens, select cells A2:A16 in the worksheet Click OK Step 5. In the Format Data Labels task pane, deselect Y Value under Label Contains b. Which airport has the lowest TSA wait time? c. Which airport has the most enplanements?

Solution: a.

b. The airport with the lowest TSA wait time is BNA. c. The airport with the most enplanements is the one with the largest bubble: ATL. 24. Regional Gains and Losses in Population. The U.S. Census Bureau tracks shifts in population by each state and region in the United States. The net migration rate is an indicator of the movement of people from one area to another. A positive net migration rate means more people moved to the area than moved away, and a negative net migration rate means more people left the area than moved to the area. Mathematically the net migration rate N is defined as follows:

(I-E) 1000 M

Where I = number of people moving to the area in the year under consideration, E = the number of people moving away from the area in the year under consideration, and M = the mid-year population of the area. The file netmigration contains net migration rates for four regions of the United States. LO 1, 4 a. Create a heat map using conditional formatting with the Blue-White-Red Color Scale. b. Which regions are losing population? Which regions are gaining population?

Solution: a.

b. The Northeast and Midwest regions are losing population whereas the South and West are gaining population. 25. Income Statement. An income statement is a summary of a company’s revenues and costs over a given period time. The data in the file bellevuebakery is an example of an income statement. It contains the revenues and costs for last year for Bellevue Bakery. Revenues include gross sales and other income. Costs include returns, cost of goods sold, advertising, salaries/wages, other operating expenses, and taxes. In the income statement there are intermediate calculations: Net Sales = Gross Sales – Returns Gross Profit = Net Sales – Other Income – Cost of Goods Sold Net Income Before Taxes = Gross Profit – Advertising – Salaries/Wages – Other Operating Expenses Net Income = Net Income Before taxes – Taxes Create a waterfall chart of the income statement for Bellevue Bakery. Use “Bellevue Bakery Income Statement” for the chart title. Click the column associated with each of the calculations above and select Set as Total. Edit the chart to make it easier to interpret. LO 1, 2 Solution:

26. Marathon Records. The file marathonrecords contains marathon world records for ages from 6 to 90 for women and men (records for ages 9 and 10 are missing) LO 1, 2, 4 a. Create a scatter chart with age on the horizontal axis and the women’s marathon record on the vertical axis. Use “Female Marathon Records (minutes)” as the vertical axis title and “Age (years)” as the horizontal axis title. Edit the chart to improve interpretation. b. Create a scatter chart with age on the horizontal axis and the men’s marathon record on the vertical axis. Use “Male Marathon Records (in minutes)” as the vertical axis title and “Age (years)” as the horizontal axis title. Edit the chart to improve interpretation. c. Create a scatter chart that plots both the women’s record versus age and the men’s record versus age. Select Scatter with Straight Lines. Use “Marathon Records (minutes)” as the vertical axis title and “Age (years)” as the horizontal axis title. Edit the chart to improve interpretation. d. Based on the charts in parts a, b, and c, what observations can you make regarding the women’s and men’s marathon records? Solution: a.

d. The female and male data follow the same pattern. As expected, younger and older ages have slower record times. The fastest records for both females and males are generally between the ages of 20 and 40. Male records are faster for a given age with a few exceptions at earlier ages.

Solution and Answer Guide

END OF SECTION PROBLEM SOLUTIONS CONCEPTUAL 1.

Memory Used for Preattentive Attributes. Which of the following types of memory is used to process preattentive attributes? LO 1 i. Iconic memory ii. Short-term memory iii. Long-term memory iv. Random access memory Solution: i.

2. Preattentive Attributes in a Data Visualization. Which of the following statements about the use of preattentive attributes in a data visualization are true? (Select all that apply.) LO 1 i. The use of preattentive attributes reduces the cognitive load required by the audience to interpret the information conveyed by a data visualization. ii. Preattentive attributes can be used to draw the audience’s attention to certain parts of a data visualization. iii. Overuse of preattentive attributes can lead to clutter and can be distracting to the audience. iv. Preattentive attributes include attributes such as proximity and enclosure. Solution: i., ii., and iii 3. Descriptions of Gestalt Principles. For each description below, provide the name of the Gestalt principle that is being described. LO 2 a. Objects that are physically close to one another are seen as belonging to the same group. b. Objects that are linked in some way are seen as belonging to the same group. c. Objects that are physically bound together are seen as belonging to the same group.

d. Objects with like characteristics such as color, shape, size, etc. are seen as belonging to the same group. Solution: a. Proximity, b. Connection, c. Enclosure and d. Similarity 4. Scatter with Straight Lines and Markers Chart in Excel. Using a Scatter with Straight Lines and Markers Chart in Excel makes use of which Gestalt principle? LO 2 i. Similarity ii. Proximity iii. Enclosure iv. Connection Solution: iv. 5. Increasing the Data-Ink Ratio on a Chart. Which of the following changes to a chart would increase the data-ink ratio? (Select all that apply.) LO 3 i. Removing unnecessary gridlines. ii. Removing a legend on a bar chart where each bar is already labeled with the same information. iii. Adding axes labels to a chart where the units used in each axis are not clear from the chart title. iv. Adding data labels for each point on a scatter chart when the audience does not need to know exact values. Solution: i., ii., and iii. 6. Pie Charts versus Bar or Column Charts. Which of the following reasons accurately describe why bar or column charts are often preferred to pie charts for a data visualization? (Select all that apply.) LO 4 i. Bar and column charts utilize the Gestalt principle of proximity while pie charts use the Gestalt principle of connection. ii. Using a legend for a pie chart creates unnecessary eye travel that can often be reduced by using a bar or column chart that does not require a legend. iii. Bar and column charts use length rather than size to make comparisons, and length is much easier for the audience to interpret than size. iv. Pie charts often use different colors to differentiate each piece of the pie that can create unnecessary clutter compared to bar or column charts that can display the same information without the use of multiple colors. Solution: ii., iii., and iv. 7. Market Segmentation for Brandience. Brandience Marketing LLC provides marketing analytics consulting for clients. For one of its clients, Brandience has been asked to perform a market segmentation study for a business client that provides auditing services to manufacturing customers. The client believes there are two variables of importance that should be used to group similar customers into clusters: Years of Service with the Client and Total Assets. Brandience plans to use a clustering algorithm to group similar customers into different clusters, but before applying the algorithm, Brandience creates a simple scatter chart to plot each customer based on their Years of Service with the Client and Total Assets. The scatter chart created by Brandience follows. LO 2

a. Based on the Gestalt principle of proximity, how many different groups of customers are shown in the scatter chart? b. What other Gestalt principle could be used to reinforce the appearance of the different groups of customers for the audience? Solution: a. Based on the principle of proximity, there appears to be three distinct groups of customers. One group has relatively low Total Assets and Years of Service; another group has relatively low Total Assets but high Years of Service; and another group has high Total Assets and Years of Service. b. The Gestalt principle of enclosure could be used to emphasize these three different groups in the scatter chart. (See solution to Problem 17 for an updated chart.)

8. Philadelphia City Schools. The following chart displays data related to the student enrollment at several schools located in the Philadelphia City School District. LO 1, 4

a. Which preattentive attributes are being used in this chart? b. What changes would you suggest to this chart to make it easier for the audience to interpret? Solution: a. The pie chart uses the preattentive attributes of size and color to differentiate between the enrollments. Because size is difficult for people to compare, it is difficult to tell how much larger the enrollments of some schools are than others. The use of color here is also overwhelming and distracting. b. This chart would be better displayed as a sorted bar (or column) chart that uses length rather than size to make relative comparisons. Using a sorted bar (or column) would also remove the need for using color to differentiate among the schools. It would be less distracting and would require less eye travel than this pie chart where the audience must match up the school name from the legend with the appropriate piece of the pie. 9. Percentage of College Graduates and Median Monthly Rent in New York City. The following scatter chart shows the relationship between the percentage of college graduates and the median monthly rent in different sub-boroughs (a description of an area within the city) of New York City. Each point in the scatter chart represents a different sub-borough in New York City. LO 1

College Graduates (%) 100

0 0

500

1,000

1,500

2,000

2,500

Median Monthly Rent ($)

a. Which preattentive attribute is used in this scatter chart? b. What can you say about the relationship between the percentage of college graduates and the median monthly rent in sub-boroughs of New York City? Solution: This scatter chart uses the preattentive attribute of spatial positioning to show the relative percentage of college graduates and median monthly rent of different subboroughs in New York City. The higher the point corresponding to a sub-borough is placed vertically on the chart, the higher the percentage of college graduates within that sub-borough. The further right horizontally the point corresponding to a sub-borough is placed on the chart, the higher the median monthly rent is in that sub-borough. This chart tells us that as median monthly rent increases, the percentage of college graduates increases. 10. Patient Waiting Times at Scott & White. The Scott & White Medical Center in Temple, Texas, is examining the mean waiting time for patients to receive common orthopedic surgeries at its facility. The medical administrators have collected data on patient waiting times. The mean patient waiting times (in days) for different orthopedic surgeries are shown in the chart below. The medical center is particularly interested in the wait times for anterior cruciate ligament (ACL) reconstruction surgeries because ACL reconstructions are the most common type of orthopedic surgery performed at its facility. LO 1

What types of preattentive attributes are used in this chart and what information do these attributes convey to the audience? Solution: The bar chart uses the preattentive attribute of length to make it easy to compare the mean patient wait time for each type of surgery. It is easy to determine that Ankle Repairs have the longest patient wait time and Shoulder Arthroscopies have the shortest wait time. The chart also uses the preattentive attribute of color to direct the reader’s attention to the mean patient wait time for ACL Reconstruction since this type of surgery is of special interest to medical administrators. 11. Comparing Managing Consultants at Platt Consulting. Platt Consulting Services is interested in comparing the number of customer accounts managed by its consultants. In particular, the company needs to compare the number of accounts managed by Bernie Smith, Stanley Lucas, and Gracie Rogers because these three accountants started with the company at approximately the same time and have similar skills. The chart below is a default chart produced in Excel. LO 1

a. Which preattentive attributes are used in the pie chart? b. What preattentive attributes would you use in redesigning this chart to improve its clarity for the audience? Solution: a. The pie chart uses the preattentive attributes of shape and color. However, the use of shape is ineffective because it is difficult to compare the relative sizes of the pieces of the pie to determine which managers are handling the greatest number of customer accounts. The use of a different color for each piece of the pie in the chart is also overwhelming and ineffective. b. A bar chart uses length to compare the number of customer accounts management, so would likely be a much better data visualization choice here. Because we are most interested in comparing Bernie Smith, Stanley Lucas, and Gracie Rogers, it would make more sense to use one color for these three bars and a different color for the other bars to make the comparison easy for the audience. (See solution to Problem 17 for an example chart.) 12. Basketball Scouting Report. Meg Lawson is an assistant coach for a high school basketball team. Meg has prepared a scouting report on her team’s upcoming opponent, which is led by a star player who prefers to shoot from near the 3-point line. Meg has created the following diagram to show this star player’s shot selection from a recent game. Each × corresponds to a missed shot and each ⚫ corresponds to a made shot. Meg has noticed that the star player made most of her shots from the area on the court located at the top of the 3-point line while missing most of her shots from the corners. Meg wants to draw her team’s attention to these areas so that they can properly defend against this star player. LO 1, 2

a. What preattentive attributes and Gestalt principles has Meg used in this diagram? b. Are the preattentive attributes and Gestalt principles used in this diagram effective in communicating Meg’s message to her audience? Why or why not? Solution: a. The diagram uses the preattentive attributes of shape and spatial positioning. The diagram differentiates between made and missed shots using different shapes, and it uses spatial positioning to communicate the positions on the court from which the star player took her shots. It is very helpful that the diagram includes the markings of an actual basketball court to indicate this spatial positioning in a frame of reference that should be familiar to Meg’s team. The diagram also uses the Gestalt principles of similarity, proximity, and enclosure. Meg has used a common symbol to indicate made shots so the audience groups these together, and a different common symbol to indicate missed shots so the audience easily groups these together. Meg has also used dashed lines to enclose groups of shots taken by the star player that are in close proximity in three different areas of the court: at the top of the 3-point line and in each corner. This could correspond to the star player’s preferred areas from which to shoot. b. The diagram is effective at indicating from where the star player likes to take shots and also where she tends to make a higher percentage of her shots. It should be noted that these data are only for a single game, and we do not know what type of defense the opposing team was playing against the star player when these data were collected. However, this diagram is likely still useful for helping Meg’s team prepare a defensive plan against this player.

13. Revenues and Costs at a Manufacturing Firm. A manufacturing firm would like to compare its monthly revenues and costs for the previous year. The following table shows the revenues and costs for the company in each month of the previous year. Of particular interest to the company is how the company’s revenues and costs perform during the summer months because that is when the company’s sales tend to be highest. LO 3 Month

Revenues ($1,000s)

Costs ($1,000s)

Jan

1,121

1,007

Feb

997

1,002

Mar

1,151

1,010

Apr

1,202

1,085

May

1,422

1,287

Jun

1,877

1,488

Jul

1,911

1,621

Aug

1,988

1,625

Sep

1,521

1,617

Oct

1,288

1,178

Nov

1,100

1,008

Dec

1,022

987

a. Compare the two charts above (Chart A and Chart B). Which chart has the higher dataink ratio? Why? b. How does the chart with the higher data-ink ratio help the audience interpret the chart more effectively? Solution: a. Chart B has the higher data-ink ratio. The ink used in Chart B is more relevant to emphasizing what is important to the audience in the chart. b. In Chart A, the gridlines serve no useful purpose. The horizontal and vertical axes titles in Chart A are also redundant and do not convey unique useful information. However, Chart A is missing important information on the scale of the vertical axis – the revenues and costs are shown in $1,000s. Chart A labels each month on the horizontal axis, but it does not clearly define the summer season. Because summer is the most important season to label for this chart, Chart B includes a better use of ink to label the summer season. 14. Serif versus Sans-Serif Fonts. Consider the fonts that are shown in the table that follows. LO 5 (1) (2) (3)

Abcd123 Abcd123 Abcd123

(4) A b c d 1 2 3 (5) A b c d 1 2 3 (6) A b c d 1 2 3

(7) A b c d 1 2 3 (8) A b c d 1 2 3 (9) A b c d 1 2 3

a. Which of these fonts are considered serif fonts? b. If you are creating text for a data visualization that will mostly be viewed on smartphones and other small mobile devices, would you suggest using the serif fonts for text in the data visualization? Why or why not? Solution: a. Fonts (3), (4), (6), and (7) are serif fonts because the characters contain serifs. b. Sans serif fonts are preferred to serif fonts for data visualizations viewed on smartphones and small mobile devices. This is because sans serif fonts are generally better for text displayed digitally and for text displayed at small sizes.

15. Approval Voting Results. Approval voting is a type of voting system in which voters can vote for any number of eligible candidates. A vote for a candidate in this type of system indicates that the voter “approves” of that candidate for the position the candidate is seeking. In the final tally of the election results, the total number of approval votes for each candidate is calculated. The candidate who receives the most approval votes is declared the winner. The figure below displays the results from an approval voting election in which there were four eligible candidates: C. Sittenfeld, A. Marshall, S. Keskin, and K. Nowak. A total of 1218 voters participated in this election. The table below shows the number of votes received by each candidate as well as the proportion of voters who approved of each candidate. LO 4, 6 Candidate C. Sittenfeld A. Marshall S. Keskin K. Nowak

Number of Votes Received 482 689 354 514

Proportion of Voters Who Approve of This Candidate 39.6% 56.6% 29.1% 42.2%

a. The figure displays the election result proportions as a 3D pie chart. What problems are associated with displaying the election results in this way? b. Suggest an alternative type of chart for displaying these data that would be easier for the audience to interpret. Explain why this alternative type of chart would be easier to interpret. Solution: a. Because the percentages do not add up to 100%, a pie chart is not an effective way to display this information. Furthermore, the third dimension in this 3D pie chart does not convey any meaningful information, so it only adds clutter. The pie chart also requires considerable eye travel because the audience must match the name of each candidate with the appropriate color in the pie. b. A 2D bar or column chart would be better to display these data. Bar and column charts use length rather than area to communicate insights to the audience, so they are easier to interpret. A bar or column chart also removes the need for a legend,

which reduces eye travel as well as the need for different colors and simplifies the chart. Finally, a bar or column chart can also be sorted to make it even easier for the audience to interpret the results of the election. 16. Headcount and Annual Revenues at BeFit Gyms. BeFit Gyms operates four exercise gyms in the state of Michigan. The gyms are located in Saline, Tecumseh, Dexter, and Jackson. The gyms are different sizes, and they require different headcounts of staff to operate. The table below displays the headcount, in full-time equivalent (FTE) employees and annual revenue ($1,000s) for each of the four locations. LO 4, 6 Gym Location Saline Tecumseh Dexter Jackson

Headcount (FTE)

Annual Revenue ($1,000s)

18 11 9 12

787 674 784 642

a. The clustered column chart displays the headcount and annual revenue for each location. What are the problems with displaying these data using this type of chart? b. Would it be appropriate to display these data using a stacked column chart rather than a clustered column chart? Solution: a. The units for headcount (FTE) are different from the units for annual revenue ($1,000s), so it is not clear what the vertical axis in this graph represents. Because the scale of headcount is also very different from annual revenue, it is difficult to see the columns corresponding to headcount in this chart, and it is nearly impossible to make relative comparisons among these columns. b. Because the two variables shown in this chart represent different units (FTE for Headcount and $1,000s for Annual Revenue), it would not be appropriate to combine them for a stacked column chart.

APPLICATIONS 17. Comparing Managing Consultants at Platt Consulting (Revisited). In this problem, we revisit the pie chart in Problem 11. Platt Consulting Services is interested in comparing the number of customer accounts managed by its managing consultants. In particular, the company needs to compare the number of accounts managed by Bernie Smith, Stanley Lucas, and Gracie Rogers because these three accountants started with the company at about the same time and have similar skill sets. The following chart is a default chart produced in Microsoft Excel. LO 1, 6

Use the data in the file plattconsulting to create a new data visualization that uses a different type of chart and proper use of preattentive attributes to allow for easier comparison of the number of accounts managed by each consultant. Solution: Instead of a pie chart that relies on the preattentive attribute of size, we choose a bar chart because it uses the preattentive attribute of length. This makes it much easier to compare the relative number of customer accounts managed by each manager. We also use the preattentive attribute of color to differentiate the managers Bernie Smith, Gracie Rogers, and Stanley Lucas to make it easier to compare these three managers against one another.

18. Market Segmentation for Brandience (Revisited). In this problem, we revisit the scatter chart in Problem 7. Starting with the chart in the file brandience_chart, modify the chart by using an additional Gestalt principle to make it more obvious to the audience which clients are in each cluster. Which additional Gestalt principle did you use? LO 2, 4 Solution:

Market Segmentation Analysis of Brandience Clients Total Assets ($ millions) 30

0 0

Years of Service

The modified chart also uses the Gestalt principle of enclosure.

19. Comparing Direct and Indirect Costs at Sackenheim Compressors. Sackenheim Compressors, Inc. manufactures air compressors for industrial use. The company is performing an analysis of its direct and indirect costs related to manufacturing a particular type of air compressor. It has gathered cost data for the previous 20 weeks, and it would like to identify any trends in the data. A scatter chart created using the data follows. LO 2, 4

a. Which Gestalt principle should be used here to make it easier for the audience to identify trends in the data? b. Using the chart included in the file sackenheim_chart as a starting point, create an improved version of this chart by applying the Gestalt principle from part a that makes any trends in the data more obvious to the audience. Solution: a. The Gestalt principle of connectedness should be applied separately to the series of data points for direct and indirect costs. This will make any trends in the data more obvious to the audience. b. The chart below uses the Gestalt principle of connectedness to make trends more obvious to the user. This chart does not include the individual markers at each data point, but this is mainly a matter of preference, and these can be retained if preferred.

20. Sales Performance Bonuses. A sales manager is trying to determine appropriate sales performance bonuses for her team this year. The following table contains the data relevant to determining the bonuses, but it is not easy to read and interpret. Reformat the table using the data in the file salesbonuses to improve readability and to help the sales manager make her decisions about bonuses. (Hint: It will also help the sales manager if the table is ordered from top-to-bottom by Total Sales.) LO 3

Salesperson Smith, Michael

Total Sales ($) 325000.78

Average Performance Bonus Previous Years ($) 12499.3452

Customer Accounts 124

Years with Company 14

Yu, Joe

13678.21

239.9434

Reeves, Bill

452359.19

21987.2462

175

Hamilton, Joshua

87423.91

7642.9011

Harper, Derek

87654.21

1250.1393

Quinn, Dorothy

234091.39

14567.9833

Graves, Lorrie

379401.94

27981.4432

121

Sun, Yi

31733.59

672.9111

Thompson, Nicole

127845.22

13322.9713

Solution: Total Sales ($) 452,359

Average Performance Bonus Previous Years ($) 21,987

Customer Accounts 175

Years with Company 21

Graves, Lorrie

379,402

27,981

121

Smith, Michael

325,001

12,499

124

Quinn, Dorothy

234,091

14,568

Thompson, Nicole

127,845

13,323

Harper, Derek

87,654

1,250

Hamilton, Joshua

87,424

7,643

Salesperson Reeves, Bill

Sun, Yi

31,734

673

Yu, Joe

13,678

240

The readability of the table has been improved by removing unnecessary gridlines, leftaligning the first column because it contains text entries, right-aligning all other columns because they contain numerical values, and sorting the table to put the largest values of Total Sales ($) at the top of the table. 21. Gross Domestic Product Values over Time. The following table shows an example of gross domestic product values for five countries over six years in equivalent U.S. dollars ($). LO 3 Gross Domestic Product (US $) Country

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Albania

7385937423

8105580293

9650128750

11592303225

10781921975

10569204154

Argentina

169725491092

198012474920

241037555661

301259040110

285070994754

339604450702

Australia

704453444387

758320889024

916931817944

982991358955

934168969952

1178776680167

Austria

272865358404

290682488352

336840690493

375777347214

344514388622

341440991770

Belgium

335571307765

355372712266

408482592257

451663134614

421433351959

416534140346

a. How could you improve the readability of this table? b. The file gdp_years contains sample data from Year 1 to Year 6 in us $. Create a table that provides all these data for an audience. Format the table to make it as easy to read as possible. Hint: It is generally not important for the audience to know GDP to an exact dollar figure. It is typical to present GDP values in millions or billions of dollars. Solution: a. The readability of the table could be improved by removing unnecessary gridlines, left-aligning the first column because it contains text entries, right-aligning all other columns because they contain numerical values, reducing the number of digits displayed by displaying numerical values in millions or billions of dollars, and using shading to differentiate the columns. Note that we could also sort these values by GDP in the most recent year if this was the value of most interest.

b. Gross Domestic Product (US $ billions) Country

Year 1

Year 2

Year 3

Year 4

Year 5

Year 6

Albania

7.4

8.1

9.7

11.6

10.8

10.6

Argentina

169.7

198.0

241.0

301.3

285.1

339.6

Australia

704.5

708.3

916.9

983.0

934.2

1178.8

Austria

272.9

290.7

336.8

375.8

344.5

341.4

Belgium

335.6

355.4

408.5

451.7

421.4

416.5

Brazil

756.8

935.5

1175.0

1407.0

1370.8

1782.4

Canada

1056.8

1193.9

1332.1

1404.8

1245.2

1469.9

18.9

21.2

24.7

28.0

33.9

Costa Rica Czech Republic

111.7

128.7

156.6

194.6

170.9

172.6

Finland

169.9

180.2

214.8

236.6

206.7

207.9

France

1915.0

2015.0

2312.8

2541.6

2367.9

2301.8

Germany

2516.9

2632.8

2984.7

3258.9

2973.7

2966.1

Greece

217.2

234.3

273.8

307.1

292.6

269.1

Ireland

177.7

195.3

229.6

233.3

199.5

186.2

Israel

122.4

133.2

153.4

185.7

178.7

199.8

Italy

1597.3

1661.9

1892.7

2063.9

1900.0

1836.9

Mexico

823.3

928.5

1011.9

1085.6

858.3

1010.3

Netherlands

567.8

600.9

694.8

775.4

708.0

700.8

New Zealand

108.9

105.1

129.0

125.7

112.9

136.2

Peru

72.3

84.4

98.5

117.8

120.4

144.3

Poland

267.8

300.1

371.9

463.4

282.6

413.4

Portugal

165.3

172.9

200.1

218.7

206.0

200.5

Saudi Arabia

317.4

358.4

386.7

477.3

374.4

436.2

Singapore

119.7

139.1

167.0

179.3

173.8

209.7

South Africa

220.3

232.2

254.4

247.5

256.9

328.8

Spain

1012.0

1100.0

1293.2

1459.0

1361.0

1287.9

Switzerland

250.6

368.2

409.2

474.7

464.9

498.2

Turkey

425.5

467.9

579.0

656.6

557.7

655.8

United Kingdom

2030.3

2178.0

25.04.6

2381.9

1959.2

2005.6

United States

12,579.7

13,336.3

14,010.9

14,369.5

14,113.3

14601.6

22. Monthly Revenue Data at Tedstar, Inc. The following table provides monthly revenue values for Tedstar, Inc., a company that sells valves to large industrial firms. The monthly revenue data have been graphed using a line chart in the following figure. LO 3, 4 Month Revenue ($)

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

145,869

123,576

143,298

178,5050

186,850

192,850

134,500

145,286

154,285

148,523

139,600

148,235

a. What characteristics of this line chart make it more difficult than necessary to interpret? b. Using the data in the file tedstar, create a new line chart for the monthly revenue data at Tedstar, Inc. Format the chart to make it easy to read and interpret. Use the chart title “Tedstar, Inc. Revenue Analysis” and the vertical-axis title “Monthly Revenue ($).” Solution: a. The chart contains unnecessary gridlines, the vertical-axis label values are spaced much too close together, and the shading of the chart does not add value. b.

23. Comparing Hoxworth Blood Center Locations. Hoxworth Blood Center, located in Cincinnati, Ohio, is a leader in transfusion medicine. Founded in 1938, it is the second oldest blood bank in the United States. Hoxworth operates at seven locations in the Greater Cincinnati area where it collects blood from donors. Hoxworth is performing a comparison of the amount of blood donors serviced by each of its locations during the month of October. Suppose that Hoxworth has a stated goal of each location servicing an average of 50 blood donors per day each month. The column chart that follows compares the average number of blood donors at each Hoxworth location. However, this chart is cluttered, and the data-ink

ratio is low. Starting with the chart in the file hoxworth_chart, declutter the chart and improve the data-ink ratio to produce an improved column chart. LO 3, 4

Solution: There is a lot of redundant information in this chart that can be removed to declutter the chart. The grid lines, other than the goal of 50 donors, serve no useful purpose and can be removed. The legend of “Number of Donors Serviced” is redundant with both the chart title and the vertical axis title; therefore, we can also remove the chart legend. Finally, we can further improve the data-ink ratio by simplifying the vertical axis title and the label for the goal amount. In the chart shown below, we have also moved the vertical axis title location and rotated the text to reduce the amount of eye travel required by the audience.

24. Comparing Package Delivery Drivers. Red Sky Delivery performs “last-mile” delivery services for online retailers such as Amazon. Red Sky employs delivery drivers who perform the “last-mile” of delivery service by delivering packages to individual residence and business locations. Red Sky measures several delivery driver performance metrics, including number of delivery stops completed per eight-hour shift. The table below provides data on nine Red Sky delivery drivers and their average number of packages delivered per shift over the previous 30 days. LO 1, 3, 4 Delivery Driver

Average Number of Delivery Stops Completed (per shift)

Amy Urbaczewski

92.87

Sally Melouk

110.78

Brenda Barnes

114.2

Jonathan Payne

132.5

Bruce Wheeler

148.2

Cam Madsen

87.51

Sheila Stevens

139.82

Grant Inhara

154.23

Finn Helton

109.11

a. Using the data in the file redsky_deliveries, create a column chart to display the information in the table above. Format the column chart to best display the data. Use a chart title of “Comparing Red Sky Delivery Drivers,” and a vertical axis title of “Average number of deliveries per shift.” Get rid of any unnecessary gridlines and add data labels that show the average number of delivery stops completed for each driver. b. Create a sorted column chart to make it easier for the audience to see which drivers have the highest and lowest average number of delivery stops per shift. c. Further investigation by Red Sky indicates that all of these delivery drivers except Amy Urbaczewski have similar delivery routes. Amy typically delivers in more rural areas while all other drivers support more urban routes. Red Sky wants to draw attention to the fact that Amy’s routes are different than the others. Modify the sorted column chart by changing the color of the column associated with Amy Urbaczewski to indicate that this column is different from the others. Solution: Note that we do not include vertical axes in the charts below because we use data labels on the columns. This makes the vertical axis redundant for these charts.

25. Inbound and Outbound Shipping Costs at Rainbow Camping. Rainbow Camping makes outdoor equipment for camping and backpacking that it sells online. Because Rainbow Camping mails each of its sales from its distribution center direct to the consumer, it spends considerable funds each month on outbound shipping. Rainbow Camping also has costs related to inbound shipping to get its products from the manufacturers to its

distribution center. Rainbow Camping would like to examine its outbound and inbound shipping expenses over the last 12 months (labeled months 1 through 12). These shipping costs are shown in the following table. LO 4 Shipping Costs ($1,000s) Month

Outbound

Inbound

478

324

524

274

628

224

787

292

648

348

612

309

598

378

641

287

546

292

476

345

378

304

512

298

a. Using the data in the file rainbowcamping, a single chart that displays both the outbound and inbound shipping costs for each month on the same chart. Use the title “Rainbow Camping Shipping Costs Analysis” for the chart title, “Costs ($1,000s)” for the vertical-axis title and “Month” for the horizontal-axis title. Format the chart title and vertical axis title to make these easy for the audience to interpret. Modify the Minimum and Maximum Bounds of the horizontal axis so that this axis starts at 1 and ends at 12 (Minimum and Maximum Bounds can be found in the Format Axis task pane by clicking on the Axis Options icon and then under Axis Options.) Remove the markers so that only lines are displayed for each of these shipping costs. Delete any unnecessary gridlines in the chart. b. To further minimize the eye travel required by the audience, delete the default legend created with the chart and use text boxes to label the lines in the chart as “Outbound” and “Inbound”.