Demand forecasting: weighted averages, exponential smoothing and least squares

•0•0Comment iconComment iconComment iconComment icon

Learn how to forecast demand by giving more weight to recent data, using exponential smoothing to adjust for errors, and identifying trends with a least-squares line.

Writer image

تمت الترجمة بواسطةEditorial

Writer image

تمت المراجعة بواسطةEditorial

تحرير المادة

Introduction

In the previous lesson, we looked at two simple forecasting methods: using the most recent period's demand and taking a moving average of recent months. Both have the same weakness: they treat the past as if timing did not matter. Using the most recent period ignores everything before it; a moving average gives the same weight to last month and a month a year ago, so it is slow to pick up a lasting change in demand.

In this lesson, we take three steps further:

  • Weighted moving average: recent months carry more weight.
  • Exponential smoothing: a way to weight the entire history using just two numbers.
  • Least squares (linear regression): the first model that can identify a trend—demand that consistently rises or falls.

Finally, we will compare the methods by measuring their errors. No forecast is perfect; the question is always, "Which method is least wrong for this product?"

Weighted moving average

In a 12-month moving average, each month has a weight of 1/12. But in most businesses, last month tells us more about next month than a month a year ago does. A weighted moving average accounts for this by assigning each period a weight (W) based on its importance:

Forecast = Σ (Dᵢ × Wᵢ) ÷ Σ Wᵢ

where Dᵢ is demand in period i and Wᵢ is that period's weight. If the weights already add up to 1 (for example, 0.5; 0.3; 0.2), we can simply add the weighted values.

Example. Consumption over the last three months was 99, 100 and 102 units. We assign weights of 1, 2 and 3, giving the most recent month the greatest weight:

MonthConsumptionWeightConsumption × weight
199199
21002200
31023306
Total6605

Forecast for month 4 = 605 ÷ 6 = 100.83 units. The simple average would be 100.33; the weighted average is closer to the most recent figure (102) because that month accounts for half the total weight.

Choosing weights is a management decision, usually made by testing several sets against historical data and keeping the one with the lowest error (see the final section). If all the weights are equal, the weighted average becomes an ordinary moving average.

A store sold 200, 260 and 300 units in the last three months, from oldest to most recent. Using weights of 0.2; 0.3 and 0.5, what is the forecast for next month?

Loading icon

Answer: 268 units — 200 × 0.2 + 260 × 0.3 + 300 × 0.5 = 40 + 78 + 150 = 268. Because the weights already add up to 1, there is no need to divide. 253.3 is the simple average, which ignores the weights.

Exponential smoothing

Exponential smoothing is the time-series forecasting technique most widely used in inventory management systems. It is a weighted average in which the weights decline exponentially the further back in time we go—hence the name. The formula is:

P(t+1) = α × D(t) + (1 − α) × P(t)

where:

  • P(t+1) is the forecast for the next period;
  • D(t) is actual demand in the period that has just ended;
  • P(t) is the forecast we made for that same period;
  • α (alpha) is the smoothing constant, a number between 0 and 1.

Rearranging the terms makes the same formula easier to understand:

P(t+1) = P(t) + α × [D(t) − P(t)]

In other words, the new forecast is the old forecast adjusted by a fraction α of the error we just made. If sales exceeded the forecast, the forecast rises; if they fell short, it drops. The α determines the size of the adjustment.

Why "exponential"?

If we replace P(t) with its own formula, then do the same for P(t−1), and continue backward, we get:

P(t+1) = α × D(t) + α(1 − α) × D(t−1) + α(1 − α)² × D(t−2) + α(1 − α)³ × D(t−3) + ...

Each past demand figure enters the forecast with a weight equal to the preceding weight multiplied by (1 − α). The entire history contributes, but the influence of the distant past fades. Better still, calculating the next forecast requires us to keep only two numbers: the latest forecast and the latest actual demand.

