Решение финансовых и оптимизационных задач в Microsoft Excel (стр. 2 из 4)
.Целью комбината является определение среди всех допустимых значений
, 
, 
таких, которые максимизируют суммарную прибыль, т. е. целевую функцию 
. Перейдем к ограничениям, которые налагаются на , , . Объем производства мебели не может быть отрицательным, следовательно: 
.Расход древесно-стружечных плит для производства всех видов мебели не может превосходить максимально возможный запас, следовательно:
, 
, 
.Кроме того, общая трудоемкость для производства всех видов мебели не должна превосходить плановый фонд рабочего времени, следовательно:
Таким образом, математическая модель данной задачи имеет следующий вид:
максимизировать
при следующих ограничениях:
Данная модель является линейной, т. к. целевая функция и ограничения линейно зависят от переменных.
Задача решается в MicrosoftExcelпри помощи команды Сервис, Поиск решения. Ячейки А11, В11, С11 отведены под значения переменных
, , . В ячейку Е10 введена целевая функция.Для приведенного на рис 6.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 6.2.
Рисунок 6.1
Рисунок 6.2
В диалоговом окне Поиск решения введены данные, показанные на рис 6.3 и рис 6.4.
Рисунок 6.3
Рисунок 6.4
В поле Установить целевую ячейку диалогового окна Поиск решения дается ссылка на ячейку с функцией, для которой будет находиться максимум, минимум или заданное значение. Для данной задачи в поле Установить целевую ячейку вводится $Е$10 (рис. 6.3).
Тип взаимосвязи между решением и целевой ячейкой задается путем установки переключателя в группе Равной. Для нахождения максимального или минимального значения целевой функции этот переключатель ставится в положение максимальному значению или минимальному значению, соответственно. Для нахождения значения целевой функции, заданного в поле группы Равной , переключатель ставится в положение значению. В данной задаче переключатель установлен в положение максимальному значению, т. к. планируем производство, обеспечивающее максимальную прибыль.
В поле Изменяя ячейки указываются ячейки, которые должны изменяться в процессе поиска решения задачи, т. е. ячейки отведенные под переменные задачи. В нашем случае введем в поле Изменяя ячейки диапазон $А$11:$С$11.
Ограничения, налагаемые на переменные задачи, отображаются в поле Ограничения (рис. 6.3). Средство поиска решений допускает ограничения в виде равенств, неравенств, а также позволяет ввести требование целочисленности переменных. Ограничения добавляются по одному. Для ввода ограничений необходимо нажать кнопку Добавить (Add) в диалоговом окне Поиск решения (рис. 6.3) и в открывшемся диалоговом окне Добавление ограничения (рис. 6.5) заполнить поля.
Рисунок 6.5
В поле Ссылка на ячейку вводится левая часть ограничения – $D$13, а в поле Ограничение – правая часть, в данной задаче – ячейка $В$7. С помощью раскрывающегося списка вводится тип соотношения между левой и правой частями ограничения. В данной задаче это <=.
После ввода всех ограничений необходимо нажать кнопку Параметры в диалоговом окне Поиск решения (рис. 6.3), для того чтобы проверить, какие параметры заданы для поиска решений.
В открывшемся диалоговом окне Параметры поиска решения (рис. 6.2) можно изменять условия и варианты поиска решения исследуемой задачи, а также загружать и сохранять оптимизируемые модели. Значения и состояния элементов управления, используемые по умолчанию, подходят для решения большинства задач.
Рассмотрим элементы этого окна:
- Поле Максимальное время служит для ограничения времени, отпускаемого на поиск решения задачи;
- Поле Предельное число итераций служит для ограничения числа промежуточных вычислений;
- Поля Относительная погрешность и Допустимое отклонение служат для задания точности, с которой ищется решение. Рекомендуется после нахождения решения с величинами данных параметров, заданными по умолчанию, повторить вычисления с большей точностью и меньшим допустимым отклонением и сравнить с первоначальным решением. Использование подобной проверки особенно рекомендуется для задач с требованием целочисленности переменных;
- Флажок Линейная модель служит для поиска решения линейной задачи оптимизации или линейной аппроксимации нелинейной задачи. В случае нелинейной задачи этот флажок должен быть сброшен, в случае линейной задачи – установлен, т. к. в противном случае возможно получение неверного результата;
- Флажок Показывать результаты итераций служит для приостановки поиска решения и просмотра результатов отдельных итераций;
- Флажок Автоматическое масштабирование служит для включения автоматической нормализации входных и выходных значений, качественно различающихся по величине, например, при максимизации прибыли в процентах по отношению к вложениям, исчисляемым в миллионах рублей;
- Группа Оценки служит для выбора метода экстраполяции;
- Группа Метод служит для выбора алгоритма оптимизации.
Из результатов расчета видно (см. рис 6.1), что оптимальным является производство 551 трельяжа, 181 трюмо, 1693 тумбочек под телевизор. Этот объем производства принесет мебельному комбинату 56435 у.е. прибыли.
Задача № 7(Вариант 7 Задача № 1)
В данной задаче необходимо спланировать объем производства так, чтобы максимизировать прибыль. Обозначим через
, , – объемы производства продукции А, В и С соответственно. Суммарная прибыль от производства равна: 
.Целью предприятия является определение среди всех допустимых значений
, , таких, которые максимизируют суммарную прибыль, т. е. целевую функцию . Перейдем к ограничениям, которые налагаются на , , . Объем производства не может быть отрицательным, следовательно: 
.Расход ресурсов для производства всех видов продукции не может превосходить максимально возможный запас, следовательно:
, 
, 
, 
.Таким образом, математическая модель данной задачи имеет следующий вид:
максимизировать
при следующих ограничениях: