Portfolio project — Associate Analyst, Network Planning & Analytics (United Airlines)
United's Network Planning team decides which aircraft flies which route. That choice drives both cost (fuel, crew, gate fees) and revenue potential (seats, fares). This project asks: which of United's current route ↔ aircraft pairings look mismatched, and what would fixing them be worth? This directly mirrors the job posting's mandate to "analyze fleet deployment to maximize revenue and minimize costs."
- Route network: OpenFlights public dataset (routes,
airports, airlines). Filtered to United mainline, non-stop, non-codeshare routes with a US
endpoint → 845 routes across 14 aircraft types.
- Caveat: OpenFlights is a sporadically-updated historical snapshot (last major refresh ~2014), so it reflects United's network structure and aircraft mix directionally, not today's exact schedule (e.g. it still includes the 747-400, which United retired in 2018).
- Cost/revenue benchmarks: United Airlines Holdings FY2025 Form ARS/10-K (SEC EDGAR) — system-average CASM (16.46¢/ASM), TRASM (17.88¢/ASM), load factor (82.2%), and average stage length (1,488 mi).
- Aircraft seat counts: publicly available manufacturer/airline fleet specifications.
- Compute each route's real distance via the haversine (great-circle) formula from airport coordinates.
- Assign each route's primary aircraft from OpenFlights' equipment field.
- Model per-route CASM = system CASM × an aircraft-category cost index (gauge economics — larger aircraft have lower unit costs) × a stage-length adjustment (well-documented in airline economics: unit costs fall as stage length grows, since fixed costs like gate/turn fees spread over more miles).
- Hold revenue (TRASM) at the system average across all routes, to isolate the fleet-cost question the role is scoped around, rather than modeling demand/pricing (which the public data can't support).
- Simulate an operationally realistic "what-if" swap — regional aircraft upgauged to mainline narrowbody, or narrowbody upgauged to widebody only past 2,500 miles — instead of pure cost-minimization (which would unrealistically suggest widebodies on thin, short routes).
| Category | Avg. margin / ASM | Routes |
|---|---|---|
| Widebody | +9.6¢ | 150 |
| Narrowbody | ~break-even | 674 |
| Regional Turboprop | –60.1¢ | 21 |
- Regional Turboprop routes are the clear outlier — an estimated ~$146M/year margin drag across just 21 routes.
- Upgauging the 313 loss-making routes to a realistic best-fit aircraft recovers an estimated ~$90M/year, though most thin regional routes don't fully flip to profitable — suggesting frequency, pricing, or partnership review is the likely next step beyond equipment alone.
| File | Skill shown |
|---|---|
build_analysis.py, aircraft_specs.py, build_charts.py |
Python (pandas ETL, haversine calc, unit-economics model, matplotlib) |
analysis_queries.sql |
SQL (Teradata/Hive-compatible: aggregation, ranking, window functions) |
United_Route_Fleet_Analysis.xlsx |
Excel (formula-driven workbook, 2,500+ live formulas, zero errors, charts) |
United_Route_Fleet_Analysis.pptx |
PowerPoint (6-slide leadership-ready deck) |
ua_route_fleet_analysis.csv |
Underlying processed dataset |
This is a directional demonstration of method, not United's actual internal route P&L. Real per-route fares, load factors, and crew/fuel costs — plus a genuine demand constraint — would be needed to turn this into an operational recommendation. That's exactly the kind of refinement I'd want to make with access to real internal data.