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:
Here's what the output looks like (below):
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.
Accordingly, I created measures/calculated fields in PowerPivot:
The Excel PowerPivot report looks like this. I highlighted the calculated Turnover which had the same results from Sage 300 ERP.
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.
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.
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!