Расчет доходности рейсов - сопоставление доходов от перевозки с затратами на выполнение рейса

Расчет доходности рейсов является ключевым элементом управленческого учета в логистике и авиации. Он объединяет финансовую аналитику и оперативную информацию о перевозке - от пассажирских билетов и фрахта грузов до затрат на топливо, экипаж и обслуживание воздушного судна. В рамках BI DWH задача состоит не только в вычислении чистой прибыли по каждому рейсу, но и в построении устойчивой архитектуры данных, поддерживающей сравнение различных сценариев, мониторинг изменений и оперативную корректировку бизнес-моделей.

Эта глава сочетает теоретические основы и практические подходы к реализации в корпоративной среде. Рассматриваются архитектура данных, методы учета затрат, принципы агрегирования и контроль качества данных, а также сценарии внедрения в составе единой платформы BI и DWH. Особое внимание уделяется балансированному подходу: с одной стороны - точным расчетам и архитектурной прозрачности, с другой - управленческим процессам, организационным изменениям и сценарному анализу.

Ключевые концепции расчета доходности рейсов, их связь с моделями затрат и источниками дохода.
Архитектура DWH для учета доходности: фактовые таблицы, измерения, агрегации и управление качеством данных.
Практики интеграции данных из разных систем, методы распределения затрат и единая метрическая база.
Алгоритмы расчета и валидирования: от простого вычитания до продвинутого распределения затрат через ABC и альтернативные подходы.
Как представить результаты в BI: показатели производительности по рейсам, маршрутам и флоту, сценарный анализ и управленческие отчеты.

Краткое содержание главы

Архитектура данных и модель измерений для расчета доходности рейсов.
Источники данных, их интеграция, качество и согласование валют.
Методы расчета затрат и доходов, включая распределение косвенных затрат.
Алгоритмы расчета прибыли, валидирование и контроль данных.
Аналитика, визуализация и сценарии внедрения в операционные процессы.

Архитектура и моделирование данных

Расчет доходности рейсов опирается на хорошо продуманную модель данных. В ходе проектирования следует выбрать архитектуру, которая обеспечивает устойчивость к изменениям бизнес-модели, поддерживает расширяемость по масштабам флотилии и маршрутам, а также позволяет оперативно переключаться между разными методиками учета затрат.

Основные принципы архитектуры:

Стартовая концепция - звездная схема (star schema) с центральной фактной таблицей, где хранятся агрегаты по рейсам, и набором размерностей: рейс, маршрут, дата, самолёт, аэропорт. В качестве альтернативы можно рассмотреть легковесную снежную схему или гибрид Data Vault для гибкости хозяйственных изменений.
Фактовые таблицы: Revenue и Cost как отдельные факты или как разделенные строки в одной агрегированной таблице. Это позволяет быстро строить показатели, такие как общий доход, затраты и прибыль по рейсу, маршруту и дате.
Измерения (dimension tables): Flight, Route, Aircraft, Date, Airport и Currency. В контексте доходности особенно важно хранить валюты и курсы конверсии для корректного сравнительного анализа.
Агрегации и уровни детализации: дневной уровень (flight_date), рейс/маршрут/самолёт, а также агрегаты по недельному и по месячному уровням. Материализованные представления и кэшируемые предикаты помогают ускорить запросы в BI.
Для распределения затрат применяются методологии ABC (Activity-Based Costing) и альтернативные подходы на основе часов полета, цикла обслуживания или блок-часов. Выбор зависит от структуры затрат авиаперевозчика и доступности драйверов затрат.
Управление изменениями: поддержка Slowly Changing Dimensions (SCD) для маршрутов, самолётов и базовых тарифов. Это обеспечивает корректную ретроспективную аналитику и прозрачность в отчётности.
Интеграции и управление данными: на уровне архитектуры целесообразно применить ELT-подход, dbt-трансформации и оркестрацию через Airflow или Prefect. В качестве слоя представлений - OLAP для быстрой агрегации, а для детального анализа - Data Lake с возможностью гибкого хранилища и версионирования данных.

