Решение задачи
1 Экономико-математическая модель задачи
Известно, что величина дохода линейно связана с объемом продажи товаров х1, х2 и х3. В связи с этим целевую функцию можно записать таким образом:
f = (3x1 + 5х2 + 4х3) → max.
Очевидно, что объем продажи товаров не может быть отрицательной величиной. Поэтому x1 ≥ 0, x2 ≥ 0, x3 ≥ 0.
Учитывая нормы затрат рабочего времени и то, что общие затраты в целом не должны превышать имеющихся ресурсов, запишем следующее ограничение:
Исходя из торговой площади и общей площади запишем следующее ограничение:
Поскольку известны ограничения по издержкам обращения, запишем последнее ограничение:
Экономико-математическую формулировку и модель этой задачи в компактном виде можно представить таким образом: из существующего множества решений системы линейных ограничений по ресурсам
,
,
,
x1 ≥ 0, x2 ≥ 0, x3 ≥ 0,
найти такие величины объемов продажи товаров x1, x2, x3, которые бы обеспечили максимальную величину дохода в линейной функции цели:
f = (3x1 + 5х2 + 4х3) → max.
2 Решение задачи с помощью инструмента Excel Поиск решения
Алгоритм решения задачи состоит из нескольких этапов:
1) Ввод исходных данных (таблица 1.1)
Таблица 1.1 - Постановка условий задачи
| А | В | С | D | E | F | G | |
| Переменные | |||||||
| имя | Товар А | Товар В | Товар С | ||||
| значение | |||||||
| ЦФ | направление | ||||||
| коэффициент в ЦФ | макс | ||||||
| Ограничения | |||||||
| вид | Товар А | Товар В | Товар С | левая часть | знак | правая часть | |
| Рабочее время, чел.-час. | 0,1 | 0,2 | 0,4 | < | |||
| Площадь торговых залов, м2 | 0,05 | 0,02 | 0,02 | < | |||
| Издержки обращения, ден. ед. | < |
2) Ввод зависимости из математической модели
Ввести зависимость для целевой функции:
- курсор в ячейку Е5;
- курсор на кнопку Мастер функций;
- на экране диалоговое окно Мастер функций – шаг 1 из 2;
- курсор в окно Категория на категорию Математические;
- курсор в окно Функции на СУММПРОИЗВ;
- на экране диалоговое окно (рисунок 1.1);
- в массив 1 ввести В$3:D$3;
- в массив 2 ввести B5:D5;
- курсор на кнопку ОК;
- на экране (таблица 1.2) в ячейке Е5 введены значения целевой функции.
Ввести зависимости для левых частей ограничений:
- курсор в ячейку Е5;
- курсор на кнопку Копировать;
- курсор в ячейку Е8;
- курсор на кнопку Вставить;
- скопировать в Е9:Е10;
- на экране (таблица 1.2) в ячейки Е15:Е17 введены функции.
Рисунок 1.1 - Мастер функций
Таблица 1.2 - Ввод зависимостей
| А | В | С | D | E | F | G | |
| Переменные | |||||||
| имя | Товар А | Товар В | Товар С | ||||
| значение | |||||||
| ЦФ | направление | ||||||
| коэффициент в ЦБ | =СУММПРОИЗВ (В$3:D$3;B5:D5) | макс | |||||
| Ограничения | |||||||
| вид | Товар А | Товар В | Товар С | левая часть | знак | правая часть | |
| Рабочее время, чел.-час. | 0,1 | 0,2 | 0,4 | =СУММПРОИЗВ (В$3:D$3;B8:D8) | < | ||
| Площадь торговых залов, м2 | 0,05 | 0,02 | 0,02 | =СУММПРОИЗВ (В$3:D$3;B9:D9) | < | ||
| Издержки обращения, ден. ед. | =СУММПРОИЗВ (В$3:D$3;B10:D10) | < |
3) Решение задачи
Ввести данные в окно поиска решения. Для этого:
- выполнить команду Сервис → Поиск решения;
- на экране диалоговое окно Поиск решения (рисунок 1.2);
- в окне Поиск решения в поле Установить целевую ячейку ввести ссылку $E$5, щелкнув по ячейке с целевой функцией Е5 на рабочем листе;
- в группе Равной установить опцию Максимальному значению;
- в поле Изменяя ячейки ввести диапазон ячеек $B$3:$D$3, выделив их на рабочем листе;
- нажать на кнопку Добавить для ввода ограничений;
- в окне Добавление ограничения в поле Ссылка на ячейку ввести ссылку на ячейку, содержащую формулу левой части текущего ограничения, например $E$8, щелкнув по этой ячейке на рабочем листе. Затем выбрать из раскрывающегося списка в средней части поля нужное ограничение (<=). В правую часть поля ввести значение правой части ограничения (число 1100 или адрес G8 с этим числом). Далее нажать на кнопку Добавить, чтобы ввести следующие ограничения. После добавления всех ограничений нажать на кнопку ОК для возвращения в окно Поиск решения (рисунок 1.2);
Рисунок 1.2 - Диалоговое окно Поиск решения
- если при вводе задачи возникает необходимость в изменении или удалении внесенных ограничений, то надо щелкнуть на кнопку Изменить или Удалить;
- в окне Поиск решения щелкнуть на кнопку Параметры и установить параметры поиска решения (рисунок 1.3), т.е. установить флажок на Линейная модель и нажать на кнопку ОК;
Рисунок 1.3 - Диалоговое окно Параметры поиска решения
- в окне Поиск решения нажать на кнопку Выполнить;
- на экране появится диалоговое окно Результаты поиска решения (рисунок 1.4);
- нажать на кнопку ОК.
Рисунок 1.4 - Диалоговое окно «Результаты поиска решения»
Результат оптимального решения приведен в таблице (таблица 1.3).
Таблица 1.3 - Результаты расчета
| А | В | С | D | E | F | G | ||
| Переменные | ||||||||
| имя | Товар А | Товар В | Товар С | |||||
| значение | ||||||||
| ЦФ | направление | |||||||
| коэффициент в ЦФ | макс | |||||||
| Ограничения | ||||||||
| вид | Товар А | Товар В | Товар С | левая часть | знак | правая часть | ||
| Рабочее время, чел.-час. | 0,1 | 0,2 | 0,4 | <= | ||||
| Площадь торговых залов, м2 | 0,05 | 0,02 | 0,02 | <= | ||||
| Издержки обращения, ден. ед. | <= | |||||||
3 Анализ оптимального решения
Чтобы вызвать отчеты анализа, необходимо в диалоговом окне Результаты поиска решения установить курсор на одном из отчетов и нажать кнопку ОК.
return false">ссылка скрытаОтчет по результатам (таблица 1.4) состоит из трех таблиц:
- таблица 1 приводит сведения о целевой функции;
- таблица 2 приводит значения искомых переменных, полученные в результате решения задачи;
- таблица 3 показывает результаты оптимального решения для ограничений и для граничных условий.
Отчет по пределам (таблица 1.5) показывает, в каких пределах может изменяться объем реализации продукции, вошедшей в оптимальное решение, при сохранении структуры оптимального решения.
Отчет по устойчивости (таблица 1.6) состоит из двух таблиц:
- в таблице 1 приводятся значения для переменных: результат решения задачи; нормируемая стоимость, т.е. дополнительные двойственные переменные, показывающие, насколько изменяется целевая функция при принудительном включении единицы этой продукции в оптимальное решение; предельные значения приращения коэффициентов целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение;
- в таблице 2 приводятся значения для ограничений: величина использованных ресурсов; теневая цена, т.е. двойственные оценки, показывающие, как изменится целевая функция при изменении ресурсов на единицу; значения приращения ресурсов, при которых сохраняется оптимальный набор переменных, входящих в оптимальное решение.
Таблица 1.4 - Отчет по результатам
| Целевая ячейка (Максимум) | ||||||
| Ячейка | Имя | Исходно | Результат | |||
| $E$5 | Коэффициент в ЦФ | |||||
| Изменяемые ячейки | ||||||
| Ячейка | Имя | Исходно | Результат | |||
| $B$3 | значение Товар А | |||||
| $C$3 | значение Товар В | |||||
| $D$3 | значение Товар С | |||||
| Ограничения | ||||||
| Ячейка | Имя | Значение | Формула | Статус | Разница | |
| $E$8 | Рабочее время, чел.-час. левая часть | $E$8<=$G$8 | связанное | |||
| $E$9 | Площадь торговых залов, м2 левая часть | $E$9<=$G$9 | связанное | |||
| $E$10 | Издержки обращения, ден. ед. левая часть | $E$10<=$G$10 | не связан. | |||
| $B$3 | значение Товар А | $B$3>=0 | не связан. | |||
| $C$3 | значение Товар В | $C$3>=0 | не связан. | |||
| $D$3 | значение Товар С | $D$3>=0 | связанное |
Таблица 1.5 – Отчет по пределам
| Ячейка | Целевое имя | Значение | |||||||
| $E$5 | коэф.в ЦБ ЦФ | ||||||||
| Ячейка | Изменяемое имя | Значение | Нижний предел | Целевое результат | Верхний предел | Целевое результат | |||
| $B$3 | значение Товар А | ||||||||
| $C$3 | значение Товар В | ||||||||
| $D$3 | значение Товар С |
Таблица 1.6 - Отчет по устойчивости
| Изменяемые ячейки | |||||||
| Ячейка | Имя | Результ. значение | Нормир. стоимость | Целевой коэффициент | Допустимое увеличение | Допустимое уменьшение | |
| $B$3 | значение Товар А | 9,5 | 0,5 | ||||
| $C$3 | значение Товар В | 2,555555556 | |||||
| $D$3 | значение Товар С | -5,75 | 5,75 | 1E+30 | |||
| Ограничения | |||||||
| Ячейка | Имя | Результ. значение | Теневая цена | Ограничение Правая часть | Допустимое увеличение | Допустимое уменьшение | |
| $E$8 | Рабочее время, чел.-час. лев.часть | 23,75 | |||||
| $E$9 | Площадь торговых залов, м2 лев.часть | 12,5 | |||||
| $E$10 | Издержки обращения, ден. ед. лев.часть | 1E+30 |
Вывод: Максимальный доход торгового предприятия составит 27625 ден. ед. при следующей структуре товарооборота: товарная группа А – 250 единиц; товарная группа В – 5375 единиц, товарная группа С – 0 единиц.
Если торговое предприятие будет продавать товар группы С, то оно снизит свой доход на 5,75 ден. ед. за единицу проданного товара группы С.
Сохраняется структура оптимального товарооборота, т.е. по-прежнему выгодно продавать товарные группы А и В, если торговое предприятие увеличит цены на товары группы А и группы В соответственно на 9,5 ден. ед. и на 1 ден. ед., или снизит цены на товары группы А и группы В соответственно на 0,5 ден. ед. и на 2,56 ден. ед.
Что касается использования ресурсов, то рабочее время и площадь торговых залов будут использованы полностью, а издержки обращения будут снижены с 8000 ден. ед. до 6125 ден. ед., т.е. экономия издержек обращения составит 1875 ден. ед.
При изменении количества рабочего времени на 1 чел.-час. и площади торговых залов на 1 м2 максимальный доход изменится, соответственно, на 23,75 ден. ед. и на 12,5 ден. ед.
Оптимальная структура товарооборота сохраняется при увеличении использования рабочего времени на 100 чел.-час. или его уменьшении на 860 чел.-час., при увеличении торговой площади на 30 м2 или ее уменьшении на 10 м2.