Нахождение значения за счет изменения нескольких величин

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

Например, можно определить бюджет на рекламу в каждом квартале, соответствующий наибольшей годовой прибыли. Поскольку задаваемая в 3 строке сезонная поправка входит в расчет числа продаж (строка 5) в качестве сомножителя, целесообразно увеличить затраты на рекламу в 4 квартале, когда прибыль от продаж наибольшая и уменьшить, соответственно, в 3 квартале. Поиск решения позволит найти наилучшее распределение затрат на рекламу по кварталам.

Для этого необходимо:

1) Выбрать команду Сервис - Поиск решения.

2) В диалоговом окне Поиск решения в качестве результирующей ячейки (общая прибыль за год) на листе Excel в поле Установить ячейку задать F15.

3) Выбрать поиск наибольшего значения и в поле Изменяя ячейки указать в качестве изменяемых ячеек B11:E11(расходы на рекламу в каждом квартале).

4) Запустить процесс поиска решения кнопкой .

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

Рассмотренная задача является нелинейной задачей оптимизации средней степени сложности, то есть поиск значения уравнения с четырьмя неизвестными в ячейках с B11 по E11. (Нелинейность уравнения связана с операцией возведения в степень в формуле строки 5). Результат этой оптимизации без ограничений говорит о возможности увеличения годовой прибыли до 79706 р. при годовых затратах на рекламу 89706 р. (рисунок 1.13).

Рисунок 1.13 Нахождение наибольшей прибыли при изменении затрат на рекламу по кварталам в задаче Модель сбыта

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

Добавление ограничения

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

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

Для этого необходимо:

1) Выбрать команду Сервис-Поиск решения и нажать кнопку .

2) В диалоговом окне Добавление ограничения (рисунок 1.14) нужно задать ссылку на ячейку ограничения F11 (общие расходы на рекламу).

Содержимое этой ячейки не должно превышать 40000 р. Установленное по умолчанию отношение <= оставить без изменения. В поле, расположенном справа, нужно ввести число 40000 и нажать кнопку .

3) Запустить процесс поиска решения кнопкой .

Рисунок 1.14 Диалоговое окно Добавление ограничения

В соответствии с найденным решением на рекламу будет выделено 5117р. в 3 квартале и 15263р. - в 4. Прибыль увеличится с 69662р. до 71447р. (рисунок 1.15).

Рисунок 1.15 Нахождение наибольшей прибыли без увеличения бюджета на рекламу в задаче Модель сбыта

Изменение ограничения

Поиск решения позволяет экспериментировать с различными параметрами задачи, для определения наилучшего варианта решения. Например, изменив ограничения, можно оценить изменение результата. Попробуем на листе примера изменить ограничение на рекламный бюджет с 40000р. до 50000р. и посмотреть, как изменится при этом общая прибыль.

Для этого необходимо:

1) Выбрать команду Сервис - Поиск решения и нажать кнопку , предварительно выделив предыдущее ограничение $F$11<=40000.

2) В диалоговом окне Изменение ограничения (рисунок 1.16) нужно изменить поле значения 40000 на 50000 и нажать кнопку .

3) Запустить процесс поиска решения кнопкой .

 

Рисунок 1.16 Диалоговое окно Изменение ограничения

Найденное решение соответствует прибыли 74817р., что на 3370 р. больше прежнего значения 71447 р. (рисунок 1.17).

Для большинства предприятий увеличение капиталовложений на 10000 р., приносящее 3370 р. (т.е. 33,7 % возврат вложений) является оправданным.

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

 

Рисунок 1.17 Нахождение наибольшей прибыли без увеличения бюджета на рекламу (с измененным ограничением) в задаче Модель сбыта