29  Time Series Data and Seasonality Detection

29.1 Why Time Series Matters

Most business data lives in time, and visualisation reveals what summary statistics cannot.

A meaningful share of the data the firm cares about is time series: monthly revenue, daily transactions, hourly web visits, real-time sensor readings. Time-series data carries structures that ordinary cross-sectional data does not — trend, seasonality, cycles, and noise — and analysing it requires a distinct toolkit.

The foundational reference is Time Series Analysis: Forecasting and Control by Box and Jenkins (George E. P. Box & Gwilym M. Jenkins, 1976), which established the formal statistical framework. The modern open practitioner reference is Forecasting: Principles and Practice by Hyndman and Athanasopoulos (Rob J. Hyndman & George Athanasopoulos, 2018), which brought time-series forecasting into the mainstream of business analytics.

For a visualisation-focused book, time series is one of the most important categories of data: virtually every dashboard contains at least one time-series chart, and recognising trend, seasonality, and anomaly is the analyst’s first job before any forecast is produced.

29.2 The Components of a Time Series

flowchart LR
    Y["Observed series<br>Y(t)"]
    Y --> T["Trend<br>Long-run<br>direction"]
    Y --> S["Seasonality<br>Regular cycle<br>(weekly, monthly,<br>yearly)"]
    Y --> C["Cyclical<br>Irregular<br>multi-year<br>swings"]
    Y --> N["Noise / Residual<br>Unexplained<br>variation"]
    style Y fill:#e3f2fd,stroke:#1976D2
    style T fill:#fce4ec,stroke:#AD1457
    style S fill:#fff3e0,stroke:#EF6C00
    style C fill:#fff8e1,stroke:#F9A825
    style N fill:#e8f5e9,stroke:#388E3C

A typical decomposition splits the observed series into four components:

  • Trend — the long-run direction (rising, falling, stable). Often estimated by a moving average or a smoothing technique like loess.
  • Seasonality — patterns that repeat over a fixed period (weekly, monthly, yearly). Festival peaks, weekend effects, daily commute patterns.
  • Cyclical — irregular multi-year swings driven by macroeconomic or business-cycle effects. Distinct from seasonality because the period is not fixed.
  • Noise (Residual) — the variation left unexplained after trend, seasonality, and cycles are removed.

The components combine either additively (Y = Trend + Season + Noise) or multiplicatively (Y = Trend × Season × Noise). The multiplicative form is appropriate when the seasonal swings grow with the trend; the additive form when they are constant in magnitude.

29.3 Visual Diagnostics for Time Series

The visual toolkit for time series:

  • Time-series line chart — the default; shows the raw series.
  • Seasonal-subseries plot — separate line per period (one line per month), showing each period’s trajectory across years.
  • Calendar heatmap — day-of-week × week-of-year coloured by value.
  • Autocorrelation plot (ACF) — correlation between the series and its lagged versions, revealing seasonality and persistence.
  • Decomposition plot — separate panels for trend, seasonal, and residual components.
  • Forecast plot — historical series with point forecast and prediction interval extending into the future.

29.4 Forecasting Methods

TipCommon Forecasting Methods
Method When to Use Notes
Naive / Seasonal Naive Baseline reference Predicts last value (or value from last season)
Moving Average Stable series, no strong trend Smooths short-term noise
Exponential Smoothing (Simple) Stable series without trend or seasonality Single smoothing parameter
Holt’s Linear Trend Trended series without seasonality Two smoothing parameters
Holt-Winters Trended series with seasonality Three smoothing parameters; the basis of FORECAST.ETS
ARIMA General-purpose; trended or stationary series Box-Jenkins methodology
SARIMA Seasonal extension of ARIMA
Prophet Business series with multiple seasonalities and holidays Facebook open-source
Machine learning (LSTM, XGBoost) High-frequency or richly featured series Heavier infrastructure

For dashboard-level forecasting in BI tools, the Holt-Winters family (used by Power BI’s built-in Forecast and Excel’s FORECAST.ETS) covers most practical needs. Heavier methods belong in a dedicated forecasting pipeline.

