- Business management thinker Joseph M. Juran suggested the principle and named it after Italian economist Vilfredo Pareto, who observed in 1906 that 80% of the land in Italy was owned by 20% of the population.
- Pareto principle serves as a baseline for ABC-analysis, widely used in logistics and procurement for the purpose of optimizing stock of goods, as well as costs of keeping.
- Pareto charts commonly display an ordered column chart and a line chart showing the cumulated percentage on top.
I decided to combine both charts in one :
- Each segment represent the individual weight of each item within the total.
- Segments are stacked one after the other, simulating the cumulated percentage line.
In ABC Analysis for example :
- "A class" inventory will typically contain items that account for 80% of total value, or 20% of total items.
- "B class" inventory will have around 15% of total value, or 30% of total items.
- "C class" inventory will account for the remaining 5%, or 50% of total items.
Unlike the previous version of Pareto(), this release requires only one function in the upper cell (left cell if data is organized in one row x several columns) to display the entire chart.
The reference lines are now continuous and the chart is more readable.
Pareto() UDF uses the following parameters :
- ColorRange is a range containing RGB color codes (provided in the manual), so each individual segment can have a specific color. ColorRange and Points must have identical number of values.
This can be usefull to group items by family or, as show in the picture, to better visualize the A, B and C product groups.
Sparklines for Excel 2007 v. 3.6 beta available here. (Box.net might rename the file into "sparkline.xlam.zip"... so please remove the ".zip")
Demo file for Pareto available here.
PS : To provide a scale to Pareto(), use ScaleXY() ...
Pareto() UDF uses the following parameters :
- Points : Range of values - Mandatory
- ColorRange : Range of colors values. Allows one color per segment - Optional
- Target : Percentage - Red line - Optional
- Target2 : Percentage - Green line - Optional
- HighlightPosition : Position - integer value - Optional
- Legend : True / False - Display cumulated % - Optional
- ColorRange is a range containing RGB color codes (provided in the manual), so each individual segment can have a specific color. ColorRange and Points must have identical number of values.
This can be usefull to group items by family or, as show in the picture, to better visualize the A, B and C product groups.
Sparklines for Excel 2007 v. 3.6 beta available here. (Box.net might rename the file into "sparkline.xlam.zip"... so please remove the ".zip")
Demo file for Pareto available here.
PS : To provide a scale to Pareto(), use ScaleXY() ...
2 comments:
Fabrice, you are genius.
Hi Fabrice, absolut phantastic work...-Thank you so much!!!
Post a Comment