Pareto Chart | Excel
Sponsored Links
The economist Vilfredo Pareto once observed that 80% of the land in Italy was owned by 20% of the people, and, similarly, 80% of the peas in his garden came from 20% of the pea pods. In America, it was noticed that 80% of sales revenue often come from 20% of the customers. The management consultant Joseph M. Juran generalized this as the Pareto Principal, or the 80-20 rule, which states that often, 80% of the effects come from 20% of the causes.
Sponsored Links
A Pareto Chart is created when you combine a line graph with a bar graph. As an example, say you have ten customers who each generate a certain percentage of your revenue. You first construct a bar graph whose X-axis is taken up by those customers, by order of descending revenue, with the Y-axis indicating each customer’s contribution to your revenue.
Next, you superimpose a line graph, with the Y-axis indicating the total revenue. If customers A and B contribute 45% and 35% of total revenue each, the line graph will register, 45% and then 80% as it moves along those two customers.
Those two customers, two out of ten, are 20% of your customer base. You have proven that the 80-20 rule holds in your example.
How to Create a Pareto Chart
A Pareto Chart is easy to construct with the Excel spreadsheet.
You start by listing your customers in one column, and their percentage of revenue contribution to the right in the next column. You can either enter them highest down to lowest, or have Excel sort them so. If you are working with data that isn’t presented in percentage form, like perhaps, the number of bad parts coming from a given industrial process, or the number of complaints about a service, there will be another normalization step at this point. Other wise, go ahead and create the third column now, which will be titled Cumulative Percentage, which you can easily cause the spreadsheet to compute in each space.
Next, simply use your mouse to select the three columns that you have just built. Then the next step in constructing you Pareto Chart with Excel is to click the Inset Tab and choose first Chart and then Custom; then, opt for “Line – Column on 2 Axes”. If you allow excel to finish the job at this point, you will see an unadorned Pareto Chart, with the characteristic bar graph containing declining values and a line graph superimposed over it showing the cumulative sums of those bar graph values up to each point. At this juncture, you can go back a step and take additional steps to better illustrate your Pareto Chart and make it more readable.
In any case, if you really do learn that you are achieving 80% of your revenue (or complaints!) from 20% of your customers, I think you’ll agree that creating a Pareto Chart as well worth the effort.
Sources
Sponsored Links