1.10. Решение задачи с использованием

пакета MS Excel

Рассмотрим методику решения ЗЛП средствами пакета электронных таблиц MS Excel и возможность поведения экономического анализа полученных результатов на примере решения следующей задачи.

Пример 2. Предприятие выпускает 3 вида изделий (В₁, В₂, В₃), для изготовления которых используется различные виды оборудования: А₁ (фрезерное), А₂ (токарное), А₃ (сварочное) и А₄ (шлифовальное). Удельные затраты рабочего времени а_ij для каждого типа оборудования, общий фонд рабочего времени b_i, а также прибыль c_j от реализации единицы продукции каждого вида приведены в таблице. Требуется так спланировать объемы выпуска изделий, чтобы прибыль от их реализации была максимальной.

Таблица 1.46

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

F = 10х₁ + 14х₂ + 12х₃ ® max;

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

Реализация расчетных формул представленной математической модели средствами MS Excel показана на рис. 1.19.

Ячейки B3:D6 содержат удельные затраты рабочего времени а_ij для каждого типа оборудования.

Ячейки F3:F6 – имеющийся в наличии общий фонд рабочего времени b_i для фрезерного, токарного, сварочного и шлифовального оборудования соответственно.

В ячейках B7:D7 находится прибыль c_j от реализации единицы изделия каждого вида.

Ячейки B8:D8 отведены под значения неизвестных х_j (оптимальный план выпуска продукции).

Рис. 1.20. Реализация расчетных формул ЗЛП средствами MS Excel

В ячейке Е7 задана целевая функция (ЦФ), вычисляющая общую прибыль как сумму произведений удельной прибыли от реализации единицы каждого вида продукции на объем выпуска соответствующего вида продукции. Эту же формулу можно записать в более компактном виде

=СУММПРОИЗВ(В7:D7;B$8:D$8),

что особенно актуально при решении задач, содержащих большое количество переменных.

Ячейки Е3:Е6 содержат формулы для расчета затрат каждого вида ресурсов (времени) при производстве указанного количества продукции каждого вида.

Выделим ячейку, содержащую целевую функцию (E7). Теперь в меню «Сервис» выберем команду «Поиск решения…» и заполним диалоговое окно надстройки «Поиск решения» как показано на рис. 1.21.

Использование кнопки «Предположить» в нашем примере для попытки автоматического определения изменяемых ячеек приведет к неверному результату (B7:D8). Это обусловлено тем, что ячейки B7:D7 (прибыль от реализации единицы изделия каждого вида), хоть и влияют на формирование значения общего объема прибыли, но не должны изменяться в ходе решения задачи.

Рис. 1.21. Диалоговое окно «Поиск решения» для ЗЛП

Не забудьте установить флажок параметра «Линейная модель».

Нажмем кнопку «Выполнить» для поиска оптимального решения сформулированной ЗЛП.

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

В столбце «Статус» отчета по результатам состояние «связанное» определяет дефицитный ресурс, а «не связан.» – недефицитный. В нашем случае дефицитными ресурсами являются фонды времени фрезерного и сварочного оборудования. Фонды рабочего времени токарного и шлифовального оборудования, напротив, превышают потребность на 112 и 156 ед. рабочего времени соответственно (см. столбец «Разница»). Размер этих фондов можно уменьшить на величину избытка без изменения значения целевой функции.

Столбец «Теневая цена» отчета по устойчивости (рис. 8) показывает решение задачи где?

f = 120y₁ + 280y₂ + 240y₃ + 360y₄ ® min;

y₁ ³ 0, y₂ ³ 0, y₃ ³ 0, y₄ ³ 0,

которая является двойственной к исходной. Решение двойственной задачи имеет вид: y₁ = 2,9, y₂ = 0, y₃ = 0,6, y₄ = 0.

Решение двойственной задачи также позволяет определить наиболее выгодный ресурс из дефицитных ресурсов. В нашем случае при вложении дополнительных средств предпочтение следует отдать первому ресурсу (в первую очередь следует увеличивать фонд рабочего времени фрезерного оборудования): у₁ = 2,9 > у₃.

Решение двойственной задачи предоставляет возможность оценить целесообразность введения в оптимальный план продукцию нового вида. Если выполняется условие

, (1.23)

то введение в план новой продукции выгодно.

Выполняя вычисления по формуле (9), можно сказать, что введение в оптимальный план нового изделия В₄, требующего 3 ед. времени фрезерного оборудования, 2 ед. – токарного, 5 ед. – сварочного и 4 ед. – шлифовального невыгодно, если цена нового изделия не будет превышать 3 ´ 2,9 + 2 ´ 0 + 5 ´ 0,6 + 4 ´ 0 = 11,7 ден. ед.

Значения в столбцах «Допустимое Уменьшение» и «Допустимое Увеличение» для изменяемых ячеек определяют вариации коэффициентов целевой функции, в пределах которых оптимальные значения переменных х_j не изменяются (не перемещается точка оптимального решения), а изменяется только значение самой целевой функции.

В нашем случае, если цены на изделия В₁, В₂ и В₃ будут изменяться в пределах

10 – 3 = 7 < с₁ < 10 + 14,5 = 24,5;

14 – 4,4 = 9,6 < с₂ < 14 + 6 = 20;

12 – ¥ = 0 < с₃ < 12 + 5,5 = 17,5

оптимальным планом производства будет х₁ = 24, х₂ = 18, х₃ = 0.

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

Значения в столбцах «Допустимое Уменьшение» и «Допустимое Увеличение» для ограничений задачи определяют пределы изменения правых частей ограничений, в которых текущее решение остается допустимым. При этом могут изменяться как оптимальные значения переменных х_j, так и значение целевой функции.

Так изменение недефицитного фонда рабочего времени токарного и шлифовального оборудования в пределах

280 – 112 = 168 < b₂ < 280 + ¥ = ¥;

360 – 156 = 204 < b₄ < 360 + ¥ = ¥

не меняют точку оптимального решения.

В то же время при изменении дефицитного фонда рабочего времени фрезерного и сварочного оборудования в диапазоне

120 – 43,08 = 76,92 < b₁ < 120 + 43,08 = 163,08;

240 – 120 = 120 < b₃ < 240 + 180 = 420

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

Рис. 1.22. Отчет по результатам

Рис. 1.23. Отчет по устойчивости

94