How to Forecast Inventory in Excel

Running out of your best-selling product is frustrating, and so is having your cash tied up in slow-moving inventory. The good news is you can prevent both problems without complicated software. This tutorial will walk you through building a powerful yet simple inventory forecasting model right inside Excel, giving you clear signals on when and how much to reorder.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Inventory Forecasting Matters

Guesswork is expensive. When you don't have a clear picture of future demand, you open your business up to costly mistakes. Effective inventory forecasting isn't just about counting stock, it's about making your business healthier and more profitable. It helps you:

• Avoid Stockouts: Nothing is worse than having a customer ready to buy a product you don't have. Lost sales and damaged customer trust can be devastating, especially for growing businesses.
• Prevent Overstocking: Excess inventory ties up cash that could be used for marketing, growth, or other opportunities. It also increases storage costs and the risk of products becoming obsolete or expiring.
• Improve Cash Flow: By ordering only what you need, when you need it, you keep more cash in the bank. This financial flexibility is critical for managing day-to-day operations and investing in your business’s future.
• Make Smarter Purchasing Decisions: A good forecast gives you the data to negotiate better terms with suppliers - whether that means placing larger, less frequent orders to get a discount or aligning your orders with their production schedules.

In short, it moves you from a reactive "Oh no, we're almost out!” mindset to a proactive, strategic approach to managing one of your most important assets.

Start with the Right Data

Before you open a new Excel sheet, you need to gather some essential pieces of information. The quality of your forecast depends entirely on the quality of your data. Don't worry, you don't need a mountain of it, just a few key metrics for each product (or SKU) you want to track.

Essential Data Points:

• Historical Sales Data: This is the foundation of your forecast. You’ll need the number of units sold per day. Collect at least 30-60 days of data for a reliable baseline. If your business is seasonal, try to grab data from the same period last year. You can usually export this from your e-commerce platform like Shopify or your point-of-sale system.
• Current Inventory Level: How many units of the product do you have on hand right now? A physical count or a report from your inventory management system will work.
• Supplier Lead Time: How long does it take for your inventory to arrive after you place an order? Measure this in days, from the moment you hit "send" on the purchase order to the moment the stock is on your shelves and ready to sell. Be realistic here, include shipping and receiving time.

Key Formulas for Your Excel Forecast

To build our model, we’ll rely on a few simple, yet powerful, inventory management formulas. Let's get familiar with the terms before plugging them into spreadsheet cells.

1. Average Daily Sales

This is the starting point for understanding how quickly your products move. It's simply the average number of units you sell each day over a specific period.

Average Daily Sales = Total Units Sold / Number of Days

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

2. Safety Stock

Safety stock is the buffer inventory you keep on hand to protect against unexpected sales spikes or supplier delays. It's your insurance policy against stockouts. A simple way to calculate it is to decide how many "safety days" of inventory you want.

Safety Stock = Average Daily Sales * Number of Safety Days

For example, if you sell 10 units per day and want 7 days of safety stock, you’d aim to always have an extra 70 units on hand as a buffer.

3. Reorder Point

The reorder point is the magic number. It's the inventory level that triggers you to place a new order. The goal is to receive your new stock just as you're about to run out of your safety stock, ensuring a seamless supply.

Reorder Point = (Average Daily Sales * Lead Time) + Safety Stock

Think about it: the (Average Daily Sales * Lead Time) portion covers the stock you'll sell while waiting for your new order to arrive. The + Safety Stock adds your buffer back in, just in case.

Step-by-Step: Building Your Inventory Model in Excel

Alright, let's open Excel and put these concepts into practice. We'll create a simple inventory dashboard that tells you what needs attention at a glance.

Step 1: Set Up Your Spreadsheet Columns

Create a new sheet and set up your columns. Each row will represent a different product. For this tutorial, we'll keep the historical sales data on a separate tab, but you can structure it however you like.

Your main dashboard sheet should have these columns:

