Порядок выполнения расчетов

Решение задач прикладной информатики в менеджменте.

Использование средства 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, прибыль от реализации одной путевки в эту страну p_i, тогда целевая функция:

Q = S p_i X_i или Q =(90+N)X₁ + (100+N)X₂ +(120+N)X₃ + (140+N)X₄ + (100+N)X₅
Функцию Q необходимо максимизировать: Q ® max

Ограничением задачи является требование, чтобы затрачиваемые ресурсы не превосходили имеющиеся R_j: Sr_ji X_i<= R_j,
где r_ji – затраты ресурса 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 С помощью команд Сервис – Сценарии отобразите результаты решения для случая целочисленных и дробных переменных. Сравните полученные решения.