Example: Method 6: Least Squares Regression
Linear Regression, or Least Squares Regression (LSR), is the most popular method for identifying a linear trend in historical sales data. The method calculates the values for a and b to be used in the formula:
Y = a + b X
This equation describes a straight line, where Y represents sales and X represents time. Linear regression is slow to recognize turning points and step function shifts in demand. Linear regression fits a straight line to the data, even when the data is seasonal or better described by a curve. When sales history data follows a curve or has a strong seasonal pattern, forecast bias and systematic errors occur.
Forecast specifications: n equals the periods of sales history that will be used in calculating the values for a and b. For example, specify n = 4 to use the history from September through December as the basis for the calculations. When data is available, a larger n (such as n = 24) would ordinarily be used. LSR defines a line for as few as two data points. For this example, a small value for n (n = 4) was chosen to reduce the manual calculations that are required to verify the results.
Minimum required sales history: n periods plus the number of time periods that are required for evaluating the forecast performance (periods of best fit).
This table is history used in the forecast calculation:
Past Year |
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 |
None |
None |
None |
None |
None |
None |
None |
None |
131 |
114 |
119 |
137 |
This table is the calculation of Linear Regression Coefficients, given n = 4:
Month and Year |
X |
Y |
XY |
X2 |
---|---|---|---|---|
September |
1 |
131 |
131 |
1 |
October |
2 |
114 |
228 |
4 |
November |
3 |
119 |
357 |
9 |
December |
4 |
137 |
548 |
16 |
Totals (Σ) |
ΣX = 10 |
ΣY = 501 |
ΣXY = 1264 |
ΣX2= 30 |
b = (nΣXY – ΣXΣY) / [nΣX2 – (ΣX)2]
b = [4 (1264) – (10 × 501)] / [4 (30) – (10)2]
b = (5056 – 5010) / (120 – 100)
b= 46 / 20 = 2.3
a = (ΣY / n) – b (ΣX / n)
a = (501 / 4) – [(2.3)(10 / 4)] = 119.5
This table is the Linear Regression forecast for next year, given Y = 119.5 – 2.3 X, where X = 1 >= September of past year 1:
Jan |
Feb |
Mar |
Apr |
May |
Jun |
Jul |
Aug |
Sep |
Oct |
Nov |
Dec |
---|---|---|---|---|---|---|---|---|---|---|---|
131 |
133 |
136 |
138 |
140 |
143 |
145 |
147 |
149 |
152 |
154 |
156 |
January forecast equals 119.5 + (5 × 2.3) = 131.
February forecast equals 119.5 + (6 × 2.3) = 133.3 or 133.
March forecast equals 119.5 + (7 × 2.3) = 135.6 rounded to 136.