Weight given to each past demand figure in the forecast, for three values of α
Weight given to each past demand figure in the forecast, for three values of α

The chart shows the effect of α:

  • Small α (0.1 to 0.2): weights decline slowly, so many earlier periods still count. The forecast is stable and filters out noise, but it takes longer to respond to a genuine change.
  • Large α (0.5 or higher): almost all the weight falls on the last two or three periods. The forecast responds quickly, but it also chases every random fluctuation.

In practice, values between 0.1 and 0.3 are most common for stable demand. The best α for a product is found by testing several values against historical data and choosing the one with the lowest error.

To start using the method, we need two things: a value for α and an initial forecast. With no previous forecast available, a common starting point is the first observed demand figure or the average of the first few months.

Example

A company uses α = 0.30. Its forecast for the first week of July was 800 units, and actual demand was 850. What is the forecast for the second week?

P(2) = 0.30 × 850 + 0.70 × 800 = 255 + 560 = 815 units

We can also calculate it from the error: demand was 850 − 800 = 50 units higher than forecast. We adjust for 30% of that error, or 15 units. 800 + 15 = 815.

A caution: simple smoothing does not track trends

Simple exponential smoothing can only adjust a forecast toward what has already happened. If demand grows every month, the forecast will consistently lag behind and come in too low. Variants exist for data with a trend (Holt's method, for example, smooths the slope as well), but the classic, simpler tool for capturing a trend is linear regression, which we will look at next.

A company uses exponential smoothing with α = 0.2. Its forecast for last month was 1,000 units, and actual demand was 1,200. What is the forecast for next month?

Loading icon

Answer: 1,040 units — the error was 1,200 − 1,000 = 200; we adjust for 20% of it: 1,000 + 0.2 × 200 = 1,040. The result of 1,160 comes from reversing the weights and giving actual demand a weight of 0.8—in other words, using α = 0.8.

A product has stable demand but a lot of random variation from week to week. Which value of α is likely to produce the most useful forecast?

Loading icon

Answer: a small α, such as 0.1, to filter out random fluctuations — with a small α, the forecast gives weight to many periods rather than being swayed by an unusual week. An α close to 1 virtually repeats the latest demand figure, copying the noise along with it.

Least squares (linear regression)

All averages—simple, weighted or exponential—tend to pull a forecast back toward the middle. Consider what happens when demand rises by 50 each month but we keep forecasting with the average of the last three months, using each forecast in place of the actual figure to predict the following month:

MonthConsumption
150 (actual)
2100 (actual)
3150 (actual)
4100.00
5116.67
6122.22
7112.96
8117.28
9117.49
10115.91
11116.90
12116.77

The forecast settles at around 116 to 117, even though the data shows growth of 50 units a month. An average simply has no way to recognize the trend.

To account for the trend, we fit a straight line to the historical data:

Y = b + a × X

where:

  • X is the period (1, 2, 3, ...);
  • Y is forecast demand for that period;
  • a is the slope: how much demand rises (positive a) or falls (negative a) each period;
  • b is the intercept, where the line crosses the vertical axis.

Of all the possible lines, the least-squares method chooses the one that minimizes the sum of the squared differences between actual values and values on the line. Squaring prevents positive and negative errors from canceling each other out and penalizes larger errors more heavily. The resulting formulas are:

a = [N × ΣXY − ΣX × ΣY] ÷ [N × ΣX² − (ΣX)²]

b = [ΣY − a × ΣX] ÷ N

where N is the number of historical periods. We calculate a first, then b.

Worked example

Beta, a company that makes HR parts, recorded these sales this year: January 2,400; February 2,100; March 2,550; April 2,700; May 2,750; June 2,800; July 3,000. What is the forecast for August using the trend line?

We set up a table with the columns needed for the formulas:

MonthXY (demand)X²X × Y
January12,40012,400
February22,10044,200
March32,55097,650
April42,7001610,800
May52,7502513,750
June62,8003616,800
July73,0004921,000
Total2818,30014076,600

With N = 7:

a = [7 × 76,600 − 28 × 18,300] ÷ [7 × 140 − 28²] = [536,200 − 512,400] ÷ [980 − 784] = 23,800 ÷ 196 = 121.43

b = [18,300 − 121.43 × 28] ÷ 7 = [18,300 − 3,400] ÷ 7 = 2,128.57

The line is Y = 2,128.57 + 121.43 × X. Sales are growing by an average of about 121 parts a month. For August, X = 8:

Y = 2,128.57 + 121.43 × 8 = 3,100 parts

The least-squares line tracks rising sales; the simple average falls behind
The least-squares line tracks rising sales; the simple average falls behind

Notice in the chart that the simple average for the seven months (2,614) would be almost 500 parts below the trend-based forecast. If the growth is real, a company relying on the average would run out of stock.

Two cautions about regression:

  • It assumes the trend will continue. Forecasting many periods ahead is risky: sales cannot grow in a straight line forever. Use it for short horizons and update the line as new data comes in.
  • It does not account for seasonality. If the product has peaks in certain months, combine the trend with the seasonal index covered in the previous lesson: forecast the trend, then multiply by that month's index.

In a spreadsheet, there is no need to build the table by hand: in Excel or LibreOffice, the INCLINAÇÃO and INTERCEPÇÃO functions return a and b, while PREVISÃO (or PREVISÃO.LINEAR) calculates Y for any X. It is worth working through the calculation by hand once to see where the numbers come from.

Sales of a product over the last three months were 100, 120 and 140. Using a least-squares line with X = 1, 2 and 3, what is the forecast for month 4?

Loading icon

Answer: 160 — the data already falls on a straight line: ΣX = 6, ΣY = 360, ΣXY = 100 + 240 + 420 = 760, ΣX² = 14. a = (3 × 760 − 6 × 360) ÷ (3 × 14 − 36) = 120 ÷ 6 = 20 and b = (360 − 20 × 6) ÷ 3 = 80. Y = 80 + 20 × 4 = 160. The simple average is 120—the very error regression corrects.

How to choose a method: measuring error

Every forecasting system needs a way to measure the gap between forecasts and actual demand. The simplest measure is mean absolute deviation (MAD): the average size of the errors, regardless of whether forecasts were too high or too low.

MAD = Σ |actual demand − forecast| ÷ number of periods

Applying exponential smoothing to Beta's sales, with the February forecast set equal to January's sales of 2,400, gives the following MAD for each α:

MethodMAD (Feb to Jul)
Exponential smoothing, α = 0.1331 parts
Exponential smoothing, α = 0.3290 parts
Exponential smoothing, α = 0.5254 parts
Exponential smoothing, α = 0.8216 parts
Least-squares line (Jan to Jul)94 parts

Two lessons emerge from this table. First, because Beta’s series is trending upward, simple exponential smoothing produces large errors. A higher α reduces those errors because the forecast lags less. Second, the fitted line produces much smaller errors, confirming that a trend-based model is more appropriate. (One important caveat: the line’s MAD was measured using the same data used to fit it, which gives the line an advantage. A fairer test would fit the model using part of the historical data and measure its errors over the following months.)

MAD also connects to the lesson on safety stock: the larger the typical forecast error, the more extra inventory is needed to avoid stockouts.

An analyst tested two methods against the same 12 months of historical data: a 3-month moving average had a MAD of 40 units, while exponential smoothing with α = 0.2 had a MAD of 25 units. What should the analyst conclude?

Loading icon

Answer: Smoothing with α = 0.2 is the better choice for this product because its average error is lower — MAD is used to compare methods against the same historical data, and the method with the lower mean absolute deviation is preferred. Every forecast has errors; the goal is to choose the method that makes the smallest ones.

Exercises

Easy

1) Long-term demand for a product is relatively stable, and an exponential smoothing constant of 0.30 is considered satisfactory. The forecast for the previous period was 1,100 units, but actual demand was 1,000 units. What is the forecast for this period?

2) (Logistics exam, CEFET/RJ, 2018) A company uses exponential smoothing with α = 0.30. The forecast for week 1 of July was 800 units, and actual demand was 850 units. Find the forecast for week 2 of July.

3) Vitória uses exponential smoothing with α = 0.8. The forecast for month 1 was 1,800 units, and actual demand was 1,500 units. What is the forecast for month 2?

4) (Logistics exam, CEFET/RJ, 2018) The old forecast is 820 units, and the most recent actual demand was 750 units. What is the exponential smoothing forecast for the next period, with α = 0.2?

5) Using the weighted-average values from the first section (99, 100 and 102), calculate the forecast for month 4 with weights of 0.2, 0.3 and 0.5, and compare it with the result using weights of 1, 2 and 3.

Hard

6) An appliance line sold the numbers of boxes below over the last 7 months. Forecast sales for August, September and October using the least-squares line.

MonthBoxes
January100
February110
March125
April135
May140
June153
July167

7) A company needs to forecast sales of a seasonal product and has collected quarterly consumption for the last 4 years. How much should be consumed in each quarter of year 5, given that the forecast total for the year is 3,000 units? (Use the seasonal indices from the previous lesson.)

QuarterYear 1Year 2Year 3Year 4
1150180230210
2340420510650
3190260350390
4410700750980

8) A company uses exponential smoothing with α = 0.1 to forecast weekly demand for cases of canned beer. In the first week of February, the forecast was 500 cases, and actual demand came to 450.
a) What is the forecast for the second week of February?
b) If actual demand in the second week was 515 cases, what is the forecast for the third week?

9) Redo exercise 8 with α = 0.15 and compare the results.

10) Monthly demand for a product over the last 7 months was: 40, 44, 43, 48, 50, 53 and 55.
a) Calculate the forecasts for months 4 through 7 using a 3-month simple moving average and a 3-month weighted moving average with weights of 0.2, 0.3 and 0.5 (the most recent month has a weight of 0.5).
b) Calculate the MAD of each method over those four months. Which one has smaller errors?
c) Calculate the forecast for month 8 using both methods and using the least-squares line fitted to the 7 months.
d) Looking at the errors in item a, what flaw do both averaging methods have in this series, and which of the three forecasts in item c would you use?

Answer key

1) F = α × D + (1 − α) × previous F = 0.3 × 1,000 + 0.7 × 1,100 = 300 + 770 = 1,070 units. In error form: the error was 1,000 − 1,100 = −100; we correct 30% of it: 1,100 − 30 = 1,070.

2) 0.3 × 850 + 0.7 × 800 = 255 + 560 = 815 units.

3) 0.8 × 1,500 + 0.2 × 1,800 = 1,200 + 360 = 1,560 units. With a high α, the forecast stays close to the latest demand.

4) 0.2 × 750 + 0.8 × 820 = 150 + 656 = 806 units.

5) 99 × 0.2 + 100 × 0.3 + 102 × 0.5 = 19.8 + 30 + 51 = 100.8. Practically the same result (100.83), because the weights 1, 2 and 3 divided by their sum of 6 give 0.17, 0.33 and 0.5, which are very close.

6) With X = 1 (January) to 7 (July) and N = 7: ΣX = 28; ΣY = 930; ΣX² = 140; ΣXY = 100 + 220 + 375 + 540 + 700 + 918 + 1,169 = 4,022.
a = (7 × 4,022 − 28 × 930) ÷ (7 × 140 − 28²) = (28,154 − 26,040) ÷ (980 − 784) = 2,114 ÷ 196 = 10.79.
b = (930 − 10.786 × 28) ÷ 7 = (930 − 302) ÷ 7 = 89.71.
The line is Y = 89.71 + 10.79 × X. August (X = 8): 89.71 + 10.79 × 8 = 176 boxes; September (X = 9): 187 boxes; October (X = 10): 198 boxes.

