Расчетно-графическая работа №1

Министерство образования и науки Российской федерации

Федеральное государственное бюджетное образовательное учреждение

Высшего профессионального образования

«Московский государственный университет леса»

Международная школа управления, бизнеса и экономики

Кафедра менеджмента и информационных технологий

Расчетно-графическая работа №1

по дисциплине «Методы принятия управленческих решений»

по теме «Принятие решений в условиях определенности. Решение линейных задач с помощью надстройки «Поиск решений»

Выполнил:

студент группы МБ-..

Проверил:

Доцент, к. ф - м. н. А. М. Фролов

Мытищи, 2015

Решение задач ЛП с помощью надстройки «Поиск решения»

Для иллюстрации алгоритма решения задач линейного программирования рассмотрим следующий пример.

Пример 5.1.

Мебельная фабрика выпускает три вида изделий: шкафы, тумбочки и столы, расходуя для их производства ресурсы четырех типов: ДСП, фанеру, стекло, крепежные изделия. Нормы расхода ресурсов на одно изделие каждого типа (удельные расходы ресурсов на единицу продукции) и суточные запасы, которыми располагает фабрика, приведены в табл. 5.1 (значения условные).

Табл. 5.1.

Ресурсы	Нормы расхода ресурсов для производства единицы продукции	Ограничения по ресурсам
Шкафы	Тумбочки	Столы
ДСП
Фанера
Стекло
Крепеж

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

Табл. 5.2.

С1 (шкафы)	С2 (тумбочки)	С3 (столы)

Требуется:

1) Найти производственную программу — объемы выпуска шкафов, тумбочек и стульев, — обеспечивающую максимальную прибыль.

2) Установить размеры максимальной прибыли.

Решение

Обозначим через х₁ х₂, х₃ искомую производственную программу — объемы выпуска (в штуках) шкафов, тумбочек и столов. Тогда математическая модель задачи оптимизации примет вид

Z = 3х₁ + 4 х₂ + 2х₃ => max (5.1)

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

х₁ + 2х₂ + х₃ 18,

2 х₁ + х₂ + х₃ 16,

х₁ + х₂ 8 (5.2)

х₂ + х₃ 6

х₁ 0, х₂ 0, х₃ 0.

Для решения создадим на рабочем листе Excel две таблицы (рис. 5.6).

Рис. 5.6.

Выделим на рабочем листе Excel ячейки В4, С4, D4 (массив B4:D4) для переменных решения х₁, х₂, х₃. В ячейку Е4 запишем формулу для вычисления целевой функции. Так как целевая функция равна сумме попарных произведений коэффициентов целевой функции с₁, с₂, с₃ и соответствующих переменных решения х₁, х₂, х₃, то формулу в ячейке Е4 удобно записать с помощью стандартной функции Excel СУММПРОИЗВ (Вставка Функция > Математические.>СУММПРОИЗВ) (рис. 5.6).

Соответствующая формула в ячейке Е4, вычисляющая значения целевой функции для производственной программы х₁, х₂, х₃, запишется следующим образом:

=CУMMПРОИЗВ(B4:D4;B3:D3).

В ячейках B4:D4 расположены (и далее будут подбираться «Поиском решения») значения х₁, х₂, х₃, а в ячейках B3:D3 записаны значения коэффициентов целевой функции. На этой стадии переменным х₁, х₂, х₃ можно не придавать никакого начального числового значения — оставить их пустыми или положить равными нулю.

Для записи ограничений в табличной модели предусмотрены ячейки А6:Н11 (рис. 5.6). В ячейках B8:D11 записаны известные из условий задачи удельные расходы ресурсов, необходимых для производства каждого изделия. В колонку «Запас ресурсов» помещены значения запасов ресурсов — правые части ограничений. Для удобства анализа полученных решений можно также добавить колонку «Запас-Расход», в которой будут вычисляться излишки каждого ресурса, остающиеся после выполнения найденной производственной программы. Формула для нахождения остатка проста: это разность между имевшимся запасом и фактически потребленным количеством ресурса.

Колонка «Расход ресурса» предусмотрена для записи формул, вычисляющих левые части ограничений системы (5.2). Как видно из структуры ограничений (5.2), их левые части представляют собой сумму попарных произведений переменных х₁, х₂, х₃ на удельные расходы ресурсов. Следовательно, для записи формулы здесь также целесообразно применить стандартную функцию Excel СУММПРОИЗВ.

