Share [Social9_Share]

80-20 (Pareto) Analysis in Excel

80-20 Pareto Analysis 1

Quick Recap of what happened yesterday!

Yesterday was Sunday (23 Apr) and I binged like a mad man. Blueberry Jam Cake, Salted Lentils, Stuffed Pranthas, Beer, Chicken, Ice-Cream, oh yes.. and the lunch was separate 😀

But I burnt this all.. How ??  I walked for an hour in a scorching summer evening holding my notorious Son (often found dancing on my head)

In less than 20% of my day’s time, I burnt all the junk that I ate throughout the day. Classic example of 80-20 rule

You’ll find a ton of real life (business) examples which abide by the 80-20 Rule. Let’s take an example of how would you perform 80-20 (a.k.a Pareto) analysis in Excel

 

Consider this Dataset..

80-20 Pareto Analysis 2

A simple sales data set with

  • Transaction No
  • Customer Name
  • Region of Sale
  • Sales Amount

 

80-20 Analysis : What if as a manager I would like to see the top (20%) customers that contribute (80%) of the Sales. Note that in most cases you wouldn’t find the numbers to be exactly 20% or 80% but this has been theoretically correct.

In my option these days the numbers are getting more and more skewed, for eg 2% of customer that contributed to 95% of sales

Anyways let’s see how can this be solved in Excel and just to make things more interesting, I would like to also filter the Customers by Regions as well (i.e. top 20% customers in East, West, North or South and Overall)

 

Create a Pivot Table

80-20 Pareto Analysis 3

3 Quick Steps after you create a Pivot Table

  1. Drop the Customers in Rows
  2. Drop the Sales in Values
  3. And sort the Sales in the Pivot Table in the descending order

 

Find Running Totals

80-20 Pareto Analysis 4

  1. Add the Sales in the values once again. Sum of Sales will appear again in the Pivot
  2. Right Click on Sum of Sales (2) in the Pivot Table and go to Value Field Settings
  3. In show values as pick Running totals in % and apply that calculation on the Customer field
  4. And Boom Done!

 

What this is showing you is that – how Sales are totaling up to a 100% and now you can easily find out which customers contributed 80% of the total sales

 

Filter by Region using a Slicer

80-20 Pareto Analysis 5

Now it’ll be pretty cool if I can do the Pareto analysis and split the sales contribution by region too!

  1. Right Click on Region in the Pivot Field List
  2. Choose Add as Slicer
  3. A Slicer (filter) gets added in the sheet and clicking on any of the region will filter the report by the specific region selected

 

Related Topics

  1. Learn everything about Slicers
  2. Learn everything about Slicer formatting (how to make them look sexy)

 

Create a Chart Instead!

Now that we have the report ready, it would be nice to behave like a civilized analyst and create a dynamic chart 😀

80-20 Pareto Analysis 6

Don’t get too bedazzled by this. All that I have done is

  1. Created Pivot Chart (for Sales and Running Total %)
  2. Formatted the Slicer a bit and placed it on the top
  3. Done!

 

DOWNLOAD THE COMPLETED WORKBOOK – 80-20 (Pareto) Analysis

 

For Video Lovers..

 

 

  • Ranjith Kumar

    https://uploads.disquscdn.com/images/ddd4ca7d59fcbef80ad78ba1855eba1d244796720cb1737918a42f4c8c918a32.png
    Hi Chandeep,
    I have frequently used pareto chart in my MIS work. Please see the sample screenshot of my custom pareto chart, where the audience can easily see the exact clients/customer come under the 80% composition (rectangle box line). I couldn’t upload the excel, I hope you can easily recreate this chart.

    Regards,
    Ranjith

  • GraH

    Totally off topic and a few hours too late. Happy remembrance of “the-day-you-were-born”, man. Like NOW is the DATE to wish you a wonderfull DAY with a MAX of LARGE and SMALL attentions given without any MODeration by your dears and followers. It is NOT IMAGINARY your are loved…

Chandeep

Popular Posts

© Goodly