The Pareto chart analysis is a statistical graphical technique used to map and rank business process problems starting from the most frequent to the least frequent with the ultimate goal of focusing efforts on the factors that produce the greatest impact overall. To do this effectively, it utilizes the Pareto Principle, which is most predominantly known as the 80/20 rule.
The Pareto chart is sometimes also referred to as the Pareto analysis or Pareto diagram. If you hear those terms anywhere else, just know that they are almost interchangeable. In this article, we will show you how and when to use a Pareto chart analysis using Microsoft Excel or simply by hand. Mastering Pareto diagrams will help you maximize the efficiency of your business processes.
Origins of Pareto Chart Analysis
The Pareto chart is derived from the Pareto principle, which was suggested by a Romanian-born American management consultant, Joseph Juran, during WWII. The name of principle, however, is derived from the Italian economist Vilfredo Pareto.
In a nutshell, Vilfredo Pareto noticed throughout extensive observations and research that during the late 1890s in Italy, 80% of the wealth and land belonged to roughly 20% of the population.
Such a relationship is so universal that it can be applied to almost any field. Here it is shown by the figure below: with a Pareto chart, there are always two variables at play which share the same ratio (80/20) in all cases.
Some examples from different industries include:
- Business – 80% of the work is carried out by 20% of the employees.
- Software Development – 80% of the logic of a program is run using 20% of the classes or code
- Software Efficiency – 80% of the errors are caused by 20% of the bugs. Fixing 20% of the bugs with the highest frequency should solve about 80% of the customer issues. As the Microsoft ex-CEO stated, the 80-20 rule does not apply just to features but also to software bugs.
- Health – Roughly 80% of injuries and accidents happen due to 20% of the possible hazards.
- Crime – 80% of crimes are committed by 20% of criminals
- Sales – 80% of sales and revenue are gathered by 20% of the customers
Again, the main point here is that 80% of problems and events happen because of 20% of the causes and resources. Overall, the Pareto 80/20 rule is not like the immutable law of physics. It is simply a principle followed by the Pareto power law Distribution. It is based on continuous observations, and it has turned out to be applicable to almost any field in life and to many natural phenomena.
When to use a Pareto Chart Analysis
Considering the examples mentioned above, we can notice that Pareto charts have a common function despite the field in which they are applied. This common function is optimization.
It doesn’t matter whether you are trying to optimize code, a business process, workflows, documentation practices, and so on. Pareto chart analysis can be applied to any efforts for optimization.
As a rule of thumb, Pareto chart analysis can be used when trying to find a pattern that can generate the greatest impact, while employing the most significant resources and activities.
In order for us to use a Pareto diagram, our process data needs to fulfill the following two criteria:
- The data must be arrangeable into categories – Some example categories can be defects, count, or cost.
- The ranking of the categories should matter – If the ranking of the categories does not matter, then the frequency of the data will not be relevant. In such a case, it is required to change the specifies categories.
Several companies have used Pareto charts to optimize their businesses. Zappos, for example, encourages their leaders and managers to spend at least 20% of their time hanging out and socializing with their team instead of spending 100% of their time working.
This practice has proven to be quite successful in building trust and team unity. The difference between organizing team building activities [todo: team building] periodically and applying the 80/20 rule as Zappos has, is that the Pareto Principle requires to be applied continuously and at a more frequent rate than team building activities. However, the alternation of bonding activities with actual work has proved successful for the company known for its experimental corporate culture.
In a more practical sense, Pareto charts are most useful for identifying what the biggest issues regarding your business are. They also help you analyze how to present the issues that need tackling in a simpler, more understandable manner. In addition, they also help to guide where to look in terms of figuring out the frequency of a certain problem in your company.
Components of a Pareto Chart
To construct a Pareto chart, you first need to understand its components and the relationship between them. Essentially, the Pareto chart is a bar chart. Being a bar chart, it is made of two main variables which form the x-axis and y-axis. The x-axis is used for plotting the different categories into which the data is broken down.
On the y-axis, is shown the number of occurrences or the count for each specific category. The bars are ordered from the highest frequency to the lowest frequency, starting from left to right.
After ranking the bars in descending order according to their frequency, a line graph is used to depict the cumulative percentage of the total number of occurrences. The line graph is a visual sub-tool used to immediately spot whether a certain set of data follows the 80/20 rule.
Below you can find a summary of the Pareto Chart components:
- x-axis – categories of data
- y-axis – the number of occurrences
- ranked bars in descending order – the order in which the bars are placed is aligned with the frequency of the event
- cumulative percentage curve – show the cumulative percentage (on the y-axis) while traversing the categories from left to right.
In the figure below, you can see all of the different components of a Pareto diagram discussed in this section demonstrated with arrows. We will show you how to build the Pareto chart in the figure in Microsoft Office Excel.
Building a Pareto Chart
To demonstrate the components and process of building a Pareto diagram, we will use the example of a fictional business situation. Let’s assume you want to find the occurrences of certain bottlenecks in your business process.
Step 1 – Decide on the category you will use to group the bottlenecks. In our case, the categories will be different groups of bottlenecks in the workflow process. Let’s call these bottlenecks A, B, C, D, etc.
Step 2 – Establish which measurement is the most appropriate to measure the grouped categories. For example, the measurement for the different groups of bottlenecks can be the number of occurrences of the specific bottleneck.
Step 3 – Come up with a specific timeline which the Pareto chart will cover. This can be one work cycle, a sprint, one full day, one week, one month etc. For the purpose of this example, let’s assume it is one week.
Step 4 – After you have determined the first three, you have to record or organize your data in a table in accordance with the steps. The table will have two columns: Bottlenecks and Number of times Bottlenecks occur.
This requires the company to collect the data on the number of bottlenecks for each of these categories of bottlenecks if this data is not available already. If you have the data, simply organize the data that already exists according to each bottleneck category you have determined in the first step.
Pareto Chart Analysis in Excel
Step 5 – After gathering or organizing all the data, you should put everything in an Excel table.
Initially, you need to find the sum of the number of all of the different occurrences (bottlenecks). You can do this by using Excel summation tools or a calculator by hand.
Your table should look something like the table below at this point:
Step 6 – At this point, you have to bring together the total of all occurrences for each respective bottleneck category. This can be done by constructing a new, smaller table, or by hiding the columns for “days of the week”.
After that, arrange your data from the largest number of occurrences towards the smallest (descending order). Your new table should look like this:
Step 7 – Now that you have these columns side by side, you should calculate the cumulative percentage of each bottleneck. In the figure below, you can see the formula we have used on one cell (C27). To apply the same formula to the other bottleneck categories, you can simply drag the formula down to the last row.
Step 8 – Select all the data set (without the total number of occurrences), so in our case, from A25 up until C31. After that, go to INSERT → CHARTS → 2D COLUMN → CLUSTERED COLUMN.
This will create a regular 2D chart with two sets of data which will take the form below:
Step 9 – For the chart to take its final form, you should right click with your mouse in any of the bars of the chart and select Change series chart type.
A dialogue box will appear. From All Charts window, select COMBO in the far below left of the dialogue box. Here, the following changes should be made to the drop-down windows (under Chart Type column) that correspond with your data sets:
- The number of occurrences should remain as Clustered Columns.
- The cumulative percentage should be changed to Line and the Secondary Axis checkbox should be checked.
In all, your window should now look like this:
Step 10 – After pressing OK, your Pareto chart will be ready. Put a title on the chart and change the value of the secondary axis to a percentage for ease. Your finished Pareto chart will now look like this:
How to Interpret Pareto Diagrams
Now the Pareto diagram is available to you with all of its components. A trickier issue is how to exactly interpret the results of this chart. Drawing on what we have discussed before on the implications of Pareto chart analysis, this particular chart clearly shows the bottlenecks which the company should focus more on.
Here we can clearly see that taking care of the first two bottlenecks: Bottleneck D and Bottleneck A will take care of around 80% of the entire defects. The analysis would be the same if there were more bottlenecks to take care of, as the Pareto Rule of 80/20 is in play.
Pareto Chart Analysis in Quality Control
One of the greatest uses for the Pareto chart analysis is total quality control. It is used as a tool within the Six Sigma framework, a mathematical method for tracking company performance. Pareto chart analysis visually displays the data so as to make it easier to judge whether the Pareto Principle can be applied to the data.
Using charts and statistical tools for analyzing data is one of the core competencies within projects managed using the Six Sigma framework. Pareto chart analysis would fall perfectly under the Measurement and Improvement stages of DMAIC (Define, Measurement, analysis, Improvement, and Control).
Knowing whether the 80/20 principle applies to a specific process can be very helpful in Six Sigma. When selecting which project to take up, managers must focus their resources on the few projects that can deliver the most significant results.
After all, a manager has only 24 hours per day like everyone else in the company. If he or she wants to have the best results, he will have to first address the projects that have the greatest impact.
Conclusion
Pareto Diagrams (known more commonly as the 80/20 Pareto rule) are very useful for managers and figuring out problems in the workflow process. As we have demonstrated using a real-life example in Excel, you can clearly figure out which top 20% of your company’s processes are causing 80% of the problems. By taking care of the main problems, you make sure the overall processes of your business are running smoother as you take care of potential or actual bottlenecks.
Did you find this post useful and practical? Is Pareto diagram analysis a tool you are already using in your business? Let us know if you practiced building Pareto diagrams in Excel using our tips or if you learned something new today. And if you liked the article or if you think we missed something, please leave your feedback in the comment section below!
