Double moving average forecasting method

The double moving average forecasting method uses two successive trailing averages to estimate a level and a linear trend. It can reduce the lag of a simple moving average when business volume is rising or falling steadily. Test forecasts against later actuals before using them to set staffing or purchasing levels.

What the second average adds

A simple moving average smooths recent observations but trails a rising series. Averaging those averages again creates another lagged series. The gap between the two supplies a trend estimate that adjusts the forecast forward.

The NIST handbook describes double moving averages for a linear trend: calculate a second moving average using the same window, then derive a slope and intercept from both averages. This is different from centering an average for a historical chart. A forecast must use only observations available at its origin.

The moving average glossary gives the underlying smoothing definition. For ordinary rolling averages and charts, use moving averages in Excel; the distinct step here is the trend adjustment.

Start with completed periods from an export

Consider an illustrative US service company planning monthly order-handling capacity. Export order ID, accepted date, status, and business unit. Remove duplicates and canceled orders under a consistent policy, then aggregate accepted orders by completed calendar month. Keep returns or reversals separate if they represent a different workload.

Use a continuous monthly calendar. A genuinely empty month can contain zero; a missing export cannot. Reconcile the aggregated order count to the source register before calculating any average. If the current month remains open, leave it outside the completed history.

For this example, January through August 2026 appear below. All averages, trend estimates, and forecasts are displayed to two decimal places; spreadsheet calculations retain full precision. Dashes mean insufficient history, rather than zero.

Build the spreadsheet calculations

Use a window of three months for both averages. With actual volume denoted by Y, the first trailing average by M, and its trailing average by S, the calculations are:

M at month t = Average of the latest n actual observations
S at month t = Average of the latest n values of M
Adjusted level a = 2 × M − S
Trend b = 2 × (M − S) / (n − 1)
Forecast h months ahead = a + b × h

The window n must exceed one. Both averages use the same window and end at the same month. The horizon h counts periods after that month.

Put the table headers in row 1 and January in row 2. Columns A through G match the table. These Excel formulas start the calculations:

C4 = AVERAGE(B2:B4)
D6 = AVERAGE(C4:C6)
E6 = 2*C6-D6
F6 = 2*(C6-D6)/(3-1)
G6 = E6+F6

Enter each formula, beginning with =, in its named cell. Fill C4 down through C9; fill D6:G6 down through row 9. The first second-stage average needs the March, April, and May first averages, so the first usable forecast is made at the end of May for June.

For May, the adjusted level is 2 × 130 − 120 = 140 orders. The monthly trend is 2 × (130 − 120) / (3 − 1) = 10 orders. June's forecast is therefore 140 + 10 = 150 orders.

Microsoft's AVERAGE documentation explains that referenced empty cells are ignored while zeros are included. Check completeness separately so a missing month cannot silently shorten a window. Covirage is not affiliated with Microsoft.

Compare genuine forecasts with actuals

Save each forecast before the target month closes. June's forecast uses history through May, July's uses history through June, and August's uses history through July. Recomputing a past forecast with its target month's actual included would invalidate the comparison.

Forecasting: Principles and Practice explains forecast evaluation on test data, distinct from how well a method fits its training history. Compare every method on the same months and horizon.

The simple forecast uses the first trailing average at the origin. The prior-month forecast repeats the last observed actual. On a separate comparison sheet with this table in A1:E4, calculate absolute errors and their average:

F2 = ABS(B2-C2)
G2 = ABS(B2-D2)
H2 = ABS(B2-E2)

Fill down through row 4. Then =AVERAGE(F2:F4) gives the double-average error, with equivalent formulas for G and H. Its absolute errors are 0, 5, and 6.11 orders, giving 3.70 orders using unrounded inputs.

The method wins this small illustration, but the growing overforecast shows it reacting slowly to the slowdown. Three comparisons do not establish dependable performance. Use a longer history and the business workflow for evaluating time-series forecasts before choosing a method or window.

Where it goes wrong

• Averaging averages without correcting trend. The second average alone remains a lagged smoother. The adjusted level and slope create the trend forecast.
• Using centered windows. Future observations can enter a historical estimate and make apparent accuracy look better than a real forecast.
• Treating missing periods as ordinary blanks. An average may still return a number even though the intended history is incomplete.
• Extending a temporary surge. Promotions, shortages, and calendar effects can be mistaken for a persistent trend. Review the business cause before extrapolating.
• Choosing the window on the final test months. Repeated tuning uses up the independence of that test. Separate window selection from final evaluation.

Compare your workbook forecasts in Covirage

Bring a workbook containing dated forecast versions, their horizons, and completed-period actual orders. Covirage maps the columns, and its tools compute and check the forecast comparisons; the external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own workbook.

See Covirage for Excel analysis for the supporting file analysis workflow.