Seasonal sales forecasting in Excel, scored with MAPE

A shop selling fans and air coolers in Can Tho has to place its purchase orders for the next 12 months. May sells far more than January, about 70% more by the seasonal indices below, and the yearly total is still rising. How many units should the owner buy for each month, and how large an error is acceptable? This post works through the method in Seasonal-index and trend forecasting in Excel. The shop is an invented example and the numbers come from a script I wrote. If your sales have been hit by a shock like the pandemic, read the Starbucks post instead, because then history no longer tells you much about the future.

What does the invented dataset contain?

It holds 48 months of sales, counted in units. I use the first 36 months to build the model and hold back the last 12 for scoring, which is 25% of the sample. Hyndman and Athanasopoulos (2021, section 5.8) say the test set is typically about 20% of the sample, depending on the length of the sample and how far ahead you forecast, and should ideally be at least as long as the longest forecast horizon you need (Hyndman and Athanasopoulos, 2021).

Each month equals a trend line of 200 + 3t (t is the month number), times a seasonal index I set in advance, times random noise with a 4% standard deviation, rounded to whole units. Because I know the true answer, I can later ask how well the method recovers it. With real data you never have that advantage.

Months 1–12 (year 1):   173 172 230 280 281 262 221 198 213 200 204 188
Months 13–24 (year 2):  195 204 267 304 346 306 260 232 252 227 238 235
Months 25–36 (year 3):  220 223 304 335 404 342 284 293 251 262 252 240
Months 37–48 (year 4):  237 283 373 411 451 388 336 306 281 267 312 300

Paste the first 36 numbers into C2:C37. The last 12 are year 4's actual sales, used only for scoring, in J38:J49.

Step 1: how do you extract the seasonal indices?

In classical multiplicative decomposition you take a 2×12 centred moving average as the trend, divide actual sales by it, and average the ratios for each calendar month (Hyndman and Athanasopoulos, 2021). The book notes that these indices are adjusted so they sum to 12 (section 3.4). For an even seasonal period such as 12 months it uses a 2×m average: a 12-month average followed by a 2-month average, so that the result sits symmetrically on a single month. Other window lengths will usually leave seasonality in the trend (section 3.3).

In Excel, column A holds the month number, B the calendar month (1 to 12), C the sales; A and B run down to row 49:

D8:  =(C2/2+SUM(C3:C13)+C14/2)/12         fill down to D31
E8:  =C8/D8                                fill down to E31
N2:  =AVERAGEIFS($E$2:$E$37,$B$2:$B$37,M2,$E$2:$E$37,">0")    M2:M13 hold 1…12
O2:  =N2*12/SUM($N$2:$N$13)                fill down to O13
F2:  =INDEX($O$2:$O$13,B2)                 fill down to F49

The moving average exists only for months 7 to 30, because it needs six months on each side. So each calendar month gets just two ratios. The ">0" condition in AVERAGEIFS skips the blank cells. The result:

May runs about 41% above the average month and January about 19% below. April is furthest off: 1.21 against 1.30. Two ratios per month are too few for the 4% noise to cancel out.

I chose the multiplicative form because the seasonal swing grows with the level of sales: the gap between the best and worst month is 109 units in year 1 and 184 units in year 3.

Steps 2 and 3: how do you remove seasonality and fit the trend?

Divide each month's sales by that month's index, then fit a straight line to the adjusted figures against the month number. Hyndman and Athanasopoulos (2021, section 5.7) describe forecasting the two parts separately: the seasonal part and the seasonally adjusted part. For the second, the book allows any non-seasonal method, such as the drift method, Holt's method or a non-seasonal ARIMA (Hyndman and Athanasopoulos, 2021). A least-squares line is my choice, and it matches step two of the library framework.

G2:  =C2/F2                                fill down to G37
a:   =INTERCEPT($G$2:$G$37,$A$2:$A$37)     returns 204.16
b:   =SLOPE($G$2:$G$37,$A$2:$A$37)         returns 2.653

