Demand Forecasting in Excel: Everything You Need Including Formulas
Many e-commerce businesses still rely on spreadsheets to manage their operations. While advanced software and AI tools are on the rise, Excel remains one of the most widely used platforms for demand forecasting. With the right formulas, Excel can provide meaningful insights into future sales and inventory needs.
In this guide, we’ll show you how to forecast demand in Excel step by step, using real formulas, examples, and charts. You’ll also see the limitations of Excel and when it makes sense to move beyond spreadsheets to automation.
Why Use Excel for Demand Forecasting?
Excel is often the first step for merchants building forecasting processes because it’s:
Accessible → nearly everyone knows how to use it.
Flexible → customizable models for any business.
Low cost → no software investment needed.
But it comes with challenges:
Manual updates → time-consuming and error-prone.
Doesn’t scale → struggles with hundreds of SKUs.
No real-time integration → can’t sync directly with Shopify or Amazon.
📌 For a broader overview of planning and forecasting, see our Inventory Forecasting and Planning guide.
Preparing Your Data in Excel
Before you start forecasting, you need clean, structured data. Set up your spreadsheet with columns like:
Date (daily, weekly, or monthly sales).
SKU/Product Name.
Units Sold.
Revenue (optional).
👉 Pro Tip: Use at least 12–24 months of sales data if available. This makes seasonality easier to detect.
Simple Demand Forecasting Methods in Excel
There are three practical approaches you can apply in Excel:
1. Moving Average Forecast
Moving averages smooth out fluctuations and give you a baseline forecast.
Formula example:
=AVERAGE(B2:B13)
This calculates the average sales across the last 12 months (column B).
Pros: simple and easy to apply.
Cons: ignores seasonality and long-term trends.
2. Exponential Smoothing (ETS)
Excel has a built-in FORECAST.ETS function that automatically accounts for seasonality and trends.
Formula example:
=FORECAST.ETS(target_date, values, timeline)
target_date= the future date you want to forecast.values= the range of historical sales data.timeline= the dates corresponding to those values.
This is powerful for e-commerce businesses with seasonal peaks (e.g., Black Friday or Christmas).
3. Linear Regression (Trend Forecasting)
Excel also includes linear regression forecasting with the FORECAST.LINEAR function.
Formula example:
=FORECAST.LINEAR(x, known_y’s, known_x’s)
x= the future time period you want to forecast.known_y’s= historical sales values.known_x’s= corresponding dates/time periods.
This works well for products with steady upward or downward demand trends.
📌 For broader forecasting methods beyond Excel, see E-commerce Forecasting.
Step-by-Step Example in Excel
Let’s say you’re forecasting monthly sales for “Hoodie A” with 12 months of data:
Month | Units Sold |
|---|---|
Jan | 120 |
Feb | 130 |
Mar | 125 |
… | … |
Dec | 200 |
Step 1: Moving Average
Use =AVERAGE(B2:B13) to calculate the 12-month moving average.
Step 2: ETS Forecast
Use =FORECAST.ETS("2025-01-01", B2:B13, A2:A13) to predict January 2025 demand.
Step 3: Linear Regression
Use =FORECAST.LINEAR(13, B2:B13, A2:A13) to project sales for the 13th period (month 13).
Step 4: Chart the Results
Highlight your data and forecasts → Insert → Line Chart.
You’ll now see Actual vs. Forecast demand trends.
Adding Safety Stock in Excel
Forecasting is only part of the equation — you also need safety stock to buffer against uncertainty.
Safety Stock Formula:
Safety Stock = (Max Daily Usage × Max Lead Time) − (Avg Daily Usage × Avg Lead Time)
Once you’ve calculated safety stock, add it to your forecasted demand to calculate the Reorder Point (ROP):
ROP = (Average Daily Demand × Lead Time) + Safety Stock
📌 Learn more in:
Limitations of Excel Forecasting
While Excel is a good starting point, it has serious limitations:
Doesn’t scale → difficult to manage hundreds or thousands of SKUs.
No automation → forecasts must be updated manually.
Lacks accuracy compared to AI forecasting.
Disconnected from e-commerce platforms (Shopify, Amazon, etc.).
📌 For more advanced strategies, see Inventory Forecasting.
Beyond Excel — When to Upgrade
Excel works well for early-stage businesses, but as complexity grows, forecasting needs automation.
AI-powered forecasting tools like Verve AI:
Integrate directly with Shopify.
Provide SKU-level forecasts.
Adjust automatically for seasonality, promotions, and lead times.
Save hours of manual work.
Instead of spending days updating spreadsheets, Verve AI generates forecasts in minutes.
Conclusion
Excel is an excellent starting point for demand forecasting. By using methods like:
Moving Averages → to smooth data.
Exponential Smoothing (ETS) → to adjust for seasonality.
Linear Regression → to project trends.
… you can build a reliable forecasting model directly in spreadsheets.
But as your store grows, Excel quickly becomes a bottleneck. That’s when upgrading to AI-powered tools like Verve AI ensures accuracy, automation, and scalability.
👉 Try Verve AI Forecasting today and let AI take your forecasting beyond spreadsheets.