Let me tell you about the time our CEO had to rescue a product shipment by driving it across the country.
Brittany, who also co-owns the skincare brand Sole Toscana, was prepping for a major sales period when things went sideways. Sales had exploded, and they were about to run out of several bestselling products. The shipment had made it to New Jersey, but their warehouse was in Idaho. And standard shipping was way too slow. So Brittany flew to New York, rented a giant SUV, picked up the product from the port and drove it across the country to avoid a stockout.
Thankfully, they’d placed the order just early enough. A few days later, and they wouldn’t have had anything to sell, right when it mattered most.
That close call is exactly why she built this Inventory Forecasting Template – to help her own brand, and ecommerce sellers like you avoid that kind of chaos.
Because here’s the hard truth:
- Stockouts kill your sales
- Overordering kills your cash flow
And most sellers are just guessing or hoping it works out. But this free Inventory Forecasting Template helps you stop guessing and start planning with real data from your past sales and lead times.
In this video, you’ll learn:
- How to plug your sales data into the template
- How the forecasting math works
- How to use the template to know when to reorder (before it’s too late)
And stick around to the end, because I’ll show you what to do when spreadsheets simply can’t keep up anymore and how to automate this whole process as your business grows.
If you’re managing fewer than 50 SKUs, this tool is perfect. But if you’ve got hundreds or thousands, let’s just say that Excel isn’t the place for that.
But for now? This is the smartest way to get started.
Step 1: Set Up Your Products
So, first things first. Let’s get your products into the spreadsheet.
Each row in the template is set up for one SKU. That’s important because every product moves differently. Your bestsellers aren’t gonna behave like your slow movers, and if you lump them together, your forecast is going to be all kinds of wrong.
Now if you’ve only got a handful of SKUs, you’re golden. Just fill in each row with the product name or SKU, one by one.
But if you’ve got more SKUs than rows? Don’t panic. Just copy the section of the spreadsheet that includes all the input columns, right-click where you want to add more and select “Insert copied cells.” Boom! Now you’ve got room for more products.
Here’s a pro tip: Don’t just type over the old cells. Copy the formatting and formulas too. That’s what keeps everything calculating cleanly.
Britt built this template for her own ecommerce brand with about 15 SKUs, and this part took maybe 5 minutes. If you’ve got fewer than 50 products, this will be fast and painless.
But if you’ve got hundreds of SKUs and you’re still trying to do this in a spreadsheet? That’s like trying to drive a minivan in a NASCAR race. It’ll work for a minute. But eventually, you’re gonna crash.
For now, though? Get your SKUs in, get them organized, and let’s keep going.
Step 2: Add Key Details for Each SKU
Once your products are listed, it’s time to feed the spreadsheet the info it needs to actually forecast.
You’ll enter 4 key things for each SKU:
- Cost per Unit
This is what you pay your vendor for each unit. We’re not talking about what the customer pays. We’re talking COGS here.
Why include this? Because your forecast isn’t just about units. It’s about cash. We’ll use this cost later to calculate how much you’ll need to spend when it’s time to reorder.
- Lead Time (in Months)
How long does it take between placing an order and receiving the goods in your warehouse? If it takes 3 months for your supplier to deliver, and you wait until inventory hits zero, well… may the ecommerce gods have mercy on your cash flow.
Input that lead time now so the spreadsheet can tell you when to order before disaster hits.
- Beginning Inventory Count
You’ll want to start this process with your most accurate count, ideally from a physical inventory at year-end or month-end.
Enter how many units you had on hand at the beginning of the forecast period (typically January). That becomes your jumping-off point.
- Last Year’s Monthly Sales
This is where you enter the quantity sold each month for that SKU last year. If you don’t have that yet, pause and go pull it. Why? Because this spreadsheet projects forward based on your past sales. The more accurate your history, the better your forecast.
Here’s a helpful tip: If you had a one-time spike or drop last year (like a big promo or a supplier delay), make a note of it. You’ll want to account for that when adjusting this year’s forecast.
And that’s it for setup. Think of it like prepping ingredients before cooking. This is the mise en place of inventory forecasting. Do it right, and the rest flows like butter.
Step 3: Forecast This Year’s Sales
Now that you’ve plugged in your product info and last year’s numbers, it’s time to look ahead. This spreadsheet uses last year’s monthly sales to forecast this year’s demand.
But here’s the twist: it doesn’t assume your business is standing still. You get to grow!
Start with the “Growth %” line. At the top of each product section, you’ll see a row with a bunch of percentages.
That’s your crystal ball.
If you’re expecting 20% more in sales this year, enter “20%” (meaning 120% of last year’s sales). The spreadsheet then multiplies each month from last year by that number to give you this year’s forecasted sales.
Be realistic, not wishful.
This is a great moment to ask:
- Did your ad budget go up or down?
- Are you launching new products that’ll pull focus from this SKU?
- Did last year have weird spikes or supply issues?
If December was inflated by a huge one-time sale, maybe don’t assume this December will be the same.
You can always override the numbers manually, too. This spreadsheet is your tool, NOT your boss.
Once your growth % is in, the forecasted quantities will auto-populate month-by-month. This is what you’ll be using to plan your reorders. It gives you a window into the future.
Pro tip: Once the year gets going, replace forecasted sales with actuals as each month closes.
This keeps your data fresh and makes the rest of the model more accurate. This step is where your spreadsheet stops being a guessing game and starts becoming a strategic planning tool.
Step 4: Monitor Inventory Movement Monthly
This is where it gets real. You’ve forecasted what you think will sell. Now it’s time to track what’s actually happening.
Every month, your inventory is going to move. Some will be sold, some given away, some disposed of, and, hopefully, some will arrive from your suppliers.
This spreadsheet helps you keep tabs on all of it. Here’s how.
Start with your beginning inventory.
Each month starts with a number: how many units you have on hand. That number flows from the ending inventory of the month before.
January starts with your physical inventory count from the end of December. (That’s why January is highlighted. It’s the only time you need to manually enter that starting number, though doing counts on a regular basis to true things up is a VERY good idea.)
From there, the spreadsheet does the math for you.
Then track inventory going OUT.
There are two ways product leaves your shelves:
- What you sold
This can be forecasted or (better!) manually updated with actuals at the end of the month. Just plug in the quantity sold in the right column.
- What was given away, damaged, or disposed of
This row is for all the weird stuff that isn’t a sale – samples, promo giveaways, broken units, the box your toddler used as a spaceship. You know… life.
If you’re not sure how much to expect here, don’t guess. Just come back at the end of the month and fill it in after the fact. That’s the best way to keep your numbers honest.
Next, track inventory coming IN.
Scroll down to the “Inventory Arrived” row. This is the month your product actually hits your warehouse, NOT when you ordered it. BIG difference.
Let’s say your lead time is four months. If you ordered in May, your inventory won’t arrive until September, and that’s when you enter the quantity here.
This step is key for visibility. It’s how you avoid the nightmare of assuming your order showed up when it’s actually still sitting on a boat somewhere.
Watch your Ending Inventory update automatically.
Once all your ins and outs are tracked, the spreadsheet calculates your ending inventory each month.
And here’s the fun part. When inventory gets low or goes negative, the cell turns red. Red = A stockout is incoming. Cue dramatic music. So, when you start seeing red on the horizon, you’ll know it’s time to reorder…
BEFORE you’re scrambling to rent an SUV and traipse across the country.
Step 5: Plan Your Reorders
Now that you’ve got a month-by-month forecast of when you’re likely to run out of stock, it’s time to do the thing that actually prevents a stockout: Reorder at the right time.
This spreadsheet helps you do exactly that by showing you when inventory dips too low AND working backwards, based on your lead time, to tell you when to place your next order.
First, know your lead time.
Lead time is how long it takes from the moment you place an order until that inventory lands in your warehouse. Is it 4 weeks? 2 months? 120 days? Put that number in the lead time column at the top.
That number is going to guide everything else in this step. Because if it takes 3 months for your product to arrive, and you wait until you’ve only got a month of inventory left?
Well… you’re going to be very sad.
Next, look ahead to see when you’ll run out.
Scroll to the right on your spreadsheet and look for the red cells in the ending inventory row. That’s your warning light. That’s when your inventory hits zero or goes negative. Find the first red cell and ask yourself: “When do I need this inventory to arrive to avoid going negative?”
Let’s say you’re going to run out in October. That means you want your next batch to arrive in September, ideally before the chaos hits.
Now, work backwards. If your lead time is 4 months and you want product in September, you need to place your order in May. That’s the beauty of this spreadsheet: it lets you reverse engineer your reorder timeline before you’re in panic mode.
Lastly, calculate how much to order.
Look at your forecasted sales for the months your order needs to cover. If you’re ordering in May and your inventory won’t arrive until September, you need to cover September through December (or longer, depending on how often you want to place orders).
Let’s say your forecasted sales over those months total 1,000 units. That’s your reorder quantity. Plug that number into the “Inventory Ordered” row for May. Then, 4 months later (in September), enter those same units in the “Inventory Arrived” row.
Boom. Your spreadsheet updates, and you’ll see your ending inventory rise again – no stockout, no stress.
As a bonus: See your cash needs in advance. Because you entered your cost per unit, the spreadsheet will automatically calculate:
- Total cost of each order
- Down payment (if you enter a percentage)
- Remaining balance due when the shipment arrives
That means you’re not just forecasting inventory. You’re forecasting cash flow, too.
This step is what makes the whole system work. You’re not just reacting to what’s running out. You’re planning ahead with confidence.
Step 6: Know When to Upgrade from a Spreadsheet
Because while this template is powerful, it’s not forever. Spreadsheets like this are a great starting point. They give you structure and visibility to help you plan smarter.
But let’s be honest. There comes a point when spreadsheets start to crack under the pressure. Usually when:
- You’re selling on multiple channels
- You’ve got 50+ SKUs (or a whole warehouse of them)
- You place large orders with long lead times
- Or you’re constantly stuck in the stockout/ overstock whiplash
At that point, it’s not just inconvenient. It’s risky. A missed update, a formula error, or forgetting to reorder on time can cost you thousands in lost sales (or tie up a bunch of cash in inventory you don’t need yet).
That’s where inventory systems like Finale or Settle come in.
They connect directly to your platforms and pull in your sales and inventory data automatically, so there’s no more manual data entry. They track reorder points in real time and alert you the moment something needs to be ordered. And in some cases, they can even generate purchase orders for you, based on your lead time and sales velocity.
It’s like strapping a jet engine to your inventory planning process. No more manual updates. No more playing spreadsheet detective. Just clean, accurate, real-time forecasting.
So, if you’re starting to feel like your spreadsheet is holding you back, that’s your sign.
Automation isn’t just easier. It’s the only way to scale without chaos.
How to Choose the Right Inventory Management System
And if you want help figuring out the right inventory system for your business?
Well, that’s exactly where LedgerGurus comes in. We specialize in Inventory Accounting & Consulting for ecommerce brands.
We help you:
- Get your COGS right, so you know exactly what every product actually costs
- Clean up your inventory data and align it with your books
- Help you pick and integrate the right tools for your size and growth
- And make sure your numbers actually match and make sense
Because real-time tracking is only helpful if the numbers are accurate. And that’s our job: to make sure you’re working with truth, not guesses.
So whether you’re scaling fast, digging out of a stockout mess, or just ready for clean, reliable data, click the link to learn more about our Inventory Accounting services. Let’s take your inventory from a guessing game to a powerful growth strategy.
Remember to grab the free forecasting template. Start simple. Plug in your numbers. Use the tool to plan ahead and make your next inventory decision your smartest one yet.
And if you’re ready for deeper clarity and confidence in your numbers, we’re here to help. Don’t wait until you’re renting an SUV in New Jersey.
Download the Inventory Forecasting Template here.
