Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

In the previous post, you have learnt how to perform ABC analysis/Pareto analysis using the DSUM function. By now, after reading that post you must have realised that there were no product categories in the previous data – as a result of which all the SKUs (regardless of their product categories) that contributed to top 80% of the sales were classified as A class items. Now, it is quite possible that a particular SKU could have sales not in the top 80% of the overall sales but in the top 80% of the category sales that it belongs to. In order words, you would want each categories to have its own A , B & C Class SKUs. This requirement is a simple extension of the following two posts:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

 

Let us see how…

Real Life Scenario

You have now received category wise SKU sales data and your manager wants you to run an ABC analysis such that each category will have its own A,B & C  class. Just when you thought that you can handle this request, it strikes you that in this particular case, there are categories involved. You realise that the new ABC analysis would require you to tweak the query, but you just cant figure out how?

You simply stare at the data in front of you and keep on thinking…..

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

 

Classifying ABC across each category

As we have seen by now, whenever you want to make use of DSUM() function to come up with a running total, you need to prepare the data by first sorting the data in descending order and then adding an extra column of unique values. This column of unique values will act just as a helper column while constructing the DSUM() function. In this case you’ll be using a “Make Table” query.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

On clicking the “Run” option, a new table will be created as follows but without a column of unique values:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

On saving the above changes, the new table will be created as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

Once you have the new table ready in the above format, you need to create a query on this table in order to  calculate the running total:

Step1:

Running total/Cumulative Sum – Here, you will construct a DSUM function with a criteria, as shown below:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

Step 2:

Running Total%/Cumulative Sum% – Here, first you will create a helper column containing the total category value across the corresponding category’s record. Then using this helper column and the running total column above, you will calculate Running Total% (or CumPerc) by making use of a simple division”/” operator.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

Step 3:

ABC Classification – Here, using a IIF() function you’ll classify each SKU into A/B/C class such that top 80% will be A class, next 15% will be B class and the remaining 5% will be classified C class.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

The final query would look like as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

On running the overall query, you will get the following result:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

Summarising the Results

Now that you have the classification ready, you just need to create another query based on the above query to summarise the results just like you did in the previous post.

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

On running the query, you’ll get the final summary as follows:

Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

Next Steps:

Please practice this and previous post, create similar queries using your data and share the results with all our readers.

If you are facing any problem understanding this post, it is probably a good idea to go through the following post to refresh the basics:

Advertisements

4 responses to “Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 2 of 2]

  1. أشكركم علي حسن الشرح المتكامل الذي قراءته و الذي يفوق الكثير من المواقع الآخرى التي تصفحتها ولم استفيد بهذا القدر و س.ف احاول غدا“ التنفيذ و اتمني القدرة علي الوصول للنتيجة المطلوبه و ارجو منكم افادتي كيف يمكنني عرض مشكلة حين اواجهها و شكر ا” لهذا الشرح الوافر

    • Hi Salama…..I do not understand your language. Sorry for that. Could you please give your feedback or questions in English? This would enable me to help you better, if I can.
      Anyways, thank you for stopping by and dropping a comment. Good day.

  2. Hi nice tutorial, have I missed something because I don’t see how to create the pareto chart. Great info thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s