Your online store sold 120 units in January, 150 in February, and 180 in March.
Now, you need to estimate April’s demand before placing another purchase order.
Should you prepare 150 units, 180 units, or more?
Guessing might work occasionally. However, consistent inventory planning requires a more reliable approach.
An inventory forecasting formula helps businesses estimate future product demand using historical sales and measurable patterns.
Different formulas produce different results. Therefore, understanding how each method works is essential before choosing one.
This guide explains five practical forecasting formulas, provides calculation examples, and shows how to use them in Excel.
What Is an Inventory Forecasting Formula?
An inventory forecasting formula is a mathematical method used to estimate future demand.
Businesses use these estimates to support purchasing, stock planning, and replenishment decisions.
For example, an ecommerce seller may forecast monthly demand for cosmetic jars based on recent sales.
However, forecasting demand is different from calculating purchase quantities.
A demand forecast estimates how many units customers may buy.
Meanwhile, a purchasing plan also considers available stock, incoming deliveries, and required inventory buffers.
1. Simple Average Forecasting Formula
The simple average method calculates expected demand using historical sales across selected periods.
Formula:
Forecast Demand = Total Historical Demand ÷ Number of Periods
Calculation Example
Suppose a product recorded these monthly sales:
| Month | Units Sold |
|---|---|
| January | 100 |
| February | 120 |
| March | 140 |
The calculation is:
(100 + 120 + 140) ÷ 3 = 120 units
Therefore, the forecast for April is 120 units.
This method is easy to calculate and understand.
However, it gives equal importance to every period, even when recent demand is changing.
Best for: Products with relatively stable sales patterns.
2. Moving Average Forecasting Formula
A moving average uses a fixed number of recent periods to estimate future demand.
Unlike an average based on all available history, it moves forward as new sales data becomes available.
Formula:
Moving Average Forecast = Sum of Demand in the Latest N Periods ÷ N
Calculation Example
Consider these monthly sales:
| Month | Units Sold |
|---|---|
| January | 100 |
| February | 120 |
| March | 140 |
| April | 160 |
Using a three-month moving average, the forecast for May is:
(120 + 140 + 160) ÷ 3 = 140 units
January is excluded because the calculation uses only the latest three months.
As a result, the forecast reflects more recent sales information.
However, moving averages may still lag behind rapidly increasing demand.
Best for: Products with moderate fluctuations and sufficient sales history.
3. Weighted Moving Average Formula
A weighted moving average assigns different importance to historical periods.
For example, recent sales may receive higher weights than older sales.
Formula:
Weighted Forecast = (D₁ × W₁) + (D₂ × W₂) + … + (Dₙ × Wₙ)
Where:
- D represents demand in each period.
- W represents the assigned weight.
- All weights must total 100%, or 1.
Calculation Example
Suppose the previous three months produced these results:
| Month | Sales | Weight |
|---|---|---|
| January | 120 | 20% |
| February | 150 | 30% |
| March | 180 | 50% |
Calculate:
(120 × 0.20) + (150 × 0.30) + (180 × 0.50)
= 24 + 45 + 90
= 159 units
Therefore, the next month’s forecast is 159 units.
Because March receives the highest weight, its sales have the greatest influence.
However, the weights should be tested against actual results rather than selected arbitrarily.
Best for: Products where recent sales better reflect current demand.
4. Exponential Smoothing Formula
Exponential smoothing updates a previous forecast using the latest actual demand.
The method assigns a smoothing factor, called alpha (α), between 0 and 1.
Formula:
New Forecast = α × Actual Demand + (1 − α) × Previous Forecast
A higher alpha makes the forecast respond more quickly to recent changes.
Meanwhile, a lower alpha produces smoother results.
Calculation Example
Suppose:
- Previous forecast: 150 units
- Actual demand: 180 units
- Alpha: 0.30
Calculate:
(0.30 × 180) + (0.70 × 150)
= 54 + 105
= 159 units
The updated forecast is 159 units.
This example uses simple exponential smoothing, which does not explicitly model trends or seasonality.
Best for: Products with relatively stable demand that still experience short-term fluctuations.
5. Seasonal Forecasting Formula
Seasonal forecasting adjusts expected demand based on recurring patterns.
For example, gift packaging may experience stronger demand before major holidays.
A basic seasonal adjustment uses a seasonal index.
Formula:
Seasonal Forecast = Baseline Forecast × Seasonal Index
Calculation Example
Suppose a business expects baseline monthly demand of 200 units.
Historical analysis suggests that demand during a particular holiday month is typically 30% higher than the baseline.
The seasonal index is 1.30.
Therefore:
200 × 1.30 = 260 units
The seasonal forecast is 260 units.
However, seasonal indexes should be supported by sufficient historical data.
One unusually successful promotional period doesn’t necessarily indicate a recurring seasonal pattern.
Best for: Products with consistent seasonal demand changes.
Inventory Forecasting Formula Comparison
Each method has different strengths.
| Forecasting Method | Complexity | Main Advantage | Main Limitation |
|---|---|---|---|
| Simple Average | Low | Easy to calculate | Slow to reflect changes |
| Moving Average | Low | Uses recent periods | Can lag behind trends |
| Weighted Moving Average | Medium | Emphasizes selected periods | Requires appropriate weights |
| Exponential Smoothing | Medium | Updates forecasts efficiently | Basic version ignores trend and seasonality |
| Seasonal Forecasting | Medium | Accounts for recurring demand patterns | Requires reliable seasonal data |
For small businesses, moving averages often provide a practical starting point.
However, the most appropriate method depends on demand behavior and data quality.
How to Calculate Inventory Forecasts in Excel
Excel can simplify forecasting calculations.
Suppose monthly sales are entered in cells B2 through B4.
Simple Average
=AVERAGE(B2:B4)This calculates the average demand across the selected cells.
Weighted Moving Average
Assume sales are in B2 and corresponding weights are in C2.
=SUMPRODUCT(B2:B4,C2:C4)Make sure the weights total 100%.
Exponential Smoothing
Suppose:
- B4 contains actual demand.
- C4 contains the previous forecast.
- E2 contains alpha.
Use:
=$E$2*B4+(1-$E$2)*C4These formulas can support basic forecasting without specialized inventory software.
However, maintain consistent reporting periods and verify your source data.
How to Measure Forecast Accuracy
A forecast should be evaluated against actual demand.
One common metric is Mean Absolute Percentage Error (MAPE).
MAPE = Average of (|Actual − Forecast| ÷ Actual) × 100
Suppose your forecast is 180 units, while actual demand reaches 200.
The absolute percentage error is:
|200 − 180| ÷ 200 × 100 = 10%
For multiple periods, calculate each period’s absolute percentage error and average the results.
However, MAPE is undefined when actual demand is zero and can be misleading for very low-volume products.
In those situations, Mean Absolute Error (MAE) may provide a useful alternative.
How to Turn a Forecast Into a Purchase Plan
Forecasting demand is only one part of inventory planning.
Before ordering, consider existing inventory and incoming deliveries.
A simplified calculation is:
Suggested Purchase Quantity = Forecast Demand + Target Ending Stock − Available Inventory − Incoming Stock
For example:
| Variable | Units |
|---|---|
| Forecast Demand | 500 |
| Target Ending Stock | 100 |
| Available Inventory | 200 |
| Incoming Stock | 50 |
The calculation is:
500 + 100 − 200 − 50 = 350 units
Therefore, the suggested purchase quantity is 350 units.
This simplified model assumes the quantities refer to the same planning horizon and that incoming stock arrives in time.
Businesses should also consider supplier minimum orders, delivery schedules, and existing commitments.
Common Inventory Forecasting Mistakes
One common mistake is using sales history without checking stock availability.
For example, recorded sales may appear low because a product was out of stock.
Consequently, the forecast may underestimate actual demand.
Another mistake is choosing a formula without considering product behavior.
A simple average may perform poorly when demand has a strong upward trend.
Businesses should also avoid confusing forecast accuracy with profitability.
A highly accurate demand forecast doesn’t automatically guarantee efficient purchasing.
Finally, review forecasts regularly rather than treating calculations as permanent decisions.
Final Thoughts
Inventory forecasting formulas help businesses replace guesswork with structured demand estimates.
Simple averages and moving averages offer accessible starting points. Meanwhile, weighted averages, exponential smoothing, and seasonal adjustments provide additional flexibility.
However, no single formula works best for every product.
Start with reliable sales data, compare forecast results with actual demand, and adjust your approach when necessary.
The best forecasting formula isn’t the most complicated one. It’s the method that consistently helps your business make better inventory decisions.