Optimising Ratio Packs - Part 2

As we look to determine the optimal purchasing behaviour and configuration, there are some useful mathematical tools at our disposal which can take a lot of the guess work out of these decisions.  Linear optimisation for example, also known as linear programming, is a mathematical technique used to find the optimal solution to a problem with linear constraints. The goal of linear optimisation is to maximize or minimize a linear objective function subject to a set of linear inequality or equality constraints and when applied properly can solve problems well beyond the capabilities of a standard spreadsheet.

If we follow the same example in Part 1, first we need to determine the constraints that need to be met:

• The units per pack and the amount of packs required must be integers - for obvious reasons

• The volume purchased must satisfy all sales demand  for each size in each store - in reality this would be relaxed in most cases

• The same pack configurations selected must be used across all stores, therefore each store can not have their own unique pack configurations given this would be unmanageable to procure 

• The maximum units per size per carton is 6 - can be calibrated depending on need

• In this exercise however I’ve removed the 19 units per pack constraint to allow for the optimal mix as well as optimal qty per pack

With the constraints now set, we need to now set the objective function which is to minimise the sum of the excess volume for each size in each store. Depending on the desired outcome, different techniques can be applied to arrive at the optimal outcome. Given we’re looking to solve for 24 variables simultaniously (21 size across 3 pack configurations as well as the amount to purchase of each of the packs) we’re going to  look for an approximate global optimum rather than a specific local optimum.

The process then commences of iterating through many different configurations of the 24 variables in pursuit of the minimum summed error.

Each of these iterations then drives out a value based on our original function which is the sum of all excess units across all sizes and all stores. You’ll notice that in the pursuit of a global minimum, the results regularly return to a high error as it explores new configurations.

Ultimately the following pack configuration provide the minimum amount of excess units based on the constraints provided.

In this scenario, we’re able to capture 100% of the sales demand (21,383) by purchasing 23,888 which is 11.7% above the planned sales. A relatively modest improvement off the previous 3 configurations which were biasing small, flat and larger curves.