Pareto analysis spreadsheet

Many of your daily work problems don’t have a single root cause, rather several quite separate contributing factors. You can use Pareto analysis to analyse this cause and effect.

The 80/20 rule

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 it 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 simple terms you write out a list of the likely causes and then enumerate how often each cause occurs. The Pareto principle says that the first issue to tackle is the one that has the highest score from your analysis.

Roughly 80% of the problems stem from around 20% of the possible causes. It’s important to note that the actual percentages (say, 94% to 6%) 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 problems will be handled. Our step by step guide is as follows:

Step 1. Determine the problem for which you want to investigate causes, and the scope of the investigation. For example, the problem: “We want to determine what are the top customer complaints.” Then for scope: “We are going to analyse all complaints that were received in the last year.”

Step 2. Decide what categories it makes sense to analyse. For instance, if you’re investigating the source of complaints, the categories might be “too long on hold”, “rude staff”, “transferred too many times” etc.

Step 3. Assemble data in each category for the “frequency of occurrence”. In our project complaints example, you’d need to obtain data by talking to customer services or reviewing complaint files.

Step 4. Input your data into our Pareto Analysis Spreadsheet. This includes columns showing the “Percentage of total” and “Cumulative percentage”. Use the Data/Sort function in the spreadsheet to sort the data in descending order, i.e. starting with the most common reason down to the most rare.

Step 5. Then look for a pattern indicating a Pareto effect at work: a few categories contributing an overwhelming majority to the total frequencies you charted. Ideally, you will see a clear break point at or around 80% on your Cumulative percentage line. In some cases you may see a more gradual curve - not exactly a Pareto effect, but still a good indication of the key issues. Or you may find a fairly level curve, with each category contributing almost equally to the total.

Step 6. The first cause to tackle is the one that has the highest score. This one will give you the biggest benefit if you solve it. The options with the lowest scores will probably not even be worth bothering with.