Excel programming – Support for Incoming Goods

Excel Goods-In support - User interface
It's worth automating something that occurs regularly - and handling incoming goods is definitely such a thing. Useful also if the process is not as streamlined as should be.

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

the speed of the delivery management increases
with the same personnel more time can be dedicated to physically check the goods
the ‘boring part’ is removed from the staff and carried out by the computer
the personnel shall focus only on the occuring problems, if any
as the repetitive part is done by the computer, the number of human mistakes decrease

Custom delivery handling Excel solution

Button-controlled functions

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

Dynamically populated dropdown menus

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

Result list exported as interface file

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.

Scroll to Top