Let’s talk inventory forecasting! It’s one of those accounting topics that can feel insurmountable to our clients and potential clients alike. While it’s not the easiest job for the books, it can be way simpler than you might expect. In this post, we’ll cover inventory forecasting in Excel with our purpose-built template.
We can’t promise you’ll leave as an expert, but you should feel much more confident about your next steps!
A Quick Note About Our Inventory Forecasting Excel Template
This post links to our purpose-built inventory forecasting excel template. We use it ourselves for our own ecommerce business but it’s important to mention that we don’t manage a staggering number of SKUs. If you stock hundreds or thousands of different products, this template will likely make you crazy – that’s simply too many products to handle in Excel.
It’s technically possible, but you’d be better served by using specialized software when you’re operating with such a diverse stock of products. You might like to check out any of the following options for this:
Further reading: How to Choose Inventory Management Software
How Much Inventory Should I Have? Why Inventory Management Matters
Inventory stockout can be a real killer for ecommerce platforms. While there are a whole host of benefits to selling on sites like Shopify or Amazon, the intensity of the competition you’re facing is certainly something to navigate. If you run out of stock – and even worse, if you’re then dealing with a 4+ month lead time – your prospective customers will simply look elsewhere.
When asking yourself “how much inventory should I order?”, you should have the information you need ready to go. Otherwise, you may struggle to maintain your edge online. The template we discuss in this post not only makes forecasting inventory simpler, but it also helps you to make more accurate predictions about how much your orders will cost and when you should be placing them in the first place.
Further reading: Causes of Stock Discrepancies | Why Your Inventory Count is Off
Our Inventory Forecasting Excel Template
Right, let’s get into it! This inventory management template for Excel is designed to handle small to medium inventories. When used correctly it can:
- Enable you to make clearer predictions about order costs
- Provide a quick overview of your available and forecasted stock
- Help you keep stocks above empty forever!
We explore the various components of the template below.
Visual Learner?
Those who prefer a visual approach might like our video on this exact topic. It can watched below:
Step 1 – Prior Year Sales Data
Before we get into the nitty-gritty of our template, we want to flag up the following four words: prior year sales data. This is what you’ll be using to build your inventory forecast and is pretty critical if this template is going to be of any use for you.
You’ll essentially be inputting the number of sales you made of a given product last year and using that data to make predictions about the year ahead. Be sure to have this information on hand before continuing.
For the most accurate results, you may want to fill out this spreadsheet immediately after a full inventory check. Let’s say you run a full inventory in December – January would be a good time to get all your information inputted!
The columns to the right of your “prior year sales data” section will use the data you’ve added to make predictions about your sales for the next year. You’ll have to tweak the % predictions to better match your real-world sales.
Step 2 – The Year Ahead
The section titled “projected sales growth over prior year” is designed to help you make accurate – or at least more accurate – predictions about how your inventory levels are going to fare month on month. We explain each row below:
- Beginning – The number of SKUs you have (/had for prior year) at the start of the month.
- Sold/ forecasted – How many units you sold OR how many units you expect to sell. Use your prior year data to predict this. Once you reach the end of each month, replace your forecasted figure with the number of units you actually sold.
- Given away/ disposed of – Inevitably some inventory will leave your warehouse without being sold. This might be through marketing, gifts, or damage. Pop these details in this row.
- Ending – How many units are left at the end of the month? Input this data here.
- Ordered – Placed an order? Input this data here to update your inventory overview in the spreadsheet. It uses your lead time column to calculate this.
Further reading: How to Calculate & Optimize Inventory Turnover for eCommerce Sellers
Step 3 – Product Rows
The product rows in our template are where you input data about a given SKU’s name and cost. You can get this figure by looking at what you paid your vendors for each of your products last year. It’s a good idea to add this data if you have it available as the spreadsheet will use it to calculate the cost of your orders.
This gives you a speedy overview of what each of your products costs you per unit.
Note – Want more rows? Follow these steps:
- Select the bottom row, right click, and press copy.
- Right click in the cell directly below your copied cell.
- Click “paste copied cells”
Step 4 – Lead Time Column
The lead time column is where you input the number of months it usually takes for your orders to arrive. This can help you avoid unnecessary delays. For example, if you know you’ll need fresh stock of an SKU with a 4-month lead time by June, you should probably place an order around February.
Step 5 – Calculating Order Costs
The bottom section of our template is designed to give you a quick overview of how much your orders are likely to cost in a given month. It uses your “cost of SKU” and “ordered” sections to do this.
From our work with our clients, we know that down payments are often a necessary part of ordering, so we’ve included space for this information here. We’ve also built this spreadsheet on the assumption that each of your SKUs is coming from one vendor. If this isn’t the case for you, the accuracy of this section may serve as an approximation rather than an accurate overview.
Download Your Inventory Forecasting Excel Template Today!
We hope this post has helped you get to grips with how our template works. The best way to learn this approach is to get in and give it a go.
LedgerGurus – Here to Help With Inventory Forecasting and More
Most accounting topics seem insurmountable at first glance. With the right support and guidance, however, you’ll soon learn that this stuff is much less complicated than first meets the eye.
If you’re still scratching your head and wondering where to start, please don’t hesitate to contact us. We have helped many businesses manage their inventory more effectively and efficiently. We can help you, too!