7) First, each quarter's share of each year's total (totals: year 1 = 1,090; year 2 = 1,560; year 3 = 1,840; year 4 = 2,230). For example, quarter 1 in year 1: 150 ÷ 1,090 = 13.8%.

QuarterYear 1Year 2Year 3Year 4Average
113.8%11.5%12.5%9.4%11.8%
231.2%26.9%27.7%29.1%28.7%
317.4%16.7%19.0%17.5%17.7%
437.6%44.9%40.8%43.9%41.8%

The average share times 4 is the seasonal index (for example, 11.8% × 4 = 0.47 in quarter 1), and applying the share to the annual total gives the same result as applying the index to the quarterly average (3,000 ÷ 4 = 750). Forecasts: 1st quarter ≈ 3,000 × 11.8% ≈ 354; 2nd ≈ 862; 3rd ≈ 530; 4th ≈ 1,254. (If, instead, we add the 4 years of each quarter and divide by the overall total of 6,720, the result changes little: 344, 857, 531 and 1,268.)

8) a) 0.1 × 450 + 0.9 × 500 = 45 + 450 = 495 cases.
b) 0.1 × 515 + 0.9 × 495 = 51.5 + 445.5 = 497 cases.

9) a) 0.15 × 450 + 0.85 × 500 = 67.5 + 425 = 492.5, that is, about 493 cases.
b) 0.15 × 515 + 0.85 × 492.5 = 77.25 + 418.6 = 495.9, that is, about 496 cases.
With a higher α, the forecast reacts more: it falls further in week 2 (492.5 versus 495) and, since it started from a lower point, it still ends up slightly lower in week 3 (495.9 versus 497), although it rose more (3.4 cases versus 2).

10) a) and b)

MonthActual3-month moving averageErrorWeighted 0.2/0.3/0.5Error
448(40 + 44 + 43) ÷ 3 = 42.33+5.670.2 × 40 + 0.3 × 44 + 0.5 × 43 = 42.7+5.3
550(44 + 43 + 48) ÷ 3 = 45.00+5.000.2 × 44 + 0.3 × 43 + 0.5 × 48 = 45.7+4.3
653(43 + 48 + 50) ÷ 3 = 47.00+6.000.2 × 43 + 0.3 × 48 + 0.5 × 50 = 48.0+5.0
755(48 + 50 + 53) ÷ 3 = 50.33+4.670.2 × 48 + 0.3 × 50 + 0.5 × 53 = 51.1+3.9
MAD21.33 ÷ 4 = 5.3318.5 ÷ 4 = 4.63

The weighted average has smaller errors (MAD 4.63 versus 5.33), because it gives more weight to the most recent month.
c) 3-month moving average: (50 + 53 + 55) ÷ 3 = 52.67. Weighted: 0.2 × 50 + 0.3 × 53 + 0.5 × 55 = 10 + 15.9 + 27.5 = 53.4. Line: with X = 1 to 7, ΣX = 28, ΣY = 333, ΣX² = 140, ΣXY = 1,402; a = (7 × 1,402 − 28 × 333) ÷ (7 × 140 − 28²) = (9,814 − 9,324) ÷ 196 = 490 ÷ 196 = 2.5; b = (333 − 2.5 × 28) ÷ 7 = 263 ÷ 7 = 37.57; Y(8) = 37.57 + 2.5 × 8 = 57.57.
d) All the errors of both averages are positive: they always forecast below actual demand, the typical lag of averages in the face of an upward trend (demand grows by about 2.5 a month). The weighted average reduces the lag but does not eliminate it. For month 8, the forecast most consistent with the data is the line's, about 58 units.

ما رأيك في هذا المقال؟

0%
تقييم المستخدمين (0)

شارك هذا المقال!

المواضيع previsãodemandasuavizaçãoexponencialregressãomínimosquadradosestoquelogí
إرسال أخبارك