Excel Formula to Calculate Days of Supply From Inventory and Sales Velocity

Managing stock levels without risking costly stockouts or excessive holding fees is a constant struggle for inventory planners. While relying on traditional safety stock formulas or manual spreadsheets offers a baseline, these static replenishment methods often fail to reflect active market demand. Implementing a dynamic calculation grants you immediate foresight into your exact inventory runway.

Stipulation: This approach assumes a relatively stable daily sales velocity, excluding sudden seasonal anomalies. For example, determining your precise "Days of Supply" ensures timely reordering. Below, we outline the exact Excel formula to divide inventory count by velocity, streamlining your forecasting workflow.

Introduction to Inventory Velocity Metrics

In supply chain management and e-commerce, keeping the right amount of stock is a delicate balancing act. If you hold too much inventory, you tie up valuable capital and risk stock obsolescence. If you hold too little, you face stockouts, backorders, and disappointed customers. To strike the perfect balance, inventory planners rely on a key metric: Days of Inventory (DOI), also known as Days of Supply.

This metric tells you exactly how long your current inventory will last based on your current rate of sales. To calculate this in Excel, you must divide your current inventory count by your Daily Sales Velocity (DSV). While this sounds like simple division, building a dynamic, error-free Excel model requires understanding how to calculate daily sales velocity, how to write the division formula, and how to handle common spreadsheet errors like division by zero.

Understanding the Variables

Before jumping into the Excel syntax, let's define the two crucial variables used in this calculation:

1. Inventory Count

This is the physical quantity of a specific Stock Keeping Unit (SKU) currently available in your warehouse or fulfillment center. It should only include sellable inventory (excluding damaged or reserved items).

2. Daily Sales Velocity (DSV)

Daily Sales Velocity is the average number of units you sell per day. Because sales fluctuate day-to-day, this is typically calculated over a specific historical window, such as the last 7, 30, or 90 days. The formula for Daily Sales Velocity is:

Daily Sales Velocity = Total Units Sold in Period / Number of Days in Period

For example, if you sold 300 units of a product over the last 30 days, your Daily Sales Velocity is 10 units per day (300 / 30 = 10).

The Basic Excel Formula

Once you have both variables, the basic formula to calculate Days of Inventory in Excel is straightforward division. If your inventory count is in cell B2 and your Daily Sales Velocity is in cell C2, the formula in cell D2 is:

=B2/C2

Let's look at a basic table showing how this executes in a standard spreadsheet:

Handling Errors and Edge Cases in Excel

In a real-world inventory sheet, your data won't always be perfect. You will encounter products with zero sales velocity (new products or discontinued items) or temporary out-of-stock situations. If you apply the basic =B2/C2 formula to these rows, Excel will return errors or confusing decimals. Here is how to make your formula robust and professional.

1. Preventing the #DIV/0! Error

If a product has a Daily Sales Velocity of 0, Excel will attempt to divide your inventory by zero, resulting in a messy #DIV/0! error. This ruins the visual appeal of your dashboard and can disrupt other formulas downstream.

To prevent this, wrap your division formula inside an IFERROR function. The IFERROR function lets you specify what Excel should display if an error occurs:

=IFERROR(B2/C2, 0)

Alternatively, you can return a text string like "No Sales" or "N/A" to alert your procurement team:

=IFERROR(B2/C2, "No Sales")

2. Handling Zero Velocity Cleanly with IF

If you prefer not to use IFERROR, you can use a logical IF statement to check if the sales velocity is greater than zero before performing the division:

=IF(C2>0, B2/C2, 0)

This formula checks if cell C2 is greater than 0. If it is, Excel divides B2 by C2. If C2 is 0 or empty, Excel simply returns 0.

3. Rounding Your Days of Supply

Days of stock are rarely clean whole numbers. For instance, 100 units of stock divided by a velocity of 3 units per day equals 33.333... days of supply. In supply chain planning, it is safest to round down to avoid overestimating your buffer, or round to one decimal place for clarity.

To round your results, combine your division formula with the ROUND, ROUNDDOWN, or INT functions:

• Standard Rounding (1 decimal place): =ROUND(B2/C2, 1)
• Conservative Rounding (Always round down to the nearest whole day): =ROUNDDOWN(B2/C2, 0) or =INT(B2/C2)
• Aggressive Rounding (Round up to ensure you don't run out): =ROUNDUP(B2/C2, 0)

Building a Complete Dynamic Inventory Spreadsheet

Let's look at how to build a dynamic Excel system that calculates daily velocity from historical sales data and then calculates the days of supply in one clean sheet. This is a common setup used by operational managers.

Assume your sheet has the following columns:

• Column A: SKU / Product ID
• Column B: Current Inventory Count
• Column C: Units Sold (Last 30 Days)
• Column D: Daily Sales Velocity (Calculated: 30-Day Sales / 30)
• Column E: Days of Supply (Calculated & Rounded)
• Column F: Status / Action Required (Dynamic Alert)

Here are the step-by-step formulas to apply in Row 2:

Step 1: Calculate Daily Sales Velocity

In cell D2, write the formula to divide the 30-day sales by 30 days:

=C2/30

Step 2: Calculate Days of Supply with Error Handling

In cell E2, divide the Inventory Count (B2) by the Daily Sales Velocity (D2), wrapping it in an IFERROR and rounding it to a whole number:

=IFERROR(ROUNDDOWN(B2/D2, 0), 0)

Step 3: Create an Automated Action Alert

In cell F2, you can use a nested IF statement to flags items that need immediate reordering. For example, if your lead time to get stock from your manufacturer is 14 days, you want to flag any item with fewer than 15 days of inventory as "REORDER NOW":

=IF(E2<=0, "Out of Stock", IF(E2<=15, "REORDER NOW", "Healthy Stock"))

Example Table in Action

When you apply these dynamic formulas down your entire sheet, your inventory replenishment dashboard will look like this:

Pro-Tips for Advanced Inventory Planners

To take your inventory analysis to the next level, consider implementing these advanced techniques:

• Use Conditional Formatting: To visually quickly identify SKUs that are running low, apply conditional formatting to your "Days of Supply" column. Set a rule that highlights cells in light red if their value is less than or equal to 14, and light green if their value is greater than 30.
• Account for Seasonality: If your product sales are highly seasonal (e.g., summer gear or holiday gifts), a 30-day lookback window might be misleading. Instead, use a formula that calculates daily velocity based on the same month from the previous year.
• Integrate Lead Time: Do not just look at when you will run out of inventory; calculate your reorder point. Your Reorder Point = (Daily Sales Velocity × Lead Time in Days) + Safety Stock.

Conclusion

Dividing inventory count by daily sales velocity is one of the most powerful diagnostic calculations an inventory manager can set up in Excel. By tracking this metric dynamically, you move from reactive inventory management to proactive forecasting. Using robust formulas like =IFERROR(B2/C2, 0) ensures your tracking sheets remain clean, readable, and free of division errors, giving you the clean insights you need to keep your business running smoothly.