Managing inventory can be challenging, especially with many SKUs. A few calculations, however, can make the process easier.
“Quantity on hand” is physically available inventory. It’s a simple number, but it does not include unfulfilled sales and outstanding purchase orders where the inventory has not arrived. It also does not include future subscription-type sales and predictions of sales.
Those scenarios can be addressed in an “available to sell” calculation.
Available to Sell = (Quantity on Hand) + (Outstanding P.O.s) – (Outstanding Sales) – (Future Sales)
The formula itself is not complex. However, it can become complex quickly when dealing with multiple P.O.s, multiple shipping dates, and many SKUs. Consider the following scenarios.
| Scenario | Quantity on Hand | Outstanding Purchase Orders (Inventory in Transit) | Outstanding Sales (Inventory Not Shipped) | Future Sales (Predicted or Contracted) | Available to Sell |
|---|---|---|---|---|---|
| Customer just purchased 10 items | 140 | 10 | 130 | ||
| Merchant just orders 200 items | 140 | 200 | 340 | ||
| Corporate customer reserves 20 items for next month | 140 | 20 | 120 | ||
| All three scenarios concurrent | 140 | 200 | 10 | 20 | 310 |
Beyond the basic formula above, there are typically additional variables that are difficult to calculate.
Available-to-sell Variables
Purchase order delivery date. You may wish to only include P.O.s with delivery dates that are before your typical shipping times. For example, say you sell an item today with a ship date of two days. If the available inventory is based on an outstanding P.O. arriving in one week, including that P.O. in an ATS calculation makes no sense. Instead, adjust the formula, as follows.
ATS = (Quantity on Hand) + (P.O.s with Delivery Dates < X Days from Today) – (Outstanding Sales) -(Future Sales)
Future sales. Some businesses have no future sales as they sell one item at a time, shipped as soon as possible. Other businesses have future sales such as, again, subscription products or items with custom shipping dates. This is where it gets tricky, as the business would manage two dates: P.O. delivery date and future sales ship date. That formula is as follows.
ATS = (Quantity on Hand) + (P.O.s with Delivery Dates < X Days from Today) – (Outstanding Sales) – (Future Sales Where Shipping Date Is Before P.O. Delivery Date)
Expiration dates. Another complicating factor is inventory expiration dates. A merchant may have quantity on hand but a limited time to sell as it will expire. That requires modifying the formula for expired quantity on hand.
ATS = (Quantity on Hand) + (P.O.s with Delivery Dates < X Days from Today) – (Outstanding Sales) – (Future Orders Where Shipping Date Is before P.O. Delivery Date) – (% of Quantity on Hand Expiring before P.O. Delivery Date)
Predicting sales should be based on history. For example, say, based on the prior years’ sales, you anticipate needing 100 items of product A in the third quarter. You have 50 items on hand at the end of the second quarter, and you want a minimum of 20 items on hand at any time. Thus you would place a P.O. before the third quarter, as follows.
P.O. Quantity = (Future Sales) – (Quantity on Hand) – (Minimum Quantity)
or
P.O. Quantity = 100 – 50 + 20
P.O. Quantity = 70
Use Excel
Some inventory management systems have ATS calculations built in. But if you manage a smaller number of SKUs, you build an ATS calculation in Excel using the formulas above.