Demand Forecasting Spreadsheet: Moving Average, Weighted Average, and Trendline Methods

Overview

If you need a simple inventory demand planning template, start by separating two questions: what your past demand looked like, and what forecasting method best reflects that pattern. Many spreadsheets fail because they jump straight to formulas without first checking whether demand is stable, noisy, seasonal, or trending upward or downward.

The three methods in this article are useful because they solve different problems:

• Moving average smooths recent history and works well when demand is fairly stable.
• Weighted average also smooths history, but gives more importance to recent periods, which helps when demand is changing gradually.
• Trendline forecasting projects a directional pattern over time, which is helpful when demand has a clearer upward or downward slope.

In a practical demand forecasting spreadsheet, it is often better to calculate all three side by side and compare their errors than to rely on instinct. That turns the spreadsheet from a static template into a lightweight decision making spreadsheet.

A clean layout usually includes these columns:

• Period or date
• Actual demand
• Moving average forecast
• Weighted average forecast
• Trendline forecast
• Forecast error
• Absolute error
• Percentage error or absolute percentage error

This structure works in both Excel business templates and Google Sheets templates. It is also easy to extend into a dashboard template later if you want charts, variance highlights, or rolling accuracy metrics.

How to compare options

The most useful way to compare forecasting methods is to judge them on fit, effort, and maintainability. A forecast that is mathematically cleaner but hard to update every month will often be less useful than a slightly simpler method that your team can trust and maintain.

Here is a practical comparison framework.

1. Look at the shape of the demand history

Before writing formulas, plot actual demand as a line chart. Even a basic chart can reveal patterns that matter:

• If the line moves around a fairly stable center, a moving average may be enough.
• If recent periods clearly matter more than older ones, weighted average is often better.
• If the line trends steadily up or down, a trendline method may be the better starting point.

If your data has strong seasonality, promotions, stockouts, or one-off disruptions, none of these methods should be used blindly. In that case, treat the base forecast as a starting point and layer judgment or scenario planning on top. For that next step, a related model is Scenario Planning Spreadsheet: Best Case, Base Case, and Worst Case Models.

2. Compare forecast accuracy with the same historical window

To compare methods fairly, run each forecast across the same historical periods and calculate error metrics consistently. At a minimum, include:

• Error = Actual - Forecast
• Absolute Error = ABS(Actual - Forecast)
• Absolute Percentage Error = ABS((Actual - Forecast) / Actual), where actual is not zero

Then summarize average absolute error or average absolute percentage error for each method. The lowest error does not always win automatically, but it gives you an objective starting point.

3. Consider responsiveness versus stability

This is one of the most important tradeoffs in any forecast spreadsheet template.

• A more stable method changes slowly and avoids overreacting to random noise.
• A more responsive method adapts faster when demand genuinely shifts.

Moving averages are typically more stable. Weighted averages are usually more responsive. Trendline methods can be very useful when direction matters, but they can also overproject if recent growth or decline is temporary.

4. Match the method to the business decision

Not every planning task needs the same kind of forecast.

• For weekly reordering of a stable item, a moving average may be appropriate.
• For a product with recent demand acceleration, weighted average can be a better fit.
• For capacity planning over the next quarter, a trendline may provide a clearer directional estimate.

Think about what decision the spreadsheet supports. A forecast used for purchasing may need caution and buffers. A forecast used for staffing may need broader ranges and managerial review. If your planning process also touches cash constraints, pair the forecast with a cash model such as Burn Rate and Runway Spreadsheet for Startups and Small Businesses.

Feature-by-feature breakdown

This section walks through what each method does, how to build it, and what to watch for in Excel or Google Sheets.

Moving average

A moving average forecast uses the average of the most recent periods to predict the next one. A 3-period moving average, for example, uses the last three actual values.

Why it is useful: It is simple, transparent, and easy to explain to non-technical users. That makes it a strong default for a small business spreadsheet template.

Basic setup:

• Column A: Period
• Column B: Actual demand
• Column C: 3-period moving average forecast

If your actual demand starts in cell B2, the forecast in row 5 can be written as:

=AVERAGE(B2:B4)

Then copy downward.

What to customize:

• Use 3 periods if you want a more responsive forecast.
• Use 6 or 12 periods if you want more smoothing.

Strengths:

• Easy to audit
• Good for stable demand
• Low risk of overfitting

Limitations:

• Lagging during growth or decline
• Treats older recent periods the same as the newest period
• Can hide turning points

If you are specifically searching for a moving average Excel forecast approach, this is often the fastest method to implement and test first.

Weighted average

A weighted average demand forecast improves on the moving average by assigning different importance to each recent period. Usually, the most recent periods get higher weights.

Why it is useful: It balances simplicity with responsiveness. For many operating teams, this is the most practical middle ground.

Example setup:

Suppose you use the last three periods with weights 50%, 30%, and 20%, where the most recent period gets 50%.

If B2:B4 contains the three most recent actual values for the forecast in row 5, you could write:

=B4*0.5+B3*0.3+B2*0.2

Or use a more flexible setup with a separate weights row and SUMPRODUCT.

Flexible version:

• Put weights in cells H1:H3
• Use =SUMPRODUCT(B2:B4,$H$1:$H$3) adjusted so the order matches your intended recency

Strengths:

• More responsive than a simple moving average
• Still easy to explain
• Customizable for different products or categories

