Need more freedom with data transformation and automation? Check out Mipler Data Workflow!

Shopify Demand Planning & Inventory Forecasting: How to Do It Successfully

· 5 min read
Oleksandr Drok

Accurate forecasting of necessary inventory and demand planning is a constant operational activity for any store that operates with its own warehouses. Precise forecasting helps avoid two critical situations:

  • Stockouts of essential items;
  • Unjustifiably high overstock of goods in warehouses

While inventory forecasting is just a projection, with the right approach, it allows maintaining optimal stock levels consistently. Unlike other reports (such as inventory value reports), inventory forecasting (also known as demand planning) poses questions for all three time periods:

  • Past: How many products were sold and how quickly?
  • Current: What is the actual stock status now?
  • Future: How many products do we need, when, and how quickly?

It is worth noting separately that there are several different strategies/approaches to inventory planning. No strategy is exclusive, and they can be combined, adjusted throughout the year, or applied differently for various product groups.

  • Continuous Availability Strategy:

    Maintaining a large quantity of inventory to cover both current and potential seasonal fluctuations.

  • Minimum Inventory Level Strategy:

    Keeping the minimum possible quantity of goods in stock. Requires reordering even with a slight increase in demand.

  • Demand-Oriented Planning:

    Planning the optimal inventory for each item based on current demand.

These are just a few of the strategies commonly used in e-commerce.

Questions That Inventory Forecasting Can Answer

Since inventory forecasting is a complex topic that involves many processes, typically, a separate report is used for each question and answer due to its multifaceted nature. Let's explore some of the most relevant problem-solution pairs:

  • Sales Velocity: How quickly inventory is utilized (sales velocity).
  • Minimizing Out-of-Stock Occurrences: How to minimize the chances of product unavailability in the warehouse.
  • Replenishment Timing: When to replenish stocks.
  • Identifying Dead Inventory: Which inventory is non-selling (i.e., has been inactive for a long time considering the current stock).
  • Managing Overstock: Which inventory is excessive.
  • Turnover Ratio: What is the turnover ratio for stock.
  • Pricing and Discount Policy Review: Which products require a review of pricing or discount policies.

While this list is incomplete, it provides a general overview of the issues that can be addressed through inventory planning.

Inventory Forecasting

In general, to forecast how much inventory we need for the next week or month, we must understand how much product we plan to sell during that period – this is the sales velocity (also known as the average daily sales volume).

Once we have calculated the sales velocity, we can easily determine the quantity of products we need for the next 14 days. Additionally, since products are ordered from suppliers, we also need to know when to place an order with the supplier – the reorder date. Alternatively, we can determine the reorder point (ROP), which is the minimum quantity in stock at which we should place an order.

Furthermore, for more accurate forecasting, external factors such as delivery lead time should be considered. Lead time is the number of days needed to transport goods from the supplier's warehouse to our own.

As a result, for a simplified (linear) forecast, we just need three values:

  1. Sales Velocity
  2. Actual Quantity
  3. Lead Time

Using these values, numerous derivative metrics can be calculated and utilized in the forecasting process.

Key Terms, Metrics and Calculations

The provided graph illustrates a linear model for maintaining optimal inventory levels based on three key metrics (sales velocity, lead time, and stock quantity).

