CHARTING: Pareto Analysis

A Pareto chart is a graphical representation that contains both bars and a line graph, where individual values are represented in a descending order by bars, and the cumulative total is represented by the line.

Pareto charts are based on the Pareto principle (also known as the 80/20 rule), which states that 80% of the effects come from 20% of the causes. In other words, 20% of the customers are likely produce 80% of revenue.

Use of Pareto Charts

In financial modeling, Pareto charts are primarily used to represent which customer segment or product line is the most profitable or contribute the most to a company’s revenue.

These charts are widely used in quality management to identify the most important causes of defects. They are used to optimize the processes, product mix or customer mix as they help identify the concentration of buyers, suppliers or processes.  

Making a Pareto Chart in Excel

For example, we have a company that sells its products in the US across 11 states. Thus, to analyze the state-wise revenue data, we use a Pareto chart.

Part One: Pareto Chart Data

1.        Populate the revenue data from each state in Column C

2.        In column D, create a cumulative revenue column. Start with the first amount, i.e., 1,538 or C6. Further on, each amount builds on one before it. For example, in D7, enter “=D6+C7”

3.        In Column E, create a cumulative percentage column. To get the cumulative percentage figure, divide the respective cumulative revenue in column D with the total revenue, i.e., 6,261 or D16

Part Two: Create a Pareto Chart

1.        Select the data from B5:E16 and go to the Insert tab and click on Recommended Charts. Click the All Charts Tab and select the Combo category in the dialogue box

2.        Select the secondary axis box for the cumulative % and click OK

3.        Click on the Chart Area and select Cumulative Revenue Bar and press the delete button

4.        After deleting and adjusting the chart formatting, the final output will be something like the following chart:

Interpretation of a Pareto Chart

·         As shown in the above chart, the company generates 80% of its total revenue by selling its products in 5 states, i.e., Massachusetts, Northern California, Texas, Colorado, and Illinois. Thus, any change in demand in these 5 states can impact the company’s revenue considerably and consequently its cash flow projections.

·         This analysis also provides insights on operational and marketing planning. For example, this could help the company focus on priority states to channelize more efforts by forgoing or reducing operations in the non-performing states.