29.5 Common Pitfalls

  • Ignoring Seasonality: Forecasting a seasonal series with a method that does not account for it; the forecast misses every peak and trough.
  • Spurious Trend: Reading a trend into noise on a short series.
  • Holiday Blindness: Forecasts that ignore holidays, festivals, and calendar irregularities; large errors on those dates.
  • Time-Zone Bugs: Hourly series that span time zones with the boundary unaccounted for.
  • Aggregation Mismatches: Forecasting at the wrong granularity for the question; daily forecasts for a quarterly review introduce noise.
  • No Confidence Interval: Reporting a point forecast as if it were certain.
  • No Holdout Validation: Reporting a model fit on the entire training data as a forecast quality measure.

29.6 Illustrative Cases

Festival-Driven Retail Demand

A retail chain’s monthly revenue shows clear annual seasonality with peaks in October-November (Diwali) and a quieter season in February. A naive forecast misses both peaks. A Holt-Winters forecast with annual seasonality captures them within 5 % accuracy.

Hourly Web Traffic with Weekly Pattern

A digital firm’s hourly web-visit data shows two superimposed patterns: a daily rhythm (low at night, peak in early evening) and a weekly rhythm (higher Saturday and Sunday). A model with both seasonalities outperforms a model with only one.

29.7 Hands-On Exercise: Time Series Decomposition and Forecasting

Aim: Decompose a 36-month revenue series into trend, seasonal, and residual components in Excel; produce a 6-month forecast in both Power BI’s built-in Forecast and Excel’s FORECAST.ETS; and validate against a held-out test period.

Scenario: 36 months of Yuvijen Stores monthly revenue exhibiting trend (rising), seasonality (festival peaks at Diwali), and noise.

Deliverable: An Excel decomposition workbook plus a Power BI report showing the trend-seasonal-residual breakdown and the 6-month forecast with confidence intervals.

29.7.1 Step 1 — The Sample Series

Tipmonthly_revenue.csv (extract)
month revenue_lakh
2023-04 62
2023-05 65
2023-06 68
2023-07 70
2023-08 72
2023-09 78
2023-10 92
2023-11 95
2023-12 80
… (continues through 2026-03) …

The series shows a rising baseline and a sharp October-November peak each year — the classic Indian retail festival pattern.

29.7.2 Step 2 — Visualise the Raw Series in Power BI

  1. Load monthly_revenue.csv via Get Data → Text/CSV.
  2. Mark the date table (Modeling → Mark as Date Table).
  3. Insert a Line Chart with month on the axis and revenue_lakh on values.
  4. Add a Year slicer to allow zooming.
  5. Visually identify the trend (rising baseline) and seasonality (annual peak).

This is the diagnostic step before any decomposition or forecasting.

29.7.3 Step 3 — Decompose in Excel

In Excel, build columns alongside the series:

  1. Trend (12-month centred moving average):

    =AVERAGE(B2:B13)   // for the value at month 7 (centred)

    The first six and last six rows have no centred trend; leave blank.

  2. Detrended series: =B7 - C7 for each row that has a trend value.

  3. Seasonal index: For each calendar month (Apr, May, …, Mar), average the detrended values across all years that have data for that month. Twelve seasonal-index values, one per month.

  4. Reconstruct seasonal column: For every row, look up the seasonal index by month: =VLOOKUP(month_name, season_table, 2, 0).

  5. Residual: =B7 - C7 - E7 (observed minus trend minus seasonal).

Plot the four columns — observed, trend, seasonal, residual — as four separate small-multiple line charts. The decomposition is now visible.

29.7.4 Step 4 — Forecast in Power BI

On a line chart in Power BI:

  1. Hold out the last 6 months of the series for validation by filtering the chart’s date range to exclude those months.
  2. Open the visual’s Format → Analytics pane.
  3. Add Forecast → On.
  4. Set Forecast length: 6 months, Seasonality: 12 months, Confidence interval: 95 %.
  5. Power BI uses exponential smoothing (Holt-Winters) under the covers and renders the forecast band as a shaded area.
  6. Compare the forecast against the held-out actuals — visually overlay or compute MAPE.

29.7.5 Step 5 — Forecast in Excel

Excel’s FORECAST.ETS family handles the same job:

Point forecast for next month:
=FORECAST.ETS(target_date, B2:B31, A2:A31, 1)

Confidence interval:
=FORECAST.ETS.CONFINT(target_date, B2:B31, A2:A31, 0.95, 1)

Detected seasonality:
=FORECAST.ETS.SEASONALITY(B2:B31, A2:A31)

Or use the built-in Data → Forecast Sheet wizard: select the date and value columns, click Forecast Sheet, set the end date and confidence level. Excel produces a chart with the forecast band automatically.

