Решение. 1. Построим экономико-математическую модель представленной задачи линейного программирования (с учетом условия целочисленности).

1. Построим экономико-математическую модель представленной задачи линейного программирования (с учетом условия целочисленности).

Обозначим через (где ) – объём производства продукции ООО «ТИТАН» (количество гаек стремянки, гаек штанги, гаек МОД и колец шкворня).

Тогда дополнительная прибыль предприятия при заданном ассортименте выпускаемой продукции составит:

Ограничения по запасам имеющихся ресурсов:

Ограничения по объему производства:

Условия неотрицательности объемов производства:

Условия целочисленности объемов производства:

.

Экономико-математическая модель представленной задачи планирования производства составлена.

2. Определим с помощью надстройки «Поиск решения» в Microsoft Excel оптимальный план производства продукции ООО «ТИТАН» (количество гаек стремянки, гаек штанги, гаек МОД и колец шкворня).

На листе 1 новой книги Microsoft Excel, названной «Задача планирования производства», в ячейки А3: F12 введем исходные данные как на рис. 1.

Рис. 1. Ввод исходных данных задания 1

В ячейках А14: Е16 сформируем таблицу оптимального плана производства токарной продукции ООО «ТИТАН», причем ячейки В16: Е16 заполняем нулями (см. рис. 2).

Ячейки G7: G8 объединим и введем «Требуемое количество ресурсов», а в ячейку G9 формулу, определяющую общее количество стали, необходимое для производства всего объема токарной продукции: «=СУММПРОИЗВ(B9: E9; $B$16: $E$16)» (Для вставки функции «СУММПРОИЗВ» необходимо выполнить команду «Вставка»/«Функция», в открывшемся окне Мастера функции выбрать категорию «Математические», функцию СУММПРОИЗВ(). Данная функция возвращает сумму произведений соответствующих элементов массивов и диапазонов. Далее необходимо заполнить поля функции СУММПРОИЗВ согласно рис. 3: в поле «Массив 1» указываются ячейки B9: Е9, содержащие нормы затрат стали на производство единицы продукции каждого вида, в поле «Массив 2» – ячейки $B$16: $E$16, содержащие оптимальный план производства токарной продукции, причем ссылки на эти ячейки должны быть абсолютными, для чего после выбора этих ячеек нажимаем клавишу F4 на клавиатуре). Далее копируем формулу из ячейки G9 в ячейку G10, как на рис. 2.

Ячейки G11 и G12 объединим и введем формулу, определяющую общее количество денежных средств, необходимых для оплаты труда рабочих и последующую обработку произведенной продукции (рис. 2): =СУММПРОИЗВ(B11: E11; $B$16: $E$16)+СУММПРОИЗВ(B12: E12; $B$16: $E$16)».

Рис. 2. Ввод формул

Рис. 3. Ввод аргументов функции СУММПРОИЗВ()

Затем в ячейку А18 введем «Целевая функция (доп. доход ООО " ТИТАН" от производства токарной продукции):», а в ячейку G18 – формулу: =СУММПРОИЗВ(B4: E4; B16: E16)» (в поле «Массив 1» окна аргументов функции СУММПРОИЗВ указываются ячейки B4: E4, содержащие прибыль от реализации единицы продукции каждого вида, в поле «Массив 2» – ячейки B16: E16, содержащие оптимальный план производства токарной продукции ООО «ТИТАН»).

Результатом расчета дополнительной прибыли предприятия от производства токарной продукции в Microsoft Excel на данном этапе выполнения задания является значение, равное нулю (рис. 4). В дальнейшем (после выполнения некоторых манипуляций) в ячейке G18 будет рассчитана максимальная дополнительная прибыль ООО «ТИТАН».

Рис. 4. Результат вычисления введенных формул

Теперь определим с помощью надстройки «Поиск решения» в Microsoft Excel оптимальный план производства продукции ООО «ТИТАН» (количество гаек стремянки, гаек штанги, гаек МОД и колец шкворня), при котором предприятие получит дополнительный доход.

Для этого необходимо выделить ячейку, содержащую значение целевой функции (ячейку G18), и выполнить команду «Сервис»/ «Поиск решения…»[1]. В открывшемся окне надстройки «Поиск решения…» в поле «Целевая ячейка» уже будет указано: «$G$18» (т.е. ячейка, содержащая значение дополнительной прибыли рассматриваемого предприятия от производства токарной продукции), необходимо установить флажок «Равной максимальному значению», в поле «Изменяя ячейки» – выбрать «$В$16: $Е$16», т.е. ячейки, содержащие оптимальный план производства гаек стремянки, гаек штанги, гаек МОД и колец шкворня (рис. 5). Далее указываем ограничения следующим образом: нажимаем кнопку «Добавить», и в открывшемся окне вводим ограничение по запасам имеющихся ресурсов (стали и трудозатрат) (рис. 6), нажимаем «ОК», еще раз нажимаем кнопку «Добавить» и вводим ограничение по имеющимся денежным средствам на оплату труда рабочих и последующую обработку токарной продукции (рис. 7). Аналогично добавляем ограничение по объему производства (рис. 8), условие неотрицательности объема производства (рис. 9), а также условие целочисленности объема производства (рис. 10).

Рис. 5. Окно " Поиск решений" задания 1

Рис. 6. Ввод ограничения по запасам имеющихся ресурсов (стали и трудозатрат)

Рис. 7. Ввод ограничения по имеющимся денежным средствам

Рис. 8. Ввод ограничения по объему производства

Рис. 9. Ввод условия неотрицательности

Рис. 10. Ввод условия целочисленности

Далее в окне поиска решений (рис. 5) нажимаем кнопку «Параметры», в открывшемся окне «Параметры поиска решения» устанавливаем флажок «Линейная модель» (рис. 11), нажимаем «ОК». В окне поиска решений (рис. 5) нажимаем «Выполнить».

Рис. 11. Окно «Параметры поиска решения»

В результате получим такой план производства гаек стремянки, гаек штанги, гаек МОД и колец шкворня ООО «ТИТАН» (ячейки В16: Е16), при котором дополнительный доход предприятия примет максимальное значение, равное 26 650 руб. (ячейка G18) (рис. 12).

Рис. 12. Результат поиска решения (оптимального плана производства)

3. Дадим экономическую интерпретацию полученного решения. Сформулируем оптимальное управленческое решение в описанных условиях.

Для обеспечения максимальной дополнительной прибыли в размере 26 650 рублей предприятию ООО «ТИТАН» следует производить гайки стремянки в количестве 1300 шт., гайки штанги – 950 шт., гайки МОД – 260 шт. и кольца шкворня – 330 шт. в месяц.

Дефицитным ресурсом при этом являются трудозатраты, поскольку его остатки равны нулю. Увеличение недефицитных ресурсов: запаса стали, денежных средств на оплату труда рабочих и последующую обработку изделий, не может влиять на оптимальный план производства токарной продукции ООО «ТИТАН».

[1] Если во вкладке «Сервис» отсутствует пункт «Поиск решения…», необходимо выполнить команду «Сервис»/ «Надстройки…», и в открывшемся окне выбрать надстройку «Поиск решения», после чего во вкладке «Сервис» появится выбранный пункт.