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% customers are likely produce 80% of the 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 contributes the most to a company’s revenue.
These charts are widely used in quality management to identify the most important causes of defects. These are used to optimize 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
- Populate the revenue data from each state in Column C
- In column D, create a cumulative revenue column. Start with the first amount, i.e., 1,538 or C6. Further, each amount builds on one before it. For example, in D7, enter “=D6+C7”
- 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
- Select the data from B5:E16 and go to the Insert tab, and then click on Recommended Charts. Click the All Charts Tab and select the Combo category in the dialogue box
- Select the secondary axis box for the cumulative % and click OK
- Click on the Chart Area and select Cumulative Revenue Bar and press the delete button
- 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 into 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 non-performing states.
- Want to become an expert in Financial Modeling – Enroll in our Investment Banking Course
- Learn MS Excel Online for Free – Yes, I wish to learn