Building a Forecast Baseline From Excel History in SQL

A manager asks for a forecast, and an analyst reaches for something clever. The cleverness is usually wasted. Before building a model, you need to know what a trivial method would predict, because a complicated forecast that cannot beat "same as last year" is a cost without a benefit. Baselines are cheap, can be written in SQL against a sheet of monthly history, and give a yardstick. This post builds four of them, tests them on held-out months and says what the results can and cannot tell you. It is a spoke of doing proper analysis on Excel data with SQL.

Prepare the series

Forecasting needs a series with one value per period and no holes. Group the sheet to months, then fill missing months deliberately (year over year comparison from a monthly Excel sheet shows the calendar fill). Check the series before using it: any months that are zero because the data was not loaded, any one-off events (a closed month, a bulk order), any change in definition. Mark outliers and decide whether to leave, cap or remove them. A baseline fitted to a series with a data error forecasts the error.

Assume a table monthly(month, revenue) with 24 months from 2023-01 to 2024-12, and that we want to forecast 2025.

Baseline 1: the last value (naive)

The forecast for every future month is the latest observed value.

SELECT revenue AS forecast
FROM monthly
ORDER BY month DESC
LIMIT 1;

If December 2024 was 130, every month of 2025 is forecast at 130. This ignores seasonality and trend and is the weakest baseline for seasonal data. It is the right yardstick for series with neither, and it sets a floor: a method that loses to it is broken.

Baseline 2: seasonal naive

The forecast for a month is the value from the same month a year earlier:

SELECT m.month + INTERVAL 1 YEAR AS forecast_month,
       m.revenue                 AS forecast
FROM monthly m
WHERE m.month >= DATE '2024-01-01';

January 2025 is forecast as January 2024, February as February 2024. For series with strong yearly seasonality (retail, travel, energy) this is surprisingly hard to beat, and for those series it is the baseline to keep in mind. It carries last year's noise and one-offs into the forecast, and it needs at least twelve months.

Baseline 3: moving average

The forecast is the mean of the last k months, for example three or twelve:

SELECT AVG(revenue) AS forecast_ma3
FROM (SELECT revenue FROM monthly ORDER BY month DESC LIMIT 3);

A twelve-month average smooths seasonality away by averaging a full cycle, giving a level forecast. A three-month average reacts faster and carries part of the seasonal swing of the last quarter. The window length is a choice to test, not to assume. Moving averages lag any trend, so in a growing series they under-forecast.

A rolling version for every month, useful for backtests:

SELECT month, revenue,
       AVG(revenue) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING) AS ma3_forecast
FROM monthly;

The window ends at 1 PRECEDING, so each month is forecast from the three months before it, never from itself. Including the current row would be leakage, and make the forecast look perfect.

Baseline 4: linear trend

Fit a straight line through the history and extend it. PostgreSQL and DuckDB have regression aggregates:

WITH t AS (
    SELECT revenue, ROW_NUMBER() OVER (ORDER BY month) AS x FROM monthly
),
fit AS (
    SELECT regr_slope(revenue, x) AS slope, regr_intercept(revenue, x) AS intercept FROM t
)
SELECT slope, intercept,
       intercept + slope * 25 AS forecast_next_month   -- x = 25 is the month after 24 observations
FROM fit;

regr_slope and regr_intercept return the least-squares line of revenue against the month number. Extending to x = 25, 26, ... forecasts ahead. A straight line suits steady growth or decline and misses seasonality; it also extrapolates indefinitely, which is dangerous beyond a few months. Combine with seasonality by adding the seasonal difference from the previous year to the trend, which is a simple seasonal-trend baseline.

Hold out and compare

Never judge a forecast on the data it was built from. Hold out the most recent months, forecast them using only earlier data, and compare. With 24 months, use months 1 to 18 to forecast months 19 to 24. For the seasonal naive baseline:

SELECT cur.month,
       cur.revenue AS actual,
       prev.revenue AS forecast_seasonal,
       ABS(cur.revenue - prev.revenue) AS abs_error,
       ABS(cur.revenue - prev.revenue) / NULLIF(cur.revenue, 0) AS abs_pct_error
FROM monthly cur
JOIN monthly prev ON prev.month = cur.month - INTERVAL 1 YEAR
WHERE cur.month >= DATE '2024-07-01';

Each baseline needs its own forecast column for the same months, built from data before the holdout starts. Then summarize errors per method:

Report at least two, MAE and MAPE or bias. A method can have a low MAE and a large bias. Because six held-out months is a small sample, the ranking may change with a different split; repeat the backtest with several cutoffs (a rolling origin) and see whether the order holds. If it flips, the methods are not distinguishable on this data.

What the numbers mean

A baseline's error is the standard for everything else. If the best baseline is wrong by 8 percent on average, a model claiming 3 percent has to show it on held-out data, with the same protocol. If the model's error is 7.5 percent, it is not worth the complexity. If the baselines are wrong by 40 percent, the series is noisy or has structural breaks, and no method will rescue it; the honest forecast is a range.

Give ranges, not points. The spread of past errors suggests an interval: if the seasonal naive method's errors were typically within plus or minus 10 percent, say so. A manager who sees "130, give or take about 13" plans differently from one who sees "130".

Document the forecast

A forecast is a claim, and it needs its basis stated: the method, the history it used (dates, completeness), the holdout test and its errors, the assumptions (no change in pricing, no new stores), and the date it was made. Save the SQL with the workbook. When the actuals arrive, compare them with the forecast and record the error. A forecast log is how you learn which method works for your data, and it is rare enough to be valuable.

Hand it to Excel

For presentation, write the history and the forecast to a CSV and chart them in Excel: actual as a solid line, forecast as a dashed one, a shaded range if you have it. Keep the label "Baseline forecast" so nobody reads it as a plan. Excel's own FORECAST.ETS function implements an exponential smoothing model with seasonality, and it is worth comparing against your baselines using the same holdout (translating Excel formulas into SQL covers the formula side of the move).

When a baseline is not a forecast

There is a known change coming. A new product, a price rise or a store closure breaks the continuity. A baseline extrapolates the past, and adjustments must be explicit and documented.

There is too little history. Under two years, seasonal methods have nothing to use. Say the forecast is weak.

The decision needs a model of causes. Knowing why revenue moves (price, volume, mix) calls for a driver-based model, not a time-series baseline.

The series is intermittent. Many zeros with occasional spikes (spare parts, rare events) need methods designed for intermittent demand, and the standard error measures mislead.

See the PostgreSQL manual on aggregate functions for statistics and Rob Hyndman's free textbook Forecasting: Principles and Practice for the methods and accuracy measures. To convert a small result to JSON, use the CSV to JSON converter.