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
