Analytics And Intelligent Systems

NUS ISS AIS Practice Group

Supply Chain Analytics — Optimization for Small Retail Firm — December 19, 2017

Supply Chain Analytics — Optimization for Small Retail Firm


Supply chain optimization is critical to ensure the optimal operation of a manufacturing and distribution supply chain, it basically refers to the minimizing of operating cost whilst meeting the demand of downstream customers. Nowadays, data analytics is widely used in supply chain management and optimization to make better business decisions. It is the science of examining raw data to help draw conclusions about information. The supply chain is a great place to apply analytic theories to look for a competitive advantage, because of its complexity and also because of the prominent role supply chain plays in a company’s cost structure and profitability.

In this research, the data set is simulated according to real business situation, it represents the historical data of a small fictitious retailer, including files of items, orders, purchase orders, suppliers, BOM, etc. Below is the dashboard of the data set.


The optimization is conducted based on the pull strategy and agile theory. Agile is defined as a strategy that is more responsive in a volatile market place, where this strategy is totally demand driven. As consumers buying patterns are changing on a very rapid pace, so does the whole supply chain management changes. The fundamental drivers of agile supply chain are Speed, Cost and Efficiency. A pull strategy is when customer demand drives the entire production process. Long term customer demand forecast is accomplished. The advantage of using pull strategy is that it has higher service levels, lower carrying costs, decreased inventory levels, and fewer markdowns. We then further conduct inventory optimization including ABC analysis, stock reward function calculation, and route optimization on optimal route design.

Demand Forecasting

Demand forecasting is important for the other parts of supply chain management. As the retailer, we need to do the operational short term demand forecasting. For this project, we mainly made the quantity prediction of each item. As our data records covering the time   period from 2013 January to 2016 September, we carried out the demand forecasting of 2016 October, November and December. The data structure is shown as below.


Data includes the order records of each product items in three different warehouses and on different date, each warehouse has the same 106 items. As it shows, for each item it has very little order quantity, so the data was rearranged as monthly orders. The three locations of the warehouses are Chicago, New York, Los Angeles. To simplify the introduction of our forecasting model, New York is chosen as an example to do the later forecasting and later inventory optimization. A quantitative model is built to predict future demand of each item. Following figure is the graph of one item in one location during the round 3 years’ records.


As it can be seen, the trends of one product is not so obvious. Then some traditional machine learning methods were applied, however the performance is not satisfied, compared with time series model, we tried doing the prediction, but the accuracy is not as good as expected.

The reason might caused by the demand of each item in each warehouse is small. So the model could be sensitive for the tiny variety. And the seasonality is not so obvious. So we move to try another method to do the forecasting. Long short term memory is one excellent deep learning method for time series forecasting. The partial structure of LSTM is shown as below.


The LSTM is a special kind of RNN, recurrent neural network, it is capable of learning long-term dependencies, learn from the previous data input, to our case, each neuron will learn from the previous neuron, with current month demand and the last month demand, as our model we set the time step as 6, which means we will get one prediction depends on the previous 6 months’ actual demand. After trained our model with our 45 months data, we finally can get our Oct’ s demand, and combined with the predicted Oct demand, do the prediction for the Nov, then combine and forecast Dec demand. It realized the information persistence. And not so strict to find the seasonality, so the result could show better performance.

As we can see, here is the result of the 45 months training processing results, it fits well of our original demand, and we also get the predicted demand for the last three months of 2016.


Then we repeated the same work for each item in each warehouse.

ABC Analysis

Used for: Inventory management and optimization

Based on: Pareto principle, 80/20 rule

Logic: vital few and trivial many

Objective: Maximize service AND Minimize inventory cost

Data preparation, Analysis and Results:

Inventory management is a challenging problem area in supply chain management. Companies need to have inventories in warehouses in order to fulfill customer demand, meanwhile these inventories have holding costs and this is frozen fund that can be lost. To achieve inventory management and optimization, ABC analysis is conducted to categories inventory items into ABC class based on importance rating.


Data set on sales and inventory with over 100 types of products in 2015 is deployed for analysis. To carry out such analysis, we first derive the items like unit cost and the usage of each product over 1 year. Then calculate the turnover in 2015, rank those products based on net value, and got the cumulative cost and items. After we got the pre-processed data table in above figure, we finally use the ABC rules listed below to plot the ABC classification results as a line chart.



Revealing from the figure 3, there are in total over 20% of products are classify into A type, which make up over 70 % of the net value. Then follows B type having over 30% of products which accounts for almost 20% of the revenue. Finally, products in C class can only earn less than 10% net value but make up 40% of the total products which indicates as a great amount. Overall it proves the rule of thumb: vital few but trivial many.

Inventory management Implication and Strategies


  • A-items should have tight inventory control, more secured storage areas and better sales forecasts; re-orders should be frequent, with weekly or even daily reorder; avoiding stock-outs on A-items is a priority.
  • B-items benefit from an intermediate status between A and C; an important aspect of class B is the monitoring of potential evolution toward class A or, in the contrary, toward the class C.
  • Reordering C-items is made less frequently; a typically inventory policy for C-items consist of having only 1 unit on hand, this approach leads to stock-out situation after each purchase which can be an acceptable situation, as the C-items present both low demand and higher risk of excessive inventory costs. Make to order production strategy is also applicable there.

Warehouse Insights and Optimization


ABC analysis can also be used in design of warehouse when carry out storage and retrieval process. Since majority of the picking activity is performed in a rather small area, the warehouse layout should be optimized to reduce time spent looking for product in the back of the warehouse. Demonstrated from above figure,  the fastest moving products in the inventory should be located closest to the shipping, staging, and receiving area in the lower right of the diagram below. The area marked with A, B, C indicates the ABC items.

