Statewide recycling-program reporting and forecasting: container sales forecasts across 56 series, payments, accruals, KPI tracking, and validation that holds up to review.
The short version
Fund
$1.5B
Series forecast
56
Glass forecast miss cut
83%
The improvement, drawn
Before
Models picked by hand and kept because they had been used before, on judgement dating back to 2017.
After
Every candidate scored on twelve months it has never seen; the winner recorded in a model registry with the reasoning.
As Senior Data Specialist at CalRecycle, I analyse statewide Beverage Container Recycling Program data for fiscal impact estimates, compliance reporting, operational reporting, executive decision-making, and ad hoc Public Records Act analyses. The program sits on the Beverage Container Recycling Fund, roughly $1.5 billion of public money.
Twice a year the program forecasts container sales and redemption weights across 56 series, covering every material type from aluminium and PET to the glass streams that grew when SB 1013 brought wine and spirits containers into the program. Those forecasts feed payment planning and the fund condition reporting that CalRecycle publishes in its semi-annual reports on the Beverage Container Recycling Fund.
The pipeline runs in five phases: extraction from the program's source systems, preparation into tidy series, model fitting, assembly into a fifteen-sheet forecasting workbook, and a written analysis memo. I ported the production pipeline from Python to R so analysts can run either language from a single cycle configuration, and wrote the validation harness that checks the two agree before a cycle ships. The port matches the Python pipeline to within 0.1% across all series.
The old process picked ARIMA models by hand, on a rationale dating back to 2017. I replaced that with systematic testing: every candidate model is scored on twelve months of data it has never seen, and the winner has to earn its place. When SB 1013 added wine and spirits bottles to the program, glass returns stopped following their own history, and models that only looked at past returns kept missing. Bottles come back roughly a year after they are sold, so I built a model that forecasts returns from the prior year's sales instead. It cut the forecast miss on glass by 83% against the best standard model, and the selection rule improved the whole suite's average miss by a quarter. Which model each series gets, and why, is written down in a model registry rather than kept in anyone's head.
Program payments are calculated from multi-year source files and published in reporting that has to hold up to review, in audits, and in Public Records Act requests. When a displayed total is questioned, I need to show which source file, which reporting year, and which calculation produced it, and I need to be sure a total cannot exceed the payment cap.
SQL, Access, Excel, and Power BI workflows covering recycling rates, year-end accruals, contingent receivables, processing payments, KPI tracking, validation, reconciliation, and quality assurance.
Calculation logic is kept separate from the validation checks, so a reviewer can see the controls without untangling the maths. Totals that could exceed the payment cap are flagged before publication. Source files, reporting years, and displayed metrics are mapped explicitly, so any figure can be traced back to its source.