Home / Statistical Tools / Analysis Tools / Time Series / How-To
How-To¶
The six Time Series analyses share two dialogs. Trend Analysis, Moving Average and Double Exponential Smoothing open the Time Series Smoothing window; Autocorrelation, Partial Autocorrelation and Cross Correlation open the Time Series Correlation window. Which analysis runs is fixed by the ribbon entry you clicked, not by anything inside the dialog.
The first four walkthroughs below all use the same twelve-month demand series, so you can run them one after another and compare what each one does with the same numbers.
Quick Start¶
Fit a trend line and forecast two periods, in under two minutes.
Goal¶
Fit a straight line to twelve months of demand, read the fitted equation and the accuracy measures, and forecast the next two months.
Sample Data¶
Twelve monthly demand figures. Press Copy for Excel, then paste into a blank worksheet.
| Demand |
|---|
| 129.0 |
| 136.1 |
| 132.5 |
| 132.6 |
| 136.4 |
| 150.2 |
| 148.8 |
| 151.9 |
| 169.4 |
| 174.9 |
| 183.8 |
| 179.6 |
There is no date column and none is needed. Every Time Series analysis works on row order alone: row 1 is period 1, and the Index column in the output is that position, not a date.
Steps¶
-
Put the data in Excel
Press Copy for Excel above the table, click cell A1 in a blank worksheet, and press Ctrl+V. The header lands in row 1 and the twelve figures in rows 2 through 13.
-
Launch the analysis
From the Excel ribbon, select QXL Stat Tools → Analysis Tools → Time Series → Trend Analysis.
-
Select your data
Select cells A1:A13, the header row plus all twelve rows.
-
Configure the analysis
On the Data tab, tick Demand under Data Columns. Then open the Options tab, which shows the Trend Analysis Options group:
- Linear, which is the default, or Quadratic
- Number of Forecasts:, which starts at 0
Leave Linear selected and set Number of Forecasts: to 2. Click Finish.
Result¶
Quantum XL writes a worksheet named Trend Demand. The tab name is not the analysis name: Trend Analysis abbreviates to Trend, and the column name is appended when exactly one column was selected.
The equation¶
The Equation table holds the fitted line:
y = 117.7409 + 5.2860x
Read x as the period number. The line starts at 123.03 in period 1 and climbs 5.286 per month.
Accuracy Measures¶
| Metric | Value |
|---|---|
| MAPE | 3.4591 |
| MAD | 5.1333 |
| MSD | 33.3018 |
MAD is the average size of the miss, in the same units as the data, so the line is out by about 5.13 units a month on average. MAPE is that miss as a percentage of the actual value. MSD is the average squared miss, which grows faster than MAD when a single period is badly missed.
All three cover the twelve fitted periods only. Forecast rows have no actual value to be compared against and never enter these averages.
The data table¶
Columns are Index, Actual, Predicted and Error.
| Index | Actual | Predicted | Error |
|---|---|---|---|
| 1 | 129.0 | 123.0269 | 5.9731 |
| 2 | 136.1 | 128.3129 | 7.7871 |
| 3 | 132.5 | 133.5990 | -1.0990 |
| 4 | 132.6 | 138.8850 | -6.2850 |
| 5 | 136.4 | 144.1710 | -7.7710 |
| 6 | 150.2 | 149.4570 | 0.7430 |
| 7 | 148.8 | 154.7430 | -5.9430 |
| 8 | 151.9 | 160.0290 | -8.1290 |
| 9 | 169.4 | 165.3150 | 4.0850 |
| 10 | 174.9 | 170.6010 | 4.2990 |
| 11 | 183.8 | 175.8871 | 7.9129 |
| 12 | 179.6 | 181.1731 | -1.5731 |
| 13 | 186.4591 | ||
| 14 | 191.7451 |
The two forecast rows sit in the same table, with Actual and Error left blank and the forecast value in the Predicted column. On the chart they continue the fitted line as a dashed segment that starts exactly where the solid one ends.
Things to try next¶
- Switch to Quadratic and re-run. The equation becomes y = 127.7273 + 1.0061x + 0.3292x², MAD improves from 5.1333 to 4.1561, and the two forecasts jump from 186.46 and 191.75 to 196.45 and 206.34. A curve that fits the past a little better can extrapolate very differently.
- Set Number of Forecasts to 24. Nothing stops you. That box has no upper limit, unlike Running Length and Lag Value, which are both clamped against the number of rows you selected.
- Blank out row 7 and re-run. The regression skips that row, but the row keeps its slot: the Index column still counts to 12 and the missing period simply has no Actual and no Error.
More How-Tos¶
- Moving Average: smooth the same series with a trailing window, and see why Error is not Actual minus the number printed beside it
- Double Exponential Smoothing: Holt's method on the same series, and where its starting level and trend come from
- Autocorrelation: measure how strongly a series is related to itself one period back, and further
- Partial Autocorrelation: the same question with the shorter lags divided out
- Cross Correlation: line up two series and find the lag that connects them
- GroupBy: fit each region, line or shift separately, and see what pooling them destroys