Data analytics helps warehouse management
In this story, we can experience how to make use of all the delivery vouchers to monitor the warehouse inventory.
In a supply chain, the warehousing function is very critical to link the material flows between the supplier and customer. Therewith, warehousing is actually part of a material management process concerned with the storage of materials to deliver on time. The warehouse can also be used to store the raw materials, some of the semi-finished goods and parts that are required for manufacturing, and the most important goods — finished goods which will be delivered to customers.
Last two months I was in a textile factory in China. Through talking with the owner of the factory, I have understood how this factory works to a certain extent. For some industries, the finished goods take up warehouse space enormously, e.g. grey cloth manufacturer.
1. Factory introduction
The factory 🏭 is in the textile industry on the southeast coast of China, which has more than 30 years of history.
In recent years, the textile market is volatile. The factory has made some adjustments to cater to the modern market, such as purchasing land, hiring new staff, buying advanced equipment, etc.
The product of the 30-year-old factory is grey cloth, which is rolled and stacked in the warehouse. The finished cloth rolls are waiting for being delivered by truck. In general, trucks are assigned only by customers. Therewith, the factory can’t send finished goods on its mind and must wait for an appointment from the customer. The stevedores👷🏻 can pick and pack the products until a truck arrives.
Based on this fact, outbound logistics of the factory is totally dependant on its customers. The manager of the factory can control inbound logistics on his own.
Additionally, if you want to know more details about this factory, you can read the following short story:
2. Data availability statements
The factory has two manufacturing workshops. In one workshop, there are 22 knitting machines. The other one, which was invested in 2018.02.25, contained 6 knitting machines at the beginning of the year 2018. In the year 2019, a set of new equipment was purchased and put into use. Later in this story, we focus only on the new workshop. All data and the term factory mentioned in the article only refers to this new workshop.
Therewith, the new workshop has 7 sets of production equipment now.
After getting the factory owner’s support and agreement, I’m allowed to publish this story and share the outbound delivery documents of the new workshop on my GitHub.
In the last story, I explained how to make this Excel file data visualization with Python. If you are interested in it, you can check it through the link below:
3. Calculate productivity
Through data visualization, we can observe the data more intuitively and clearly. We can plot daily, monthly, or the annual outbound amount by processing available data properly.
From the bar plot above, we can conclude that the new workshop’s outbound amount has increased after pursuing a new production machine, both on roll number and total cloth weight.
Since almost all the knitting machines run day and night without stop except factory vacation, which occurs annually once and lasts about one month (not in 2018 since the new workshop opened after Spring Festival). We can observe the daily productivity of the whole workshop as constant. To simplify the problem further, the efficiency of each production machine could be regarded as the same. So we need to know how many workdays in 2018 and 2019, respectively.
Excluding new year factory vacation that lasts usually more than one month, but including 2 days before the first shipment after every long vacation, the new workshop has run by convention, there are still 309 workdays in 2018 and 333 workdays in 2019.
Besides, we still need to find out the initial inventory and final inventory in the warehouse. Unluckily, the manager of the warehouse has never recorded the exact number of the stocked textile rolls after a day of work. He could only roughly remember that at the end of the last day in December the warehouse nearly half full. The standard capacity of the warehouse is around 1000 grey textile rolls. For emergency, it can hold 2000 grey textile rolls but it will impact the daily operations of the factory and increase additional costs. When the warehouse inventory reaches 2500, the warehouse is terribly overloaded and the overfull stacked goods can even compete with people for “oxygen”. So here I have to make a bold assumption that on the night of 2019.12.31 after the last truck left, the final stocked finished goods were 500 rolls.
productivity formula derivation:
approximative Daily productivity= annual productivity/ workdays
annual productivity= final inventory+ annual total delivery- initial inventory
each machine’s productivity in 2018= each machine’s productivity in 2019
approximative Daily productivity= each machine’s productivity* amount of machines
Therewith, we can calculate each machine’s productivity like the following:
(annual total delivery in 2018+ annual total delivery in 2019 -initial inventory+ final inventory) = (amount of machines in 2018* workdays in 2018+ amount of machines in 2019* workdays in 2019)* each machine’s productivity
The only unknown value is each machine's productivity “X”.
So, the complex question is converted into a simple algebraic equation(a Basic Linear Equation).
32609+43109–0+500= (6*309+7*333)*X
X=18.2122 means each machine’s productivity is 18.2122 rolls/ day.
Daily productivity in 2018 is 109.27 rolls/ day and Daily productivity in 2019 is 127.49 rolls/ day
4. calculate the daily stock of finished Product
Based on the above-mentioned circumstances, we can confirm that in the year 2018 the factory produced goods 129.27 rolls pro day and in 2019 produced goods 127.49 rolls pro day and during Chinese new year factory vacation 2019.1.20- 2019.2.22 (excluding 2 days before the first shipment after every long vacation, as above mentioned).
It’s easy that we get a data frame recording the daily sum of the delivered goods with Pandas (the related steps are detailed in the Story): https://towardsdatascience.com/view-a-chinese-factory-from-the-data-aspect-with-python-ad698461f40a
Here I skip the explanation of this part and get the required data frame directly.
The obtained data frame from the excel file is called new_df, by multiplying -1, we get the outbound_df, where minus stands for outbound logistics.
now, we should add in each date the daily finished goods quantity, the calculation result is actually inventory in the warehouse.
import datetime
for i,day in enumerate(day_list):
if day.start_time == datetime.datetime(2019,1,20):
vacation_start= i
if day.year == 2018:
warehouse_df.iloc[i,0]=sum(outbound_df.iloc[0:i+1,0])+109.27*(i+3)
elif day.year == 2019 and day.start_time < datetime.datetime(2019,1,20):
warehouse_df.iloc[i,0]=sum(outbound_df.iloc[0:i+1,0])+109.27*309+(i-307+3)*127.49
elif day.start_time > datetime.datetime(2019,1,20) and day.start_time < datetime.datetime(2019,2,22):
warehouse_df.iloc[i,0]=sum(outbound_df.iloc[0:i+1,0])+109.27*309+(vacation_start-307+3)*127.49
elif day.start_time > datetime.datetime(2019,2,22):
warehouse_df.iloc[i,0]=sum(outbound_df.iloc[0:i+1,0])+109.27*309+(i-307+3-34)*127.49
round_warehouse_df= warehouse_df.astype('int32')
print(round_warehouse_df)After printing the new data frame “round_warehouse_df”, we see the required results on our screen.
5. Summary
Similarly, we can plot the results with a well-designed style, where two straight lines indicate to us how is the current warehouse loading status. Below the green line means that the warehouse space is abundant, above the red line warns of the overloaded warehouse.
From the above figure, we can take a good overview of the warehouse status over time. With our provided analytics, we can reproduce the usage of the factory warehouse, so that we can better manage the warehouse in the future. In 2018, perhaps lack of experience in warehouse management of the new workshop, the inventory changed drastically. It’s difficult to take warehouse management under control. But in 2019 the factory has stepped into a stable development path. However, with a lack of delivery in October 2019, the warehouse management was once again in a deep crisis. The crisis ended in a month.
The plot of the monthly delivery can provide evidence to support our judgment. The goods storage crisis was solved after increasing the delivery in the last two months in 2019.
Therewith, on the last day in 2019, there were only 500 rolls of grey cloth in the warehouse. What a happy ending😀.
Reviewing our data processing, we used many hypotheses based on knowledge or experience to realize warehouse analysis. It’s important that we should think more about raw data when we get them. By taking a deep look at delivery vouchers, we find an approach to bridge the world of warehouse management.
At last, please remember that having a lot of data in your hand is great, knowing how to get value out of it can be challenging. Wish you a nice journey in exploring the data around you🌝.
