Решение задач прикладной информатики в менеджменте.
Использование средства Excel «Поиск решения» для решения задачи максимизации прибыли турагенства
Цель работы: освоение средства «Поиск решения» для решения практических задач
Постановка задачи и построение математической модели
1.1. Необходимо определить, сколько путевок и на какие туры турагентство должно приобрести и реализовать, чтобы получить максимальную прибыль.
1.2. Работа по приобретению и продаже путевок связана с трудовыми, материальными, финансовыми и транспортными затратами. Затраты ограничены ресурсами, которыми располагает турагенство. Из опыта известно затраты каждого вида, необходимые для реализации путевки каждого тура, известна прибыль от реализации каждой путевки. Данные сведены в таблицу 1.
Таблица 1 Исходные данные
Показатель | Страна тура | Наличие ресурса, у.е. | |||||
Турция | Греция | Испания | Франция | Англия | |||
затраты ресурсов | |||||||
Ресурсы | трудовые | 20+2N | |||||
материальные | 100+2N | ||||||
финансовые | 160+2N | ||||||
транспортные | 100+2N | ||||||
Прибыль от 1 путевки, у.е. | 90+N | 100+N | 120+N | 140+N | 100+N |
Обозначим количество путевок в отдельную страну X i, прибыль от реализации одной путевки в эту страну pi, тогда целевая функция:
Q = S pi Xi или Q =(90+N)X1 + (100+N)X2 +(120+N)X3 + (140+N)X4 + (100+N)X5
Функцию Q необходимо максимизировать: Q ® max
Ограничением задачи является требование, чтобы затрачиваемые ресурсы не превосходили имеющиеся Rj: Srji Xi<= Rj,
где rji – затраты ресурса j на одну путевку в страну i. Переменные X i неотрицательны и целые.
Порядок выполнения расчетов
2.1 Разместите на рабочем листе таблицу исходных данных со значениями:
- затрат каждого ресурса на путевку в каждую страну;
- наличных ресурсов;
- прибыли от реализации одной путевки в каждую страну.
2.2 Для работы выберите ячейки, в которых будут формироваться результаты, т.е. диапазон ячеек с количеством путевок, реализованных в каждую страну. Размерность диапазона равна количеству стран, начальные значения – количество путевок по каждому туру равно 0.
2.3 Введите формулы для вычисления затрат каждого ресурса и прибыли от реализации путевок. Затраты считаются с помощью функции СУММПРОИЗВ количества путевок на затраты ресурса. При этом массив_1 - количество путевок остается постоянным, а массив_2 меняется от ресурса к ресурсу. Прибыль от реализации также вычисляется с помощью функции СУММПРОИЗВ количества путевок на прибыль от реализации одной путевки.
2.4 Выполните команду Сервис - Поиск решения, задайте целевую ячейку, цель решения – максимизировать значение, диапазон изменяемых ячеек.
2.5 Задайте ограничения:
- управляемые параметры должны быть целыми;
- потребные ресурсы (вычисленные) не должны превышать наличные.
2.6 В диалоге Поиска решения «Параметры» установите Линейная модель и Неотрицательные значения.
2.7 Выполните Поиск решения. Сохраните сценарий решения.
2.8 Снимите требование целочисленности переменных и повторите поиск решения. Сохраните сценарий
2.9 С помощью команд Сервис – Сценарии отобразите результаты решения для случая целочисленных и дробных переменных. Сравните полученные решения.