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 Consultation

Related Content

See AR aging reports blog

See AP aging reports article

See Cash flow forecast blog article

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

Live Webcast: Orchid Systems' Bin Tracking, Process Scheduler

Posted by Chuck Reeves on Wed, Oct 28, 2009

Join us today for a live Webcast on Orchid Systems’ Bin Tracking and Process Scheduler!

Developed by Orchid Systems, Bin Tracking allows Sage Accpac ERP users to track inventory items stored in multiple bins within a warehouse or Sage Accpac ERP Inventory Control location.
Benefits include:
- Improved management of your warehouse with less work and complexity
- Pinpointing exactly where your inventory is at any point in time
- Processing warehouse activity more efficiently, resulting in faster turnaround times, improved customer satisfaction, lower inventory write-off, and lower costs
- All information contained within your Accpac database so there is no need to maintain and reconcile multiple applications and databases

Developed by Orchid Systems, Process Scheduler allows you to automate repetitive Accpac tasks such as:
- Data Integrity
- Data Dumps
- Day End Processing
- Batch Posting
- Scheduling Crystal and Financial Reports - in conjunction with Information Manager
- Scheduling refresh of data cubes in Info Explorer
- Loading databases
- And much more!

Join us for the live Webcast on Wednesday, October 28, 2009 as we show you how Bin Tracking and Process Scheduler from Orchid Systems can help your firm.

Link: https://www2.gotomeeting.com/register/511604746
Title: Orchid Systems’ Bin Tracking and Process Scheduler
Date: Wednesday, October 28, 2009
Time: 4:00 pm - 5:00 pm CST

After registering, you will receive a confirmation email containing information about joining the Webcast.

System Requirements:
PC-based attendees:
Required: Windows 2000, XP Home, XP Pro, 2003 Server, Vista

Macintosh-based attendees:
Required: Mac OS x 10.4 (Tiger) or newer

- Brian Terrell, CPA and Managing Partner

Tags: Sage Accpac ERP, automate Accpac tasks, Bin Tracking, customization, data dumps, data integrity, inventory, inventory control, Orchid Systems, process scheduler, warehouse, webcast

Bin Tracking & ACCU-DART

Posted by Meredith Gooch on Mon, Sep 28, 2009

Make bin tracking easy and efficient with ACCU-DART!

The old way:

Every time an item is picked for shipping or moved from one bin to another, the warehouse staff has to manually record which bin the item was taken from and where it will go. This information is then entered into the accounting system.

Because bin movements are not being recorded in real-time, errors are easily made and inventory is always out of date.

The ACCU-DART way:

When an item is picked for shipping or for any other bin movement, the warehouse staff can scan the items with ACCU-DART's radio-frequency scanners. ACCU-DART will prompt the user to enter the current bin they are taking the items from and the destination bin. ACCU-DART validates and, upon posting, automatically updates the accounting system in real-time.

For Sage Accpac ERP users, this functionality is available through integration with the bin tracking solution designed by Orchid Systems. This new 3rd party solution allows Sage Accpac ERP users to track inventory items stored in multiple bins within a warehouse or Inventory Control location.

ACCU-DART makes it easy to maintain inventory and bin accuracy within the warehouse. Click Here for more information about our partner.

- Rufus Vernon, Director of Sales

Tags: Sage Accpac ERP, inventory, inventory control, Orchid Systems, warehouse, ACCU-DART, Bin Tracking, scanner

Live Webcast: Orchid Systems' New Modules: Bin Tracking & Info-Explorer

Posted by Meredith Gooch on Wed, Jun 24, 2009

Bin Tracking allows Sage Accpac ERP users to track inventory items stored in multiple bins within a warehouse or Accpac Inventory Control location. Info-Explorer is a cost-effective and powerful reporting and analysis tool which is packed with features including drill down to the source Accpac screens.

Join us for a live Webcast on June 24, 2009 at 4:00 p.m. - 5:00 p.m. CST. Space is limited, so reserve your seat now at https://www2.gotomeeting.com/register/421146619.

After registering, you will receive a confirmation email containing information about joining the Webcast.

- Meredith Gooch, Marketing Manager

Tags: Bin Tracking, inventory control, Orchid Systems, warehouse, Info-Explorer, webcast, Webinar