Automation of customs invoice documents in Excel

In the months since Brexit arrangements came into force at our EU borders, there has been much discussion about the problems of increased paperwork. Getting customs invoices or clearance CSVs wrong can cause delay or rejection. However, with the right data, in the right place, exporting to the EU can be plain sailing.  

A brand new trading landscape

Preparing new customs invoices for export to the EU requires much more data to be compiled than a standard accounting invoice. These include the full name, address, EORI number and contact details of seller and buyer, the number and issue date of the commercial and proforma invoices. 

The purchase order or sales contract price, method of payment, currency and any discounts or additional charges must also be recorded along with the quantity, gross and net weight of goods and the number, weight and type of packages.

Everything must be detailed using the harmonised system (HS) tariff code with a plain English description of the products. You will need to provide incoterms including delivery and payment and record the original country of origin of the goods, the route they will take, the means of transportation and, finally, their actual value. 

All this information should be available within your systems. The trick is ensuring that it is exported efficiently and accurately so it can be supplied to customs and inspection authorities in the formats they require. 

We recently worked with a client to overcome issues they were experiencing using their current systems and streamline their data export and merging processes. 

Problems compiling customs invoices from Sage exports

A client of ours found that their current version of Sage simply wasn’t capable of creating what they needed to produce the requisite documentation. They needed a system that would merge a range of datasets. 

Using the systems they had in place was extremely time-consuming and allowed greater room for error so being able to automate the data export and compilation processes would enable them to operate more efficiently and avoid, potentially costly, mistakes.  

Automating the process

Using VBA, we were able to pull key data, such as weights and country of origin from the client’s inventory database and combine this dynamically with data exported from their SAGE systems. This new automated system has helped save significant amounts of administration time reducing the costs and potential pitfalls of incorrect information interrupting the smooth flow of their supply chain. 

Why not use vlookups?

Vlookups require a certain level of user expertise and would still require manual intervention on each invoice. While it is possible to compile the data in this way, it is hard to do this dynamically due to inconsistencies in the way that the source data, exported from Sage is laid out. This makes the process somewhat clunky and extremely time-consuming. It also opens it to human error especially if the vlookup doesn’t find all the information you require. It could be easy to miss outstanding details in larger invoices. 

Guaranteeing accuracy

The risk of getting this wrong is huge. At best, delayed or rejected shipments will cost you money. At worst, they could cost you business. So we were keen to ensure that there was no room for error and the system was as intuitive as possible. The VBA element enabled us to make it easy for a novice to use. 

They simply load the file and the process happens. However, if the system finds any data exceptions such as duplicate or inconsistent product codes within the databases, it will flag them to the user. 

It will also pick up anomalies in files or when key data elements are missing from files and alert the user so they can address these before the customs invoices are issued.

The tool we created is now working well. Smoothing the passage for our clients’ data exports is doing the same for their physical exports. To find out how we could help you overcome new data issues arising from Brexit and make trade with the EU as frictionless as possible, Get in touch.

Recommended Posts