Demand Forecasting in Excel: A Step-by-Step Guide

Demand forecasting in Excel is practical for many small and mid-sized companies, and with discipline it can produce forecasts as good as those from far more expensive tools. The method does not need to be complicated. A clean table of history, a few well-chosen formulas, a measure of error and a routine for updating it each month will take most businesses a long way. This guide walks through the process step by step, using a 12-month example whose arithmetic is checked: preparing the data, building a moving average, applying exponential smoothing, fitting a trend, adding seasonality, measuring accuracy and bias, and making the workbook robust and reusable. It is part of demand planning and inventory optimization, follows demand planning software, leads to planning automation and demand forecasting in Python, and builds on statistical forecasting in Excel with FORECAST.ETS. The financial context is in cash flow management.

Step 1: prepare the data

Start with one table: one row per period and item, with columns for the period, item, units sold and any notes. Use consistent periods, such as calendar months or weeks. Fill in missing periods with zero only if there truly were no sales. If an item was out of stock, mark the period, because the sales understate demand. Remove or flag one-off events such as a large single order or a promotion. Keep the raw data on one sheet and calculations on another, so that you can refresh the data without breaking the formulas. Use Excel Tables (Insert, Table) so that formulas expand as data is added. See how to build a clean data foundation for planning.

The example data

Use 12 months of demand for one item, in units: 100, 110, 105, 120, 115, 125, 130, 128, 135, 140, 138, 150 (illustrative). The series rises steadily with some noise. Total demand is 1,496 units, an average of 124.7 a month. Check: the sum is 100 + 110 + 105 + 120 + 115 + 125 + 130 + 128 + 135 + 140 + 138 + 150 = 1,496.

Step 2: build a moving average

A three-month moving average forecasts each month as the average of the previous three. For month 13, the average of months 10 to 12 is (140 + 138 + 150) / 3 = 142.7. In Excel, with the data in B2:B13, the formula =AVERAGE(B11:B13) gives the forecast for the next period, and copying a similar formula down gives a forecast for each earlier month for testing. The moving average is simple and robust, but it lags when demand is trending, because it averages in older, lower values. In the example, the forecast of 142.7 is below the latest value of 150.

Step 3: exponential smoothing

Exponential smoothing gives more weight to recent data. The forecast for the next period equals alpha times the latest actual plus one minus alpha times the previous forecast. With alpha of 0.3 and the first forecast set to the first actual of 100, the forecasts for months 2 to 13 are 100.0, 103.0, 103.6, 108.5, 110.5, 114.8, 119.4, 122.0, 125.9, 130.1, 132.5 and 137.7. Check the first steps: for month 3, 0.3 × 110 + 0.7 × 100 = 33 + 70 = 103.0; for month 4, 0.3 × 105 + 0.7 × 103.0 = 31.5 + 72.1 = 103.6. In Excel, the formula for the forecast in row 4 is =$E$1*B3+(1-$E$1)*C3, with alpha stored in cell E1 and the previous forecast in C3. A higher alpha reacts faster but follows noise, and a lower alpha is smoother but slower. Because this series trends upward, simple exponential smoothing lags and its forecast of 137.7 for month 13 is below the latest actual of 150.

Step 4: fit a trend

When demand trends, a linear fit can help. Excel's FORECAST.LINEAR function (or the older FORECAST) fits a straight line through the history. For the example, =FORECAST.LINEAR(13, B2:B13, A2:A13), with months 1 to 12 in A2:A13, returns 151.2. The fitted line has a slope of about 4.1 units per month and an intercept of 98.2, so month 13 is 98.2 + 4.1 × 13 = 151.5 with rounded inputs, or 151.2 using the unrounded values. A trend line should be used with care. It assumes the pattern continues, which is less safe over a long horizon, and it can be thrown off by outliers. Check it against what you know about the market.

Step 5: add seasonality

