Optimizing Inventory for a large trading company
The end client is a large trading company based out of Delhi involved in trading of steel sheets/steel pipes. The steel sheets and steel pipes are used for casting designs in automotive sector.
Problem statement: The end client is looking to improve the inventory turnover ratios( as per the blog attached herewith) and the hypothesis of the end client is that it has a large amount of slow moving stock.
Approach of the ETL team along with the management consulting firm(Govisory Services Private Limited):
- · Find the A,B,C category of stock for the items: the top 70% of the closing stock value as of the fiscal date was classified as category ‘A’, next 20% as category ‘B’ and last 10% as category ‘C’.
- · Inventory ageing analysis was done to calculate the age of the closing stock
- · Calculation of the over stock and under stock items: for the same the ETL team followed the following steps:
o Calculation of the average sale value: this was done by dividing the total of year to date sale of the item by the total number of active fiscal months in which the item was sold. Active fiscal months entail the date difference between the minimum sale month for the item and the maximum sale date(31st December 2017).
o The Lower and upper control limits were set as 6 times the average sale value and 9 times the average sale value.
Key findings: most of the category ‘A’ stock items were found to be over stock by using the average method. The ETL team along with the consulting team found that it was the variance and not the averages that are a problem(The Goal Methodology). The end client had significant variations in sales between the fiscal months for the items.
Next step: in order to calculate the effect of variances on the sale, the ETL team decided to carry out the time series analysis over the last 2(two) fiscal years for the category ‘A’ items. This action was done through the proprietary code of ARIMA(Auto Regressive Integrated Moving Average) with the ETL team.
Based on the finding of the same for most of the items it has been found that level has been the most important indicator.
Other activities done by the ETL team for inputs from MS Excel spreadsheets:
Presented by:
Ritu Lakhani
Email: ritu@mnnbi.com
Phone: 0124-4365845
Website: www.mnnbi.com
No comments:
Post a Comment