inventory planning
  • Sales Velocity

    Sales velocity represents the rate of product sales within a fixed time period, typically calculated for one day.

    Simplified formula over the last 30 days:

    Sales Velocity = Sold Quantity ÷ 30

    This metric indicates the average number of units sold per day. With this information, one can calculate the expected sales for the next 14/30/90 days.

  • Lead Time

    Lead time is the duration required to receive products in your warehouse from the moment the order is placed, encompassing all necessary steps such as placing an order with the supplier, signing, delivery, etc.

    Lead time is generally a fixed value for each supplier.

  • Lead Time Quantity

    Lead time quantity determines how many products will be sold during the lead time (forecasted sales).

    Lead Time QTY = Lead Time × Sales Velocity
  • Service Level

    The expected probability of avoiding stock-outs during the next replenishment cycle, usually ranging from 70-90%.

    Excessively high service levels lead to unnecessary increases in inventory, while too low levels increase the risk of running out of stock.

  • Safety Stock

    Extra inventory quantity (buffer or reserve) to prevent stock-outs in case of unforeseen issues.

    Safety Stock = Sales Velocity × Lead Time × Service Level

    Safety stock can be manually set for individual items, affecting the calculation of quantity and reorder time.

  • Reorder Point (ROP)

    The stock quantity that triggers replenishment.

    Reorder Point = Safety Stock + Lead Time QTY
  • Stock QTY

    Actual stock quantity (overall or per warehouse inventory planning)

  • Stock Covered Time

    How many days the current stock will cover sales.

    Stock Covered Time = Stock QTY ÷ Sales Velocity
  • Stockout QTY

    An indicator of future inventory shortage. If positive, it signifies a deficit, indicating that existing stock will not be sufficient in a defined period

    Stockout QTY = Lead Time QTY - Stock QTY
  • Replenishment Quantity

    The minimally optimal quantity of products to order to avoid stockouts.

    Replenishment QTY = Reorder Point QTY
  • Replenishment Time or Time To Reorder

    When (how many days) products should be reordered.

    Replenishment Time = (Stock QTY - Reorder Point QTY) ÷ Sales Velocity

Key takeaways

  • Forecasting requires a history. On seasonal markets, improving forecasting accuracy necessitates at least one year of sales history. In other stable (linear) markets, a sales history of the past 30-90 days is typically sufficient.
  • Modern reporting and planning platforms empower inventory management akin to an expert.
  • While numerous non-programmable factors may impact planning accuracy, having a planning system is foundational and imperative. Remaining informed about the current market situation and adjusting forecasts and plans accordingly is essential.

Examples of inventory planning reports for Shopify

Below we collected different variations of inventory forecast and demand reports that can be with our app.

Additionally, you can adjust built-in inventory reports or create new reports.

ProductsNameProduct VariantsNameInventory LevelsTotal AvailableInventory PlannerSales VelocityInventory PlannerStock Covered / daysInventory PlannerForecast QTYInventory PlannerReplenishment QTYInventory PlannerReplenishment time / daysInventory PlannerStockout QTYInventory PlannerSafety QTYInventory PlannerReorder Point QTY
Cassius Sparring TankBlue / XS150.5328121500315
Cassius Sparring TankBlue / XL30.3010790429
Cassius Sparring TankBlue / L40.2317570127
report generated with

Inventory Planning in Google Sheets

Inventory Planning in Google Spreadsheets
Inventory Planning in Google Spreadsheets

You can even plan inventory in Google Sheets, provided you have a mechanism for uploading the current product list along with their quantities on hand. All other parameters can be easily calculated using the formulas provided above. You can find a free template for Google Sheets.

What's happening in the e-commerce world?

Recently Added Articles

Blog →
Shopify Inventory Reports: all you need to know Feb 06, 2024

When dealing with Shopify inventory management on your own, reports are the best help you can get. Effective planning and tracking, especially for larger shops, is no work for pen and paper. Today, we offer you a comprehensive article on inve

How to calculate the reorder point in Shopify? Jan 26, 2024

E-commerce heavily relies on proper inventory management. Failing to account for resupply shipping delays, increased demand, or low stock can halt trading and hinder your revenue. On top of that, many Shopify merchants have a few warehouses f

Sales velocity in Shopify: All you need to know to succeed Jan 19, 2024

Inventory management is an essential part of running a store. Knowing how fast your stock gets sold out will help you plan replenishment and assess the effectiveness of your business strategy. Today, we will discuss sales velocity in Shopify

Get rid of boring and tedious tasks

Gain full access to the platform with a 7-day free trial.

© 2024 Mipler. All rights reserved.
Choose a Platform