Inventory Forecasting Spreadsheet in Excel: Formulas and Reorder Math
I ran a 3PL on spreadsheets for five years before I built forecasting software. Here’s the spreadsheet we used to run an Amazon brand with a warehouse split, every formula in it written out so you can check or rebuild it, and the honest week it stopped working.
An inventory forecasting spreadsheet tells you how many units to reorder this week for every SKU. This one uses a days-of-coverage model, which is the approach I’d put in front of any brand with a few hundred SKUs or fewer:
- Reorder quantity = daily sales × target days of coverage, minus stock at FBA, minus stock at your warehouse, minus units already on open purchase orders.
- Ship to FBA = FBA daily sales × FBA target days, minus what is already at FBA.
- You enter three numbers per SKU (units per day, stock on hand by location, open PO units) and set three defaults once: lead time, safety buffer and FBA target days. No FORECAST.ETS gymnastics and no Z-scores.
- The free download is below. You can be running it in about fifteen minutes.
- In my experience, the sheet became harder to maintain at around 150 SKUs or past two sales channels. The signs to watch for are in the checklist further down.
Why I’m writing this post
Most inventory forecasting spreadsheets you can download are lead-gen bait. They show you a dashboard screenshot, gate a toy spreadsheet behind an email wall, and drop you into a long drip campaign. The templates themselves are usually thin. They have no channel split, no way to account for open purchase orders, and no honest note about where the thing helps and where it leaves you exposed.
I’m not interested in that. I ran a third-party logistics warehouse for five years before I started SKU Compass. I built this spreadsheet because it’s what we could afford, and I used it every Monday morning to decide what to reorder from the manufacturer and what to ship into Amazon FBA. It worked well for the first few hundred SKUs. Then it started to lie to me. I’ll tell you exactly when.
So here’s the honest version: the real spreadsheet, how it works, the formulas written out, what it does and doesn’t do, and the signs that tell you it’s time to stop running inventory from a workbook.
What the template actually does
This is a days-of-coverage replenishment planner. That’s a long phrase for a simple idea: every SKU has a daily sales velocity, and your job is to keep enough stock to cover a specific number of days before your next shipment arrives. The template answers one question for every SKU: “How many units should I order or ship this week?”
It’s not a statistical forecaster. It won’t detect seasonality for you, and it doesn’t calculate safety stock with Z-scores and standard deviations. Plenty of free templates online present that math. In my experience it rarely earned its complexity. Statistical methods require sufficiently clean, representative history and more maintenance than a simple velocity rule, and a well-kept velocity rule is easier to trust and easier to fix when it’s wrong.
The operator approach is simpler: decide how many days of coverage you want, and let current sales velocity do the rest.
How it works in three steps
One sheet. One row per SKU. Three inputs you fill in, three defaults you set once, and the template writes the reorder list for you.
Step 1 — Enter three numbers per SKU
- Units per day. This is your daily sales velocity, split by channel: one column for Amazon FBA sales and one for Shopify, direct or wholesale sales. Pull these from the last 30 to 90 days of order history.
- Stock on hand. This is your current inventory, split by location: one column for what’s in FBA and one for what’s in your warehouse or 3PL. A lot of Amazon sellers get this split wrong (more on why below).
- Purchase orders. These are units already coming in on open POs. This column prevents double orders. If you forget it, the template will tell you to reorder things you ordered last week.
Step 2 — Set three account-wide defaults (once)
This is the operator move. Instead of asking you to configure lead time, safety stock and target stock for every single SKU, the template has three numbers that apply to every row. You set them at the top of the sheet and leave them alone.
| Account default (editable) | Starting value |
|---|---|
| Manufacture lead time (including shipping) | 90 days |
| Safety stock buffer | 45 days |
| FBA target stock | 40 days |
| Total target coverage (MSF) | 175 days |
The three numbers add up to what we call MSF: Manufacture + Safety + FBA. It’s the total days of coverage you want across your whole supply chain, which is enough stock to survive a late factory, a rough quarter and the inbound check-in time at Amazon. The defaults above are a sensible starting point for Amazon sellers buying from overseas. If your lead time is 30 days instead of 90, change one number and the whole sheet updates.
Step 3 — Read the output columns
The template calculates four numbers for every SKU, automatically:
- Days of stock at FBA: how long your current FBA inventory lasts at the current sales pace.
- Warehouse days against direct demand: how long your 3PL or Shopify fulfilment stock lasts against direct (non-FBA) sales only.
- Units to ship to FBA: how many units to transfer from your warehouse into Amazon to hit the 40-day FBA target.
- Units to reorder from the manufacturer: the quantity to buy right now to get back to 175 days of total coverage, after counting anything already on open PO.
That’s the whole Monday morning restock meeting in one column. Sort the sheet by “units to reorder”, largest first, work top to bottom, and you’re done.
The formulas, written out
People search for “the inventory forecasting formula in Excel” as if there’s one. There are
really four, one per output column, and none of them needs anything more advanced than
MAX and MIN. Here they are as you’d type them into a blank sheet. The
downloadable file lays its cells out its own way. These formulas are written so you can check its logic
or rebuild it from scratch.
Put the three defaults at the top: lead time in B1, safety buffer in B2,
FBA target days in B3. Then give each SKU a row. In row 6, C6 is FBA units per
day, D6 is direct units per day, E6 is stock at FBA, F6 is stock
at the warehouse and G6 is open PO units.
Days of stock at FBA
Warehouse days against direct demand
This measures the warehouse against direct sales only. It’s the number to watch if your warehouse also ships Shopify orders. It overstates how long the warehouse lasts if you’re also feeding FBA from it, which is why the FBA column sits beside it.
Units to ship to FBA
This tops FBA up to its target, but never ships more than the warehouse actually has.
Units to reorder from the manufacturer
This is total daily sales times total target days, minus everything you already own or have on order.
The G6 term is the open-PO column. Leave it out and this cell double-orders every time a
container is on the water.
One refinement worth adding: suppliers sell in case packs, so round the reorder up to a whole case.
If the case pack is in H6 and the reorder is in J6, use
=IF(J6>0, CEILING(J6, H6), 0).
A worked example
The numbers below are invented so the arithmetic can be checked. Take one SKU that sells 12 a day through FBA and 4 a day direct. It has 300 units at FBA, 900 at the warehouse and 1,000 on an open PO. The defaults are the ones above.
| Output | Working | Result |
|---|---|---|
| Days of stock at FBA | 300 ÷ 12 | 25 days |
| Units to ship to FBA | 12 × 40 = 480 target, minus 300 at FBA | 180 units |
| Target stock across the chain | (12 + 4) × 175 | 2,800 units |
| Already owned or ordered | 300 + 900 + 1,000 | 2,200 units |
| Units to reorder | 2,800 − 2,200 | 600 units |
Two things to notice. FBA has only 25 days of stock against a 40-day target, even though the SKU as a whole looks comfortable. That gap is why the split matters. And if you drop the 1,000 PO units from the last calculation, the reorder jumps from 600 to 1,600. The open-PO column is the difference between a sensible order and a surprise container.
Grab the template (free)
The Excel file below is ready to run — the three defaults are pre-set to the values above, and it includes two sample SKUs so you can see the formulas firing before you drop in your own data. Drop your email below and it’s yours.
What about Excel’s built-in forecasting?
Search for “inventory forecasting models in Excel” and you’ll land on Excel’s own tools. They’re real, they’re free, and they answer a different question from this template. Here’s what’s there, read from Microsoft’s support pages on 30 September 2026:
- Forecast Sheet is on the Data tab, in the Forecast group. Give it a column of dates and a column of sales and it draws a forecast chart with a confidence band. Microsoft says it uses the AAA version of the Exponential Smoothing (ETS) algorithm. The dates need consistent intervals, though it can cope with up to 30% of the points missing. Microsoft lists it for Excel for Microsoft 365, Excel 2024 and Excel 2021.
- FORECAST.ETS creates the forecast cell by cell; related ETS functions expose the detected seasonality (FORECAST.ETS.SEASONALITY), the confidence interval (FORECAST.ETS.CONFINT) and forecast statistics (FORECAST.ETS.STAT).
- FORECAST.LINEAR (and the older FORECAST) fits a straight line through your history and extends it.
All of those forecast demand: how many you’ll sell. None of them tells you what to order. They don’t know your lead time, your stock at FBA, your warehouse, or the container already on the water. That’s the job of the reorder math above.
So use them where they help. If a SKU is clearly seasonal, or trending hard up or down, run Forecast Sheet on its daily or weekly sales and use its projected rate for the coming lead-time window as that SKU’s “units per day”. Feed that into the template. You get a better velocity number without swapping a reorder rule you understand for a model you don’t. For relatively stable SKUs, a recent actual-sales average (the last 30 to 90 days) may be simpler and easier to explain in a meeting; compare it with the forecast before adding model complexity.
What most free templates skip (and why it matters)
I read the top results for this search before I wrote the first version of this post. Almost every one skips three things that matter for real Amazon and multi-channel sellers. Here’s what they leave out and how this template handles each.
The FBA / warehouse split
If you sell on Amazon FBA and also run a warehouse, 3PL or Shopify fulfilment out of a separate location, you don’t have one inventory bucket. You have two. The one at FBA sells through faster, and it’s quick to replenish because you just send a box. The one in your warehouse is the reserve that feeds FBA and also ships direct to Shopify customers.
A template that puts “total inventory” into a single column will keep telling you to reorder too late. It doesn’t see that your 60 days of “total” coverage is actually 10 days at FBA and 50 days at the warehouse. By the time the 50 days of warehouse stock runs down to zero, you’ve already been out of stock at FBA for a month. This template splits FBA and warehouse into separate columns from the start.
Open purchase orders
Most free templates I looked at ask for current stock but ignore what’s already on the way. Say you ordered 5,000 units last week that ship in 60 days. If the template doesn’t know that, it will tell you to order another 5,000 this week, and you’ll find out in two months when a container shows up you didn’t plan for. This template has a dedicated “Purchase Order Units” column that is subtracted from the manufacturer reorder calculation. It’s one field, and it prevents the most expensive mistake in a homemade spreadsheet.
Lead time including shipping
In my overseas-buying experience, factory lead time is not the same as lead time. A factory that says “30 days production” usually meant 30 days after the deposit cleared. Then there were about 3 weeks to the port, 2 to 4 weeks on the water, and 1 to 2 weeks for customs and inbound check-in at FBA. For us the real lead time usually landed somewhere around 75 to 110 days, not 30.
Your own receiving history is the better input: it says how long your orders actually took. The supplier lead time and reorder point guide covers how to measure it. Until you have that number, the template’s default is 90, a middle-of-the-road number. If you source domestically, change it to 14 or 21 and the whole sheet recalculates.
Setting once, applying to all SKUs
The three defaults (lead, safety, FBA) apply to every SKU on the sheet. Most templates either hard-code these per row, which is tedious and error-prone, or ask you to calculate per-SKU safety stock with a Z-score formula, which looks impressive and gets abandoned. The operator answer is to set the three numbers you’d actually defend in a meeting, apply them to everything, and override specific SKUs only when you have a real reason to. Simple beats fancy.
The templates that get shared online are training wheels. They work until they don’t, and the moment they stop working almost never shows up as an error. It shows up as a stockout on your best SKU right before your biggest week.
The numbers from running this for five years
I don’t trust inventory content that doesn’t show real numbers, so here are mine from the 3PL era:
| Figure | What it was |
|---|---|
| ~400 SKUs | What we managed on the spreadsheet before it broke |
| 40 minutes | The Monday morning recalc by year 3 (full workbook refresh) |
| $18K | The single-SKU stockout that finally convinced us to upgrade |
A 40-minute recalc is survivable. An $18,000 stockout on a SKU that had been a top-5 seller for six months is the kind of thing that makes you rebuild your whole approach. The spreadsheet had correctly predicted the stockout. The problem was that we didn’t open the file that Monday because we were busy. No template can fix that.
When Excel is exactly the right tool
If you have fewer than 100 SKUs, one or two sales channels, and one person who reliably opens the file every week, a good Excel template is the best tool you can use. It’s free and transparent, it forces you to understand your own business, and it surfaces questions that automated software hides.
In my view, most brands under $500K in revenue should not be paying for inventory software. They should be running a real spreadsheet, reviewed every Monday for about forty-five minutes. That’s the template we’re giving you.
The graduation checklist: 7 signs your spreadsheet is costing you more than software
A spreadsheet stops being free the moment stockouts and overstock cost you more than a software subscription would. Here’s how to know you’ve crossed that line. If three or more of these are true, the template is now the bottleneck:
- You’re past 150 SKUs or selling on more than two channels. The Monday recalc starts taking over an hour, and reconciling FBA with Shopify with Walmart WFS by hand becomes a real chunk of your week.
- You stocked out on a top-10 SKU in the last 90 days. One stockout is a mistake. Two on your best SKUs in a quarter is a system failure. The template was probably right; you just didn’t open it in time.
- You’re paying Amazon’s aged inventory surcharge on slow movers (the fee that replaced long-term storage fees). Over-ordering is the quiet twin of stocking out. It can cost as much, but it rarely makes you angry enough to notice. Check the current age thresholds and rates in Seller Central; they have changed more than once.
- The file has six different versions and you’re not sure which is current. “final_v3_RANDY_EDIT.xlsx” is the canary in the coal mine. A spreadsheet has no real version control.
- You can’t answer “what should I order this week?” in under 10 minutes. If the template answers the question but building the answer takes a founder half a Monday, you’re paying for it in opportunity cost.
- Your daily velocities are stale and you know it. The template is only as good as the “units per day” numbers you put in. If yours are from last month because refreshing them is a chore, the reorder math is already wrong.
- You’re thinking about hiring an inventory manager. That’s the real signal that the work has outgrown the tool. Before you post the job, read our inventory manager vs software cost breakdown; the math is not what most founders expect.
If you recognise three or more of those, the spreadsheet has already quietly become the expensive option. Software doesn’t have to mean enterprise bloat. SKU Compass is the tool I built to answer the same what-to-order-and-when question without relying on a workbook someone has to refresh by hand. Plans and pricing are here.
Frequently asked questions
How do I build an inventory forecasting spreadsheet from scratch?
Use one row per SKU. Put three defaults at the top (lead time, safety buffer and FBA target days), then give each row five inputs: FBA units per day, direct units per day, stock at FBA, stock at the warehouse and open PO units. Add four output columns: days of stock at FBA, warehouse days against direct demand, units to ship to FBA and units to reorder from the manufacturer. The exact formulas are written out above. Sort by units to reorder, largest first, and work down the list each week.
How do I forecast inventory in Excel?
For most ecommerce brands with a few hundred SKUs or fewer, use a days-of-coverage model. Work out each SKU’s daily sales from the last 30 to 90 days, multiply by the number of days of stock you want to hold, split it by where the stock sits (FBA or your own warehouse), and subtract what you already have and what is already on order. What’s left is the reorder quantity. It’s simpler and easier to trust than statistical forecasting with FORECAST.ETS or Z-score safety stock, both of which need clean, representative history and more upkeep.
What is the inventory forecasting formula in Excel?
The reorder formula is =MAX(0, daily sales × target days of coverage − (stock at FBA + stock at warehouse + open PO units)). With the three defaults in B1:B3 and the SKU’s inputs in columns C to G, that’s =MAX(0, (C6+D6)*($B$1+$B$2+$B$3) – (E6+F6+G6)). The FBA transfer is =MAX(0, MIN(warehouse stock, FBA daily sales × FBA target days − stock at FBA)). Days of stock is simply stock on hand divided by daily sales.
Should I use FORECAST.ETS or Excel’s Forecast Sheet for inventory?
Use them to estimate demand, not to decide orders. Forecast Sheet (Data tab, Forecast group) and FORECAST.ETS project future sales from history using exponential smoothing, and they can pick up seasonality. They don’t know your lead time, your stock by location or your open purchase orders. A good pattern is to use Forecast Sheet to estimate the coming daily sales rate for a seasonal or trending SKU, then feed that rate into the days-of-coverage reorder math.
How many days of stock should I keep on hand?
For overseas manufacturing with 60 to 90 day lead times, a sensible starting target is roughly 175 days of total coverage: 90 days of manufacturing lead time, 45 days of safety stock buffer, and 40 days of target stock at FBA. Brands buying from domestic manufacturers with 14-day lead times can run much leaner; 60 to 75 days of total coverage is usually enough. Adjust the three defaults in the template to match your actual supplier situation.
How do I account for open purchase orders in a forecasting spreadsheet?
Add a dedicated Purchase Order Units column that tracks units already ordered but not yet received, and subtract it in the manufacturer reorder calculation. Without this column the spreadsheet double-orders whenever a shipment is in transit, which is the single most expensive mistake in a homemade spreadsheet. The template in this post has the column built in.
Should I forecast FBA and warehouse inventory separately?
Yes. FBA and warehouse or 3PL inventory sell through at different speeds, and FBA is usually faster. They also replenish on different lead times: topping up FBA from your own warehouse is a same-week shipment, while reordering from a factory takes 60 to 90 days. Pooling them into one total hides the FBA stockout that’s about to happen. Keep them in separate columns and calculate days of stock for each.
Does this template handle seasonality?
Not automatically. It uses your most recent daily velocity, so heading into your busy season you need to update the units per day column to the rate you expect, not the one from 60 days ago. Most brands handle this by refreshing velocities monthly or reviewing their top 20% of SKUs by hand before a peak. Excel’s Forecast Sheet can help estimate that seasonal rate. Automated seasonal handling is one of the things that pushes brands from a spreadsheet to dedicated software.
At how many SKUs does Excel stop working for inventory forecasting?
From running a 3PL on spreadsheets, the practical ceiling is around 150 to 250 SKUs on a single channel, or about 100 SKUs across multiple channels. Past that, the Monday recalc starts taking over an hour, daily velocity numbers go stale because refreshing them is tedious, and version control becomes a real problem. We ran ours to about 400 SKUs before it became the most expensive free tool in the warehouse.
Do forecasting tools work better than spreadsheet tracking?
Not automatically. On stable SKUs, a well-kept spreadsheet and dedicated software do the same arithmetic, so the answers are close. Software pulls ahead on volatile SKUs, new launches and multi-channel catalogues, and anywhere refreshing the daily velocities has stopped being a weekly habit. In my experience running a 3PL, under 100 SKUs on one channel the difference rarely justified paying for software, and past a couple of hundred SKUs on two or more channels it usually did.
Ready to stop rebuilding the reorder list every Monday?
SKU Compass is the software I built to answer the question this template answers, what to order and when, without a workbook to refresh by hand. Built by the operator who wrote the template in the first place.
See plans and pricing →