A spreadsheet modelling approach to the assortment problem

A list of similar items of different sizes are required on a regular basis, but it is impractical to stock each of the different sizes. Demands for any size that is not stocked must be met by supplying the nearest acceptable size that is stocked, resulting in increased cost or trim wastage for example. The problem of determining which sizes should be stocked to minimise or maximise an appropriate objective function is formulated as a shortest or longest path problem on a directed acyclic network. A spreadsheet model is used to solve the problem, in such a way that showing precedents on the spreadsheet results in the basis tree for the shortest or longest path solution being drawn without the need for special software. The basis tree produced by this method is shown to be planar for practical applications, enabling improved efficiency of the algorithm used. Examples are given to illustrate the application of this approach.