Методика и специфика решения задач нелинейной оптимизации в msExcel

Задачу оптимизации нелинейной функции (6) можно было бы решить не путем анализа с использованием производной, и используя инструмент «Поиск решения» в MS Excel.

Если реализовать вычисления функции (6) по значению переменной в ячейке (см. рис. 2), то можно определить оптимально значение переменной в этой ячейке.

Рис. 2. Реализация вычисления функции прибыли в MSExcel

Для оптимизации используем инструмент «Поиск решения». Настраиваем параметры поиска решения (рис. 3) следующим образом:

Рис. 3. Настройка инструмента «Поиск решения»

• в поле «Оптимизировать целевую функцию» указываем ячейку, где реализована формула для прибыли;

• в поле «До» указываем «Максимум»;

• в поле «Изменяя ячейки переменных» указываем ячейку, предназначенную для значения переменной ;

• в поле «В соответствии с ограничениями» добавляем ограничение невозможности большого увольнения ;

• состояние поля «Сделать переменные без ограничений неотрицательными» в нашей задаче безразлично, так как единственная переменная ограничена;

• в поле «Выберите метод решения» выбираем «Поиск решения нелинейных задач методом ОПГ» (так как наша задача нелинейная).

Нажав кнопку «Найти решение» получаем форму «Результаты поиска решения» (рис. 4). Убедившись, что в этом окне написано «Решение найдено. Все ограничения и условия выполнены», выбираем «Сохранить найденное решение» и нажимаем кнопку «Ок».

Рис. 4. Форма «Результаты поиска решения»

В ячейке переменной получаем оптимальное решение (рис. 5).

Рис. 5. Значения переменных после оптимизации

Как видим, нам удалось найти правильное решение автоматически.

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

Важно отметить такую специфику решения нелинейных задач в MS Excel. Поиск решения методом ОПГ ищет значения переменных от начального заданного, обеспечивая постоянное улучшение результата с текущего места. Такая реализация приводит к поиску локального, а не глобального экстремума. Так, «начав» поиск от 70 рабочих мы нашли оптимальное значение 100 человек. Начав же, например, со 160 человек, получим «оптимальное» количество 150 (рис. 6). Выбрав же вначале 200 человек можно снова прийти к оптимальному значению 100.

Рис. 6. Изменение решения при смене начального приближения

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

Задание для самостоятельного решения

Числовые условия задания формируются на основе двух последних цифр зачетной книжки или студенческого билета. Выполнение чужого варианта задания не допускается.

В задании данной темы:

;

;

;

;

;

;

;

;

–последняя цифра номера зачетной книжки;

–предпоследняя цифра номера зачетной книжки.

Вы – новый руководитель фирмы, производящей дорогостоящие автоматизированные станки. В настоящее время фирма производит 100 станков в год. Вам необходимо проанализировать и при возможности оптимизировать работу фирмы.

Задание

Определите оптимальное количество станков , которое необходимо выпускать в год для оптимизации прибыли при следующих условиях функционирования фирмы:

• Цена продажи одного станка равна млн. руб.

• Постоянные издержки фирмы равны млн. руб., переменные издержки равнымлн. руб.

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

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

• Сформулируйте оптимальное экономико-управленческое решение в имеющихся условиях.

• Дайте экономическое обоснование полученного решения.

• Приведите сравнение предлагаемого Вами плана выпуска станков с используемым ранее вариантом. Обоснуйте необходимость изменений.

• Приведите график зависимости прибыли от количества выпускаемой продукции. Обоснуйте свое решение с помощью графика.