Forecasting in Excel: Simple Methods That Work

Most business forecasts do not need specialised software. A few Excel formulas, applied thoughtfully and checked honestly, produce forecasts good enough for budgets, staffing and stock planning. The key word is checked: every method below is scored against what actually happened, because a forecast method that sounds sophisticated can lose to a simple one. Forecasting: Principles and Practice, the standard free textbook, calls such simple methods extremely simple and surprisingly effective benchmarks (Hyndman and Athanasopoulos: simple forecasting methods).

The example series is monthly bike rentals in Washington, D.C., for 2011 and 2012 (UCI: bike sharing dataset): strong seasonality, with busy summers and quiet winters, plus strong growth, typical of a business in expansion. The methods are fitted on 2011 and scored on how well they forecast every month of 2012.

The sheet layout

Put months in column A as numbers 1 to 24, actual values for 2011 in B2:B13, and forecasts for 2012 in column C, rows 14 to 25. Five methods, from simplest to most useful:

Naive (repeat last month):        =$B$13
3-month moving average:          =AVERAGE($B$11:$B$13)
Linear trend:                    =FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)
Seasonal naive (same month):     =B2
Trend x seasonal index:          =FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)
                                   * (B2 / FORECAST.LINEAR(A2, $B$2:$B$13, $A$2:$A$13))

FORECAST.LINEAR predicts a value by linear regression on the existing points (Microsoft Support: FORECAST.LINEAR). The last method combines two ideas: the trend line says where the level will be, and each 2011 month's ratio to that year's trend, its seasonal index, says how far above or below the trend that month usually sits.

To score each method, add a column for the percentage error, =ABS(C14-B14)/B14 with 2012 actuals in B14:B25, and average it: the mean absolute percentage error.

The test: which method forecasts 2012 best?

The same calculations in Python, so every number can be checked:

import io
import urllib.request
import zipfile
import numpy as np
import pandas as pd

url = "https://archive.ics.uci.edu/static/public/275/bike+sharing+dataset.zip"
days = pd.read_csv(zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())).open("day.csv"), parse_dates=["dteday"])
monthly = days.groupby(days["dteday"].dt.to_period("M"))["cnt"].sum()
history, actual = monthly[:12].to_numpy(), monthly[12:].to_numpy()        # 2011 to fit, 2012 to test
print("2011 total:", history.sum(), "| 2012 total:", actual.sum())

2011 total: 1243103 | 2012 total: 2049576

months_2011, months_2012 = np.arange(1, 13), np.arange(13, 25)
slope, intercept = np.polyfit(months_2011, history, 1)                  # the line FORECAST.LINEAR fits
trend_2011, trend_2012 = intercept + slope * months_2011, intercept + slope * months_2012
forecasts = {
    "last month repeated (naive)": np.repeat(history[-1], 12),
    "3-month moving average": np.repeat(history[-3:].mean(), 12),
    "linear trend": trend_2012,
    "same month last year (seasonal naive)": history,
    "trend x seasonal index": trend_2012 * (history / trend_2011),
}
for name, forecast in forecasts.items():
    mape = np.mean(np.abs(forecast - actual) / actual)
    print(f"{name:<40} average error {mape:6.1%}   2012 total {forecast.sum():>9,.0f}")

last month repeated (naive)              average error  44.9%   2012 total 1,047,876
3-month moving average                   average error  35.7%   2012 total 1,252,004
linear trend                             average error  23.4%   2012 total 2,051,458
same month last year (seasonal naive)    average error  40.8%   2012 total 1,243,103
trend x seasonal index                   average error  12.0%   2012 total 2,044,082

What the results teach

• Naive and moving averages fail on seasonal data. Repeating December's low figure all year misses the summer peak by a wide margin.
• Last year's pattern alone misses growth. Seasonal naive gets the shape right but, with rentals growing 65% year on year, every month is far too low.
• A trend line alone misses the shape. It gets the annual total almost exactly right, but spreads it evenly across months, over-forecasting winter and under-forecasting summer, hence a 23% monthly error despite a near-perfect total.
• Combining trend and seasonality wins. The trend times seasonal index method cuts the average error to 12%, about a third of the naive methods' errors, with nothing more than FORECAST.LINEAR and a division.

The general lesson: identify the patterns in the series, level, trend and seasonality, and choose a method that captures the ones present. Then test it on a period it has not seen.

FORECAST.ETS and Forecast Sheet

Excel also includes exponential smoothing. FORECAST.ETS predicts future values using the AAA version of the exponential smoothing algorithm, and can detect seasonality automatically or take a season length you supply (Microsoft Support: FORECAST.ETS):

=FORECAST.ETS(A14, $B$2:$B$13, $A$2:$A$13, 12)

Forecast Sheet, on the Data tab in the Forecast group, builds a forecast chart and table with a confidence interval in a few clicks. It needs a timeline with consistent intervals, such as the first of every month, and tolerates up to 30% of points missing (Microsoft Support: create a forecast). These tools are convenient, but treat them like any other method: hold back the most recent period, forecast it, and compare the error with the simple methods above. With only one year of history, as here, a method that must estimate seasonality from the data has very little to learn from, which is one reason the transparent seasonal index approach is easier to reason about.

Presenting a forecast to managers

A forecast is used for decisions, so present it in the form decisions need:

• Lead with the planning number and its range, in a form such as "about 18,000 units in June, likely between 16,000 and 20,000", with the range based on how far past forecasts missed.
• Show the history and the forecast on one chart, with the forecast visually distinct, so the reader can judge whether it continues the pattern sensibly.
• State the method and its assumptions in one sentence: "last year's monthly pattern, scaled to the current growth trend".
• Name what would change it, such as a new pricing plan, a competitor or a weather event, so managers know when to ask for an update.

Seasonal patterns deserve their own explanation; time intelligence in analytics covers comparing periods, trends and seasons clearly.

Practical rules for Excel forecasts

1. Plot the history first and name the patterns you see.
2. Always compute a benchmark, such as seasonal naive, and beat it before trusting anything more complex.
3. Hold out the latest period for testing, then refit on all data for the real forecast.
4. Report a range, not just a number. Past errors tell you how wide it should be.
5. Update monthly and track forecast error over time.
6. Adjust for known events, such as promotions or price changes, separately and transparently.

Time series forecasting for beginners covers the same ideas with more methods and a longer series.

Forecasting basics, trend and pattern analysis, and Excel formulas and functions are part of the Data Analytics course.

Sources

• FORECAST and FORECAST.LINEAR functions (Microsoft Support) — Microsoft Support
• FORECAST.ETS function (Microsoft Support) — Microsoft Support
• Create a forecast in Excel for Windows (Microsoft Support) — Microsoft Support
• Some simple forecasting methods (Forecasting: Principles and Practice) — OTexts
• Bike Sharing (UCI Machine Learning Repository) — UCI Machine Learning Repository