It is so weird that sometimes, you can bring a solution to a vital problem of a company, by checking out the things you can extract from academic world (or from google). A colleague and I were asked to develop a tool that enables the optimization of safety stock. We had no idea about how the new units were re-ordered and it was actually surprising for us to find out the absence of a proper re-ordering mechanism in an e-commerce company. With some re-call of academic knowledge and with some research on web, we came up with the solution by using mainstream re-order level concept.
What is re-order point? (or re-order level)
It is an indicator that allows you to understand when it’s necessary to reorder certain items for the inventory. In fact, this metric is more popular in production business (where “inventory” is replaced with the “Raw materials”) than in online shopping. My 15 months of experience in e-commerce industry taught me that if you want to manage an online shopping website, you have to consider your product entry as your “production”, and you need to treat people who are in charge of product creation on the website, with almost the same KPIs that you would potentially have in a production business. That holds for the merchandisers also. Merchandisers are responsible for ensuring that there is sufficient stocks to meet demand while avoiding a level where the cost of carrying the stock is cost prohibitive
What is the formula?
1) Simple re-order formula – Delivery time stock
Re-order point: Average usage (or average demand) X Lead Time
This one above is the simple version, which basically comes from the regular demand for that item and how long is the duration between the order date from the supplier and the receiving date from the supplier to the warehouse. For example, let’s say you have an online store where you sell apparels and let’s assume that you want to calculate your re-order point for Nike X t-shirt. If you sell 50 t-shirts daily (on average) and if it takes 5 days to get new t-shirts delivered to your warehouse after your order, then you need to guarantee that you need to keep at least 250 (=50×5) Nike X t-shirts in your warehouse all the time. Once your stock declines till 250, if you don’t re-order, than you will be running out of Nike X t-shirt in 4 days, which will result in customer dissatisfaction.
Important note: If you want to use daily demand, then you have to adjust lead time in days, or if you want to use another time period (e.g. week, month, year) you need to adjust your lead time accordingly.
Here is a good video of mbabullshit.com 🙂 , where you can find an example of the re-order point calculation. The name of the website indicates how easy the formula can be understood.
2) Advanced re-order formula – Safety stock
Let’s revisit the formula by an addition of fluctuation factor, since your customers’ demand rate or the lead time of your supplier can fluctuate from time to time:
Re-order point: (Average usage (or average demand) X Lead Time) + Service Level*SQRT(Avg. Lead Time^2*Standard Deviation of Demand^2 + Avg. Demand^2*Standard Deviation of Lead Time^2)
The first part is what we had from the previous calculation. Second part, however, is the part that includes the safety stock that will meet a specific service level, with the assumption of uncertain and independent demand during each day of lead time. We also assume that it can be described by a normal distribution.
Wikipedia made it easier to understand: (http://en.wikipedia.org/wiki/Reorder_point)
Reorder point = S x L + J ( S x R x L) Where
- S = Usage in units per day
- L = Lead time in days
- R = Average number of units per order
- J = Stock out acceptance factor
- The stock-out acceptance factor, `J’, depends on the stock-out percentage rate specified and the probability distribution of usage (which is assumed to follow a Poisson distribution).
After capturing information from different websites, this is what I came up with:
sku: The id of a particular product
# SOH: stock on hand (how many units you have in the inventory for that product)
sell through: sold quantity / (sold quantity + stock quantity)
average cogs: average cost of goods sold
demand variation: it’s a long formula but you can consider this one as related to standard deviation within a certain period of time
z value sqrt: J ( S x R x L) (the safety stock part)
re-order point: (avg weekly sales x lead time) + z value sqrt
reorder status: it shows if the merchandiser should make a reorder for that particular product. If SOH>reorder point, it is “no re-order”, if SOH<reorder point it is “re-order”
lead time: this part is blank in the model because the actual lead time used for this formula is a figure of an average
reorder amount: re-order point – SOH
reorder value: reorder amount x average cogs (to make it clear, please note that this has another currency in the dashboard, which makes the calculation divided by a currency at the end)
The more you are stick to this formula, the less you will have disputes concerning the effects of inventory management, because this is something solid through which you can explain your boss the reason of the re-order decisions you make.
Image source: Flickr