Надстройка “Поиск решения”

Для решения задач оптимизации, требующих применения математического аппарата линейного и нелинейного программирования, служит надстройка

“Поиск решения”, имеющаяся в Microsoft Excel*.

Для рассмотрения работы надстройки “Поиск решения” воспользуемся типичной моделью сбыта, отражающей увеличение числа продаж от заданной величины при увеличении затрат на рекламу.

Поиск решения поможет определить необходимость увеличения рекламного бюджета или его перераспределения с учетом сезонной поправки.

* Если данной надстройкой ранее не пользовались, то нужно ее установить: в главном меню выберем: Сервис – Надстройки – Поиск решения.

Имеем следующие исходные данные некоторого предприятия:

Рассмотрим варианты получения наибольшей прибыли:

1) Нахождение значения, при котором заданная величина максимальна. Например, необходимо добиться наибольшей прибыли, изменяя затраты на рекламу. Возьмем 1 квартал: увеличивая расходы на рекламу проанализируем увеличение производственной прибыли.

Итак, в результате найденного решения мы видим, что постепенно увеличивая затраты на рекламу прибыль тоже увеличивается, но этот процесс конечен: это можно увидеть выполнив прогноз по регрессионной модели. Пример 1А (Приложение 1)

Расчетные данные подтверждают наши выводы:

затратив на рекламу 12 700 000 р. получаем производственную прибыль 12 520 749р., т.е. на рекламу затратили больше, чем получили прибыль.

2) Нахождение значения за счет изменения нескольких величин. Т.е. попробуем определить бюджет на рекламу в каждом квартале, соответствующий наибольшей годовой прибыли. Обратимся к надстройке Поиск решения:

В результате найденного решения мы видим, что затраты на рекламу составят 63 млн. 530 тыс.р., а произв. прибыль составит 63 млн. 407.тыс.р. Опять производство получилось убыточным и затраты на рекламу огромные.

3) Добавление ограничения: по полученным данным делаем вывод, что нужно ввести ограничение на рекламу.

Т.е. при ограничении на рекламу мы получаем прибыль, причем большую, чем в исходном варианте (+90тыс.р.) Пример 3 (приложение).

Итак, не увеличивая затраты на рекламу, мы имеем большую прибыль только за счет перераспределения средств на рекламу.

4) Увеличение ограничения. Попробуем увеличить ограничение на рекламу на 100 тыс.р.:

Мы видим что при увеличении затрат на рекламу на 100 тыс. рублей мы получим прибыль на 1,5 млн.р. больше.

Как поступать дальше будет решать руководство предприятия: посчитают ли они нужным вкладывать большую сумму в рекламу? Смогут ли “найти” данную сумму и т.д.

Задачи, для решения которых можно воспользоваться надстройкой “Поиск решения”, имеют ряд общих свойств:

Имеется единственная целевая ячейка, содержащая формулу, значение которой должно быть сделано максимальным, минимальным или же равным какому – то конкретному значению.

Формула в целевой ячейке содержит ссылки на ряд изменяемых ячеек.

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

Может быть задано некоторое количество ограничений – условий или соотношений, которым должны удовлетворять некоторые из изменяемых ячеек.