FREE Inventory Forecasting Template: How to Avoid Stockouts + Overbuying

In this video, you'll learn how to plug your sales data into the template, how the forecasting math works, and 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.

1:21

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.

1:34 Step one, set up your products

Step one, set up your products. 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 best sellers aren't going to 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 the formulas too, because that's what keeps everything calculating cleanly.

Britt built this template for her own ecommerce brands with about 15 SKUs, and this part took maybe five 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, well, that's like trying to drive a minivan in a NASCAR race. It'll work for a minute, but eventually you're going to crash. But for now though, get your SKUs in, get them organized, and let's keep going.

2:47 Step two, add details for each SKU

Step two, 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 four 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 also 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 three 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.

3:38

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.

Now 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.

4:29 Step three, forecast this year's sales

Step three, 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.

So start with the growth percentage line at the top. 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%. That means 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 that 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 percentage is in, the forecasted quantities will auto-populate month by month. This is what you'll be using to plan your reorders. It'll give 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 becomes a strategic planning tool.

5:58 Step four, monitor inventory movement monthly

Step four, monitor inventory movement monthly. This is where it gets real. You've forecasted what you think will sell, but 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 actually the only time that 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.

6:49

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. And 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 that your order showed up when it's actually still on a boat somewhere.

7:50

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 equals a stockout is incoming. So when you start seeing red on the horizon, you'll know that it's time to reorder before you're scrambling to rent an SUV and traipse across the country.

8:15 Step five, plan reorders and see cash needs

Step five, plan your reorders. Now that you've got a month by month forecast of when you'll likely 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 four weeks, two months, 120 days? Put that number in the lead time column at the top. That number is going to [unclear] everything else in this step. Because if it takes three 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 where 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 four 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 order timeline before you're in panic mode.

9:41

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, let's say, December or longer, depending on how often you want to actually 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 four 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.

10:19

As a bonus, see your cash needs in advance. Because you entered your cost per unit, the spreadsheet will automatically calculate the total cost of each order, and the down payment if you're [unclear] a percentage, the remaining balance due when the shipment arrives, those kinds of things. 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.

10:46 Step six, when to upgrade from a spreadsheet

Now, let's wrap this up with step six. 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. This usually happens when you're selling on multiple channels, you've got 50 plus 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, formula error, or forgetting to reorder on time can cost you thousands in lost sales or tie up a bunch of cash in inventory that you don't need yet.

That's where inventory systems like Finale or Settle come in. They connect directly to your platforms and pull 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 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.

12:07

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 and consulting for ecommerce brands. We can 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 that 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 that you're working with truth and not guesses.

So whether you're scaling fast, digging out of a stockout mess, or just ready for clean, reliable data, click the link below to learn more about our inventory accounting services. We'll take your inventory from a guessing game to a powerful growth strategy. Remember to grab the free forecasting template using the link below too. Start simple, plug in your numbers, use the tool to plan ahead and make your next inventory decision your smartest one. 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.

Now, if you found this video helpful, go ahead and like it and subscribe for more tips like this. And if you've got an inventory headache that you're trying to solve, drop it in the comments. We'd love to hear what you're facing and point you in the right direction.