Marketers know that typically 80% of sales come from just 20% of customers, and by focusing on that group, you can gain valuable insights into your data. With the help of Excel, it’s easy to determine not only who your top 80% of customers are, but also how much revenue they’re actually bringing in. In the example below, we’re going to go over how to do such an analysis, and identify how much your top 20% and bottom 20% of customers generate.
And you can also do further analysis by looking at the top 10% or whatever cutoff points you want. In this example, we’ll create a spreadsheet which will allow you to set these different cutoff points however you want.
This is the data set that I’m going to use for this example.
Step 1: Convert the data into a pivot table
The first thing we’ll want to do is to convert this into a pivot table. By doing this, we can sum up the totals by individual customers. We don’t want to apply the analysis on raw numbers as that will only give us the top invoices when instead, we’re looking at sales by customers.
If you select a cell on the data and go to the Insert tab, you can click on the button to create a Pivot Table. Then, pull the sales data into the fields section and the customer field into the Rows section. This now creates a summary by customer.
Step 2: Calculate the cutoff points
Now that the data is grouped by customer, we can start to calculate the sales by different percentiles. If you want to determine the top 20%, then we need to get the 80% percentile. At that level, the values will be higher than 80% of the rest of the data, and thus, leave us with the cutoff for the remainder, the top 20%.
With the sales data in column B, we can use the following formula to get this cutoff point:
=PERCENTILE(B:B,0.8)
The 0.8 indicates the 80% percentile, which means that the value returned is higher than 80% of the values in the data set. In my data set, this returns a value of 276.34. Any customer whose total is at or above this cutoff point means that they are part of the top 20%.
To get the cutoff value for the bottom 20%, the same formula is used, except now for the second argument, the value is set to 0.2. This returns a value of 101.90. In this situation, we’ll be focusing on values at or below this cutoff.
Step 3: Calculate the sales that fall within the top and bottom 20%
Now, let’s calculate the total dollar amount of the sales that fall in the top and bottom 20% ranges. For the top 20%, we’ll need to sum up all the values that are equal to or greater than that cutoff point of 276.34. With that value in cell B3, my formula is as follows:
=SUMIF($B$9:$B$1000,”>=”&$B3)
The pivot table sales field is in column B. This calculation returns a value of $35,072. That is the total dollars that my top 20% of customers have generated.
To calculate the bottom 20%, we can use the following formula, assuming that the cutoff point for 101.90 is in cell B4:
=SUMIF($B$9:$B$1000,”<=”&$B4)
The total of this value is 7,894, and that indicates how much revenue the bottom 20% have brought in.
We can visualize these data points by plotting them on a column chart:
If you want to calculate different percentiles and cutoff points, you just need to adjust the second argument in the percentile formula.
If you liked this post on How to Do an 80/20 Sales Analysis in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow me on Twitter and YouTube. Also, please consider buying me a coffee if you find my website helpful and would like to support it.