Inventory forecasting attempts to predict demand for your products such that you can make the best purchasing decisions to maximize cash-flow and minimize lost sales from stock outs.
There is no perfect forecasting model. Stock outs will occur as demand is not 100% predictable. A great example of this is during COVID this year. Many retailers for essential goods were facing a huge surge in demand. If you were a retailer selling toilet paper and making purchase decisions in January of 2020 with a 90 day leadtime, you may have looked at the previous year’s Q2 sales as the most reliable indicator of what demand would look like. Obviously COVID caused all Q2 demand projections to be extremely low.
Many retailers lack any sort of sophisticated inventory forecasting model and rely simply on gut feel. Even a model built in excel could produce a significant improvement that will directly impact the bottom line.
The Basic Forecasting Formula
The basic forecasting components are as follows:
Sales Velocity (Units Sold per Day)
Current Stock
Leadtime
Example:
Sales Velocity: 3 units / day
Current Stock: 300
Leadtime: 30 days
You would first calculate how many days of inventory you have in stock:
300u / (3u/d) = 100 days
If the inventory goal is to never run out of stock and to have just in time delivery, then the retailer should order in 70 days time such that the new shipment will come in the exact day that the current inventory runs out.
But how many units should be ordered? Let’s make some simplified assumptions at first:
- Demand is 100% constant
- Ordering overhead is zero (administrative and shipping costs)
If these two assumptions were true, then the optimal strategy would be to order daily. So when there is 30 days of inventory, you will want to order 1 day of inventory (or 3 units), then 3 units the next day, and so on. You can see this example here: https://docs.google.com/spreadsheets/d/1VptZPI6g_8c2fWhhiJSn6Dyiw777zPWqg6Fm6Da9I3o/edit?usp=sharing
Unfortunately we don’t live in a world where these assumptions are true. There is always going to be overhead when purchasing, so as a business you have to decide how many days of stock you want to maintain. Let’s say you decide to maintain 30 days of stock, this would mean you would typically order 12 times per year. If you want to maintain 180 days of stock, you would order about twice per year. Your decision will be greatly impacted by shipping costs and leadtime. Let’s look at another example but this time we decide to maintain 90 days of stock: https://docs.google.com/spreadsheets/d/1VptZPI6g_8c2fWhhiJSn6Dyiw777zPWqg6Fm6Da9I3o/edit#gid=896966811
With this example, we see the order quantity now goes up to 270 units (equivalent of 90 days of stock) and is reordered 90 days later.
A further consideration is that if demand is higher than expected or leadtime is longer than expected, the stock can run out. In order to compensate for this possibility, a “Minimum Stock Level” can be added. Let’s assume a Minimum Stock Level of 20 units in the next example. The purpose of this is to provide a conservative buffer so that a stock out is less likely to occur:
https://docs.google.com/spreadsheets/d/1VptZPI6g_8c2fWhhiJSn6Dyiw777zPWqg6Fm6Da9I3o/edit#gid=1544113419
Beyond the Basics, additional demand assumptions
In the previous examples we assumed a velocity of sales that was constant at 3 units/day. The limits of such simplicity is that many businesses are seasonal, and velocity may not be constant throughout the year. A more sophisticated model could look at what the sales velocity was during the same month last year as a predictor of demand this year.
A model could also assume an annual growth on units sold from last year, or even have manual override assumptions for how many units the seller thinks will sell in a give month.
These added variables would result in a calculated forecasting schedule representing estimated future sales. This could then be used to calculate reorder points and quantities.
Adding Automation
The next layer of automation would most likely take us beyond excel and into an Inventory Management solution or Operations Platform with such functionality built in. The platform would hold all historical sales, and potentially be able to draft entire purchase orders based on forecasting without User action. The User could simply review the draft purchase orders and approve them.
An operations platform could even go a step further to recommend when warehouse transfers should be done. If one Warehouse A is moving 3x as much product as Warehouse B, if Warehouse A has excess stock, a transfer between Warehouse A and B could make sense and the operations platform could perform calculations to determine this.
Inventory Forecasting Results
The goal of any inventory forecasting model would be to maximize sales and cash flow. A good software system could also minimize labor required to maintain the data needed by the model, make the most optimal purchasing suggestions, and ultimately prevent stock outs and maximize cash flow.