Skip to Content
Purdue Krannert School of Management logo

An Optimization Approach for Assortment Planning

Rishabh Mohan, Saurabh Kurjekar, Matthew A. Lanham

Purdue University Krannert School of Management

mohan35@purdue.edu, skurjeka@purdue.edu, lanhamm@purdue.edu

Introduction

Assortment Planning: The retail sector is obsessed with targeting a multitude of issues such as customer targeting, segmentation, peer competition, pricing, location selection, and multichannel/Omni-channel strategies, etc. It is witnessed, however, that minimum importance has been given to one of the most significant strategies -assortment of SKUs. Proper assortment of SKUs based on customer requirements addresses significant underlying issues involved in supply chain such as proper product mix as desired by customers, variety of product mix, minimized lost sales, minimized excess inventory, the top line effects (increase in sales/profit margin/market shares), the bottom line effects (reduction in inventory costs, increase in productivity). A similar opportunity of optimizing various business objective functions like space, cost, and inventory arose with a firms focused on spares. We developed mathematical models to support and augment the efficiency of legacy heuristics that were followed retail locations selling spares.

image of car broken into parts

Methodology

The data is provided by a national retail that sells spares and accessories in the United States. The data consists of 14,625 SKUs and 16 stores. The SKUs have different size and cost. Each store is divided into 4 MPOGs (planograms), and each MPOG has its own constraints of space and budget. Metrics like bundling, supersessions, exclusion, space, budget serve as constraints to define the criteria for SKUs to be in the assortment. Few additional variables were also considered to improve the accuracy of our model.

Figure: Data preparation methodology

Model Building The objective of our optimization model is to maximize the profit. We have chosen multiple constraints like shelf space, MPOG size, total store budget, budget per MPOG, and minimum quantity required if a SKU is stocked. Our decision variables are price per SKU, space per SKU, and whether the SKU should be stocked or not (binary model). With this, we first created a prototype in excel on a small sample dataset, and later replicated the model to include all stores using the R.

Figure: Model Building Methodology

Decision Model

The optimization model was developed in excel solver first, and run for small number of SKUs (~100). It produced excellent results. As solver has a limit of ~200 for number of decision variables, our next step was to import the optimization model in Gurobi, and run it using R. Firstly, the file was imported in R. All the missing values were imputed. In gurobi, model$A is the linear constraint matrix, so subset was then converted into a matrix, and then transposed. Gurobi library was then imported in R, and all the constraints and values were specified in the model in the form of a vector or a matrix. In model$A component, all constraints were added in the form of a matrix. In model$obj component, objective function which is profit (price-cost) or profit per shelf space is added in the form of a vector. Model$vtype tells model that the variable type vector should be binary, integer or semi-integer.

Figure: Formulas

Results

Decision model 1Maximizing profit- The total profit obtained for all 16 stores is mentioned in the table with maximizing profit as objective function. The total sales were also calculated for all stores.

Decision model 2Maximizing profit per shelf space- The total profit per shelf space obtained for stores was also calculated. The total calculated sales is mentioned in the table below.

Objective Function -Profit Objective Function -Profit per shelf space Objective Function -Profit
Store No. Total calculated sales Total Sales Profit Last year sales Difference in sales
3571 459757.9 196241.2 154333.2 88995.4 370762.5
3813 459758.3 430972.1 307658.8 333954.6 125803.7
2584 46332.4 435525.4 311632.8 349484.6 -303152.2
5720 371622.1 347310.9 255522.7 226791.7 144830.4
5012 206758.6 193552.5 157260.0 161372.6 45386.0
4135 199636.6 187448.9 155337.9 197077.8 2558.8
1009 206037.5 192786.2 155638.3 175270.5 30767.0
3686 158253.9 147718.3 124454.7 108093.0 50160.9
5177 134092.2 124833.8 107993.7 56402.1 77690.2
3439 160226.7 149622.5 125327.3 67544.7 92682.0
2571 214275.4 200355.4 158976.5 126500.7 87774.7
3213 192427.8 179815.4 143330.9 82247.3 110180.5
1872 154198.7 143772.6 121499.6 90717.5 63481.2
3123 178158.5 166563.2 137259.7 112886.1 65272.4
2767 180087.8 168031.1 138988.3 89509.9 90577.9
5102 252078.0 236337.2 183079.3 162278.9 89799.1
2429127.5 1054775.9

Graph: Calculated vs last year sales

Conclusions

We have described the optimization model which has been built using various constraints, and representing the various assortment challenges for the retail store. The objective function of the model is to maximize the profit as well as maximizing profit per shelf space. Various constraints are used like shelf space, MPOG, total store budget, budget per MPOG, and minimum quantity required if a SKU is stocked. The model is first run on excel solver on a small scale, about 100 SKUs, and then imported to R, and run using powerful Gurobi package. With this model, we have successfully obtained our objective function profit for all the stores with all MPOGs. We have obtained binary results for about more than 14000 SKUs, and determined which SKUs to stock based on various constraints like cost, space, minimum quantity.

Acknowledgements

We would like to the thank Business Information and Analytics Center (BIAC), our industry partner and professor Lanham for their guidance and support on this project. It was a great opportunity to learn more about optimization and learn more about how such problems are solved in practice.