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

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

Припустимо, що компанія, де ви працюєте, має два складських приміщення, звідки товар поступає в п'ять ваших магазинів, розкиданих по всій Москві.

Кожен магазин в змозі реалізувати певний, відоме нам кількість товару. Кожен з складів має обмежену місткість. Завдання полягає в тому, щоб раціонально вибрати - з якого складу в які магазини потрібно доставляти товар, щоб мінімізувати загальні транспортні витрати.

Перед початком оптимізації необхідно буде скласти нескладну таблицю на аркуші Excel - нашу математичну модель, що описує ситуацію:

Сіра таблиця (B3: G5) описує вартість доставки одиниці від кожного складу до кожного магазину.
Лілові осередки (C14: G14) описують необхідне для кожного магазину кількість товарів на реалізацію.
Червоні осередки (J10: J11) відображають ємність кожного складу - гранична кількість товару, яке склад може вмістити.
Жовті (C12: G12) і сині (H10: H11) осередки - відповідно, суми по рядку і стовпцю для зелених осередків.
Загальна вартість доставки (E17) обчислюється як сума добутків кількості товарів на відповідні їм вартості доставки.

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

Якщо в меню Сервіс або на вкладці Дані вашого Excel такої команди немає - нічого страшного - значить надбудова просто ще не підключена. Для її підключення:

Запустимо надбудову. Відкриється ось таке вікно:

У цьому вікні потрібно задати такі параметри:

Цільова осередок (Targetcell) - тут необхідно вказати кінцеву головну мету нашої оптимізації, тобто рожеву клітинку із загальною вартістю доставки (E17). Цільову комірку можна мінімізувати (якщо це витрати, як в нашому випадку), максимізувати (якщо це, наприклад, прибуток) або спробувати привести до заданої константі.
Змінні комірки (Bychangingcells) - тут вкажемо зелені осередки (C10: G11), варіюючи значення яких ми хочемо домогтися нашого результату - мінімальних витрат на доставку.
Обмеження (SubjecttotheConstraints) - список обмежень, які треба враховувати при проведенні оптимізації. У нашому випадку це обмеження на місткість складів і потреби магазинів. Для додавання обмежень в список потрібно натиснути кнопку Додати (Add) і ввести умову в вікно, що з'явилося:

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

Воно додатково уточнить, що обсяг перевезеного товару (зелені осередки) не може бути негативним - для людини таке само собою зрозуміло, але для комп'ютера це треба прописати явно.

Після налаштування всіх необхідних параметрів вікно має виглядати наступним чином:

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

Зверніть увагу на те, як цікаво розподілилися обсяги поставок по магазинах, не перевищивши при цьому ємності наших складів і задовольнивши всі запити по необхідному кількості товарів для кожного магазину.

Якщо знайдене рішення нам підходить, то можна його зберегти, або відкотитися назад до початкових значень і спробувати ще раз з іншими параметрами. Також можна зберегти підібрану комбінацію параметрів як Сценарій. За бажанням користувача Excel може побудувати три типи Звітів з розв'язуваної задачі на окремих аркушах: звіт за результатами, звіт по математичної стійкості рішення і звіт по межах (обмеженням) рішення, однак вони, в більшості випадків, цікаві тільки фахівцям.

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

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

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