Assortment planning

Abstract

The scope of the project was to carry out assortment planning for UAE based Retail Company. The objective of the project was to come up with the methodology or model to assist the merchandize manager for assortment planning. The retail data from POS (Point of sales) has been shared by the company for three months (Sep-Nov) of four stores operating at Dubai airport. Factors which are significant for the assortment mix are Demand Estimation & Forecasting, Consumer Segmentation, Basket Lift & Store Layout, Substitution Effect & Profitability Analysis of categories & subcategories. The assortment mix of the items sold is majorly divided into three categories: Food & Confectionary, Liquor & Tobacco and further divided into different subcategories and brands. The model development was started with a basic analysis of the shared data. Basis analysis has been done on overall data comprising of all the categories to see the variation in monthly and weekly demand. The weekday factor has been captured & taken into consideration for forecasting. The data was further bifurcated based on three categories mentioned above and insights were reported on the percentage share of each category (SKU wise and revenue-wise). Data was further drilled down based on subcategories and a demand forecast was done on dominant subcategory against each category using Additive Modelling and Exponential Smoothing. The consumer segmentation (Clustering) was done to analyze the impact of new consumer profiling & better planning of assortment mix. The K-Mean methodology was used for clustering of Airlines. Revenue estimation was done for every nine categories of airlines using Regression analysis. Bundling & Basket lift analysis was carried out to understand the positive correlation between categories subsequently for subcategories & selected brands. The bundling results were used to design the layout of the retail store. The Layout was suggested based on two-way lift & commutative lift factors for categories & subcategories. Solver Tool was used to maximize the lift factor & utilizing the bundling or basket effect on consumers. The layout design can be proposed for brands by applying a similar model. The substitution effects of the brand were captured by the correlation matrix. Limited subcategories (more than five brands) were selected to measure the effect of substitution of one brand to another. A negative correlation was observed for the selected combination of brands. The profitability analysis was done on category & subcategory level to determine the priority order of products under consideration for aggressive push or to discard from the portfolio. In profitability analysis, variables named fixed cost factor, holding cost, review period, service level, purchasing cost, lead time & space required were taken into consideration with dummy values. The accurate profitability of categories & subcategories can be predicted from actual values of these variables. The demand (daily) & revenue (per unit) were taken directly from the data provided. The brand-wise profitability analysis was also carried out along with substitution matrix for better understanding of brand dynamics. All the above-mentioned methodology & tools were useful for assortment planning. First & foremost accurate demand forecasting needs to be done at each hierarchical level. With the combination of bundling & profitability analysis, store layout & assortment mix should be decided. These models would be the foundation stone for an assortment related strategy. Clustering & segmentation will help as an additional tool to understand the consumer pattern & to decide assortment policies.