• A: SKU / Product Name
• B: Current Inventory (The only number you'll update manually)
• C: Average Daily Sales (This will be a formula)
• D: Lead Time (Days) (Enter your supplier lead time here)
• E: Safety Stock (Units) (This will be a formula)
• F: Reorder Point (Units) (This will be a formula)
• G: Status / Order Needed? (A helpful flag)

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Step 2: Calculate Average Daily Sales

First, get your historical daily sales data organized. On a separate tab named "Sales Data," list your SKUs in Column A. In the columns next to it (B, C, D, etc.), paste your last 30 days of unit sales for each SKU.

Now, back on your main dashboard sheet, in cell C2, we’ll use the AVERAGE function to calculate the average daily sales for your first product:

=AVERAGE('Sales Data'!B2:AE2)

This formula tells Excel to go to the "Sales Data" tab and calculate the average of all values from cell B2 to AE2 (representing a 30-day period). Drag this formula down Column C for all your products.

Step 3: Calculate Safety Stock

Let's decide on a reasonable buffer. For many businesses, 7-14 days of safety stock is a good starting point. You can create a cell somewhere in your sheet (e.g., J1) to enter your "Safety Days" so you can easily change it later.

In cell E2, enter the formula for Safety Stock. This will multiply your average daily sales by your desired number of safety days. Let's assume you've put 7 in cell J1.

=C2*$J$1

(Note the $ on $J$1. This locks the cell reference, so when you drag the formula down, it will always refer to J1 for the safety day value.)

Step 4: Calculate the Reorder Point

Now it's time for the most important metric. Using the formula we discussed, let’s calculate the trigger point for reordering. In cell F2, enter:

=(C2*D2)+E2

This formula multiplies the Average Daily Sales (C2) by the Lead Time (D2) and then adds the Safety Stock (E2) you just calculated. Drag this formula down Column F.

Step 5: Create a "Status" Column

Your dashboard now has a reorder point, but you still have to manually check if your current inventory is below it. Let's automate that with a simple IF statement. This will give us a clear "YES" or "NO" signal.

In cell G2, type the following formula:

=IF(B2<=F2, "ORDER NOW", "OK")

This tells Excel: If the value in Current Inventory (B2) is less than or equal to the Reorder Point (F2), then display "ORDER NOW." Otherwise, display "OK." Drag this down the column to see the status for every product.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 6: Use Conditional Formatting to Make It Visual

This final step makes your dashboard instantly scannable. We'll make any row that needs an order turn red.

1. Select all the data in your dashboard (from A2 to the last row in G).
2. Go to the Home tab on the Excel ribbon, click Conditional Formatting > New Rule.
3. Choose "Use a formula to determine which cells to format."
4. In the formula box, enter: =$G2="ORDER NOW"
5. Click the Format... button, go to the Fill tab, and choose a light red background color. Click OK.

Now, any product whose current inventory has fallen to or below its reorder point will automatically be highlighted. All you need to do is update your "Current Inventory" column, and your dashboard will instantly show you what needs your attention.

Beyond the Basics: Improving Your Forecast

This model is a fantastic starting point. As you get more comfortable, you can improve its accuracy by accounting for trends and seasonality.

• Moving Averages: Instead of a simple average, a "moving average" looks at sales data over a shorter, more recent period (like the last 7 days). This makes your forecast more responsive to recent sales trends.
• Seasonal Forecasting: If you sell products with predictable seasonal demand (like swimwear in summer or coats in winter), Excel's FORECAST.ETS function is a game-changer. It analyzes your historical data and automatically detects seasonal patterns to create a much more accurate forecast for future periods.
• Demand Planning: Don't forget about upcoming promotions, marketing campaigns, or industry trends. These events won't be in your historical data. You may need to manually adjust your forecast to account for an expected increase in demand from a big sale or ad campaign.

Final Thoughts

Building an effective inventory forecast in Excel doesn't require a degree in data science. By setting up a simple model with key metrics like average daily sales, safety stock, and reorder points, you can move away from reactive guesswork and take firm control over your stock levels, cash flow, and overall business health.

That said, a common challenge is the manual work of constantly exporting CSVs from Shopify, ads platforms, and other tools, then wrangling that data into your master spreadsheet. We built Graphed to eliminate that exact problem. By connecting your e-commerce store and marketing platforms directly, we can instantly turn your data into real-time dashboards. You can ask for what you need in plain English - like "create a report of my top 20 best-selling SKUs from Shopify and their current inventory on hand" - and get it in seconds, without ever touching a spreadsheet again.