I. Начисление бонусных комиссионных агентам авиакомпании

Для увеличения объемов продаж пассажирских авиаперевозок путем повышения материальной заинтересованности агентов, авиакомпания разработала ряд бонусных программ. Задача ЦВР авиакомпании состоит в расчете объемов бонусных вознаграждений агентам, выполнившим условия соответствующей программы. База данных о продажах авиабилетов размещена в файле «Бонус.xlsx» (получить у преподавателя).

Ниже представлены условия трех бонусных программ. Для каждой из них необходимо, используя инструменты EXCEL:

- определить агентов, выполнивших условия бонусной программы, путем обработки базы данных о продажах билетов;

- рассчитать выбранным агентам размер бонусного вознаграждения.

Бонусная программа 1

Для материальной заинтересованности агентов на территории Германии (включая прямых агентов авиакомпании и агентов BSP) в увеличении объемов продаж по направлению «города Германии - города Казахстана», авиакомпания разработала следующие условия поощрения:

- минимальная сумма к перечислению Перевозчику, позволяющая получить дополнительное комиссионное вознаграждение в размере 670 EUR, составляет

46 900 EUR в месяц;

- расчет суммы дополнительных комиссионных производится по формулам:

n EUR / 46900 = m (1)

Бонус = m * 670 EUR (2)

где n EUR - сумма к перечислению в авиакомпанию,

m - только целое число, без округлений.

База данных о продажах пассажирских перевозок за месяц всеми агентами авиакомпании расположена на листе «Продажа за месяц» файла «Бонус».

Для обработки базы используйте автофильтр, с помощью которого сначала выделите агентов, расположенных на территории Германии; затем среди всех сумм, представленных в отчете агента, выделите только суммы «к перечислению» - именно эти суммы будут перечислены перевозчику за проданные билеты. Чтобы определить агентов, выполнивших условия бонусной программы, установите фильтр в столбце «Сумма»: в качестве опции фильтрации выберете «Числовые фильтры», критерий отбора – сумма больше или равна 46900.

Далее определите коэффициент кратности бонусного вознаграждения (m). Для этого в свободном столбце справа от данных выполните необходимые вычисления, используя формулу (1). При этом помните, что «m» должно быть целым числом, поэтому используйте функцию «ЦЕЛОЕ», а в качестве аргумента данной функции – формулу (1). Расчет суммы бонусного вознаграждения выполните по формуле (2) в следующем столбце.

Бонусная программа 2

Для увеличения объемов продаж по направлению Сеул-Алматы-Сеул агентами в Сеуле, авиакомпания разработала следующие условия поощрения:

- за каждые 150 RT авиабилетов в месяц по маршруту Сеул-Алматы-Сеул агенту предоставляется один бонусный OW билет по направлению Сеул-Алматы или Алматы -Сеул ;

- при расчете количества проданных авиабилетов 2 билета OW приравниваются к одному билету RT, два билета со скидкой CH приравниваются к одному взрослому, билеты со скидкой IN не учитываются.

База данных о продажах авиабилетов за месяц по направлению Сеул-Алматы-Сеул расположена на листе «Продажа SEL-ALA-SEL» файла «Бонус».

Для решения задачи предварительно выполните следующие действия:

1. Определите вид маршрута: OW (в одну сторону) или RT (туда и обратно).

Маршрут для каждого билета размещен в столбце Н. Если в маршруте пункт начала перевозки (код города или аэропорта -3 символа) совпадает пунктом окончания перевозки, то это перевозка Round Trip (RT), в противном случае – One Way (OW). Для определения вида маршрута используйте столбец справа от данных, куда введите следующую формулу:

=ЕСЛИ(ЛЕВСИМВ(H2;3)=ПРАВСИМВ(H2;3);"RT";"OW")

2. Исключите билеты со скидкой IN. Для этого используйте фильтр в столбце «Тарифы». Также с помощью фильтра определите наличие билетов со скидкой CH.

3. Определите количество билетов RT и OW с помощью сводной таблицы, при построении которой в качестве строк используйте поля «Валидатор» и «Вид маршрута», в качестве данных – поле «Валидатор»; операция, производимая над данными – «Количество».

Проанализируйте количество проданных билетов RT и OW каждым агентом, определите агентов, выполнивших условия бонусной программы, и размер поощрения.

Бонусная программа 3

Для увеличения объемов продаж по направлениям «Москва - города Казахстана» и «города Казахстана – Москва» агентами на территории России, авиакомпания разработала следующие условия поощрения:

- количество проданных за месяц билетов - не менее 70, независимо RT или OW, по маршрутам Москва - города Казахстана, города Казахстана - Москва;

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

продажа от 70 до 85 билетов - бонус 5000 рублей

продажа от 86 до 100 билетов - бонус 7000 рублей

продажа свыше 100 билетов - бонус 10000 рублей

- при расчете количества проданных авиабилетов 2 билета, оформленных по детским тарифам (CH, IN), приравниваются к одному взрослому.

База данных о продаже авиабилетов за месяц агентами на территории России расположена на листе «Продажа в РФ» файла «Бонус».

Для решения задачи используйте навыки, полученные при решении двух предыдущих задач.