Time Series
The examples below refer to three years of observed quarterly data.
Forecasts are made for a further four quarters (one extra year).
Level only
| Simple moving average period 5 | |
|---|
| C4: | =AVERAGE(B2:B6) | copy down to C11 |
| C14: | =C$11 | copy down to C17 |
| Centred moving average period 4 | |
|---|
| D4: | =(AVERAGE(B2:B5)+AVERAGE(B3:B6))/2 | copy down to D11 |
| D14: | =D$11 | copy down to D17 |
| Exponentially weighted moving average | |
|---|
| E2: | =B2 | initial level estimate |
| E3: | =$G2*B3+(1-$G2)*E2 | copy down to E13 |
| E14: | =E$13 | copy down to E17 |
The chart was drawn by highlighting B1:B17 and E1:E17 then using Insert > Charts > Line> 2-D Line.
Level and constant trend
| C2: | =FORECAST(A2,$B$2:$B$13,$A$2:$A$13) | copy down to C17 |
Level and changing trend
| C2: | =B2 | initial level estimate |
| C3: | =$F2*B3+(1-$F2)*(C2+D2) | copy down to C13 |
| D2: | =B3-B2 | initial trend estimate |
| D3: | =$G2*(C3-C2)+(1-$G2)*D2 | copy down to D13 |
| E3: | =C2+D2 | copy down to E13 |
| E14: | =C$13+(A14-A$13)*D$13 | copy down to E17 |
Level, changing trend and seasonality
| C5: | =AVERAGE(B2:B5) | initial level estimate |
| C6: | =G$2*B6/E2+(1-G$2)*(C5+D5) | copy down to C13 |
| D5: | =(AVERAGE(B6:B9)-C5)/4 | initial trend estimate |
| D6: | =H$2*(C6-C5)+(1-H$2)*D5 | copy down to D13 |
| E2: | =B2/C$5 | copy down to E5, initial seasonal estimates |
| E6: | =I$2*B6/C6+(1-I$2)*E2 | copy down to E13 |
| F6: | =(C5+D5)*E2 | copy down to F13 |
| F14: | =(C$13+(A14-A$13)*D$13)*E10 | copy down to F17 |
contents
close this window