BTerrell Group Blog

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 30 Minute 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