BTerrell Group Blog

Sage 300 ERP (Accpac): Using PowerPivot for Dynamic ABC Analysis

Posted by Tony Zhang on Fri, Aug 30, 2013

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.

ABC Analysis

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.

ABC Analysis Ranking Report

Click here to get a static version of the report to get a sense of the look and feel. I will share this report later after I go through other blogs in this series, which will cover OE Aged Orders and Power BI, and Purchase Analysis.

Stay tuned...

I’d like take this opportunity to thank to many great people for being so open in sharing their solutions to these real world problems, namely: Chris Webb, Rob Collie and Kasper de Jonge, Marco Russo and Alberto Ferrari, Gerhard Brueckl, and Jeffery Wang.

If you would like to get an unprotected version of the worksheet and/or would like to connect the worksheet your Sage 300 ERP system, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

Free Consultation

Related Content

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

See

If you would like to get an unprotected version of the worksheet and/or would like to connect the worksheet your Sage 300 ERP system, don't hesitate to ask for a FREE 30-minute consultation from BTerrell Group...we'd be happy to help!

"FREE

Related Content

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

" title=" Inventory control article" target="_blank"> Inventory control article

Tags: Sage 300 ERP, PowerPivot, Power Pivot, ABC Analysis, Sales History, Item Valuation, Inventory Categorization