Первое ограничение системы (5.2) имеет вид х₁ + 2х₂ + х₃ 18,

Левая часть ограничения — это сумма попарных произведений чисел {1, 2, 1} на х_1; х₂, х₃, т.е. удельных расходов ресурса ДСП на объемы производства каждого изделия. Так как удельные расходы для ДСП {1, 2, 1} расположены в ячейках B8:D8, а производствен­ная программа х_1; х₂, х₃, (объемы выпуска изделий) расположена в ячейках B4:D4, то для вычисления левой части ограничения в ячейку Е8 записываем формулу

=CУMMПРОИЗВ($B$4:$D$4;B8:D8).

Знак абсолютной ссылки — $ (клавиша F4) — добавлен к адресам ячеек B4:D4 для того, чтобы записанную один раз формулу можно было скопировать в нижерасположенные ячейки Е9:Е11 с целью вычисления левых частей других ограничений.

После того как вся необходимая информация размещена на рабочем листе, можно переходить к решению оптимизационной задачи с помощью надстройки «Поиск решения» (Сервис > Поиск решения...).

В открывшемся диалоговом окне (рис. 5.8) вводим адрес целевой ячейки — Е4 (поле «Установить целевую ячейку:») — и адреса массива ячеек B4:D4, в которых содержатся переменные решения — х_1; х₂, х₃, (поле «Изменяя ячейки:»). Устанавливаем флажок «Равной: максимальному значению».

Рис.5.8.

Устанавливаем параметры поиска решения, необходимые для решения задачи линейного программирования: с помощью кнопки «Параметры» переходим к окну «Параметры поиска решения» (рис. 5.9).

Рис5.9.

Устанавливаем флажки «Линейная модель», «Неотрицательные значения», «Автоматическое масштабирование». После установки параметров нажатием кнопки «ОК» возвращаемся в основное диалоговое окно (рис. 5.8).

Для ввода ограничений используем кнопку «Добавить» и переходим в окно «Добавление ограничения».

Для рассматриваемой модели это можно сделать двумя способами: вводя поочередно каждое ограничение системы или введя сразу массивы левых и правых частей ограничений («Поиск решения» это допускает). В первом случае поле «Ограничения:» будет содержать 4 строки (рис. 5.8), во втором случае — одну строку.

После заполнения всех полей надстройка «Поиск решения» готова к решению задачи оптимизации и выводу результатов на рабочий лист Excel. Для этого необходимо нажать кнопку «Выполнить».

Прежде чем вывести результаты оптимизации на рабочий лист, «Поиск решения» выводит на экран диалоговое окно «Результаты поиска решения» (рис. 5.13.)

Рис. 5.13.

В зависимости от того, имеет задача решение или нет, в окне могут выводиться различные сообщения. Если в окне «Результаты поиска решения» появляется сообщение «Решение найдено. Все ограничения и условия оптимальности выполнены», то задача успешно решена. После нажатия кнопки «ОК» на рабочий лист Excel выводятся результаты оптимизации (рис. 5.14).

Рис. 5.14.

Заключение

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

• шкафы необходимо выпускать в количестве 5 шт.;

• тумбочки — в количестве 3 шт.;

• столы — в количестве 3 шт.

Соответствующие оптимальные значения переменных х_1; х₂, х₃, найденные «Поиском решения» и обращающие целевую функцию в максимум, расположены в ячейках B4:D4 (рис. 5.14).

При такой производственной программе будет достигнута максимальная прибыль Z_max = 33. Максимальное значение целевой функции, соответствующее оптимальному решению, вычислено «Поиском решения» в ячейке Е4.

Расходы ресурсов, необходимые для выполнения оптимальной производственной программы, помещены в ячейки Е8:Е11. Откуда следует:

• ДСП необходимо 14 единиц, в то время как запас составлял 18 единиц. Следовательно, запас данного ресурса избыточен. Излишек ресурса равен 4 единицам — ячейка Н8.

• Фанера, стекло и крепеж при оптимальной производственной программе расходуются полностью — ячейки Н9:Н11.