Hi,
We all at some time in our career have issued a management report with the holding/interest cost of excess inventory pile up. Excess inventory is measured by comparing the actual and standard inventory holding item wise (one of the methods of doing so).
Today we set about understanding how to compute actual inventory holding in months using IDEA.
If you work backwards all you need is the closing stock of inventory on the reporting date and average monthly consumption for the review period (say a year). The ratio of the closing stock to the average monthly consumption will give you the inventory holding in months.
So you have the first data file containing -
- Inventory Code
- Closing Stock Balance Quantity as of the reporting date
The second data file contains -
- Inventory Code
- Consumption for April
- Consumption for May
- Consumption for June ---- so on
So begin by arriving at the average monthly consumption on the second data file by appending a virtual numeric field titled 'Average Monthly Consumption' with the criteria 'Consumption for April + May + June ... / 12'
Join the second file to the first file on inventory code so as to get the closing stock and average monthly consumption in one file.
Finally append a virtual numeric field in the joined file titled 'Inventory Holding in Months' with the criteria 'Closing Balance/Average Monthly Consumption'
While you've got your actual monthly inventory holding you can take the analysis ahead to a logical conclusion by appending an editable numeric field in the joined file with the title 'Standard Inventory Holding'. In this editable field you can key in the standard holding in months for each inventory code.
With the actual and standard holding now in the same file its a breeze to identify excess inventory pile-up through Direct Extraction with the criteria 'Actual Holding > Standard Holding'.
Viva Idea !
Jairam