Пример структуры модельной схемы (без подробной спецификации всех полей):

Факты: revenue_fact (flight_id, date_id, revenue_amount, currency_id, revenue_driver), cost_fact (flight_id, date_id, cost_amount, currency_id, cost_driver)
Размерности: flight_dim (flight_id, aircraft_id, route_id, departure_airport, arrival_airport, date_id), route_dim (route_id, origin, destination, distance), aircraft_dim (aircraft_id, model, capacity), date_dim (date_id, day, week, month, quarter, year), currency_dim (currency_id, code, rate_to_base)

Почему важна такая архитектура:

Она обеспечивает единое основание для расчета прибыльности по рейсам и маршрутам, а также разрешает многократное использование одних и тех же измерений в разных отчетах.
Она упрощает внедрение новых методик учета затрат (ABC, driver-based) без радикальной переработки существующей модели.
Она поддерживает качественную агрегацию и точное сравнение между периодами, флотами и маршрутами, что критично для управленческих решений.

В качестве примера можно рассмотреть упрощённую схему звездой: факт Revenue и факт Cost связаны с измерениями Flight, Route и Date. Это облегчает построение аналитических запросов и визуализаций, таких как прибыль по рейсу за день или по маршруту за месяц.

-- Пример запросa: прибыль по рейсу за конкретный день
SELECT
  f.flight_id,
  r.origin AS origin,
  r.destination AS destination,
  SUM(rr.revenue_amount) AS revenue_sum,
## SUM(cr.cost_amount) AS cost_sum,
  SUM(rr.revenue_amount) - SUM(cr.cost_amount) AS profit
## FROM flight_dim f
JOIN revenue_fact rr ON rr.flight_id = f.flight_id
JOIN cost_fact cr ON cr.flight_id = f.flight_id
JOIN route_dim r ON r.route_id = f.route_id
## WHERE f.flight_date = DATE '2025-07-15'
GROUP BY f.flight_id, r.origin, r.destination
ORDER BY f.flight_id;

Источники данных, интеграция и качество данных

Эффективность расчета доходности рейсов во многом зависит от качества и полноты данных. В рамках BI DWH целевые данные собираются из нескольких источников: планирование и продажи (билеты, фрахт), операционные системы (показатели полета, эксплуатационные журналы), финансовые модули (попытки учёта затрат), а также внешние данные (курсы валют, регуляторные сборы).

Ключевые аспекты интеграции:

Единая временная ось: Date и DateTime, унифицированная временная зона и единицы измерения времени для всех источников данных. Это критично для сопоставления доходов и затрат по рейсу и маршруту.
Валюта и конвертация: хранение курсов валют и базовой валюты, возможность пересчета в базовую валту для корректного сравнения. В рамках многих компаний в качестве базовой валюты применяется USD или локальная валюта материнской компании.
Источник и трассируемость: связь данных с исходными системами (ERP, TMS, CMS, тарификатор) через поля lineage и версионирование. В идеале - автоматическое отслеживание изменений (data lineage) и журнал изменений (audit logs).
Качество данных: полнота, непротиворечивость и своевременность. Важно устанавливать SLA на обновления, тесты целостности и автоматизированные проверки на несоответствия значений (например, несоответствие суммы доходов и сумм по доказательствам).
Управление кодами затрат и драйверами: единый справочник затрат (cost_driver) позволяет сравнивать сценарии и переносить вычисления между проектами и маршрутами.

Качество данных можно удерживать через:

Валидирующие правила на этапе загрузки (например, сумма затрат не может быть ниже нуля).
Контроль дубликатов по ключам рейса и дате.
Валидацию целостности между Revenue и Cost по каждому рейсу.
Мониторинг времени задержки данных и уведомления об отклонениях.

Современный стек для реализации часто включает:

