Data Loading...
Pareto Analysis Chart Template for Excel User Guide Flipbook PDF
Pareto Analysis Chart Template for Excel User Guide
116 Views
6 Downloads
FLIP PDF 380.35KB
P ARETO A NALYSIS C HART T EMPLATE FOR
E XCEL
Parteo Analysis Chart Template for Excel Pareto analysis is based on the idea that 80% of a project's benefit can be achieved by doing 20% of the work or 80% of the problems can be overcome by focusing on 20% of the causes. In its simplest terms, Pareto analysis will typically show that a disproportionate improvement (approx. 80%) can be achieved by ranking various causes of a problem and by concentrating on those solutions or items (approx. 20%) with the largest impact. Pareto analysis allows organisations to concentrate more detailed attention on the high value/important items. The Pareto analysis template is used to arrive at this prioritisation and to graphically present the results, and as a result get 80% of the benefit.
www.businesstoolsstore.com 11/16/2012
© Copyright The Business Tools Store 2012
Page |1
11/16/2012
PARETO ANALYSIS CHART TEMPLATE FOR EXCEL 1 INTRODUCTION Why a Pareto Analysis Template? We are all familiar with the 80/20 rule. The 80/20 rule uses the Pareto Principle. Pareto analysis is based on the idea that 80% of a project's benefit can be achieved by doing 20% of the work or 80% of the problems can be overcome by focusing on 20% of the causes. In its simplest terms, Pareto analysis will typically show that a disproportionate improvement (approx. 80%) can be achieved by ranking various causes of a problem and by concentrating on those solutions or items (approx. 20%) with the largest impact. Typical examples of where the Pareto analysis is applicable are:
Sales; 20% of customers account for 80% of sales and/or 20% of products generate for 80% of sales revenue Inventory; 20% of inventory items account for 80% of inventory value Quality; 80% of problems can be traced to 20% of the causes.
Pareto analysis allows organisations to concentrate more detailed attention on the high value/important items. The Pareto analysis template is used to arrive at this prioritisation and to graphically present the results, and as a result get 80% of the benefit.
Figure 1 Pareto Chart
© Copyright The Business Tools Store 2011
www.businesstoolsstore.com
1
Page |2
11/16/2012
2 USER INSTRUCTIONS MS-EXCEL The Pareto Analysis chart uses Excel.
Cut-off %
Figure 2 Pareto Analysis Input form
2.1 Data Input The template contains a data input form as depicted in figure 2 above. Those cells with a green border should be used for data input. The cells with a red border are calculated and should NOT be edited. For items to be included in the analysis and chart: the item description and the count of the number of occurrences of the item should be entered in the first two rows (rows D and E) of the form. The items should be entered in descending order of the count. Up to twenty items can be included in the analysis. The table can be easily customized to increase this number. The percentage of the total for each item and the cumulative percentage are calculated and displayed in columns F and G. The default cut-off percentage for the chart analysis is 80%. This can be amended by editing the value in cell H9. © Copyright The Business Tools Store 2011
www.businesstoolsstore.com
2
Page |3
11/16/2012
3 PARETO ANALYSIS CHART Once the data input is entered the Pareto Analysis chart, as depicted in figure 3, is automatically produced.
Axis 2
Axis 2
Figure 3 Pareto Analysis Chart
The chart has two axes. Axis 1 refers to the bar chart The bar chart shows the impact of the causes in descending order. Axis 2 refers to the cumulative curve and the cut-off percentage. The cumulative curve (in red in the image above) shows the cumulative impact of addressing the most important causes first. The blue line identifies the cut-off point at the intersection with the cumulative curve at which 80% of the benefits are achieved.
© Copyright The Business Tools Store 2011
www.businesstoolsstore.com
3