For this visulasation, I needed two sets of data:
- ATM/CNS provision costs per ANSPs
- Composite flight hours per ANSPs
Definitions
First the definitions. ATM/CNS provision costs is a metric used in the ACE reports and it excludes MET costs, EUROCONTROL costs and States/NSAs costs from the overal costs equation.
The “composite gate-to-gate flight-hours”considers a “gate-to-gate” perspective, “because the boundaries used to allocate costs between en-route and terminal ANS vary between ANSPs and might introduce a bias in the cost-effectiveness analysis.”
“The “composite gate-to-gate flight-hours” are determined by weighting the output measures by their respective average cost of the service for the whole Pan-European system. This average weighting factor is based on the total monetary value of the outputs and amounts to 0.27.”
Sourcing the data
Since I wasn´t able to find the standalone dataset on the ATM/CNS provision costs, I had to extract it from the yearly ACE reports (2016-2022), available in PDF.
To extract the tables, I used Power Query, similar to the process I wrote down in this article.
For each year the table with the data was situated at a different page, so I couldn´t fully automate the process, however after selecting the correct page, all the following steps of cleaning the data was identical.
This way I had the 5 queries (5 tables) extracted quickly and I could append them into a single query.
Composite flight hours – data verification
At first I started calculating the composite flight hours based on the figures for the en-route and airport arrival delays from the En-route IFR flights and ATFM delays and Airport arrival ATFM delays – (no post ops adjustments) datasets.
However I noticed they are already available in the yearly ACE reports as well, so I copied them from there.
To be sure, I wanted to verify them and my understanding, so I made calculations at least for a one year, using the formula A + 0.27 * B.
Note the figure in the calculated table and the figure extracted from the ACE report for 2020.