Packing optimisation in VBA for Pokemon card grading affiliate.

We designed an algorithm to optimise order packing and eliminate crossovers.

Intelligent packing algorithm

Black Label Grading needed a reliable planning and packing solution that eliminated the risk of any similar or identical playing cards from different customers being packed into the same bundle. Automating this process reduced the risk of human error and increased the speed of the process, which had become too onerous to complete manually.

Cost optimisation

International sales meant that multiple VAT schemes, such as VAT margin, needed to be taken into account. Our systems ensure tax is calculated and reported accurately. Bundles contained between 100 and 200 cards, allowing BLG to unlock favourable shipping rates.

EASY for Excel novices

Being mindful of our client’s initial budget, timelines and to allow easy integration to their current processes, the decision was made to combine a bespoke Excel VBA-based application with several pre-existing frameworks.

VBA userform-driven list builder »

Service digitisation for a Pokemon trading card intermediate »

Workflow management across several ecommerce services »

Sales software for European affiliate partners »

The challenge

Our digitisation work with Black Label Grading covered a number of areas, including their customer database, ecommerce store, workflow management and customer communication. This particular part of the project focused on optimising the physical packing of cards to be sent from the business to a third-party grading partner.

Due to postage requirements, and volume discounts, several limitations are imposed on how boxes are packed and the amount of cards in each box. This not only presented challenges around quantity calculation, but also highlighted the importance of assigning duplicate cards to different boxes to remove the risk of erroneous identification or incorrect grading.

Our approach (the science bit)

Optimisation algorithm design

In order to automate packing list creation that would meet the complex needs, we designed an optimisation algorithm in VBA based on simulated annealing. This is a technique for understanding a global optimum of any given function, and is a method that is well-suited for large quantities of data, particularly when involving high volume search.

Process optimisation

The algorithm processes up to 10,000 packing iterations in a single trial run, and may perform multiple trial runs with adjusted variables if required. In most cases, the optimum bundle set is reached within two trial runs, each of around 3,000-4,000 iterations, but extending the maximum iterations allows the algorithm to explore routes that might initially score lower but ultimately work out better and potentially lower the number of trial runs that need to be completed. The metric throughout this process is a ‘goodness score’, which will ultimately define which combination is the optimal set of bundles.

Expert input

When dealing with such complex variables, we feel it’s important to have confidence in our work. We chose to partner with a mathematician, who became a key part of the project’s overall QA process.

Compatibility

The entire framework was built within Excel, allowing the client to access the tool easily, and run it from any machine within the business. Being a VBA application, the tool can be easily modified, updated and even integrated with other critical business tools.

Excel modelling

By modelling the packing algorithm in Excel with VBA, we kept the timeline short and project overhead low. We created a working tool to solve the client’s immediate need and the model, having demonstrated effectiveness, can be adapted for the client’s other software needs when required.

The result

Unique bundle sizes now make all packages easily identifiable and traceable throughout their logistical journey
No two or more identical playing cards that have different owners will appear in the same bundle, eliminating the risk of customers’ cards being confused or incorrectly graded
Bundles and packages are now more cost-efficient, and meet all of the requirements from the third-party grading company
Our simple execution of what is otherwise a complex and technical tool means that the process is future-proofed, and can be upgraded or integrated at any time

Client testimonial

“The team got to know us, our business and our ways of working. They listened, provided helpful input and automated many of our processes to get us ready for volume, and allow us to focus on growing the business. Whilst custom software is quite a technical undertaking, we felt well informed and in safe hands at each stage and are happy with the outcome of a brilliant series of projects.” John Parker, Director, Black Label Grading