Patrick Mulligan
•
Today at 00:00
After a slow summer, your eCommerce business is having a strong September, and you’re excited for what will surely be a blowout holiday season. And then your warehouse manager lets you know that, after a recent shipment into Amazon’s warehouses, you have less than 100 units of your bestselling SKU. 😬
You reach out to your vendor to reorder, and you’re told that the lead time is 12 weeks. After pulling up your calendar you painfully count off weeks until December 10th, which is after Black Friday and Cyber Monday. You’re going to be out of stock of your hero product during the biggest shopping days of the year, and it’s all due to a failure of inventory forecasting. 😱
You don’t need to be an expert in inventory forecasting to succeed in eCommerce—in fact there are entire careers built around supply chain management—but being proficient at it could make or break your business. ECF members have lost millions of dollars of revenue due to stock-outs, so forecasting correctly will be one of the biggest wins your business can attain.
This guide will begin with the basics of inventory forecasting, walk through some key concepts and common stumbling blocks, and then help you build an inventory forecasting solution so that will never stock out of your products and can maximize the return on your cash.
Inventory Forecasting Explained
If you want to sell products online you need to have products to sell. If only it were as simple as just carrying loads of inventory at all times, way more than you could ever sell through in any reasonable period.
Unfortunately, suppliers won’t give you products for free, so lots of inventory will require lots of cash. And, just as unfortunate, inventory also takes up space, so the more that you have the more warehouse space you’ll be paying for.
Days, Not Units
To start, don’t think of your inventory in terms of units. Think of it in terms of days. If you sell 100 units of a SKU every 30 days, and you have 200 units in stock, you have 60 days of inventory. Sometimes this will be referred to as “stock coverage.”
Of course, it doesn’t always make sense to use the last 30 days to predict what the next 30 days will look like. Maybe you just ran a promotion, or your product was mentioned by an influencer. For a lookback period, 120 or 180 days might be better for calculating the “sales per day” portion of the days of inventory calculation, as the longer period will even out some of the spikes.
When it comes to days of inventory you should be thinking of the upcoming days. If you’ve been in business several years, you can use the prior year’s data to inform your days of inventory calculation. If you’re selling Christmas lights and it’s September 1st, looking at last year’s data for September, October, and December is going to be a lot more instructive that looking at the last 90 days of this year.
Even better, if you’ve been up an average of 20% on last year’s numbers, add 20% to last year’s run rate. If last September through December you sold 1000 units, bump it up to 1200 to get an adjusted rate of sale of 9.8 units per day.
Of course, when looking at last year’s data, don’t forget that sales in eCommerce can be spiky, and take into account any one-off events that might be skewing the historical data.
Ex. maybe last year was the first year a particular product was available, and now that it’s more mature you might expect it to sell at a slower rate, even if overall you’re up year-over-year. So you can make an adjustment to an adjustment to arrive at an expected rate of sale for this year.
Using Days of Inventory to Order
Your days of inventory metric is critical for knowing when to order more inventory. The first thing to do when you have this figure is to make sure that the number is higher than the number of days it takes to get more inventory. If you have 30 days of inventory, and it takes 60 days to receive more, you’re about to lose some money!
The absolute minimum days of inventory you’ll want to carry at any given time is the number of days it will take to get more. Everyone’s margin of safety is different—some eCommerce owners will want to carry 10% more than their margin of safety, whereas others that are in growth mode might want to carry 25% or 50% more. And we’ll get into below, numerous other factors will affect your reorder math.
If you’re doing any assembly or kitting you also need to make sure you’re accounting for everything you need to ship out our product, which could include packaging, product inserts, etc. Standard shipping boxes from suppliers such as Uline are usually very quick to order, but if you have any custom packaging, mailers, etc. be sure to run a days of inventory calculation for these components as well.
Let’s Make This More Complicated
Once you have your adjusted days of inventory figure for everything you need, you’re ready to order, right? There are actually a few more considerations: backorders, price breaks, shipping logistics, and warehouse space.
Backorders
If you’ve run out of a product, or if you’ve pre-sold a new product, you might have units committed to customers beyond your current inventory level. This is the backorder total, and can simply be added to your final order number.
Price Breaks
The next thing to consider are price breaks. A supplier will typically charge you less per unit as the total order size goes up. A run of t-shirts might be $8/shirt at 2,000 units, $7/shirt at 5,000 units, and $5.50/shirt at 10,000 units. If your inventory reorder calculation has you at 3,500 shirts, you might want to bump up the run to 5,000 to capture that lower price per unit. Also, most suppliers will have a minimum order quantity (MOQ), which is the absolute minimum you can order. If your projected need is coming in under this number, at the very least you’ll need to raise your order to the MOQ, or wait a bit more to order until your need is high enough to meet the MOQ.
Shipping Logistics
Similarly, when you place your order you should be thinking about shipping logistics. Domestically, products will generally ship Ground, LTL (less than truckload), or FTL (full truckload). Ground almost always means UPS or FedEx, and you are charged according to box count, and the weight and dimensions of those boxes. If you’re ordering relatively small amounts, or your product is small and lightweight, you’ll likely be shipping Ground and won’t have to consider shipping factors when setting your reorder quantity.
When you’re ordering products in the size or quantity that will ship on pallets, though, the math can get more complicated. The price of shipping will go up the more pallets you ship, but not linearly. One pallet of product might cost $400 to ship, two pallets might cost $700, and three pallets might cost $950. If each pallet holds 1,000 units, you’re adding 40 cents in cost per unit if you order one pallet but 31.6 cents if you order three pallets.
Once you’re past a certain number of pallets (usually five or six), the next step up is to ship full truckload (FTL), which can hold up to 24 standard pallets (or double that if your pallets can be stacked on top of each other). The cost of FTL won’t vary dramatically based on the number of pallets, so if you’re paying $2,500 for the truck, and each pallet holds 1,000 units, ordering 24 pallets instead of 12 will reduce the shipping contribution to COGS per unit from 20 cents to 10 cents.
If you’re sourcing your inventory from overseas, you will have to factor in more or less the same shipping considerations, although you’ll mostly be deciding between air freight and container freight. And your cost calculations will have to take into account port and brokerage fees and other ancillary line items.
Warehouse Space
One final complication: you need somewhere to store your inventory. Your math might show that a full truckload worth of inventory would result in the lowest landed cost per unit, and it would be enough to cover your inventory needs for 120 days, about twice your production time, which would give you an ideal margin of safety as you push the pedal to the floor on marketing in the coming months. But do you have space for 24 pallets of goods, in your own warehouse or in a third-party warehouse? If you’re intending to send this inventory into Amazon under the Fulfillment by Amazon program do you have enough room under your current Amazon inventory limits? A fully built-out inventory forecasting system must take into account the storage space required for inventory.
How to Forecast Inventory
Once you understand all of the factors that go into forecasting inventory, you need a system to compile all of the data, run calculations, and present action items. This can range from the basic tools such as a spreadsheet file to specialized apps to fully built-out systems that integrate inventory tracking with forecasting and financials.
Old School Methods
When eCommerce owners first attempt to wrangle inventory forecasting, a calculator is often the first step, followed soon thereafter by an Excel spreadsheet. Your spreadsheet can range from one with just a few columns and formula all the way up to intense models with macros, Pivot Tables, and VLOOKUP galore that pull in data for you and make up-to-the-second forecasts. 🤓
Excel is an incredibly powerful piece of software, and determined eCommerce owners have used it to tackle daunting inventory problems:
“Basically I use a big excel file with several sheets and a ton of VLOOKUPs. First we look at quantities sold over a certain period (3 months, 6 months, etc.) plus required lead times by manufacturer to figure out how much inventory we should be carrying for a particular SKU. This number we will adjust based on seasonality using multiples in the formula.”
-An ECF Member with 10,000+ SKUs
Even a simple Excel spreadsheet with a dozen columns can be a useful place to store MOQs and other information about each SKU.
And your homegrown system doesn’t have to be in Excel. You can do it in Airtable or any other spreadsheet or database tool.
Inventory Forecasting Software
If you’re not that comfortable with Excel, or don’t want to be responsible for updating a massive spreadsheet file, specialized inventory forecasting software is the next step up.
Compared to a full-stack ERP (discussed below), inventory forecasting software is more limited in scope but often superior at the single task of predicting your inventory needs. Some eCommerce owners who use a full ERP system still prefer to connect to an inventory forecasting app rather than using the ERP’s own built-in forecasting modules.
Inventory Planner
The simply named Inventory Planner has been the leader in this space for the past several years, and is the preferred tool of ECF members. It will connect to all of your selling platforms, pull in sales data, reconcile that with your inventory levels, and tell you how many units to order and when.
It will create purchase orders for you to send to your vendors, and will even optimize your orders based on shipping considerations (LTL, etc.) and warehouse storage costs. Note that Inventory Planner is not cheap, starting at $250/month and scaling up based on your revenue. In fact, as the price of Inventory Planner has continued to climb, some ECF members have gone back to Excel:
“As of one week ago, I decided to give up on off-the-shelf inventory planning and forecasting software. The costs and black-box nature of their forecasting has turned me off. I have opted to use a data export app to pull out all the native data from my store into Excel and build a simple stock-on-hand snapshot and forecast model.“
Finale
Finale is a long-time favorite of ECF members, and is more than just an inventory forecasting tool. In fact, it approaches much more expensive full ERP systems in terms of features. But its inventory forecasting will pull in your sales data, make forecasts, and create POs for you. ECF members do warn that it takes some fine-tuning to get right.
Newer Players
The recent price increases for Inventory Planner has eCommerce owners looking for alternatives. Some other inventory forecasting apps used by ECF members include Netstock, Cogsy, and Prediko.
Go Big: Use an ERP
Larger eCommerce businesses will likely have an ERP system to manage their inventory and financials. These systems range in price and complexity from smaller SaaS products all the way up to the same systems that Fortune 500 companies use.
Inventory forecasting and ordering is a core component of any ERP system, but as noted above a specialized tool such as Inventory Planner may still be worth using alongside an ERP.
Inventory Forecasting Pro Tips
Your ability to manage inventory will have a massive effect on your ability to manage cash. An optimized forecasting and inventory system will keep dollars in your bank account up to the second when they have to be converted into inventory.
Carrying Cost
The cost of holding inventory is known as “carrying cost.” This concept includes all of the components that go into inventory–capital cost, service cost, risk cost, and storage cost—and expresses it as a percentage of the total inventory value.
Capital cost includes the interest cost of the funds tied up in inventory. If your business has cash to deploy and doesn’t need to tap lines of credit, this cost can be very low or nothing at all. However, there is also an opportunity cost to deployed capital. If, rather than being tied up in inventory, your cash could be earning 5% interest, your capital cost would be 5%.
Service cost includes tax and insurance. Most states in the US do not tax business inventory but some, such as Texas, do. In every state, though, you’ll need insurance to protect your inventory against loss, and these sums will factor into your service cost.
Risk cost includes shrinkage (inventory disappearing from your warehouse) and spoilage (inventory damaged in your warehouse, or expiring in the case of food and other consumables).
Storage costs are all of the fees required to warehouse your inventory. If you own your own warehouse, this would include the square footage of your facility devoted to inventory, as well as all personnel costs associated with it. If you use a third-party warehouse, this number will be a line-item on your bills.
Inventory costs will never be zero, and generally efficient eCommerce businesses will operate with a 15-25% carrying cost. It’s fairly complex to figure out an exact carrying cost without a full-fledged accounting firm or ERP system, but it’s worth at least estimating it to see if your business is beyond 25%, which would indicate either excess inventory or bloated storage and capital costs.
Optimizing for Payment Terms
Once you’ve been ordering from a supplier long enough to establish a credit history you will be able to order on terms. This means you can receive inventory and not pay for it for 30, 60, or even 90 days.
A fully optimized inventory forecasting system will take the payment terms for various suppliers into account when placing reorders, as a setup in which you can sell enough of the inventory to fund a purchase order before the purchase order payment is due is a thing of beauty.
Closing Thoughts on Inventory Forecasting
Inventory forecasting can be a daunting task, and it can feel at times like one part math and one part fortune-telling in order to properly estimate future inventory needs. But it’s crucial for the success of your eCommerce business to effectively manage your inventory because you can’t sell products if you don’t have products to sell.
Learn From More than 200+ Community Discussions About Managing Inventory
Want to end stock-outs forever and make your eCommerce business cash-rich? Learn about our community of over 1,000 7- and 8-figure store owners for the latest reviews and discussions of inventory forecasting software and strategies. Or apply to join our community.
Post by Patrick Mulligan
Patrick Mulligan is the cofounder of Pop Chart, a DTC wall décor company, which he has been running since 2010. Prior to Pop Chart, he worked as a book editor at Penguin for nearly a decade.