Packing optimisation in VBA for Pokemon card grading affiliate.
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.
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.
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.
CompatibilityThe 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.
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.
- 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