The trend line is 204.16 + 2.653 × t, so the base level rises by about 2.65 units a month. The line I built in was 200 + 3t. The difference comes from the noise and from the estimated indices that are still off.

Step 4: what does the 12-month forecast look like against reality?

Forecast month t as (a + b × t) times that month's index. The book says the seasonal part of the future is usually taken as the last year's estimate, and a fixed monthly index does exactly that.

I38: =(INTERCEPT($G$2:$G$37,$A$2:$A$37)+SLOPE($G$2:$G$37,$A$2:$A$37)*A38)*F38    fill down to I49
K38: =ABS(J38-I38)/J38                                                              fill down to K49

The forecasts total 3,798 units against 3,945 actual, which is 3.7% low. The forecast sits below the actual figure in 9 of the 12 months.

What does the MAPE say, and against what does it mean anything?

MAPE is the average of the absolute errors divided by actual sales. Here it is 6.6%: =AVERAGE(K38:K49). A single figure says little on its own. The textbook uses simple methods as benchmarks, among them the seasonal naïve forecast, which uses the same month last year (Hyndman and Athanasopoulos, 2021). I add one benchmark of my own: the same month last year multiplied by the growth in total sales from year 2 to year 3 (11.2%).

The last two are almost level. The five steps are worth the effort only if they beat a rule that takes seconds, and here they beat it by very little. The second rule needs no model, but it uses all of year 3 as its base, so it carries that year's noise forward.

Scoring the model on the very data that built it gives a MAPE of 2.6%, about 2.5 times lower. Hyndman and Athanasopoulos (2021, section 5.8) write that the size of the residuals is not a reliable indication of how large true forecast errors are likely to be, and that a model which fits the training data well will not necessarily forecast well (Hyndman and Athanasopoulos, 2021). My result agrees.

Two things MAPE does not tell you:

• The floor. No forecast brings MAPE to zero. With real data you do not know where the floor is. With an invented dataset I can compute it: using the exact indices and trend I built in, the MAPE on these 12 months is still 4.4%, because of the noise I added.
• The direction and cost of the errors. A forecast below actual in 9 of 12 months means stockouts in those months if the owner orders exactly to the numbers. Lost sales from empty shelves and cash tied up in stock are different costs; see asymmetric error cost. The book notes that percentage errors punish forecasts that are too high more than forecasts that are too low, but MAPE still does not distinguish the real costs of the two directions.

When does this method not fit?

When the history is short, when the seasonal shape changes, and when a shock hits. In detail:

• With under two years of data some calendar months have no ratio to average (my arithmetic, for a 2×12 moving average), and 36 months, as here, give only two ratios per month. The index table above shows the consequence.
• Classical decomposition assumes the seasonal pattern repeats identically every year. The book's example is electricity demand, whose peak shifted from winter to summer as air conditioning spread. It also copes badly with unusual values, such as a month hit by an industrial dispute, and the book says that although the method is still widely used, it is not recommended, because there are now much better methods (Hyndman and Athanasopoulos, 2021). By my reasoning, a big promotion in year 2 would push that month's index up.
• A straight line forever is a strong assumption. If the market saturates, the line times the indices will forecast too high. A rolling forecast updates more often; see Rolling forecast cycle.
• An order that is hard to reverse needs a range, not a single figure, as in One number or several scenarios?. To choose the method, see Choosing a forecasting method.

Going deeper: what I read, what I checked, what I did not do

I read the relevant passages in sections 3.1 to 3.4 (adjustments, components, moving averages, classical decomposition), 5.2, 5.7 and 5.8 of Hyndman and Athanasopoulos (2021), online edition, skipping the R code. I have not read chapter 7 on regression, where the book covers linear trend, nor the original papers on MAPE that the book cites. Setting out the five steps as one procedure comes from the library framework, not from a single formula in the book. The choice of a least-squares line for the trend, and the "times last year's growth" benchmark, are mine.

I computed everything in Python and ran the formulas above in LibreOffice Calc, and the two agree. I have not run them in Microsoft Excel.