Function Problems

How to Forecast Demand for Multiple SKUs and Locations in Excel

Maira Mehtab Sep 28, 2026 869 views

Question details

The user needs to calculate separate seasonal forecasts and reorder points for multiple items based on SKU-specific and location-specific historical data, without manually processing each item.

Product: Excel
Device & OS: not provided
Scenario: Managing multi-location inventory and attempting to automate demand predictions using historical posting dates and quantities.
Observed behavior: The goal is to design a dynamic formula that automatically filters historical data by item number and location code, applying forecasting logic only to the relevant subset of data.

Before you start

Ensure your master historical data is organized in a clear, tabular format with separate columns for Posting Date, Item Number (SKU), Location Code, and Historical Quantity. Consistent time intervals between data points are required for accurate seasonal forecasting.

Solution 1Recommended

Calculate Seasonal Forecasts using FORECAST.ETS and FILTER

Use a combination of advanced dynamic arrays and forecasting functions to isolate data by SKU and location automatically.

To forecast demand for individual SKUs at specific locations without manual sorting, you can combine the FORECAST.ETS function with the FILTER function.

The FILTER function will isolate the historical quantities and dates for the specific SKU and location based on your criteria. Then, FORECAST.ETS will process that filtered data to predict future demand, automatically adjusting for seasonality.

Set up your forecast summary table

Create a new worksheet or table area to display your results. Include columns for the Target Date (when you want the forecast for), Target SKU, and Target Location Code.

Apply the FILTER and FORECAST.ETS combination formula

Select the cell where you want the forecast to appear. Enter the formula combining these functions. For example: =FORECAST.ETS(Target_Date, FILTER(Quantity_Column, (SKU_Column=Target_SKU)*(Location_Column=Target_Location)), FILTER(Date_Column, (SKU_Column=Target_SKU)*(Location_Column=Target_Location))).

Add IF logic to handle insufficient data

Because new SKUs or newly stocked locations might lack enough historical data to generate a forecast, wrap your formula in an IFERROR or IF function. Modify it to: =IFERROR([Your_Formula_Here], "Insufficient Data").

Copy the formula to other SKUs

Press Enter to apply the calculation. Use the fill handle at the bottom-right corner of the cell to drag the formula down your summary table, calculating forecasts for all other SKUs and locations instantly.

Function Availability: The FORECAST.ETS function requires at least two complete cycles of historical data to detect seasonality properly. The FILTER function requires a version of Excel or WPS Spreadsheets that supports dynamic array formulas.

Advanced Data Forecasting

Automate SKU Demand Forecasting with WPS Spreadsheets

WPS Spreadsheets provides powerful data analysis capabilities, including dynamic array functions like FILTER and statistical tools like FORECAST.ETS, empowering you to seamlessly manage inventory predictions across multiple locations.

1. Open your inventory master data: Launch WPS Spreadsheets and open your .xlsx file containing the historical SKU and location transaction data.
2. Insert the dynamic forecasting formula: Select the target cell in your summary sheet. Input the FORECAST.ETS and FILTER combination to accurately calculate demand based on the specific SKU and Location criteria.
3. Drag to apply to all items: Use the cell's fill handle to drag the formula down the column, instantly calculating the seasonal forecast and reorder points for your entire inventory.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Supports advanced dynamic array formulas for complex, multi-criteria data filtering.Includes built-in forecasting tools specifically designed for inventory management.Lightweight, fast, and features a familiar tabbed interface for a smooth transition.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my FORECAST.ETS formula returning a #NUM! or #VALUE! error?

The FORECAST.ETS function requires a consistent timeline of historical data. If dates are duplicated, missing, irregular for a specific SKU, or if there are fewer than two full seasonal cycles in the filtered data, the formula will return an error.

Can I forecast demand without using the FILTER function?

Yes, but it requires significantly more manual effort. You would need to sort and separate your master data by SKU and location first, and then point the FORECAST.ETS function to those specific, static data ranges instead of dynamically extracting them.

How do I calculate reorder points for SKUs with new or no historical data?

For brand new SKUs, standard statistical forecasting formulas cannot be used due to a lack of data points. You should wrap your formula in an IFERROR function to output a default minimum reorder point or a text string like 'Manual Review' when historical data is insufficient.

Are these forecasting and array formulas compatible between Excel and WPS Office?

Yes, modern functions like FORECAST.ETS, FILTER, and IFERROR are fully supported in WPS Spreadsheets, ensuring your inventory forecasting files work flawlessly across both platforms.

Excel & Data Problems