Построение нелинейной модели

Ячейки В3:ЕЗ резервированы для цен продукции, являющихся оптимизируемыми переменными. В ячейках В4:Е4 записаны значения затрат производства по видам продукции. В ячейки В5:Е5 внесены формулы для расчета единичной прибыли как разности между ценой и затратами производства. Так, в ячейку B5 внесена формула =В3-В4.

В ячейки В7:Е7 внесены формулы для расчета величины спроса по уровню цены соответствующего вида продукции. Исходный вид этих формул соответствует выражению (7.7). В ячейку B7, например, будет записана формула =300-0,106*B3.

В ячейках G11:G13 указываются формулы для расчета потребности в ресурсах по их видам. Так, в ячейку G11 будет записана формула =B7*B11+C7*C11+D7*D11+E7*E11.

В ячейках Н11:Н13 указываются величины имеющихся объемов ресурсов каждого вида.

Целевая функция, рассчитываемая по формуле

= B5*B7+C5*C7+D5*D7+E5*E7,

помещается в ячейку G9.

Для решения модели выбираем команду ПОИСК РЕШЕНИЯ меню СЕРВИС. В появившемся окне (Рис. 7.14) указываем местонахождение целевой функции, направление оптимизации (минимизация или максимизация), изменяемые ячейки (содержащие значения оптимизируемых переменных) и используемые ограничения.

Рис. 7.14. Диалоговое окно "Поиск решения"

В данной постановке нам потребуются три ограничения:

G11:G13< = H11:H13,

В7:Е7 > = 0,

В3:Е3 < 2500.

Первое из них выражает условие ограниченности используемых ресурсов. Второе - требование неотрицательности оптимизируемых величин спроса на выпускаемую продукцию по ее видам. Третье ограничение содержит определенное на стадии предварительных маркетинговых исследований условие конкурентоспособности выпускаемой продукции в отношении её цен. Войдя в окно "Параметры поиска решения" (открываемого нажатием на кнопку "Параметры" диалогового окна "Поиск решения"), следует отказаться от использования линейной модели и выбрать необходимые опции для решения нелинейной задачи (см Рис. 7.15).

Рис. 7. 15. Диалоговое окно "Параметры поиска решения"

После решения модели Вы получите возможность заказать отчеты по полученным результатам и затем увидите свою модель в окончательном виде (см. Табл. 7.24).

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

При решении нелинейных задач EXCEL 5.0 выдает два типа отчетов: отчеты по устойчивости и отчеты по пределам.

Таблица 7.24