Application of ABC analysis in warehouse


Previously, the shelves are set up in a U-shaped layout, providing a logical path for picking. However, this layout is not efficient. For each preparation of partial products, operators have to walk the whole U. If the stored items are big volumes or if the U cell is widespread, the time spent in walking and transportation becomes significant.

Since the time passed to move or transport material or parts is considered as a non-value operation, or a waste in the lean thinking way. ABC analysis is deployed here focusing on the “stock turns” index or “frequency of picking”. The A class items being so often picked, it is only common sense to place them close to entry-exit (green zone) in order to reduce reaching distance and picking time. Statistically in fewer demand, B class items are placed behind A class (orange zone) and C class items (the least picked) stored even further (red zone). So in most cases, the journey in the U cell will be restricted to the front (green zone). In few cases only, statistically less frequent, the operator will have to walk the whole U cell.

On top of time and distance reduction, we can imagine the light of our green zone should be on constantly, but orange and red zones could be equipped with sensors or switches to light them up only when somebody is in. In the same way, for the comfort of workers, heating or air conditioning isn’t necessary in zones where they seldom go. These are opportunities to reduce energy expenses.

Implication and Recommendation


Besides the implication and strategies, for further scope, developing a customized inventory management agent system is also applicable by consolidate the technology we proposed so far as demonstrated above.

  • Purpose — To determine the amount of inventory to keep in stock, which is how much to order and when to order.
  • Functions — Automation of inventory management; Timely react to demand deviation from the forecast demand; Making corrections based on stock reward function & replenishment policies
  • Working Logic — ERP data is first as employed as the input for ABC analysis. Then the LSTM algorithm are used and the related forecast error will be calculate, more deep learning algorithms like RNN or time series model like Arima can also be involved to finally choose the best. The stock rewarding algorithm (SRA) can be used to get the recommended stock level using forecast results, at the same time some replenishment policies and rules from domain expert of this company can also be added to supplement SRA to achieve optimal stock level. At last, real demand will be compared with the forecast one, and the performance of SRA will be evaluated to modify the parameters in the previous section to achieve better performance.

Stock Reward Function


Since our customer is a retail company, it should pay a lot attention to stock related management. Except for demand prediction, optimize stock is also an important point. Here our team try an emerging method, called Stock Reward Function.

Stock reward function is defined as bellow:


In which:

  • is the gross margin for selling 1 unit
  • Sis the stock-out penalty (negative) for not serving 1 unit
  • is the carrying cost penalty (negative) for not selling 1 unit in stock
  • is the number of units held in stock
  • ytis the demand for the period t
  • αis a discount factor
  • Ris identical to R but with S=0

In our implementation, we suppose that R∗ is equal to R, and S=0, and we will get Stock reward function as bellow:



Since we wanted to try different way with other teams, we not only tried Lingo to implement this function, but also used Python to implement it.

We used a Python package called DEAP. Which is a Genetic Algorithm package. Since we have fixed some parameters like bellow:

  • C = – 0.3
  • α = 0.98

Our chromosome and gene design become quite easy, which is consist of K. And as for demand yt, we choose 3-month demand.

Our target for this GA is maximizing reward for every product. And we set GA initial population as 1000, 40 generations and mutation to get different input combination.

Finally, we will get the optima stock for every product, which is showed bellow:


Order Routing


Retail Order Routing is the efficient allocation and fulfillment of customers orders. It means knowing where all the inventory is located, understanding the costs of shipping from each location and having the flexibility to change priorities on the fly to better manage the business. Order Routing helps manage, optimize and improve the profitability of the retail business. As the business grows, routing the orders becomes a complex problem that grows exponentially. Running a real-time business without a solid retail order routing system – is just not sustainable. Efficient and timely routing helps in increasing the customer satisfaction.

The problem discussed here is about:

  • A small retailer company hosts warehouses in three major cities in the United States ,New York, Chicago and Los Angeles.
  • A survey collected from the various customers indicated that they are quite happy with the quality but they are not satisfied with the on time delivery.
  • In order to analyse this issue, the customer order data was collected from the various warehouse locations to suggest any improvement in the existing routing procedures.
  • The team chose the warehouse located in New York and analysed the various outbound routes to the customer locations situated in the city.
  • The main parameters that were considered while optimization of routes were distance, delivery time window, and service time.

Routing use My Route Online

My route online is an online software which aids in optimization of multiple routes using the google map API. We can  Import as many as 350 addresses from Excel, or manually add multiple locations in various different formats.


Steps of using My Route Online

1)Enter the address manually in the prompt that appears or import the address from excel files and click on next button.


2)Set the required parameters like the departure time,number of routes, vehicle size etc and click on Plan My Route button at the bottom corner.


3)Visualization of the route


  • Print out the best routes



Manual changes in the route can be performed using the Manual Changes button and the best route map can be obtained by clicking the Print Map button.

Route with Delivery Time Constraints

For Deliveries adhering to time windows i.e the customer wants the delivery to be done with in a particular time window during the course of the day,the below instructions needs to be followed

1)Add two additional fields to your spreadsheet(excel spreadsheet). Label them “Delivery From” and “Delivery To”. The labels must be exactly as written here, without the quotes. These labels will be recognized as “Window From” and “Window To” in the software.


2)In the “Delivery From” field, put in the earliest delivery time for the stop and in “Delivery To”, put the latest. Make sure that the times are 24 hour time (military time). For example, 10:00 AM is 10:00, and 2:00 PM is 14:00.

3)Import your spreadsheet into MyRouteOnline.

4) Set the parameters you need and click Plan My Route.


Team Member

A0163200B – Anand Rajan

A0163364E – Gao Yuxi

A0163265E – Li Hangxing

A0163312U – Yao Meng

A0163313R – Shi Haiyuan