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:

1. Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2] and
2. Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – 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…..

 

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.

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

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

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:

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.

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.

The final query would look like as follows:

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

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.

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

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:

• DSUM() function –The Art of Writing a Criteria : 1
• DSUM() function –The Art of Writing a Criteria : 2
• DSUM() function –The Art of Writing a Criteria : 3
• Using MS Access to create a Running Total or a Cumulative Sum – Part 1 of 2
• Using MS Access to create a Running Total or a Cumulative Sum (Grouped Data) – Part 2 of 2
• Perform ABC Analysis (Pareto Analysis) using the DSUM () function : [Part 1 of 2]