Inventory Management Analysis For Company X

abstract: The purpose of this project is to provide our client with a tool to mitigate Company X's franchise-wide inventory control problem. The problem stems from the franchises' initial strategy to buy all inventory as customers brought them in without a quantitative way for buyers to evaluate the store's inventory needs. The Excel solution created by our team serves to provide that evaluation for buyers using deseasonalized linear regression to forecast inventory needs for clothing of different sizes and seasons by month. When looking at the provided sales data from 2014-2016, there was a clear seasonal trend, so the appropriate forecasting model was determined by testing 3 models: Triple Exponential Smoothing model, Deseasonalized Simple Linear Regression, and Multiple Linear Regression.The model calculates monthly optimal inventory levels (current period plus future 2 periods of inventory). All of the models were evaluated using the lowest mean absolute error (meaning best fit with the data), and the model with best fit was Deseasonalized Simple Linear Regression, which was then used to build the Excel tool. Buyers can use the Excel tool built with this forecasting model to evaluate whether or not to buy a given item of any size or season. To do this, the model uses the previous year's sales data to forecast optimal inventory level and compares it to the stores' current inventory level. If the current level is less than the optimal level, the cell housing current value will turn green (buy). If the currently level is greater than or equal to optimal level or less than optimal inventory level*1.05, current value will turn yellow (buy only if good quality). If the current level is greater than optimal level*1.05 current level will be red (don't buy). We recommend both stores implement a way of keeping track of how many clothing items held in each bin to keep more accurate inventory count. In addition, the model's utility will be of limited use until both stores' inventories are at a level where they can afford to buy. Therefore, it is in the client's best interest to liquidate stale inventor into store credit or cash In the future, the team would also like to develop a pricing model to better meet the needs of the client's two locations