четверг, 8 декабря 2016 г.

Планирование запасов. И никаких скриптов.

Сегодня расскажу об автоматизации планирования складских запасов. Это классическая задача поиска оптимального решения. Сциллой тут является дефицит оборотных средств, который в последнее время серьезно обострился повсеместно, а Харибдой - наличие ассортимента на складе, от которого напрямую зависит объем продаж и благополучие компании.

Рассчитать норматив хранения по номенклатурным группам несложная задача для рядового финансового аналитика. Прогнозный баланс, выделение оборотных средств на дебиторку и склад, потом распределение суммы склада в пропорциях плана продаж по номенклатурным группам и в конце отдать руководителю отдела продаж чтобы он “доработал напильником”. Гораздо сложнее задача в режиме текущей работы правильно определить необходимость и объем заказа.

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

Полностью от человека избавиться не получится, поэтому надо скорректировать задачу: требуется разработать систему поддержки принятия решений. А именно, лицу, принимающему решение об объеме “подтоварки” склада, предоставить в удобном виде информацию о товарах, которые уже давно пора заказывать, а также о товарах, которые пока есть, но ими можно “добивать” до кратности заказа.

Сначала определим логику, которую мы хотим заложить в алгоритм системы.
  1. В нашем бизнесе ярко выраженная сезонность, поэтому мы ввели у себя сезоны: “несезон”, с декабря по май, нет продаж, низкие остатки; “предсезон”, с июня по август, продажи еще невысоки, но надо наращивать остатки к сезону; “сезон”, пик продаж, но уже надо снижать остатки к зиме.
  2. Для каждого сезона мы разработали два норматива: “минимум”, минимальный остаток, который должен поддерживаться на складе, если товара меньше, то надо его срочно заказывать; “максимум”, это граница, ниже которой товар можно докупать в случае необходимости довести заказ до нужного объема.
  3. Установили еженедельный контроль нормативов склада и принятия решения о размещении заказа.
Складской учет мы ведем в программе 1С:Управление торговлей. Систему планирования запасов было решено делать на базе Таблицы Google в облаке. Фактические остатки просто копируются из отчета “Остатки товаров на складах” в 1С на отдельный лист ‘фактические остатки’ нашей таблицы. Просто Ctrl-C+Ctrl-V. Примерно так.


Делаем на соседней странице ‘нормативные остатки’ справочник нормативов. Делать его надо простым списком, а не двумерной таблицей. То есть, на каждый сезон по каждому товару будет просто отдельная строка а не разные колонки. Создаем таблицу для каждого сезона и вставляем их друг под другом. Вот так.


Чтобы сравнить нормативы по конкретному товару в разные сезоны достаточно вверху задать фильтр по данному товару.

А дальше магия формул. Создаем лист ‘запасы’. Делаем шапку

В шапке формулы простые. В ячейке A2 текущая дата
=TODAY().
В ячейке C2 текущая дата пересчитывается в номер сезона
=IF(OR(MONTH(A2)<5;MONTH(A2)=12);1;IF(MONTH(A2)<8;2;3)).
После этого заносим список номенклатуры, которая входит в складскую программу. Мы хотим максимально снизить влияние человеческого фактора. Поэтому при копировании остатков из 1С оператор выгружает все остатки подряд. А на листе ‘запасы’ мы вносим (один раз, при создании таблицы) только те товары, для которых поддерживаем складской запас. Для этих товаров ставим формулы расчета заказа.


В колонку “Свободный остаток…” нам надо загрузить фактический остаток товара. Можно это сделать, например, формулой =VLOOKUP(), но я сделал своей любимой формулой многофакторного суммирования (для строки 78)
=SUMIFS('фактические остатки'!B:B;'фактические остатки'!A:A;'Запасы'!A78).
В колонки “мин” и “макс” нужно подтянуть актуальные для текущей даты нормативы. Поскольку каждый сезон у нас в отдельной строке опять применяем ту же формулу (для той же строки, для колонки “мин”)
=IF(SUMIFS('Нормативные остатки'!D:D; 'Нормативные остатки'!B:B; 'Запасы'!A78; 'Нормативные остатки'!C:C; 'Запасы'!$C$2)>0; SUMIFS('Нормативные остатки'!D:D; 'Нормативные остатки'!B:B; 'Запасы'!A78; 'Нормативные остатки'!C:C; 'Запасы'!$C$2); 0).
В данном случае мы многофакторное суммирование обернули в конструкцию =IF(), чтобы в случае отсутствия товара на складе принудительно поставить в ячейке нуль. С ячейками, в которых может быть пустое значение наравне с числами сложней работать, не все числовые формулы корректно обрабатывают пустую ячейку. По этой же причине я отказался от формулы =VLOOKUP() в пользу =SUMIFS(). Хотя разместив сезоны по колонкам их можно было бы автоматически достать первой формулой.

Колонка “дозаказ” это просто разница между максимальным нормативом и текущим остатком. Причем если текущий остаток превышает норматив (разница отрицательная), то мы товар не заказываем вообще. Поэтому колонку “дозаказ” не заполняем. Для этого оборачиваем разность в условие
=IF(D78-B78>0;D78-B78;"").
Для удобства работы с отчетом надо еще выделить товары, по которым остаток меньше минимального норматива, то есть те, которые надо срочно заказывать. Поэтому мы их выделяем красным тревожным цветом. Делаем это с помощью условного форматирования (меню “Формат->Условное форматирование…”)


Вот и все. И никаких гвоздей скриптов. Теперь достаточно каждый понедельник открыть Таблицу, загрузить остатки из 1С (там два действия: Ctrl-C, Ctrl-V) и расчет заказа посчитается автоматически. Чтобы его посмотреть надо установить на листе ‘запасы’ фильтр в колонке “дозаказ” отключив пустые ячейки


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

Существующий функционал можно улучшать дальше. Например, создать Таблицу, открыть ее поставщикам для просмотра, и выгружать в нее автоматически формулой =IMPORTRANGE(QUERY(...)) товары для заказа. Чтобы поставщики уже сами готовили и отправляли спецификации на поставку, а нам просто надо было акцептовать нужную. Или если добавить немного Google Script можно, например, присылать выборку сразу на почту сотруднику, принимающему решения. Или присылать руководителю на почту письмо о том, что оператор к “назначенному” сроку все еще не перенес остатки из 1С. Совершенство бесконечно.


3 комментария:

  1. Вот вся суть 1С видна. Все её покупают, а пользоваться не умеют.

    ОтветитьУдалить
    Ответы
    1. Алексей, добрый день! Стараюсь никогда не судить об умениях других людей, не зная их. Легко ошибиться.

      1С что-то делает функционально и удобно. Этим все пользуются и поэтому покупают 1С. А часть модулей нефункциональны и неэргономичны. Поэтому ими никто не пользуется. На мой взгляд это ближе к истине.

      Удалить
    2. Аналитика и представление данных - это функционал электронных таблиц, а не базы данных. У 1С нет вообще никакого инструментария для этого. К сожалению. Вот еще пример, который я пытался сделать целиком в 1С, но лучший вариант получился 1С+Таблица: https://soppacloudbusiness.blogspot.ru/2016/09/blog-post.html

      Удалить