Forecasting with Moving Averages

A moving average does two things at once: it smooths out noise in historical data and projects that smoothed value forward as a forecast. It is the simplest reliable forecasting technique in Excel and the right starting point whenever your data is noisy or lacks a clear trend.

How a Moving Average Forecast Works

Instead of predicting the future based on a single prior month — which is vulnerable to one-off spikes — a moving average bases its prediction on the average of the last N months. As each new month arrives, the oldest month drops off and the newest one enters the window. The window slides forward, always reflecting the most recent behavior.

A 3-month moving average reacts quickly to recent changes but still carries some noise. A 12-month moving average is much smoother and better at revealing long-term direction, but it reacts slowly to genuine trend shifts. In practice you use both and compare them.

Building a Moving Average Forecast

Your Forecast_Input_Table already contains MA_3Month and MA_12Month columns covering historical periods. Now you'll extend them forward as actual forecasts.

Create a forecast extension area below your 36 months of history — 12 new rows covering January 2025 through December 2025. Add the following columns:

Period_Date and Period_Label — continue from your Calendar_Table:

• Period_Date: link directly from Calendar_Table rows 37–48
• Period_Label: =TEXT([@Period_Date],"YYYY-MM")

3-Month Moving Average Forecast:

• First forecast row (Jan 2025): =AVERAGE(Forecast_Input_Table[Total_Revenue]) - last 3 months of history

More precisely, referencing the last three actual revenue values: =AVERAGE(D35:D37) → where D is Total_Revenue and row 37 is Dec 2024.

Once in the forecast zone, each new row feeds off the previous forecast values:

• Feb 2025: =AVERAGE(D36,D37,D38) → two actuals + one forecast;
• Mar 2025: =AVERAGE(D37,D38,D39) → one actual + two forecasts;
• Apr 2025 onward: =AVERAGE(D38,D39,D40) → pure forecast values.

Adding a Simple Average Forecast Baseline

Before comparing methods, establish a naive baseline: the overall average of all 36 months of history projected flat into the future. Any forecasting method that can't beat this baseline isn't worth using.

Baseline forecast (same value for every future month): =AVERAGE(Forecast_Input_Table[Total_Revenue])

Label this column Baseline_Forecast in your extension table. It becomes the benchmark every other method must outperform in Lesson 2.5.

Weighted Moving Average

A standard moving average weights all N periods equally. A weighted moving average gives more influence to recent months — which makes sense in business, where last month's revenue is more predictive than revenue from 11 months ago.

3-Month Weighted Moving Average (weights: 50% most recent, 30% middle, 20% oldest): =(0.5 * D37) + (0.3 * D36) + (0.2 * D35)

Where D37 = most recent month, D36 = one month prior, D35 = two months prior.

The weights must always sum to 1.0. Adjust them based on how much you trust recent data versus older history — higher weight on recent months makes the forecast more reactive, lower weight makes it more stable.

Visualizing the Forecast

A forecast that isn't visualized is a forecast that won't be trusted. The chart below is the standard way to present a moving average forecast:

• Solid blue line — historical Total_Revenue (36 months);
• Dashed blue line — 3-month moving average forecast (12 months forward);
• Dashed gray line — baseline flat forecast;
• Vertical marker — a clear visual boundary between history and forecast at January 2025.

To create the vertical boundary, add a helper column with a single value at the forecast start date and plot it as a secondary series formatted as a vertical error bar or a thin colored line.

Task 1: Build the Moving Average Forecast

Goal: Extend your moving average columns forward 12 months and create a forecast table.

1. Below your Forecast_Input_Table, create a new area with the header Forecast_2025. Add Period_Date (Jan–Dec 2025 from Calendar_Table) and Period_Label columns.
2. Add a MA3_Forecast column. In the first row (Jan 2025), reference the last three Total_Revenue values from your history table using =AVERAGE(C35:C37) — adjust row numbers to match your actual layout.
3. In Feb 2025 onward, write the self-feeding formula =AVERAGE() referencing the two prior rows from history and one from the forecast section, sliding forward each row.
4. Add a Baseline_Forecast column using =AVERAGE(Forecast_Input_Table[Total_Revenue]) as a flat constant across all 12 forecast rows.
5. Add a Weighted_MA_Forecast column using the 50/30/20 weighting formula above for the first three rows, then adjust the references to slide forward.

Task 2: Visualize Historical Data and Forecast

Goal: Build the standard forecast presentation chart.

1. Select Period_Label, Total_Revenue (historical), and MA3_Forecast (forecast) columns — hold Ctrl to select non-adjacent columns.
2. Insert a Line Chart. Format the historical revenue series as a solid line and the forecast series as a dashed line (right-click series → Format Data Series → Dash type).
3. Add the Baseline_Forecast series and format it as a dashed gray line.
4. Add a text box on the chart marking the boundary between history and forecast at January 2025.
5. Title the chart "Revenue Forecast — 3-Month Moving Average" and label both axes clearly.