Оптимизация бизнес-модели

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

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

optimization1.gif

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

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

optimization2.gif

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

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

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

Решение

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

Если в меню Сервис или на вкладке Данные вашего Excel такой команды нет – ничего страшного - значит надстройка просто еще не подключена. Для ее подключения:

  • в Excel 2003 и старше - откройте меню Сервис – Надстройки (ToolsAdd-Ins), в появившемся окне установите флажок Поиск решения (Solver) и нажмите ОК. Excel активирует выбранную надстройку и в меню Сервис (Tools) появится новая команда – Поиск решения (Solver).
  • в Excel 2007 и новее - нажать кнопку Офис, далее выбрать Параметры ExcelНадстройкиПерейти (Excel Options - Add-Ins - Go To)

Запустим надстройку. Откроется вот такое окно:

optimization3.gif

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

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

    optimization4.gif

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

optimization5.gif

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

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

optimization6.gif

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

optimization7.gif

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

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

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

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

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

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




13.05.2013 14:33:00
Николай, насколько я знаю, Поиск решения ограничен 200 переменными и 200 ограничений.
Нельзя ли, как в случае с описанными выше "откатами", увеличить это число?
14.05.2013 09:52:57
Увеличить - сомневаюсь, не видел такого, но есть отдельные программные продукты для апгрейда Solver. Например http://www.solver.com/upgrade-excel-solver
28.06.2013 20:17:31
Николай, у Вас на старой Планете, Поиск решения рассматривался на примере "выгодности" кредита. Вы могли бы снова разместить тот пример?
01.07.2013 21:00:46
На примере выгодности кредита рассматривался не Поиск решения, а Сценарии. Весьма спорный был пример с кучей справедливой критики от финансистов, поэтому я его и убрал :)
01.07.2013 21:39:42
Спасибо, Вас понял. Теперь ясно, а то у меня Поск решения на кредитной таблице только сумму аннуитетных платежей может раскинуть. Пошел курить "Что если"
02.07.2013 10:26:10
Таблицу данных тоже можно "покурить" :) Будет раскидывать все возможные варианты по набору значений двух параметров (например, по одной оси - различные варианты ставки, по другой - срока, на пересечении - сумма выплат).
17.11.2014 07:50:40
могли бы поделиться решением выгодности кредита от разных банков?
Николай, спасибо Вам огромное за Ваш труд!
26.07.2013 10:59:55
Вы спрашивали про управление Поиском решения с помощью VBA - посмотрите тут хорошую статью http://peltiertech.com/Excel/SolverVBA.html
12.01.2014 08:42:33
  Доброго времени суток Николай! Извените что обращаюсь к Вам так часто с вопросами, но мои знания в этой области хромают. По этому у меня к Вам не вопрос, а просьба нельзя л и сконструировать Solver отдельно по моему случаю? Если Я опишу ситуацию?
12.01.2014 10:41:27
Ихтиёр, с такими задачами лучше на форум, он для того и существует :) Только правила сначала почитайте.
Наверх