A demand forecasting template puts each product's sales history and its forecast for the coming months side by side, so you can see whether the forecast is believable before it drives purchasing and production. This workbook does that at SKU level by month, rolls it up to category and division, and scores last month's statistical and consensus forecasts against actual sales.
What's inside the workbook
| Sheet | What it does |
|---|---|
| Forecast Analysis | A year-on-year view of monthly sales and forecast (2019 to 2022 in the sample) for the whole portfolio or one SKU, with a chart, so a forecast that breaks from the pattern stands out. |
| Sales+FC | The main input sheet: one row per SKU with portfolio, two category levels, description and opening stock, then monthly sales history followed by the forecast months. YTD, balance-of-year and last-three-month averages are calculated for you. |
| SKU Level Accuracy – Last Month | Last month's actual sales against the statistical and consensus forecast, in value (using each SKU's net realisable value), with accuracy and absolute deviation for each and a remark column. |
| Master | Settings: division name, the forecast months, the ABC class of each SKU and the category list used by the filters. |
Preview
| Total products | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep |
|---|---|---|---|---|---|---|---|---|---|
| 2019 | 1,443,510 | 1,765,726 | 1,259,874 | 1,645,986 | 1,502,303 | 1,366,451 | 1,370,032 | 1,231,156 | 1,949,190 |
| 2020 | 1,659,105 | 1,603,249 | 2,936,558 | 1,228,629 | 1,075,378 | 1,114,346 | 1,365,029 | 1,184,009 | 1,478,428 |
| 2021 | 1,100,978 | 1,146,630 | 1,662,446 | 1,443,891 | 1,292,191 | 1,271,194 | 1,442,400 | 2,173,807 | 1,786,984 |
| 2022 | 1,098,881 | 953,204 | 1,320,474 | 1,154,190 | 1,084,498 | 1,117,545 | 1,122,520 | 1,076,655 | 1,082,178 |
The Forecast Analysis sheet with the workbook's sample data: monthly units for all products, one row per year. Pick a single SKU in the filter to see its own history and forecast. Sample SKUs and figures are anonymised.
How to use the demand forecasting template
- Set up the Master sheet. Enter your division name, the forecast months you are planning and the ABC class of each SKU.
- Paste your sales history into Sales+FC. One row per SKU, one column per month, oldest first. Use sales quantities; if you had stockouts, note them, because sales in those months understate real demand.
- Add the forecast. Enter your statistical forecast for the coming months, then the consensus forecast after sales, marketing and supply have reviewed it.
- Check the year-on-year view. On Forecast Analysis, compare each forecast month with the same month in earlier years. A forecast that is far above or below every previous year needs a reason, such as a new customer, a promotion or a lost listing.
- Score last month. Paste last month's actuals into SKU Level Accuracy and read the accuracy of the statistical and consensus forecasts. If the consensus is regularly less accurate than the statistical forecast, the review meeting is adding error, not removing it.
How much sales history should you put in?
At least 24 months per SKU if you can. With two years of data every calendar month appears twice, which is the minimum for a method to separate seasonality from trend. With 12 months or less you can see the level and perhaps a trend, but seasonality has to come from your own judgement. Our article How much data is required for forecasting? shows what each length of history lets you see.
Keep discontinued SKUs out of the forecast rows, but keep their history if a successor product replaces them: the old product's pattern is the best starting point for the new one.
The accuracy formula the template uses
Accuracy on the SKU Level Accuracy sheet is calculated from absolute deviations, weighted by value, so a miss on a high-value SKU counts for more than the same miss on a cheap one:
This is the same as 100% minus WAPE (weighted absolute percentage error). Accuracy is floored at zero for individual SKUs, so one wild miss can't turn a total negative.
When a spreadsheet forecast stops being enough
This workbook works well for a few hundred SKUs and one forecast a month. It gets hard to keep up when you have thousands of SKU-location combinations, several channels, promotions to plan and a supply plan that has to follow every forecast change.
Planamind runs 12 forecasting models on every item, including Holt-Winters, Croston for intermittent demand, auto-ARIMA, Prophet-lite and an accuracy-weighted ensemble, and picks the best fit automatically. Accuracy (FA%, WAPE, MAPE and bias) is measured every cycle, each override is checked against the statistical baseline, and the forecast flows straight into the supply plan and the gross-profit view. See demand planning software.
Frequently asked questions
Is the demand forecasting template free?
Yes. Fill in the short form and the Excel file downloads straight away. There is no trial and nothing to install.
What is the difference between a demand forecast and a demand plan?
The forecast is the unconstrained estimate of what customers will buy. The demand plan is the forecast after the business has reviewed it and added known events such as promotions, new customers or lost listings. In this template the statistical forecast is the first and the consensus forecast is the second.
Does the template calculate the forecast for me?
No. It holds and analyses the forecast. You enter the statistical forecast from your own method (for example a moving average or exponential smoothing) and the consensus forecast after review. The workbook then summarises, compares and scores them.
Can I use it for weekly forecasts?
The workbook is laid out by month. You can relabel the columns for weeks, but the year-on-year view and the averages assume monthly buckets.
How many SKUs can it handle?
The sample holds about 460 SKUs. Excel copes with a few thousand rows, but the workbook slows down as formulas grow, which is usually the point to move to planning software.