Accessing and Analyzing Data with Microsoft Excel (Bpg-Other)

A news report might start with a paragraph similar to the following: “The Smallville County overall crime rate fell by 3% compared to last year. This continues a downward trend for the last 5 years, as the crime rate has fallen an average of 1% per year.” This is a simple trend example. In analyzing business data, trending helps to

• Measure the effectiveness of decisions over time. For example, if a car rental company decides to hire more sales agents at the beginning of a year, and the time required to service customers decreases over the course of that year, the company could use the trend to state that their hiring decision may have led to tangible customer benefits. (Of course, there might be other reasons that customer wait times decreased, which would require further data analysis.)

• Alert you to potential business problems. If you notice that a particular data value is worsening over time, you would want to find out why and decide what you could do to reverse the trend. To measure your proposed solution to the problem, you could keep looking at the trend over a longer period of time.

Determining trends in data can involve recognizing simple differences or performing complex “what if” scenarios. All the data analysis software covered in this book can perform simple trending tasks, but Excel also has built-in support for the most difficult data trending scenarios.

Your Turn

This exercise demonstrates how you can quickly tell whether data facts are improving or worsening over a given time period.

1. In the CustServ.xls file (located in the Chap02 folder), examine the data on the Summarized by Year worksheet, which summarizes a restaurant’s average monthly customer service ratings in several measurement areas, from 1 (poor) to 5 (excellent), over three years.

2. Next examine the summarizations on the Trended by Year worksheet, shown in Figure 2-4.

   Figure 2-4: Data trends for three years’ worth of customer service scores.

After a quick review, you can observe a couple of trends in the three years’ worth of customer service scores:

• All measurement areas worsened over the last three years.

• Cleanliness scores worsened the most.

• Hosts, servers, and cashiers worsened the least, but all three measurement areas worsened at roughly the same rate.

Trends allow you to assess whether data is improving or worsening over time.