Dynamics GP Reporting – Inventory Turn Over Analysis
Analysis and Reporting are considered the most important aspect of ERP systems, the added value is shown when proper reports and analysis are put in place. In a recent project with a big manufacturing company in the US, we have been asked about the capabilities of Dynamics GP to provide robust reporting and analysis insights of the inventory and production specifically, and the answer was precise.
Dynamics GP provides strong and comprehensive data structure for an analysis tool to be deployed specifically with the inventory and production module.
The challenge was represented with a specific task which we’ve been asked to deploy on not only on the existing year data but also on historical years in order to provide yearly comparisons across multiple years and be able to make proper decisions for the future. This tasks is “Inventory Turn Over Analysis” . It’s clear that this ratio is usually calculated from the general ledger module as a lump sum for all the inventory items as a bulk by considering the inventory GL accounts primarily, and either the sales or the COGS account as well. Although, this client has requested a further complicated request represented by providing this ratio for each SKU solely and based on the analysis, an evaluation of the production will be considered.
In definition, the inventory turn over is defined is “a ratio showing how many times a company’s inventory is sold and replaced over a period of time. The days in the period can then be divided by the inventory turnover formula to calculate the days it takes to sell the inventory on hand. It is calculated as sales divided by average inventory.“
From a financial perspective, inventory turn over can either be calculated using the COGS or the Sales of the items. Although, in both cases, average inventory is supposed to be calculated initially. The question is why “Average”? Right,?
Here is the answer, in multiple companies, items are purchased an sold on a specific frequency which is highly affected by what’s called the “seasonal” nature of the market. For instance, cold drinks are purchased/ sold & manufactured at its highest volume during summertime, while it is at its lowest level in winter. In this essence, if the “ending” inventory is only considered when calculating the turn over analysis, fluctuation of the items throughout the seasons is not considered at all. Therefore, average inventory is calculated considering the following formula:
Average Inventory = ( Beginning Inventory + Ending Inventory ) / 2
Now the next step is to either consider the cost of goods sold or the sales during the same period (in which we calculated the beginning and ending balances) and then divide it by the average inventory calculated above.
It’s worth to mention that all of the above can be easily calculated on the financial level using Management Reporter, although, the real challenge is to provide the same equation for the inventory items, each by itself. Although, at SiroCo … we could do it properly and professionally.
This report can be deployed either on simple Excel sheet in order to be refreshed on a frequent bases, or can be deployed on Power BI so that actual figures can be calculated directly for the stock and represented as KPIs in order for business people to make the right decision at the right time.