Оптимизация доставки

Оптимизация доставки

Постановка задачи

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

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

Решение

В математике подобные задачи выбора оптимального маршрута по нескольким точкам относят к классу так называемых «транспортных задач» (Vehicle Routing Problems, VRP). И, конечно же, давно разработаны способы их решения. Excel предоставляет пользователю один из них – с помощью мощной надстройки Поиск решения (Solver) в меню Сервис (Tools).

Если в меню Сервис вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения откройте меню Сервис – Надстройки (Tools – Add-Ins), в появившемся окне установите флажок Поиск решения (Solver) и нажмите ОК. Excel активирует выбранную надстройку и в меню Сервис (Tools) появится новая команда – Поиск решения (Solver). В Excel 2007 нужно нажать кнопку Офис, далее выбрать Параметры Excel – Надстройки – Перейти.

Перед началом оптимизации необходимо будет составить несложную таблицу  на листе Excel – нашу математическую модель, описывающую ситуацию:

Подразумевается, что:

• Серая таблица (B3:G5) описывает стоимость доставки единицы от каждого склада до каждого магазина.
• Лиловые ячейки (C14:G14) описывают необходимое для каждого магазина количество товаров на реализацию.
• Красные ячейки (J10:J11) отображают емкость каждого склада – предельное количество товара, которое склад может вместить.
• Желтые (C12:G12) и синие (H10:H11) ячейки – соответственно, суммы по строке и столбцу для зеленых ячеек.
• Общая стоимость доставки (E17) вычисляется как сумма произведений количества товаров на соответствующие им стоимости доставки.

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

Чтобы выполнить такую  оптимизацию откроем меню Сервис (Tools) и выберем команду Поиск решения (Solver). В Excel 2007 это будет кнопка Поиск решения (Solver) на вкладке Данные (Data). Откроется вот такое окно:

В этом окне нужно задать следующие настройки:

• Целевая ячейка (Target cell) – тут необходимо указать конечную главную цель нашей оптимизации, т.е. розовую ячейку с общей стоимостью доставки (E17). Целевую ячейку можно минимизировать (если это расходы, как в нашем случае), максимизировать (если это, например, прибыль) или попытаться привести к заданной константе.
• Изменяемые ячейки (By changing cells) – здесь укажем зеленые ячейки (C10:G11), варьируя значения которых мы хотим добиться нашего результата – минимальных затрат на доставку.
• Ограничения (Subject to the Constraints) – список ограничений, которые надо учитывать при проведении оптимизации. В нашем случае это ограничения на вместимость складов и потребности магазинов. Для добавления ограничений в список нужно нажать кнопку Добавить (Add) и ввести условие в появившееся окно: 
   
  

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

Оно дополнительно уточнит, что  объем перевозимого товара (зеленые  ячейки) не может быть отрицательным  – для человека такое само собой очевидно, но для компьютера это надо прописать явным образом.

После настройки всех необходимых  параметров окно должно выглядеть следующим  образом:

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

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

Если найденное решение нам  подходит, то можно его сохранить, либо откатиться назад к исходным значениям и попробовать еще  раз с другими параметрами. Также можно сохранить подобранную комбинацию параметров как Сценарий (см. прошлый пример). По желанию пользователя Excel может построить три типа Отчетов по решаемой задаче на отдельных листах: отчет по результатам, отчет по математической устойчивости решения и отчет по пределам (ограничениям) решения, однако они, в большинстве случаев, интересны только специалистам.

Бывают, однако, ситуации, когда Excel не может найти подходящего решения. Имитировать такой случай можно, если указать в нашем примере требования магазинов в сумме большие, чем общая вместимость складов. Тогда при выполнении оптимизации Excel попытается приблизиться к решению, насколько это возможно, а затем выдаст сообщение о невозможности найти решение. Тем не менее, даже в этом случае мы имеем массу полезной информации – в частности можем видеть «слабые звенья» наших бизнес-процессов и понять направления совершенствования.

Рассмотренный пример, конечно, является относительно простым, но легко масштабируется под решение гораздо более сложных нелинейных задач. Например:

• Оптимизация распределения финансовых средств по статьям расходов в бизнес-плане или бюджете проекта. Ограничениями, в данном случае, будут являться объемы финансирования и сроки выполнения проекта, а целью оптимизирования – максимизация прибыли и минимизация расходов на проект.
• Оптимизация расписания сотрудников с целью минимизации фонда заработной платы предприятия. Ограничениями, в этом случае, будут пожелания каждого сотрудника по графику занятости и требования штатного расписания.
• Оптимизация инвестиционных вложений – необходимость грамотно распределить средства между несколькими банками, ценными бумагами или акциями предприятий с целью, опять же, максимизации прибыли или (если это более важно) минимизации рисков.

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