If demand has a seasonal pattern, divide the history by a seasonal index before forecasting, then multiply the forecast by the index for the target month. To calculate indices, use at least two or three years of data, calculate the average for each month, and divide by the overall monthly average. Excel also offers FORECAST.ETS, which applies a form of exponential smoothing with seasonality detection, and a Forecast Sheet feature on the Data tab that builds a chart with confidence bounds. Availability depends on your version, so check. These automated functions are useful as a benchmark. Always compare them to a simple method before trusting them. See seasonal demand planning.

Step 6: measure error and bias

A forecast needs a score. For each month, the error is actual minus forecast. The absolute percentage error is the absolute error divided by the actual. The mean absolute percentage error, MAPE, is the average of these. Bias is the average error, and a positive value means actuals exceeded forecasts. Compare methods on the same months. For months 4 to 12, the three-month moving average gives a MAPE of 6.5 percent and a bias of +8.6 units. Simple exponential smoothing with alpha of 0.3 gives, over the same months, a MAPE of 9.6 percent and a bias of +12.6 units. Both are positive, which shows that both lag behind a rising series. On this data, the moving average has the lower error, although it would be dangerous to conclude much from nine data points. The practical lesson is to measure, and to look at bias as well as accuracy. A persistent positive bias here tells you that a trend term is missing. See forecast bias in demand planning.

In Excel, create columns for forecast, error and absolute percentage error, and use =AVERAGE() on the last column. Use =SUMPRODUCT() for weighted measures, such as weighted MAPE, which weights errors by volume and is better when items differ in size.

Step 7: handle many items

A real catalog has hundreds or thousands of items, and one row per item and period would be large. Use a layout with items down the rows and months across the columns, or use a pivot table on the raw table. Apply the same formula to every item. Segment items first: forecast high-volume steady items with the statistical approach, review intermittent items separately, and treat new products and promotions by hand. Show the planner only the items where the forecast differs from last month's or from last year's by more than a threshold. See SKU-level forecasting.

Step 8: make the workbook robust

Keep inputs in labeled cells, such as alpha, the number of months in the average and the threshold for flags. Avoid hard-coded numbers in formulas. Protect formula cells. Add checks, such as totals that must tie to the source data, and conditional formatting that highlights errors. Keep a version history, with the date of each monthly update. Record overrides with a reason and the name of the person who made them. Test by hiding the last three months of data, forecasting them, and comparing with the actuals, a procedure called a holdout test. Document the workbook in a short instruction sheet so that someone else can run it.

A monthly routine

Update the data, refresh the tables, review the exceptions, apply known adjustments for promotions, launches and supply constraints, record the final forecast and compare last month's actuals with the forecast. Store the forecast each month so that you can measure accuracy later at the lead time that matters. The whole process should take hours, not days. If it takes days, simplify it, or consider automation. See planning automation.

Limits of Excel

Excel becomes harder to manage as the number of items, locations and users grows. Version control, file size, manual errors and limited collaboration are the usual pressure points. If several people need to edit the same plan, or if the model has many linked workbooks, consider moving the data to a database and using Power BI or a planning tool for presentation and workflow. See Python vs. Excel for forecasting for a comparison.

Common mistakes

Forecasting from sales that include stockout periods understates demand. Using a high alpha on noisy data makes the forecast jumpy. Applying a trend line to a flat or declining series misleads. Ignoring bias hides systematic errors. Overwriting formulas with values breaks the model. Building a workbook only the author understands creates risk.

FAQs

Can Excel do demand forecasting?

Yes. Moving averages, exponential smoothing, trend functions and built-in forecast functions cover many needs for small and mid-sized catalogs.

Which Excel function is best for forecasting?

None is best everywhere. Compare a moving average, exponential smoothing and FORECAST.ETS on a holdout period and choose by error and bias.

How do I measure forecast accuracy in Excel?

Calculate errors for each period, convert to absolute percentage error and average them to get MAPE. Track the average error for bias.

How much history do I need?

At least two years for seasonal items. For non-seasonal items, a year or more helps. Less history means more reliance on judgment and analogs.

When should I move beyond Excel?

When the number of items, users or data sources makes the workbook slow, error-prone or hard to share.