Применение программы Поиск решения для анализа сложных задач

Программа Поиск Решения Microsoft Excel является мощным инструментом оптимизации и распределения ресурсов. С ее помощью Вы найдете наилучший вариант использования ограниченных ресурсов, обеспечивающий максимальное значение для одних величин, например, прибыли, или же минимальное – для других, например, затрат. Поиск Решения поможет Вам решить такие вопросы:

Какая цена или ассортимент товаров, предлагаемых на рынок, обеспечат максимальную прибыль?

Как не выйти за пределы бюджета?

Насколько быстро можно наращивать производство без исчерпания наличных средств?

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

Для примера рассмотрим следующую задачу.

Фирма выпускает один вид продукции. Анализ рынка позволил установить вид зависимости объема продаж изделия от его цены (эта функция называется функцией спроса). Эта функция имеет следующий вид:

где - объем продаж,

- статистические коэффициенты,

- цена изделия.

Кроме того, считаем, что себестоимость изделия пропорциональна его цене. Т.е. имеет место следующая формула

где - себестоимость изделия,

- статистический коэффициент.

Выручка от продаж определяется по формуле

а суммарные затраты рассчитываются следующим образом

Требуется определить оптимальную цену продажи изделия при которой валовая прибыль достигает максимального значения. Цена изделия может изменяться в пределах от 50 до 100 руб.

Расчетная таблица в режиме показа формул приведена на рис. 85.

Рис. 85. Расчет оптимальной цены изделия

Результаты расчета для нижнего предела цены изделия приведены на рис. 86.

Рис. 86. Результаты расчета для минимальной цены изделия

Для определения оптимальной цены изделия выполните следующие действия:

1. Выберите команду Поиск решенияв меню Сервис.На экране появится диалоговое окно Поиск решения (рис. 87).

Рис. 87. Окно диалога Поиск решения

В появившемся окне Поиск решения укажите целевую ячейку (Валовая прибыль - $G$6), изменяемую ячейку (цена - $B$6), введите ограничения на цену изделия ($B$6<= 100 и $B$6 >= 50). Ограничения добавляются по одному за один раз и отражаются в поле Ограничения. Для добавления ограничения щелкнете по кнопке Добавить, появится окно Добавление ограничения, в которое вводится ссылка на ячейку, выбирается оператор ограничения и вводится значение ограничения (рис 88).

Рис. 88. Окно диалога Добавление ограничения

После щелчка по кнопке Добавить введенные данные переносятся в поле Ограниченияокна Поиск решения и вводится следующее ограничение. После ввода последнего ограничения щелкните по кнопке ОК.Вновь появляется окно Поиск решения. Щелкните по кнопке Выполнить. После выполнения расчета появится сообщение Результаты поиска решения (рис. 89).

Рис. 89. Окно диалога Результаты поиска решения

Расчет валовой прибыли при оптимальной цене приведен на рис. 90.

Рис. 90. Определение оптимальной цены изделия (цена = 75 )

Результаты определения цены изделия графоаналитическим методом приведены на рис. 91.

Рис. 91. Определение оптимальной цены графоаналитическим методом

Из графика видно, что оптимальная цена находится в окрестности точки 75, что совпадает с точным решением.