It can be a real drawback if the ERP system (Enterprise Resource Planning) at your company is not up to the task.
Automation of incoming goods processing
The handling of incoming goods in the logistics of a manufacturing company typically requires a lot of human work. In addition to the physical movement of the goods (unloading, taking them to the storage area), the accounting of the item-by-item receipt is a lengthy process for each delivery.
The colleagues handle 10-20-100 deliveries, and the same amount of documentation a day, it is crucial they be as effective as possible.
The benefit of a customized solution with Excel programming includes
Custom delivery handling Excel solution
I was entrusted to make a program in Excel that fills the gap in the ERP system during documenting the arriving goods. The ERP system is not bad at all – it is just really time-consuming to check each and every item on a paper and look it up in the system.
So an Excel program was created in a tailor-made fashion which handles many of the nuisances the colleagues come across with during document handling.
The solution is an Excel file with some Visual Basic programming, but mainly Power Query data processing. The file also has a live SQL connection to the ERP system. This makes it possible that all the information present in the ERP is accessible during the document processing.
No need to manually download lists from the ERP system – everything is handled automatically by Excel
Steps of the Incoming Goods Handling in Excel
The whole process can be seen in the video below in case you prefer a short introduction to reading.
Supplier and purchase order selection
As the company has hundreds of suppliers, a pre-filter input cell is used. In the dropdown list only the suppliers matching with the pre-filter appear, so it is much easier to select the desired one.
Excel is connected via SQL to the live ERP system, so the
outstanding purchase orders can be read directly to Excel from the online source
No need to download manually any reports, lists from the ERP. Slick method.
Picking the arrived goods from the list
Holding the paper documents, the user checks the arrived goods one by one. For efficiency reasons keyboard shortcuts are used, so with one keystroke the item can be flagged as ‘arrived’.
Of course a convenient search option was also developed as the standard Excel Find function (CTRL+F) was not fast enough for the daily work. This feature also supports shortkeys (CTRL+RIGHT for search further, CTRL+HOME to jump back to lookup field). The matching entries are highlihted for easier visual identification. This is handy when receiving a hundred items.
Final list of receipt goods
When the process is over, the paper documentation matches with the list of selected items. Those items selected will be saved to a file with a pressing of a button. This file can be uploaded to the ERP system, so the goods arrive are booked, also the invoice or delivery note is registered in the process.
Those items that remained unchecked – so not received – are subject to further activity, usually re-ordering on a new purchase order. This also can be done via a file save or even simply using copy-paste from Excel to the ERP.
The process of handling the incoming goods can be carried out at half the time as previously
The free time gained can be used to perform other types of checks or analysis, better supplier and customer management.
