Fri Feb 13

Forecasting

Forecasting System — Technical Documentation

Overview

The forecasting system forecasts future member spending (turnover) for deals (PSAs/arrangements) based on historical data. It uses a statistical model combining historical averages, seasonal adjustment, year-over-year growth trends, and a self-correcting accuracy feedback loop. Projected turnover then feeds into rebate calculations so users can see estimated future rebate payments alongside actual ones.

The Forecasting Formula

Each projected monthly value is calculated as:

projected_value = avg_historical_value
                × seasonality_factor
                × POWER(1 + annual_growth_rate, months_ahead / 12.0)
                × correction_factor

Component Breakdown

1. avg_historical_value

The arithmetic mean of all actual turnover values for a given member + supplier + deal + figures entry combination, across all historical periods. This establishes the baseline “typical monthly spend” for each member-supplier relationship.

Only members with activity in the last 12 months are included — if a member hasn’t had any spend data in a year, they are excluded from projections entirely (assumed inactive). Members with specified leave dates also have restricted projections - see further below for details.

2. seasonality_factor

A per-calendar-month multiplier that captures seasonal spending patterns. Calculated as:

seasonality_factor(month) = AVG(value for that month) / AVG(value across all months)

A factor of 1.2 for June means June spending is typically 20% above the annual average. A factor of 0.8 for December means December is typically 20% below.

Calculated across all turnover in the deal’s categories for the entire site, not per-member. This gives a more statistically robust seasonal signal.

3. annual_growth_rate

The average year-over-year growth rate across the deal’s product categories. Computed by:

  1. Summing total turnover by calendar year.
  2. Using LAG() to get the previous year’s total.
  3. Computing (this_year - last_year) / last_year for each year transition.
  4. Averaging all YoY growth rates.

The growth is applied as compound interest over fractional years via POWER(1 + rate, months_ahead / 12.0). A projection 6 months out applies half a year’s growth; 18 months out applies 1.5 years.

Safety cap: If the calculated growth rate exceeds ±50%, it is assumed to be bad data and reset to 0% (flat projection). This prevents a single anomalous year from producing extreme projections.

4. correction_factor

A self-correcting bias adjustment derived from comparing past projections against actual outcomes. This is the feedback loop that makes projections improve over time.

correction_factor = AVG(actual_value / projected_value)

If projections have been systematically 15% too high, the average ratio will be ~0.85, meaning future projections will be multiplied by 0.85 to compensate.

Safeguards:

  • Minimum data threshold: Requires at least 6 completed comparison periods before applying any correction. New deals or deals with limited history get a factor of 1.0 (no adjustment).
  • Cap: Clamped between 0.5 and 1.5 to prevent extreme corrections from bad or anomalous data.
  • Error handling: Any error in the correction calculation falls back to 1.0 silently (with error logging).

Member Lifecycle Handling

The projection engine respects member leave dates. For each member in the deal, it looks up company.leaveDate:

  • If a member has a valid leave date, projections stop at that month — no projected spend is generated for months after the member leaves.
  • If a member has no leave date (or 0000-00-00), projections continue to the deal’s period_to date.

Projection Date Generation

Future months are generated using a recursive CTE that starts from the month after the last actual turnover entry and increments month by month up to the deal’s period_to date:

Accuracy Tracking

How Accuracy Is Measured

Variance formula:

variance% = (actual - projected) / projected × 100
  • Positive variance = under-projected (actual exceeded forecast)
  • Negative variance = over-projected (forecast exceeded actual)

Accuracy bands:

  • Within ±5% = accurate
  • Within ±10% = acceptable
  • Beyond ±5% = over/under-projected

The Self-Correction Loop

The accuracy data feeds back into projections:

projectionHistory (past predictions)
        ↓
    JOIN with turnover (actual outcomes)
        ↓
    AVG(actual / predicted) = correction_factor
        ↓
    Applied to next projection run
        ↓
    New projections stored → snapshotted to projectionHistory
        ↓
    (cycle repeats)

Each projection run automatically incorporates the cumulative learning from all prior prediction/outcome comparisons. The more history accumulates, the more data the correction factor has to work with, and the more accurate projections become.

Example Scenario

A deal runs from Jan 2024 to Dec 2025. It’s now July 2025. Actual turnover exists through June 2025.

  1. storeMonthlyProjections() is called for this deal.
  2. The system finds the last actual data is June 2025.
  3. Existing projections are snapshotted to projectionHistory with snapshotDate = now() and monthsAhead values of 1-6 (for Jul-Dec 2025).
  4. turnoverProjection is cleared and rebuilt with:
    • Actual rows from Jan 2024 – Jun 2025 (copied from turnover)
    • Projected rows for Jul 2025 – Dec 2025 (calculated)
  5. For the projections, suppose:
    • Member A’s historical average monthly spend is £10,000
    • The September seasonality factor is 1.15 (15% above average)
    • The annual growth rate is 5% (0.05)
    • The correction factor is 0.92 (projections have been 8% too high historically)
    • September is 3 months ahead
  6. Member A’s projected September 2025 value: 
    £10,000 × 1.15 × POWER(1.05, 3/12) × 0.92
= £10,000 × 1.15 × 1.0123 × 0.92
= £10,714
  7. When actual September data comes in later, the system can compare £10,714 against the real value, and this comparison feeds into future correction factors.