29.7.6 Step 6 — Validate Against Holdout

Compare predicted versus actual for the held-out 6 months:

=ABS(forecast - actual) / actual    // absolute percentage error

Average across the holdout months for MAPE (Mean Absolute Percentage Error). Visualise predicted versus actual on a single line chart with both series; the gap between them is the forecast error in plain sight.

29.7.7 Step 7 — Connect to the Visualisation Layer

Several visualisation patterns covered earlier in the book come together here:

  • The decomposition is best presented as four small-multiple panels — exactly the small-multiples idiom from Chapter 11.
  • The forecast band is a fan chart — a positional encoding of the prediction interval that uses Cleveland-friendly position-on-common-scale.
  • The predicted-vs-actual comparison is a connected scatter or two-line overlay, both established Module 2 patterns.

A serious time-series dashboard pairs every forecast with the diagnostic charts that justify it: a decomposition view, an autocorrelation view, and a holdout-validation view. Without these, the forecast is a single number with no defence.

TipFiles and Screen Recordings

Excel workbook (yuvijen-time-series.xlsx), Power BI file (yuvijen-time-series.pbix), the source monthly_revenue.csv, and screen recordings of the decomposition, Power BI Forecast, Excel FORECAST.ETS, and validation will be embedded here.

Summary

Concept Description
Foundations
Why Time Series Matters Most business data lives in time and visualisation reveals what summary statistics cannot
Time Series Sequence of observations indexed by time, with trend, seasonality, cycle, and noise components
Components of a Time Series
Trend Long-run direction of the series; rising, falling, or stable
Seasonality Patterns that repeat over a fixed period such as weekly or yearly
Cyclical Irregular multi-year swings driven by macroeconomic or business-cycle effects
Noise Variation left unexplained after trend, seasonality, and cycles are removed
Additive Decomposition Y equals trend plus seasonal plus noise; appropriate when seasonal swings are constant
Multiplicative Decomposition Y equals trend times seasonal times noise; appropriate when swings grow with trend
Visual Diagnostics
Time-Series Line Chart Default chart for a time-stamped variable
Seasonal-Subseries Plot Separate line per period showing each period's trajectory across years
Calendar Heatmap Day-of-week by week-of-year grid coloured by value
Autocorrelation Plot Correlation between the series and its lagged versions, revealing seasonality
Decomposition Plot Separate panels for trend, seasonal, and residual components
Forecast Plot Historical series with point forecast and prediction interval
Forecasting Methods
Naive Forecast Predicts the last observed value; baseline for any model
Moving Average Smooths short-term noise; reasonable for stable series
Simple Exponential Smoothing Single smoothing parameter; for stable series without trend or seasonality
Holt's Linear Trend Two parameters; for trended series without seasonality
Holt-Winters Three parameters; for trended series with seasonality; the BI-tool default
ARIMA Box-Jenkins general-purpose method for trended or stationary series
SARIMA Seasonal extension of ARIMA
Prophet Open-source method for business series with multiple seasonalities and holidays
Machine Learning Forecasting LSTM, XGBoost, and similar; for high-frequency or richly featured series
Tools
Excel Trend MA Twelve-month centred moving average to estimate trend
Excel Seasonal Index Average detrended value per calendar month across all years
Excel FORECAST.ETS FORECAST.ETS, FORECAST.ETS.CONFINT, FORECAST.ETS.SEASONALITY
Excel Forecast Sheet Wizard that produces a point forecast with confidence band automatically
Power BI Forecast Built-in Holt-Winters forecast in the Analytics pane of any line chart
Tableau Forecast Built-in forecast in the Analytics pane of any time-axis worksheet
Validation
MAPE Mean Absolute Percentage Error; average absolute percentage forecast error
Common Pitfalls
Ignoring Seasonality Pitfall of forecasting a seasonal series without accounting for seasonality
Spurious Trend Pitfall of reading a trend into noise on a short series
Holiday Blindness Pitfall of forecasts that ignore holidays, festivals, and calendar irregularities
Time-Zone Bugs Pitfall of hourly series spanning time zones with the boundary unaccounted for
Aggregation Mismatches Pitfall of forecasting at the wrong granularity for the question
No Confidence Interval Pitfall of reporting a point forecast as if it were certain
No Holdout Validation Pitfall of reporting a model fit on the entire training data as a forecast quality measure