Решение финансовых и оптимизационных задач в Microsoft Excel

Рисунок 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)

      В данной задаче необходимо спланировать объем производства так, чтобы максимизировать  прибыль. Обозначим через  , , – объемы производства продукции А, В и С соответственно. Суммарная прибыль от производства равна:

       .

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

       .

      Расход  ресурсов для производства всех видов продукции не может превосходить максимально возможный запас, следовательно:

       ,

       ,

       ,

       .

      Таким образом, математическая модель данной задачи имеет следующий вид:

      максимизировать

      

      при следующих ограничениях:

      

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

      Задача  решается в Microsoft Excel при помощи команды Сервис, Поиск решения. Ячейки В13, С13, D13 отведены под значения переменных , , . В ячейку F12 введена целевая функция (рис 7.2).

      Для приведенного на рис 7.1 расчета в соответствующие ячейки введены формулы, показанные на рис. 7.2.

Рисунок 7.1 

Рисунок 7.2

      В диалоговом окне Поиск решения введены данные, показанные на рис 7.3 и рис 7.4.

Рисунок 7.3 

Рисунок 7.4 

      Из  результатов расчета видно (см. рис 7.1), что оптимальным является производство 571 шт. продукции А, 0 шт. продукции В, 71 шт. продукции С. Этот объем производства принесет 4071 у.е. прибыли.

 

       Задача № 8 (Вариант 7 Задача № 2)

      Поскольку данная модель сбалансирована (суммарный объем произведенной продукции равен суммарному объему потребностей в ней), то в этой модели не надо учитывать издержки, связанные как со складированием, так и с недопоставками продукции (см. рис. 8.1).

      Для решения данной задачи построим ее математическую модель. Неизвестными в данной задаче являются объемы перевозок, после сокращения. Пусть – объем перевозок с -го кирпичного завода на -й строительный объект. Целевая функция – это суммарные расходы на производство и транспортировку кирпича после сокращения, т. е.

      

где – стоимость перевозки одной тонны кирпича с -го кирпичного завода на -й строительный объект;

 – сокращение объема производства на 1-ом кирпичном заводе;

 – сокращение объема производства на 2-ом кирпичном заводе.

Неизвестные в  данной задаче должны удовлетворять следующим ограничениям:

      - Объемы перевозок и сокращения не могут быть отрицательными;

      - Так как модель сбалансирована, то вся продукция должна быть вывезена с заводов, а потребности всех строительных объектов должны быть полностью удовлетворены.

      В результате имеем следующую математическую модель:

      минимизировать:

      

      при ограничениях:

       ;

       ;

       ;

       ;

где  – объем производства на -м кирпичном заводе;

      – потребность на -м строительном объекте.

      Для решения этой задачи с помощью  средства поиска решений введем данные, как показано на рис. 8.1.

Рисунок 8.1 

       В ячейки С5:Е6 введены стоимости перевозок. Ячейки С10:Е11 и I10:I11 отведены под значения неизвестных – объема перевозок и необходимого сокращения объема производства на предприятиях соответственно. В ячейки G10:G11 введены объемы производства на кирпичных заводах, а в ячейки С11:Е13 введена потребность в продукции на строительных объектах (с учетом сокращения объема производства). В ячейку F15 введена целевая функция

       =СУММПРОИЗВ(C5:E6;C10:E11)+H5*G10+H6*G11

       В ячейки С12:Е12 введены формулы (см. рис. 8.1), определяющие объем продукции, необходимой соответствующему потребителю.

       В ячейки F10:F11 введены формулы (см. рис. 8.1), определяющие объем продукции, вывозимой с кирпичных заводов.

      Далее выбираем команду Сервис, Поиск решения и заполняем открывшееся диалоговое окно Поиск решения , как показано на рис. 8.2.

Рисунок 8.2

       В диалоговом окне Параметры поиска решения (рис. 8.3) устанавливаем флажок Линейная модель и Неотрицательные значения. После

Рисунок 8.3 

нажатия кнопки Выполнить средство поиска решений находит оптимальный план поставок продукции и показывает на каких предприятиях необходимо провести сокращение производства (рис 8.4). Т. е. необходимо сократить производство на 140 т на 2-м кирпичном заводе. 

Рисунок 8.4 

 

       Задача № 9 (Задача № 2-17)

      Поскольку данная модель сбалансирована (суммарный объем запасов сырья равен суммарному объему необходимого сырья), то в этой модели не надо учитывать издержки, связанные как со складированием, так и с недопоставками сырья.

      Для решения данной задачи построим ее математическую модель. Неизвестными в данной задаче являются объемы перевозок. Пусть – объем перевозок с -го пункта получения сырья на -е предприятие. Целевая функция – это суммарные транспортные расходы, т. е.

      

где – элемент матрицы С, задающей тарифы перевозок;

Неизвестные в  данной задаче должны удовлетворять следующим ограничениям:

      - Объемы перевозок не могут быть отрицательными;

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

      В результате имеем следующую математическую модель:

      минимизировать:

      

      при ограничениях:

       ;