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

Sage 300 ERP (Accpac): Using PowerPivot for Inventory Control

Posted by Tony Zhang on Fri, Aug 16, 2013

In my previous blogs, I shared PowerPivot solutions for Sage 300 ERP (Accpac) financials (AR, AP and Cash Flow Forecast). Starting in this blog, I’d like to tackle challenges from the operational side, starting with Inventory Control.

If you manage inventory, you might have developed and used KPIs (Key Performance Indicators) such as Inventory Turnover, Turn/Earn Index or Gross Margin Return on Inventory Investment (GMROII). GMROII is particularly helpful for retail business because “Retailers usually drive their business based on sales or margin. In a retailer where budgets and bonuses are based on sales, employees often achieve that by lowering the margin or putting too much stock in their stores. A high GMROII indicates a good balance of sales, margin, and inventory cost.”

In the Sage 300 ERP world, you might have used the I/C Slow Moving Items Report which “lists items that are not selling well, according to the criteria you select when you print the report.” The screenshot below shows the input to the report:

Sage 300 ERP: I/C Slow Moving Items Report

 Here's what the output looks like (below):

Sage 300 ERP: the I/C Slow Moving Items Report Output

You might also use the I/C Overstocked Items Report which calcutes the Annualized Trun and Earn Ratio.

Both reports provide useful information with numerous criteria to choose from. Still, you might like to analyze inventory using Microsoft Excel to be able to further manipulate the data and provide your own customized reports.. If that is the case, you might like to check out the sample in this blog which calculates the Turnover, Turn/Earn Index and GMROII. To keep the blog short, I am covering the Turnover calculation in detail, but only show the results of the Turn/Earn Index and GMROII calculations.

The first step is to define how to calculate those KPIs. In this example, I follow the turnover definition used by Sage 300 ERP which is to use quantity instead of cost of goods sold.

YTD and period-to-date turnover definitions

Accordingly, I created measures/calculated fields in PowerPivot:

Excel Calculated Fields

Excel Calculated Fields - Turnover YTD

The Excel PowerPivot report looks like this. I highlighted the calculated Turnover which had the same results from Sage 300 ERP.

Excel PowerPivot Turnover Report from Sage 300 ERP data

With Excel, I gain the additional flexibility to be able to roll up the turnover calculation to the  Item or Location level. I can define the KPI to present the Turn/Earn Index status. In this example, the KPI target is defined as an absolute value, but you could use a calculated field like last year’s Turn/Earn Index.

Excel KPI Turn/Earn Index Status

Moreover, I can change the period and get the inventory balance () for the selected period and the corresponding Turnover. I highlighted the quantity available at the end of May 2020 in green, and the actual quantity available in yellow.

Inventory Balance or Quantity Available Reporting

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 include ABC Analysis for Sales and Inventory Cost, and Purchase Analysis. Stay tuned...

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

Tags: Sage 300 ERP, inventory control, PowerPivot, Power Pivot, Turnover, Turn Earn Index, GMROII