Pareto Analysis. What McKinsey, BCG and Bain consultants do first with huge XLS spreadsheets

Video transcript:

You wonder how management consultants handle huge loads of data? Watch this video and learn how you can apply Pareto or ABC analysis to huge Excel spreadsheets to keep on top of your data and get the key messages out of your data.

Gabriel Goldbrain SUCCESS Training

I'm Gabriel Goldbrain, and I help high caliber graduates and young professionals enter Tier One and Tier 2 management consulting firms. Visit my website, www.gabrielgoldbrain.com, to find out more about the Goldbrain success training.

What is ABC/PARETO Analysis?

So, what is ABC or Pareto analysis? Pareto analysis is a method to find out which is the most important and the least important element in a data collection.

Example: The most important customers

Let's look at an example. Let's assume you have a list of customers with a revenue figure attached to each customer, now, if you do a Pareto analysis, you sort the customers descending by revenue, and what you find at the top of the list, these are the most important customers. So, the customer which is on position number one is the customer with the highest revenue. Often, this is your most important customer. Customer on number two is your second important customer, and so you can go down the list, and the importance of each customer decreases with every position you move down the list until you're at the bottom of the list, where you find the least important customer measured by sales.

What you can conclude from an ABC analysis

What you often do when you're doing a Pareto or ABC analysis is that you add a column next to the sales figure where you cumulate the sales figures, and then you add another column where you calculate the cumulated percentage of revenue of the top 1, 2, 3, 4, five customers so that you know, for instance, what percentage of customers makes 50% of your revenue.

Where does the name ABC analysis come from?

This is what you can easily conclude from an ABC analysis, and the name ABC analysis comes from the fact that you often times divide the clients into A, B, and C clients. Usually, A clients are the ones who matter most. So, sometimes you define an A client as a client which has more than 10% or more than 5% revenue share. B clients could be clients which have below 10 and above 2.5% revenue share, and C clients could be all clients which are below 2.5% in revenue. And so, a Pareto or ABC analysis provides you that data set so you can quickly find out which customers matter, which customers don't matter, and whenever you hand a database to a management consultant, they will exactly do this kind of analysis along almost all dimensions where this analysis appears sensible. So, they can quickly pull out of the data set what matters and what does not matter.

PowerPoint Slide Summary

I prepared a slide, which you can find on my website, www.gabrielgoldbrain.com. On this slide, you see a small database for which a Pareto analysis has been done. So, you see there are 50 customers with a revenue number attached to them. They are sorted in a descending order, and you find two additional columns. One is the share in total sales of each customer, and the second column is the cumulated sales share by customer. So, for instance, if you look at line number three, this is the cumulated revenue share of customer one and customer two and customer three together. So, you can say for how much of revenue, for what percentage of the revenue do your top three customers account? And in this case, the top three customers make up 88% of the company's revenue, which is a high, a very high concentration. And you can say the by far most important customer is customer number one. He has a share of 48% in revenue. Customer two has a share of 24%, and customer three has a share of 16%. So, these are highly important customers.You can also see that practically every customer who comes below customer number five does not really matter to the company anymore from a revenue perspective. And therefore, it's logical to group the customers in three different groups. You have the group of the A customers, which are the customers that have a sales share of more than 10%. You have the customers in the B group, which have more than 1.5% revenue share but below 10%. And you have the C customers, which have less than 1.5% sales share. What you can tell from this chart is that the top three customers make up about 88% of your revenue. These are the most important customers. If you only lose one of those customers, your company is very likely in big trouble. The B customers, if you lose them, it hurts, but it pretty much won't kill you. And the C customers, they're pretty much unimportant. 45 customers make up only about 5% of the revenue. So, you could pretty much run the business with only 10% of your customer base or with five customers only. And if you strategically think about these groups, you would treat them pretty differently. Group A, they probably have good bargaining power because they know that they have a very high share in your total revenue. You will give them a competitive price because you know if you lose them, you got trouble. The B customers, here you have some pricing power. You can give them prices where you say it's worth having them. Their bargaining power is limited, and you don't care too much if you lose them. And then with the C customers, you only want them if you have really good margins on their business so that it's justified to keep them and to have all the hassle with dealing with them.

Examples of using ABC analysis

Pareto analysis is one of the most versatile tools out there. It can be used very flexibly. For instance, you could use it in warehousing where you analyze what are slow-moving goods, and you could identify the slow movers. And then you could remove the slow movers from the shelf, and by removing the slow movers from the shelf, you could optimize your working capital and free up capital which is otherwise sitting on your shelf and not moving. You could also use it in purchasing to sort the purchasing volume by supplier, to then segment your suppliers into A, B, and C suppliers, to see on which suppliers you really depend and on which suppliers you don't depend much.So, to summarize it, ABC or Pareto analysis is a tool every manager must know, and also, every applicant to a management consulting firm should know the principle of ABC or Pareto analysis.

Video Recommendation

Don't miss my video on the MECE principle, where I explain why this is the best method to present your data. Also, watch my video on funnel analysis and learn how to optimize your sales or quality funnel. Share my video and subscribe to my channel.