BTerrell Group Blog

This blog is a continuation on the Sage 300 ERP business intelligence journey on Power Pivot, covering ABC analysis, an inventory categorization technique often used in materials management, for both the valuation (cost) and sales of the IC item.

Back in 2010, I was first amazed by the technique used for ABC analysis in Marco Russo's book, PowerPivot for Excel 2010: Give Your Data Meaning. Earlier this year, Gerhard Brueckl wrote a fantastic blog talking about making the ABC analysis dynamic. My blog uses the technique described in the later blog, which you can refer to for more technical details.

Now let’s look at the value that PowerPivot brings to the table. As is shown in the graph and the table below, 33.3% of the items accouns for about 80% of the sales.

By defining the ABC as below:

ABC Class

Low

High

A

0

0.5

B

0.5

0.8

C

0.8

1

We see that the highlighted item, A1-400/0, in the right side of the screenshot below, falls into class C item in term of sales in year 2019, but managed to grow to class B item in year 2020, and achieved class B in total.

Applying the technique to bring the ranking of the inventory cost and the sales together, we see A1-400/0 is ranked 16th in the inventory cost among other items and 3rd in total sales.