Хранилище: PostgreSQL, ClickHouse или Snowflake в зависимости от требований к скорости и объему.
Инструменты ELT/ETL: dbt для трансформаций, Airflow или Prefect для оркестрации процессов загрузки.
Источники и интерфейсы: интеграция через API, EDI, пакетные загрузки, файлы CSV/Parquet.
Визуализация: Power BI, Tableau или Looker для оперативной аналитики.

Методы расчета и учет затрат

core-идеи:

Доходы по рейсу включают все денежные поступления, связанные с перевозкой: base fare, платные сервисы, сборы за обслуживание, фрахт за перевозку грузов.
Затраты делятся на прямые (непосредственно связанные с выполнением рейса) и косвенные (распределяемые по драйверам).
Распределение косвенных затрат требует выбор драйвера затрат: часы полета, часы блока, число взлетно-посадочных операций, пассажиро- или тонна-мили.

Методология расчета:

Прямые затраты, такие как топливо, экипаж, техническое обслуживание, авиационные сборы, должны привязываться к конкретному рейсу или быть распределены пропорционально драйверам (например, топливо - по расстоянию полета, экипаж - по кадрам за рейс).
Косвенные затраты, такие как амортизация самолета, персонал управления флотом, страхование, общие административные расходы, распределяются по драйверам, отражающим активность рейсов: блок-часам, доступной мощности (ASMs), расстоянию или количеству выполненных рейсов.
Методы распределения затрат требуется документировать и версионировать. Часто применяется ABC как более точный подход, хотя он требует дополнительных данных и процессов.
Метрика RASM (Revenue per Available Seat Mile) и CASM (Cost per Available Seat Mile) позволяют сравнивать прибыльность рейсов не только в абсолютных величинах, но и в динамике относительно загрузки и емкости флота.
BM/-метрики: чистая прибыль по рейсу, маржа по рейсу, маржа по маршруту, годовая/месячная динамика по флоту и маршрутам. Сравнение по фактору «плотность загрузки» (load factor) дает дополнительную информацию о цене на рынке и эффективности использования мощности.

Типовые расчеты в рамках DWH:

Profit_by_flight = Revenue_by_flight - Cost_by_flight
Profit_by_route = SUM(Profit_by_flight) по маршруту за период
Profit_per_flight_hour = Profit_by_flight / Block_hours
Revenue_yield_by_route = Revenue_by_route / Distance_by_route

Алгоритм реализации:

Подготовьте данные: загрузите рейсы, маршруты, даты, валюты и курсы; нормализуйте единицы величин.
Приведите доходы и затраты к общей валюте, учтите курсовые разницы за период.
Распределите косвенные затраты через выбранный драйвер (например, по блок-часам или по расстоянию).
Рассчитайте показатели прибыли на рейс, на маршрут и на флот в нужном разрезе.

Примените проверки целостности и валидируйте результаты на исторических данных.

-- Пример запроса по распределению затрат и расчете прибыли по рейсу
## WITH revenue AS (
  SELECT flight_id, SUM(revenue_amount) AS revenue_amount, currency_id
  FROM revenue_fact
  GROUP BY flight_id, currency_id
),
cost AS (
  SELECT flight_id, SUM(cost_amount) AS cost_amount, currency_id
  FROM cost_fact
  GROUP BY flight_id, currency_id
),
conversion AS (
  SELECT currency_id, rate_to_base AS rate
  FROM currency_dim
)
SELECT
  f.flight_id,
  r.origin AS origin,
  r.destination AS destination,
  (COALESCE(revenue.revenue_amount, 0) * co.rate) AS revenue_base,
  (COALESCE(cost.cost_amount, 0) * co.rate) AS cost_base,
  (COALESCE(revenue.revenue_amount, 0) * co.rate) - (COALESCE(cost.cost_amount, 0) * co.rate) AS profit_base
## FROM flight_dim f
JOIN route_dim r ON r.route_id = f.route_id
LEFT JOIN revenue AS revenue ON revenue.flight_id = f.flight_id
LEFT JOIN cost AS cost ON cost.flight_id = f.flight_id
JOIN currency_dim c ON c.currency_id = revenue.currency_id OR c.currency_id = cost.currency_id
JOIN conversion co ON co.currency_id = c.currency_id
## WHERE f.flight_date = DATE '2025-07-15'
GROUP BY f.flight_id, origin, destination, revenue.revenue_amount, cost.cost_amount, co.rate;

