Conducting a Basic Pareto Analysis

The template requires a free Member account

Please log in to download the file. Don't have a log in? Register now, it's fast and free!

Log in to download this file

Username:
Password:
Forgot your password or username?

Quick Summary
Guidelines and example for preparing a rudimentary Pareto analysis chart in Microsoft Excel®.

What this is

For project managers or project offices working on process improvement issues, a basic Pareto analysis (also called an 80/20 analysis) can help identify the most cost-effective targets for improvement efforts. But generating the chart itself can be a bit tricky. This new guideline provides a brief overview of 80/20 analysis and sample charts in Excel, along with important considerations to keep in mind during your process improvement analysis. Detailed instructions for recreating the chart are also included.

Why it's useful

The modern Pareto chart - also called an 80/20 analysis - evolved from the work of Italian economist Vilfredo Pareto. In studying distribution of wealth in Italy at the turn of the 20th Century, he found that 20% of the people controlled 80% of the land. Intrigued, he began looking around him for other examples, and found enough to satisfy him that the 80/20 effect was prevalent not only in economics, but in several other aspects of his life. Like any good statistical model, people ran with it, applying it to sociology, management, efficiency studies, and eventually quality control.

A Pareto analysis can be used to compare quantified categories in order to determine which has the most impact. Usually, this is something applied to process improvement, in an effort to find the "20% of the causes creating 80% of the problems." Arranging your data in a way that highlights the largest contributors will help identify whether there is a Pareto effect occurring, and where you may get the biggest bang for your problem-solving buck.

In the project management world, there are a number of possible uses, especially if you are charged with helping the organization improve project performance overall. For instance, if you're trying to determine how to improve the on-time performance of projects, and your organization has executed 50 projects in the last year, do you know what the most prevalent causes of delay have been across those projects, and thus where it makes most sense to put your improvement efforts? Or if you're trying to improve your software development practices, to reduce defects and testing time on each project, do you know in what software modules or sub-systems that last big release experienced the most problems? A simple Pareto analysis can help you pinpoint the places where you'll get the most return from investing in improvement activities. The charting, though simple, can also help communicate the urgency and need for focus to Management, and to team members who would need to buy into the importance of making changes.

How to use it

1. Determine the problem for which you want to investigate causes, and the scope of the investigation. E.g., for the problem: "We want to determine what are the top causes of project delays of one month or more". Then for scope: "We are going to analyze all application development projects that completed in the last year."
2. Decide what categories it makes sense to analyze. For instance, if you're out to analyze the source of major project delays, the categories might be "lack of resources", "scope creep", "technical issues", etc.
3. Assemble data in each category for the "frequency of occurrence". In our project delays example, you'd need to get data from project lessons learned meetings or other forms of history. You can use the chart in different ways also. For the 'frequency of occurrence' for each type of delay, you can count the number of projects for which that category was THE biggest source of schedule delay. Or, you could define your count as "the number of projects on which this category caused a delay of at least two weeks."
4. Open the companion Excel spreadsheet and input your data.
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 Percentages 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.

While analyzing your chart, keep in mind these considerations:

• Compare apples to apples. Make sure all the categories you're analyzing are at a comparable level. Comparing one aggregate category to several sub-divided categories is a good way to create an artificial Pareto effect on your chart, and possibly misdirected process improvement efforts as a result. For instance, if you're analyzing software error reports, comparing an aggregate category like "user error" with several sub-divided coding issues will likely reveal one very large contributor (user error) and several small ones. This could lead you to conclude that the biggest problem was with your documentation (or your users!) and overlook - for instance - more significant issues with code or user-interface.
• Make sure you are analyzing data from a stable time period relevant to your question. Analyzing data on late orders that was collected both before and after a major manufacturing systems overhaul is likely to produce mixed results at best. Analyzing causes of delays in invoice processing during an Accounts Payable software upgrade will be likely to highlight computer-based errors that are outside the usual trend. On the other hand, if you are trying to analyze the key issues during a troublesome project implementation, including frequency data from before and after that period of time will likely drown out the issues that occurred during that time.
• Don't blindly adhere to an analysis of the chart. It is possible for a Pareto analysis to be misleading, for the reasons above. Sometimes it's even possible that the overwhelming contributor identified in the analysis is one that is not cost-effective to address. (If user error really is the overwhelming problem, it's very hard to implement a customer upgrade!) In some cases, it may be appropriate to create a Pareto chart that analyzes underlying factors in the critical contributors identified by the original chart.
• Don't force an effect where none exists. A Pareto analysis may reveal a very flat curve, meaning that it takes more than 20% of the listed categories to reach the 80% "problem" threshold. If this happens, take another look at your data and make certain that the categories are appropriately divided, and that you are measuring the right things. If a re-grouping seems called for, do it and try your analysis again. If you still get a flat line instead of a pronounced curve, consider whether there are underlying causes for some or all of the categories that might provide a better focus for your efforts.

The template requires a free Member account

Please log in to download the file. Don't have a log in? Register now, it's fast and free!

Log in to download this file

Username:
Password:
Forgot your password or username?

©Copyright 2000-2008 Emprend, Inc. All Rights Reserved.
About us   Site Map   View current sponsorship opportunities (PDF)
Contact us for more information or e-mail info@projectconnections.com
Terms of Service and Privacy Policy