Оптимизация затрат предприятия с помощью Excel: простое решение

33779
Павлов Николай
бизнес-тренер и консультант центра «Специалист» по приемам эффективной работы в Microsoft Office
Оптимизация затрат предприятия не под силу современной ERP-системе - она способна лишь составить отчет, сформировать бюджет, управлять платежами и т. д. Зато с задачей оптимизации управления затратами справится Excel.

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

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

Оптимизация затрат предприятия: поиск решения

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

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

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

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

Настройки оптимизации затрат предприятия

Чтобы выполнить оптимизацию затрат, нужно открыть меню «Сервис» (Tools) и выбрать команду «Поиск решения» (Solver). После этого откроется одноименное диалоговое окно, в котором потребуется задать следующие настройки:

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

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

Полезная статья? Добавьте страницу в закладки, сохраните, распечатайте или переадресуйте. 

Универсальная оптимизация затрат предприятия

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

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

Рисунок 1. Исходные данные для оптимизации транспортных затрат
Оптимизация затрат предприятия с помощью Excel: простое решение

Рисунок 2. Результаты оптимизации транспортных затрат
Оптимизация затрат предприятия с помощью Excel: простое решение

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



Подписка на статьи

Чтобы не пропустить ни одной важной или интересной статьи, подпишитесь на рассылку. Это бесплатно.

Школа

Школа

Проверь свои знания и приобрети новые

Записаться

Самое выгодное предложение

Самое выгодное предложение

Воспользуйтесь самым выгодным предложением на подписку и станьте читателем уже сейчас

Живое общение с редакцией

А еще...


Рассылка



© 2007–2016 ООО «Актион управление и финансы»

«Финансовый директор» — практический журнал по управлению финансами компании

Зарегистрировано Федеральной службой по надзору в сфере связи,
информационных технологий и массовых коммуникаций (Роскомнадзор)
Свидетельство о регистрации Эл №ФС77-43625 от 18.01.2011
Все права защищены. email: fd@fd.ru


  • Мы в соцсетях
×
Чтобы скачать документ, зарегистрируйтесь на сайте!

Это бесплатно и займет всего 1 минуту.

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль
Внимание! Вы читаете профессиональную статью для финансиста.
Зарегистрируйтесь на сайте и продолжите чтение!

Это бесплатно и займет всего 1 минут.

У меня есть пароль
напомнить
Пароль отправлен на почту
Ввести
Я тут впервые
И получить доступ на сайт
Займет минуту!
Введите эл. почту или логин
Неверный логин или пароль
Неверный пароль
Введите пароль