Limitations:

• Weights can become arbitrary if not tested
• Still not ideal for strong long-term trend or seasonality
• Needs discipline so weights sum to 1

For many teams building an inventory demand planning template, weighted average is a useful step after a basic moving average because it adapts to change without becoming difficult to maintain.

Trendline method

Trendline forecasting estimates future demand based on the direction of historical demand over time. In spreadsheets, this can be done through a chart trendline or formulas such as FORECAST.LINEAR in Excel and Google Sheets.

Why it is useful: It is better suited to data with a clear upward or downward trend. If demand has been climbing in a relatively steady way, averaging methods may consistently under-forecast.

Basic setup:

• Column A: Time period as numbers, such as 1, 2, 3, 4
• Column B: Actual demand
• Column D: Trendline forecast

To forecast the next period using a linear method, you can use:

=FORECAST.LINEAR(A13,$B$2:$B$12,$A$2:$A$12)

Replace ranges to match your sheet.

Strengths:

• Captures direction better than averaging methods
• Useful for medium-term planning
• Simple to visualize on a chart

Limitations:

• Assumes the trend continues in a roughly linear way
• Sensitive to outliers and unusual periods
• Can mislead when trend is temporary

If you are exploring trendline forecasting in Google Sheets, the formula-based approach is usually more maintainable than relying only on chart visuals, because it lets you compare forecast values row by row and calculate accuracy.

Which method is easiest to maintain?

In everyday operations, maintainability matters almost as much as forecast accuracy.

• Moving average: easiest to maintain
• Weighted average: slightly more setup, but still manageable
• Trendline: easy enough to calculate, but requires more judgment about whether the trend is still valid

If you expect non-analysts to update the file, clear labels, protected formula cells, and a short assumptions section will do more for spreadsheet quality than adding complex logic. If you later present results in a dashboard format, these design principles also apply: Excel Dashboard Design Best Practices for Readable KPI Reporting.

Build a simple comparison table

A strong forecast spreadsheet template often includes a compact comparison area with:

• Method name
• Average absolute error
• Average absolute percentage error
• Notes on fit
• Recommended use case

This makes the spreadsheet much easier to revisit later. Instead of rebuilding the logic each month, you can update actuals, review error summaries, and see whether the current method still deserves to be the default.

Best fit by scenario

The best method depends less on theory and more on the demand pattern you actually have.

Choose moving average when:

• Demand is fairly stable
• You want a conservative, easy-to-understand forecast
• You need something fast for replenishment or weekly planning

Example: a steady seller with modest fluctuations and no clear growth trend.

Choose weighted average when:

• Recent demand matters more than older demand
• You want more responsiveness without much more complexity
• You regularly adjust for new signals but still want a spreadsheet-based method

Example: an item with recent demand pickup after a channel change or promotion, but not enough history to justify a more advanced model.

Choose trendline when:

• Demand shows a visible upward or downward slope
• You are planning farther ahead than the next immediate period
• You need to reflect direction in inventory or capacity decisions

Example: a product line with steady growth over several months where a moving average keeps lagging behind.

Use more than one method when:

• You are uncertain about the pattern
• The cost of stockouts or overstock is high
• You want a base forecast and a challenge forecast

A practical approach is to use one method as the operating forecast and another as a check. If forecast risk matters, extend the model with a range-based view using ideas from Monte Carlo Simulation in Excel and Google Sheets for Forecast Risk Ranges.

And if your work overlaps with revenue planning, it is useful to compare this demand-focused approach with Sales Forecast Spreadsheet Methods: Run Rate, Weighted Pipeline, and Seasonality, which covers a different type of forecasting logic.

When to revisit

The most reliable forecast spreadsheet is not the one with the most formulas. It is the one you revisit at the right times. Demand patterns change, and your method should change with them.

Review and update your forecasting approach when any of these happen:

• You add a meaningful amount of new demand history
• Recent forecast error rises for several periods in a row
• A product enters a new growth or decline phase
• Promotions, pricing, distribution, or product mix change
• Operational constraints make forecast misses more costly than before

A practical monthly review process looks like this:

1. Append the latest actual demand data.
2. Refresh each forecast method.
3. Check error metrics over a rolling window, such as the last 6 or 12 periods.
4. Note whether the current default method is still the most reliable.
5. Document any manual override and why it was made.

This is also a good moment to decide whether your spreadsheet needs a wider planning context. For example:

• If demand changes affect labor needs, connect the forecast to a schedule model like Employee Shift Schedule Spreadsheet With Availability, Coverage, and Labor Hours.
• If demand changes affect project timing, relate it to a planning sheet such as Gantt Chart Spreadsheet Guide: Build a Simple Project Timeline in Excel or Sheets.
• If demand changes affect operating costs, pair it with Monthly Expense Tracker Spreadsheet for Small Business Category Control.

To make your spreadsheet easier to revisit, add a short checklist tab with:

• Current default method
• Last review date
• Error summary by method
• Known exceptions or one-time events
• Next review trigger

That small addition turns a basic forecast spreadsheet template into a repeatable operating tool.

If you want one final rule of thumb, use this: start simple, compare methods with actual error data, and keep the model easy enough that it gets updated on schedule. A demand forecasting spreadsheet is most valuable when it helps you make the next decision with a little more confidence than the last one.