Как определить, какая номенклатура продуктов обеспечивает максимальную рентабельность?

Компаниям часто требуется определить месячный (или недельный) график производства, определяющий, какое количество каждого из продуктов должно быть произведено. В своей простейшей реализации проблема номенклатуры продуктов сводится к тому, как определить, какое количество каждого продукта должно быть произведено за месяц, чтобы обеспечить максимальные доходы. Часто номенклатура продуктов должна удовлетворять следующим ограничениям:

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

Спрос на каждый продукт ограничен. В течение месяца производство продукта не может превышать спрос на него, так как избыточно произведенный продукт теряется (рассмотрим, например, скоропортящееся лекарство).

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

Наименование продукта
Трудовые ресурсы на производство 1кг продукта, человеко-часов					2,5	1,5
Сырьевые ресурсы на производство 1кг продукта, кг/кг	3,2	2,6	1,5	0,8	0,7	0,3
Рыночная стоимость 1кг произведенного продукта, $	$12,50	$11,00	$9,00	$7,00	$6,00	$3,00
Потребность в произведенном продукте, кг/месяц
Себестоимость 1кг произведенного продукта, $	$6,50	$5,70	$0,60	$2,80	$2,20	$1,20

Рис. 1. Пример номенклатуры продуктов.

В строке 4 рис.1 приведено количество человеко-часов, необходимое для производства килограмма каждого продукта, а строка 5 содержит количество килограммов исходного продукта, необходимое для производства килограмма каждого продукта. Например, производство килограмма продукта 1 требует 6 человеко-часов и 3,2 килограмма исходного материала.

Цена килограмма каждого лекарства приведена в строке 6, стоимость килограмма приведена в строке 7, а прибыль на килограмм – в строке 9. Например, продукт 2 стоит 11,00 руб. за килограмм, включая стоимость 5,70 руб. за килограмм, и прибыль 5,30 руб. за килограмм.

Месячный спрос на каждое лекарство приведен в строке 8. Например, для продукта 3 спрос составляет 1041 килограмм.

В этом месяце доступно 4500 человеко-часов и 1600 килограмм исходного материала. Как может эта компания получить максимальную прибыль?

Если бы мы ничего не знали о надстройке "Поиск решения", мы решали бы проблему, создавая электронную таблицу, в которой мы бы следили за прибылью для каждой номенклатуры продуктов и за соответствующим использованием ресурсов. Затем с помощью проб и ошибок мы бы меняли номенклатуру продуктов, чтобы оптимизировать прибыль без превышения значений доступных трудовых ресурсов и исходных материалов и без превышения спроса на каждое из лекарств. В этом процессе мы используем надстройку "Поиск решения" только на стадии проб и ошибок. По существу же, надстройка "Поиск решения" – это механизм оптимизации, который безукоризненно выполняет поиск методом проб и ошибок.

Ключом к решению проблемы является эффективное вычисление используемых ресурсов и прибыли, связанных с каждой заданной номенклатурой продуктов. Важным средством, которое можно использовать для этих расчетов, является функция СУММПРОИЗВ. Эта функция умножает соответствующие значения диапазона ячеек и возвращает сумму этих значений. Размерности всех диапазонов значений, используемых при расчете СУММПРОИЗВ, должны быть одинаковы, то есть, можно использовать функцию СУММПРОИЗВ с двумя строками или двумя столбцами, но не со строкой и столбцом.

В качестве примера использования функции СУММПРОИЗВ при определении номенклатуры продуктов попробуем вычислить использование ресурсов. Трудовые ресурсы задаются следующей формулой:

 

 

(Трудозатраты для производства килограмма лекарства 1) *

(Количество килограммов лекарства 1) +

(Трудозатраты для производства килограмма лекарства 2) *

(Количество килограммов лекарства 2) +

...

(Трудозатраты для производства килограмма лекарства 6) *

(Количество килограммов лекарства 6)

В нашей электронной таблице мы могли бы вычислить используемые трудовые ресурсы (что было бы утомительно) как D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4. Аналогично, используемые исходные материалы могут быть вычислены как D2*D5+E2*E5+F2*F5+G2*G5+H2*H5+I2*I5. Ввод этих формул в электронной таблице для шести продуктов займет немало времени. Представьте, сколько времени займет ввод этих формул для компании, которая производит на своем заводе, скажем, 50 продуктов.

Намного проще рассчитать используемые трудовые ресурсы и исходные материалы, скопировав формулу из D14 в D15:

СУММПРОИЗВ($D$2:$I$2,D4:I4)

Эта формула вычисляет D2*D4+E2*E4+F2*F4+G2*G4+H2*H4+I2*I4 (наши используемые трудозатраты) и ее намного проще вводить.

Обратите внимание, что в диапазоне D2:I2 используется знак $, поэтому при копировании формулы номенклатура продуктов из строки 2 сохраняется. Формула в ячейке D15 вычисляет используемые исходные материалы.

Аналогичным образом, наша прибыль определяется следующей формулой:

(Прибыль на килограмм лекарства 1) *

(Произведенные килограммы лекарства 1) +

(Прибыль на килограмм лекарства 2) *

(Произведенные килограммы лекарства 2) +

...

(Прибыль на килограмм лекарства 6) *

(Произведенные килограммы лекарства 6).

Прибыль легко вычисляется в ячейке D12 с помощью следующей формулы:

СУММПРОИЗВ(D9:I9,$D$2:$I$2)

Теперь можно определить три части нашей модели номенклатуры продуктов для надстройки "Поиск решения":

Целевая ячейка	Изменяемые ячейки	Ограничения
Наша цель – обеспечить максимальную прибыль (вычисляемую в ячейке D12).	Количество произведенных килограммов каждого продукта (перечислено в диапазоне ячеек D2:I2).	Не использовать трудовых ресурсов и исходных материалов больше, чем доступно. То есть, значения в ячейке D14:D15 (используемые ресурсы) должны быть меньше или равны значениям ячеек F14:F15 (доступные ресурсы). Не производить лекарства больше, чем спрос на него. То есть, значения в ячейках D2:I2 (произведенные килограммы каждого лекарства) должны быть меньше или равны спросу на каждое лекарство (перечисленному в ячейках D8:I8). Нельзя производить отрицательное количество ни одного лекарства.