Важная дисциплина - верификация и валидирование:

Регулярная сверка сумм Revenue и Cost на уровне рейса и на уровне маршрута.
Контроль финансовой валидности: соответствие общих счетов и дорожных расходов требованиям регуляторов и учетной политики.
Внедрение прогнозных сценариев: тестирование чувствительности прибыли к изменениям тарифов, загрузке, цене топлива и другим драйверам.

Аналитика и визуализация прибыли

После построения модели данных и реализаций расчета следует организация визуализации и сценарного анализа. BI-инструменты должны позволять управленцам быстро получать ответы на вопросы: где рейс приносит наибольшую прибыль, какие маршруты эффективнее в определенные периоды, как влияет загрузка на маржу, и какие резервы по затратам можно реализовать.

Контроль качества данных и внедрение

Успешная реализация требует прочной дисциплины в вопросах качества данных и управления изменениями:

Управление качеством: определение геометрии данных, частоты обновления, SLA, тестовые кейсы на полноту и непротиворечивость.
Линейность и прослеживаемость: трассировка источников данных до финальной метрики. Любое изменение методики расчета должно сопровождаться регламентом версий и регрессионным тестированием.
Валидирование сценариев: проверка устойчивости расчетов на исторических периодах, сравнение с внешними отчетами, ручной аудит в критических случаях.
Организация изменений: внедрение новой методики учета затрат или измененных тарифов требует согласования между финансовым, операционным и IT-отделами, а также четкой коммуникации у пользователей BI.
В плане инструментов: саккумулирование изменений в Git-проектах dbt и хранение версий схем данных, автоматизированные тесты, несложные регрессионные тесты для основных KPI, уведомления об аномалиях и сбоях обновлений.

Применение в реальных условиях: сценарии внедрения

Этап 1. Оценка текущей архитектуры и сбор требований: определить размер флота, спектр маршрутов, источники данных и регуляторные требования; сформировать перечень KPI.
Этап 2. Проектирование модели данных: выбрать архитектуру star или гибрид; определить фактные таблицы Revenue и Cost, размерности Flight, Route, Date, Aircraft и Currency.
Этап 3. Реализация и тестирование: развернуть ELT-пайплайны, настроить трансформации в dbt, обеспечить данные курсами валют и проверками целостности.
Этап 4. Внедрение анализов и отчетности: построить дашборды и сценарные модули; внедрить автоматическое уведомление о значимых отклонениях.
Этап 5. Управление и эволюция: регулярно обновлять драйверы затрат, поддерживать версионность расчетов и обучать пользователей новым методикам.

Key takeaways

Расчет доходности рейсов требует единой архитектуры данных, объединяющей доходы и затраты на уровне рейса, маршрута и времени.
Прямые и косвенные затраты должны распределяться по выбранной методике, при этом ABC часто обеспечивает более точную картину прибыльности, но требует дополнительных данных.
Ключ к эффективной аналитике - качественные данные, единая валюта и согласованность между источниками и измерениями в DWH.
Архитектура DWH должна поддерживать как детальную аналитику по рейсам, так и управленческий обзор по маршрутам и флоту через эффективные агрегаты.
Визуализация и сценарный анализ должны быть встроены в операционные процессы, чтобы руководители могли оперативно принимать решения.
Контроль качества данных и регламенты изменений являются необходимыми условиями доверия к расчетам прибыльности.
Технологический стек должен сочетать мощные хранилища данных, современные ETL/ELT-подходы и удобные BI-инструменты, сохраняя при этом прозрачность и управляемость данных.

FAQ

Что именно называют прибыльностью рейсов в контексте BI DWH?

