Using Excel for Pareto Analysis

A Pareto analysis is the method of looking at all the root causes of a problem and trying to determine which ones have the greatest frequency. The idea behind the analysis is that an entire collection of potential causes can be broken down into those that seldom happen and those that happen on a more frequent basis – in a moment, we'll get to an example that better illustrates this idea.

The technique is called a Pareto analysis because it is based on the Pareto Principle, also known as the 80/20 Rule. This rule states that roughly 80% of the problems stem from around 20% of the possible causes. It's important to note that, in many examples, the actual percentages may differ a bit from 80% and 20%, but the spirit of the principle still remains true. That is, if you can identify and focus on the most frequent causes, the majority of your problem will be handled.

Now, we'll look at an example and show how a Pareto analysis can be performed in Excel.

For our example, we'll take on the role of a manager of a call center that has consistently been getting low ratings in the area of customer service. Our job is to improve the customer service experience, so the first thing we want to know is why our customers are so dissatisfied. To find out, we surveyed our customers and asked them what led them to be unhappy with our customer service policy. We received 366 responses and were able to separate those complaints into ten categories.

Starting Out

The first thing we want to do is create a simple table in Excel that includes the following information:

• Complaint Category
• Number of Complaints in Each Category
• Percentage of Complaints in Each Category

In our table, we want to list these categories by number of complaints in descending order. That is, first list the category with the most complaints, then the one with the second most complaints, and so on. After doing this we want to add one more column to our table:

• Cumulative Percent for Each Category

The cumulative percent for a category will include the percentage of complaints for that category plus the percentage for all categories preceding it in the list. When finished, our table should look like the one pictured below. (Click any image for a larger view.)

click to enlarge

Note: To calculate the percentage of complaints in each category, divide the number of complaints in that category by the total number of complaints. So, for instance, the percent for Too Long on Hold would be 157/366 = 0.4290 or 42.90%. To calculate the cumulative percent, add the percent of that category to all the other percentages above it in the list. For example, the cumulative percent for Not Knowledgeable would be 11.20% + 22.68% + 42.90% = 76.78%.

Even a quick glance at this table shows that the data is heavily weighted and most of the complaints are situated in the first 3-4 categories. However, to get an even better view of this data, we can make a Pareto chart. For directions on how to do this, see Creating Pareto Charts with Microsoft Excel 2007. You can format the chart in any manner you like – one example is shown below.

click to enlarge

From the chart, it becomes even clearer that the top three categories are having the most significant effect on customer service dissatisfaction, and the other categories are having a rather trivial effect. So, in light of this analysis, we know we need to concentrate our efforts on the following three complaints if we want to make any real headway toward improving customer satisfaction:

• Too Long on Hold – If such a large percentage of customers are complaining about spending too much time on hold, it's a good idea to look into hiring more service representatives. But, this problem could also relate to the other two on our list.

• No Evening/Weekend Staff – This is a pretty straightforward problem to deal with. If customers are unhappy because we're only available during daytime hours, we might want to look into extending those hours to include at least some night and weekend time. This could also help with the previous problem, because if the hours of operation are longer, customers may spread out their calling times a little more. This, in turn, could cause less of a strain on those representatives working standard business hours.

• Not Knowledgeable – This immediately suggests that we want to do a better job when training our representatives, and make sure they know their job well before putting them on the phone. Improving on this situation could also have an impact on the first complaint category. Representatives with more knowledge about their job generally handle calls more efficiently, so they would not need to spend as much time on the phone with each customer. This lowering of average handling time would allow them to take more calls during the same period, shortening the length of time that customers spend on hold.

The next step is to implement measures aimed at the three major issues that were identified in the Pareto analysis and to continue monitoring the customer dissatisfaction ratings. If no improvement is made after a suitable amount of time, then we'll have to go back to the drawing board and start from scratch. If this happens, we may see new complaints pop up the second time around or we may still see the same ones as before. In the latter case, this generally means that the measures put in place to correct the problems were ineffective. However, if the former happens and we see a list of entirely new complaints, it could mean that our initial data gathering was faulty or we've put so much emphasis on correcting these three problems that we slipped up and let others start to occur. In any case, a new plan of action will need to be developed.