Дефицитные ресурсы и линейная оптимизация
Здесь мы рассматриваем задачи, в которых в качестве ограничений выступают те или иные дефицитные ресурсы.
Узнаем, как просто найти оптимальное решение, если дефицитный ресурс один, и почему не существует простого решения, если дефицитных ресурсов несколько.
Вы научитесь:
- Определять дефицитные ресурсы.
- Легко находить оптимальное решение, если у вас всего один дефицитный ресурс.
Кейс: Компания "Холод".
Компания "Холод" занимается поставками замороженных овощей в супермаркеты.
Замороженные овощи приходят от поставщиков в стандартных картонных коробках, объем которых представлен в таблице (в литрах). Недельный запас замороженных овощей прибывает на склад в понедельник утром. Как правило, в конце недели склад компании пустеет. Объем склада - 160 кубических метров.
Компания пользуется кредитной линией, допускающей еженедельный расход в 900 000$ на покупку недельного запаса овощей (оплата может быть произведена единовременно в понедельник утром).
Компания прогнозирует объем продаж на каждую неделю как минимальное и максимальное количества коробок каждого типа овощей, которые будут проданы на следующей неделе. Минимальное количество определяется контрактами, которые компания заключила с небольшим количеством мини-супермаркетов. Максимальное количество – это пессимистический прогноз рыночного спроса на предстоящей неделе.
Цены, по которым продукция закупается у поставщика и отпускается потребителям, приведены в таблице, вместе с минимальными и максимальными объемами продаж каждого продукта на следующей неделе.
Замороженные овощи приходят от поставщиков в стандартных картонных коробках, объем которых представлен в таблице (в литрах). Недельный запас замороженных овощей прибывает на склад в понедельник утром. Как правило, в конце недели склад компании пустеет. Объем склада - 160 кубических метров.
Компания пользуется кредитной линией, допускающей еженедельный расход в 900 000$ на покупку недельного запаса овощей (оплата может быть произведена единовременно в понедельник утром).
Компания прогнозирует объем продаж на каждую неделю как минимальное и максимальное количества коробок каждого типа овощей, которые будут проданы на следующей неделе. Минимальное количество определяется контрактами, которые компания заключила с небольшим количеством мини-супермаркетов. Максимальное количество – это пессимистический прогноз рыночного спроса на предстоящей неделе.
Цены, по которым продукция закупается у поставщика и отпускается потребителям, приведены в таблице, вместе с минимальными и максимальными объемами продаж каждого продукта на следующей неделе.
- Определите объем закупок каждого типа овощей, максимизирующий прибыль компании.
Организация данных и решение задачи.
Перенесем данные задачи на рабочий лист MS Excel.
- Нам необходимо вычислить число коробов каждого продукта, которые необходимо закупить. Добавим столбец "количество коробов" и выделим его желтым цветом. Это область переменных в нашей задаче.
- Поскольку мы будем искать решение, соответствующее максимальной прибыли, то необходимо вычислить эту самую прибыль.
- Что такое прибыль? Это количество денег, которое мы получим при продаже нашей продукции за вычетом себестоимости этой продукции.
- Тогда, если из отпускной цены продукта вычесть его закупочную цену и результат умножить на число проданных коробов, то мы получим прибыль от продажи этого продукта. Сложив такую прибыль по всем проданным продуктам, мы получим искомую суммарную прибыль.
- Разницу между отпускной ценой и закупочной ценой назовем маржа.
- Замороженные овощи должны храниться в коробках на складе, но объем склада фиксирован. Следовательно, нам нужна ячейка в таблице, в которой мы будем считать занятый объем склада. Назовем эту ячейку "Склад", а саму ячейку окрасим в голубой цвет.
- У нас имеется и бюджет на закупку. Эта кредитная линия, которую выделяет банк. Поэтому суммарный объем закупки в деньгах не может превысить установленного лимита в 900 тыс. долларов.
- Назовем ячейку, в которой будет вычисляться стоимость закупленных коробов с овощами, "Деньги" и выделим ее голубым цветом.
- Для вычисления необходимых на закупку денег умножим закупочную цену каждого продукта на количество закупленных коробов и полученные результаты сложим.
Мы построили математическую модель задачи, выявили ограничения, выделили область переменных, записали формулы.
Теперь введем данные в панель "Параметры поиска решений" (анимация).
В полученном решении мы полностью исчерпали кредитную линию, потратив на закупку 900 тысяч денежных единиц, и при этом складские площади заполнились не полностью. Чуть больше 40 кубических метров остались не занятыми.
Какие продукты нам предлагают закупить?
Какие продукты нам предлагают закупить?
Неожиданный результат!
Поиск решения советует нам закупить только кукурузу! Но почему?
Посмотрим внимательно на таблицу данных и подумаем, какие показатели определяют выбор кукурузы. Для этого добавим еще два столбца и найдем отношение маржи к объему продукта и отношение маржи к закупочной цене.
Выводы:
(анимация)
Поиск решения советует нам закупить только кукурузу! Но почему?
Посмотрим внимательно на таблицу данных и подумаем, какие показатели определяют выбор кукурузы. Для этого добавим еще два столбца и найдем отношение маржи к объему продукта и отношение маржи к закупочной цене.
Выводы:
- кукуруза имеет не самый высокий показатель маржи.
- кукуруза имеет не самый высокий показатель отношения маржи к объему.
- у кукурузы самый высокий показатель отношения маржи к цене закупки.
(анимация)
Анализ результата.
Итак, что мы выяснили:
- В задаче имеется два ресурса: деньги и объем склада.
- В полученном решении первым закончились деньги, следовательно деньги - дефицитный ресурс.
- Кукуруза имеет самый высокий показатель отношения маржи к цене закупки, то есть отношения извлекаемой прибыли к потраченным средствам, или другими словами, отношение извлекаемой прибыли к использованию дефицитного ресурса.
❛❛ самый выгодный продукт не тот, у которого самая высокая маржа, а тот, который приносит наивысший эффект в расчете на единицу дефицитного ресурса.
Вы, должно быть, задаетесь вопросом:
- А почему мы никак не учитываем минимальное и максимальное количество коробов, на которое уже заключены договора, и которое мы планируем продать?
- Количество заказанных коробов должно быть не меньше минимального заказанного значения и не больше максимального значения, которое мы планировали продать.
- Математически это требование запишем двумя формулами:
- $G$5:$G$19 >= $D$5:$D$19
- $G$5:$G$19 <= $E$5:$E$19
Как видим, при введении дополнительных ограничений суммарная прибыль уменьшилась.
Да, для этого нужно сделать следующее:
Решение задачи с одним дефицитным ресурсом без применения "Поиска решения".
А можно ли решить эту задачу без применения оптимизационного инструмента?Да, для этого нужно сделать следующее:
- Выявить дефицитный ресурс.
- Найти отношение маржи к единице дефицитного ресурса.
- Отсортировать продукты по убывающей значения отношения маржи к дефицитному ресурсу..
- Закупить минимально необходимое количество всех продуктов. Если можете что-то не закупать, не закупайте.
- Закупите максимально возможное для вас количество высокорейтинговых продуктов (количество, которое сможете продать).
- Остановите изменение плана минимальных закупок на продукте, на котором дефицитный ресурс исчерпается.
- В нашем случае деньги закончатся на закупке перуанских бобов. Вычислить плановое количество закупки этого продукта можно так:
- после закупки зеленых бобов на закупку следующего продукта останется 180,6 тыс. денежных единиц ( 900 000 - 719 400 = 180 600 )
- эту сумму разделим на закупочную цену перуанских бобов и получим 2150
(180 600 / 84 =2150 )
Модифицируем задачу.
Предположим, что компании удалось договориться с банком, и как значимому клиенту, банк нарастил кредитную линию на 30%. Теперь в нашем распоряжении есть 1200 тыс. условных денежных единиц.- Для расчета оптимального плана закупок в нашей модели нужно только заменить 900тыс. на 1200тыс.
- Запустим поиск решения и получим новый результат.
Как видим, в этом случае кредитная линия не выбрана полностью, деньги на закупку у компании еще остаются, а вот складские площади закончились. Значит дефицитным ресурсом в этом случае стал объем склада.
Применив алгоритм, описанный выше, решим задачу без применения инструмента оптимизации и сравним полученные результаты.
Обратите внимание, что при приближении к граничному значению объема склада, необходимо отслеживать эту величину с большой точностью. Последнее значение вычисляем так:
Применив алгоритм, описанный выше, решим задачу без применения инструмента оптимизации и сравним полученные результаты.
Обратите внимание, что при приближении к граничному значению объема склада, необходимо отслеживать эту величину с большой точностью. Последнее значение вычисляем так:
- после того, как в план закупок будут внесены 3300 коробов перуанских бобов заполненный складской объем составит 158.135 куб.м
- тогда на следующий продукт останется 1.865 куб.м (160 - 158.135 = 1.865 )
- в этот объем мы сможем вместить только 615 коробов с зелеными бобами
( 1.865 *1000 / 16.1 = 115.8; 500 + 115 = 615 )
Как видим, мы получили почти оптимальное решение. Разница в количестве закупленных коробов брокколи и зеленых бобов. Оптимизатор получил прибыль на 2 денежных единицы больше, чем получили мы, действуя по нашему алгоритму.
Мы снова доказали, что при одном дефицитном ресурсе всегда можно найти оптимальное решение без использования инструментов оптимизации.
А если дефицитных ресурсов два?
В полученном результате оба ресурса будут исчерпаны полностью. А это значит, что у нас теперь два дефицитных ресурса.
Вы можете попытаться вручную найти оптимальное решение самостоятельно. Если у вас получится, пришлите этот алгоритм мне. Я с удовольствием изучу его. Но предупреждаю, что эта задача пока не разрешима за разумное время. Зато Поиск решения справляется с задачей за доли секунды.
Найденное решение приведено ниже:
Мы снова доказали, что при одном дефицитном ресурсе всегда можно найти оптимальное решение без использования инструментов оптимизации.
А если дефицитных ресурсов два?
Новая модификация задачи.
Изменим условие задачи еще раз.- Банк отказался от увеличения кредитной линии на 30% и компания снова располагает 900 тыс. денежных единиц, а поскольку, как известно, беда не приходит одна, часть холодильных мощностей склада вышли из строя, поэтому доступный объем склада стал 120 куб. метров.
- Найти оптимальный план закупки в изменившихся условиях.
В полученном результате оба ресурса будут исчерпаны полностью. А это значит, что у нас теперь два дефицитных ресурса.
Вы можете попытаться вручную найти оптимальное решение самостоятельно. Если у вас получится, пришлите этот алгоритм мне. Я с удовольствием изучу его. Но предупреждаю, что эта задача пока не разрешима за разумное время. Зато Поиск решения справляется с задачей за доли секунды.
Найденное решение приведено ниже:
Если у вас есть один дефицитный ресурс и вы планируете интуитивно и угадываете лучший результат, то не удивляйтесь (восхищайтесь) своей проницательности. В этом случае всегда существует простой алгоритм нахождения решения близкого к оптимальному. Но если дефицитных ресурсов два или больше, то не существует простых решений для получения оптимального результата.
В этом случае получить оптимальное решение вам помогут методы линейной оптимизации.