From 2019 to 2025, as cost per seat mile excluding fuel grew 36% against 16% for revenue per seat mile.
PROJECT 05 / Financial Planning · Forecasting · Network Analytics
Delta Air Lines FP&A and Network Analytics
A reproducible FP&A model of Delta Air Lines built only from public filings. It joins SEC financial statements, DOT Form 41 airline accounts and BTS T-100 flight data, rebuilds operating income from seven operating drivers, and serves the results in a live Streamlit dashboard with a route network map and a Tableau Public extract.
DATASETSEC CompanyFacts, DOT Form 41 and BTS T-100 segments
SIZE2019 to 2026 · 3,338 city pairs in the latest 12 months
SOURCESEC EDGAR · U.S. DOT Bureau of Transportation Statistics
MEASURED EVIDENCE
What can be verified.
Metrics and outputs drawn from the project artifacts—not estimates added for presentation.
Same quarter last year forecasting won a rolling origin backtest of 34 forecasts, one to four quarters ahead.
Effect on the next four quarters of mainline operating income, against $0.02B for 1% more capacity.
DL's Form 41 operating revenue equals 100.0% to 100.4% of SEC consolidated revenue in every quarter.
VALIDATION
One validator checks the single SQLite build: Form 41 revenue within 2% of SEC revenue each quarter, profit bridges that close, driver model actuals that match unit economics, a true trailing twelve month route window, and map coverage of at least 99% of passengers. Forecast methods are compared in a rolling origin backtest from Q4 2023, with earlier origins excluded because their trends come from the pandemic recovery.
PLAIN-LANGUAGE INTERPRETATION
The model's value is in the decomposition, scenarios and bridge, not in beating a naive baseline on point accuracy. Forward values are modeled from public data and are not Delta guidance.
Why this project matters
Airline results are reported in different places at different scopes: consolidated SEC statements, carrier accounts filed with DOT, and flight level traffic data. The question was how well Delta turns capacity into revenue and margin, which routes and airports drive its traffic since 2019, and what the next year looks like under different capacity, fuel and cost assumptions.
What was developed
A standard library Python pipeline loads the three sources into one SQLite database and one validator. Quarterly operating income is rebuilt exactly from seven drivers: seat miles, load factor, yield, other revenue, cost per seat mile excluding fuel, fuel efficiency and fuel price. A year over year bridge splits income changes by driver through sequential substitution. Eight forecasting methods were compared in a rolling origin backtest, and the simplest, carrying each driver forward from the same quarter a year earlier, became the base case. Scenarios and driver sensitivities run on top of that base.
What it means
Mainline revenue per seat mile rose 16% from 2019 to 2025 while cost per seat mile excluding fuel rose 36%, and mainline operating margin fell from 15.1% to 4.7%. The domestic network carries most of that squeeze. In the backtest the same quarter last year method had the lowest error, 3.96% on revenue across 34 forecasts, beating every trend method. One point of load factor is worth $0.64B a year while 1% more capacity adds only $0.02B, because at current margins an extra seat mile earns barely more than it costs.
04 / WHERE THIS WORK APPLIES
From project to practical use.
The driver model, bridge and scenario lab fit airline and transport FP&A, budget versus actual reviews, investor and credit analysis, network planning discussions, and any business where revenue and cost can be expressed as volume, rate and unit cost drivers.
05 / LIMITATION
What the project does not solve yet.
Mainline measures exclude Delta Connection partner flights, which report under their own carrier codes, so some short route growth may be flying moved onto Delta's own aircraft rather than new demand. Unit measures are public figures matched to one scope, not Delta's investor relations TRASM and CASM. Budget, forecast and scenario values are models built on public actuals, not Delta guidance, and the fuel scenario holds price flat with no fare response.
06 / NEXT ITERATION
Future advancement
Replace the flat fuel scenario with a jet fuel futures curve, add partner carriers to test how much route growth is shifted flying, and publish the Tableau Public workbook built from the committed extract.
TECHNICAL TOOLKIT
PythonSQLitePandasStreamlitPlotlyTableauGitHub ActionsSEC CompanyFactsDOT Form 41BTS T-100Driver Based PlanningSequential Substitution BridgeSeasonal Naive ForecastingRolling Origin BacktestingScenario AnalysisSensitivity Analysis