Прибыльность рейсов - это разность между совокупными доходами, связанными с выполнением рейса (пассажирские билеты, грузовой фрахт, доп. услуги) и связанными затратами (прямые расходы на рейс и распределяемые косвенные). В рамках DWH это рассчитывается на уровне рейса и маршрута, с возможностью детализации по датам, самолетам и драйверам затрат.

Какие данные необходимы для расчета и какие источники чаще всего используются?

Необходимы данные о рейсах (flight_id, date, route, aircraft), доходах (revenue_fact: сумма по рейсу, валюта), затратах (cost_fact: сумма затрат и драйвер затрат), а также справочники (route_dim, currency_dim, date_dim). Источники обычно включают продажи билетов, фрахт грузов, эксплуатационные журналы, финансовый учет и курсы валют.

Как выбрать метод распределения косвенных затрат?

Выбор зависит от структуры затрат и доступности драйверов. ABC дает точную картину, если есть достаточные данные о драйверах активности. В случаях ограниченной детализации чаще применяют более простые драйверы - блок-час, расстояние или доступную мощность. Важно документировать методику и поддерживать её версионирование.

Как обеспечивать качество и согласованность данных по нескольким валютам?

В базовой валюте вычислений следует хранить курсы валют и конвертации, обновляемые на период. Все расчеты в DWH должны выполняться в единой базовой валюте, чтобы предотвратить искажения из-за курсов. Регулярно проводятся проверки консистентности, сверки расходов и доходов по рейсам.

Какие KPI и показатели полезны для управленческого анализа?

RASM (Revenue per Available Seat Mile), CASM (Cost per Available Seat Mile), occupancy/load factor, прибыль на рейс, маржа по маршруту, прибыль по флоту, прибыль по часовому блоку рейса. Важна поддержка сценариев: изменение цен, загрузки, структуры затрат.

Какие технологии часто применяются для реализации BI DWH в таком контексте?

В качестве хранилища - PostgreSQL, ClickHouse или Snowflake; для ETL/ELT - dbt и Airflow; для визуализации - Power BI или Tableau. Выбор зависит от объемов данных, скорости запросов и бюджета. Важно не перегружать процессорами и помнить о существующей инфраструктуре.

Как внедрить расчет доходности в организацию?

Необходимо четко определить бизнес-слои, вовлечь финансовый, операционный и IT-специалисты, определить требования к регуляторам и внутренним политикам. Ранняя демонстрация ценности (быстрые победы по нескольким маршрутам) помогает закрепить поддержку. Обязателен план управления изменениями и обучение пользователей.

Как учитывать валютные курсовые различия в расчетах?

В рамках расчета прибыльности курс валют применяется на момент свода данных к базовой валюте. Важно фиксировать дату курса и приводить доходы и затраты к общей валюте. В случае многовалютной аналитики следить за конверсионной политикой и аудитами, чтобы сравнение между периодами было корректным.

Как обеспечить валидность расчетов в исторической ретроспективе?

Необходимо хранить версии методики и версии справочников, а также реализовать регрессионное тестирование на исторических данных. Валидации должны включать сверку итоговых KPI с внешними отчетами и самопроверку коэффициентов по драйверам затрат.

Какие подходы помогут адаптировать решение под рост бизнеса?

Модульная архитектура и версионирование: добавление новых маршрутов, самолётов и тарифов не требует переработки существующей модели. Расширение драйверов затрат, более детальная ABC-модель и расширение времени агрегации позволяют адаптироваться к росту без потери управляемости.

Эта глава ориентирована на инженеров данных, архитекторов BI и менеджеров, ответственных за финансовую механику перевозок и логистику. В balансе технических деталей и управленческой практики представлены принципы построения DWH, методы расчета и сценарного анализа, которые можно адаптировать под конкретную организационную структуру и бизнес-мотребности.

← Предыдущая статья

Контроль выполнения договорных объемов перевозок - анализ соответствия фактических перевозок согласованным объемам по контрактам

Следующая статья →

Расчет доходности маршрутов - анализ прибыли по направлениям перевозок