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

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

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

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

Давайте теперь разберем следующий пример проблемы номенклатуры продуктов. Решение проблемы можно найти в файле prodmix.xls (включенный в набор загружаемых файлов примеров), см. рис. 1.

Рис. 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). Нельзя производить отрицательное количество ни одного лекарства.

Как ввести эту модель в надстройку "Поиск решения"?

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

1. Чтобы начать, выберите команду Поиск решения в меню Сервис. (Инструкции по установке надстройки "Поиск решения" см. в статье Введение в оптимизацию с помощью надстройки "Поиск решения".)

Появится диалоговое окно Параметры поиска решения.

2. Чтобы задать целевую ячейку, щелкните поле Установить целевую ячейку и выберите нашу ячейку прибыли (ячейка D12). Чтобы задать изменяемые ячейки, щелкните в поле Изменяя ячейки, а затем укажите на диапазон D2:I2, содержащий количества произведенных лекарств в килограммах. Теперь диалоговое окно будет выглядеть как на следующем рисунке.

3. Теперь пора добавить в модель ограничения. Нажмите кнопку Добавить. Появится диалоговое окно Добавить ограничение.

4. Чтобы добавить ограничения на используемые ресурсы, щелкните поле, помеченное Ссылка на ячейку, а затем выберите диапазон D14:D15. Выберите <= в списке в середине диалогового окна. Щелкните поле, помеченное Ограничение, а затем выберите диапазон ячеек F14:F15.

Теперь гарантируется, что при проверке различных значений для изменяемых ячеек "Поиск решения" будет рассматривать только те комбинации, которые удовлетворяют обоим условиям: D14 <= F14 (используемые трудовые ресурсы меньше или равны доступным) и D15 <= F15 (используемые исходные материалы меньше или равны доступным исходным материалам).

5. Теперь, чтобы ввести ограничение на спрос, нажмите кнопку Добавить в диалоговом окне Добавить ограничение. Просто заполните поля диалогового окна Добавить ограничение, как показано на следующем рисунке.

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

§ D2 <= D8 (произведенное количество лекарства 1 меньше или равно спросу на лекарство 1)

§ E2 <= E8 (произведенное количество лекарства 2 меньше или равно спросу на лекарство 2)

§ F2 <= F8 (произведенное количество лекарства 3 меньше или равно спросу на лекарство 3)

§ G2 <= G8 (произведенное количество лекарства 4 меньше или равно спросу на лекарство 4)

§ H2 <= H8 (произведенное количество лекарства 5 меньше или равно спросу на лекарство 5)

§ I2 <= I8 (произведенное количество лекарства 6 меньше или равно спросу на лекарство 6)

6. Нажмите кнопку OK в диалоговом окне Добавить ограничение. Диалоговое окно Параметры поиска решения должно выглядеть, как на следующем рисунке.

7. В диалоговом окне Параметры поиска решения, открываемом нажатием кнопки Параметры в диалоговом окне Параметры поиска решения, введем ограничение, что все изменяемые ячейки – неотрицательны.

Выберите параметры Линейная модель и Значения не отрицательны, а затем нажмите кнопку "OK".

Что означает выбор этих параметров?

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

Мы выбрали Линейная модель, так как проблема номенклатуры продуктов – это специальный тип проблемы поиска решений, называемый линейной моделью. По существу, модель поиска решений является линейной при следующих условиях:

§ Целевая ячейка вычисляется путем сложения членов формы (изменяемая ячейка)*(константа).

§ Все ограничения удовлетворяют требованиям линейной модели. Это означает, что каждое ограничение оценивается путем сложения членов формы (изменяемая ячейка)*(константа) и сравнения этих сумм с константой.

Поиск по сайту: