Оборачиваемость запасов в Excel: формулы, дни запаса и пример

1. Что показывает оборачиваемость и зачем считать несколько показателей

Оборачиваемость показывает, насколько размер запаса соответствует реальному движению товара. Два SKU могут иметь одинаковый остаток 300 единиц, но если первый продаётся по 20 единиц в день, а второй по 2, управленческий смысл этих остатков противоположный: первого хватит примерно на 15 дней, второго — на 150. Поэтому одна сумма склада почти ничего не говорит о качестве запасов.

В рабочем отчёте полезно одновременно видеть четыре показателя: средний остаток за период, число оборотов, дни хранения и покрытие текущего остатка. Число оборотов описывает скорость использования среднего запаса, дни хранения переводят эту скорость в более понятный календарный масштаб, а покрытие отвечает на отдельный вопрос — на сколько дней хватит именно сегодняшнего остатка при выбранном темпе расхода.

Не ставьте цель просто максимизировать оборачиваемость. Слишком высокий показатель может означать не эффективный склад, а хронический дефицит: товар быстро заканчивается, продажи теряются, а средний остаток искусственно низкий. Поэтому рядом с оборотами нужны срок поставки, страховой запас и факты отсутствия товара. Задача анализа — найти баланс между замороженными деньгами и риском недопродажи.

2. Структура Excel-файла: движения, остатки, нормативы и анализ

Для регулярного расчёта удобно разделить книгу на четыре листа. «Движения» содержит дату, SKU, тип операции, количество, себестоимость и склад. «Остатки» хранит снимки остатков по датам. «Нормативы» — срок поставки, целевые дни запаса, страховой запас и себестоимость. «Анализ» собирает показатели по каждому SKU и формирует статус: норма, риск дефицита или излишек.

Ключом должен быть стабильный SKU или артикул, а не свободно введённое название товара. Даты храните как настоящие даты Excel, количества — числами, направление движения — отдельным полем. Исходные диапазоны превратите в таблицы через Ctrl+T: тогда новые строки автоматически попадают в формулы, фильтры и сводные отчёты.

На итоговом листе достаточно колонок: SKU, Товар, Расход за период, Остаток на начало, Остаток на конец, Средний остаток, Обороты, Дни хранения, Средний дневной расход, Текущий остаток, Покрытие в днях, Срок поставки, Страховой запас, Точка заказа, Рекомендуемый запас, Излишек/дефицит в штуках, Себестоимость, Сумма замороженных денег и Статус. Такая структура позволяет перейти от общего показателя к конкретному действию по SKU.

3. Средний остаток и расход: формулы, от которых зависит весь расчёт

Самый простой средний остаток — среднее между началом и концом периода: =(B2+C2)/2. Например, если на 1 апреля было 180 единиц, а на 30 апреля осталось 120, средний остаток равен 150. Этот способ допустим при относительно ровном запасе, но может сильно искажать картину, если внутри месяца была крупная поставка или резкое списание.

Если есть ежедневные снимки, используйте =СРЗНАЧ(B2:AE2). Для еженедельных снимков логика та же. Чем сильнее запас колеблется внутри периода, тем важнее частые наблюдения. Текущий остаток нельзя подставлять вместо среднего: текущий нужен для покрытия и решения о заказе, средний — для оценки того, сколько запаса в среднем было связано в обороте.

Расход за период лучше собирать из журнала движений, а не переносить вручную. Если таблица «Движения» содержит колонки SKU, Тип и Количество, формула может выглядеть так: =СУММЕСЛИМН(Движения[Количество];Движения[SKU];[@SKU];Движения[Тип];"Расход"). При анализе конкретного месяца добавьте условия по дате. Возвраты и корректировки учитывайте по заранее определённому правилу, иначе один и тот же товар в разных месяцах будет считаться по-разному.

4. Формулы оборотов, дней хранения и текущего покрытия

Коэффициент оборачиваемости в натуральном выражении равен расходу за период, делённому на средний запас. Если D2 содержит расход 600 единиц, а E2 — средний остаток 150, формула =ЕСЛИОШИБКА(D2/E2;0) вернёт 4 оборота за период. Для денежного расчёта числитель и знаменатель должны быть в сопоставимой оценке: например, себестоимость расхода и средняя стоимость запаса.

Дни хранения для 30-дневного периода можно рассчитать как =ЕСЛИ(F2>0;30/F2;0), где F2 — число оборотов. При четырёх оборотах получится 7,5 дня. Для периода другой длины вместо 30 используйте фактическое число дней. Если сравниваете месяцы разной длины, лучше считать через средний дневной расход, чтобы календарь не создавал ложное отличие.

Покрытие текущего остатка считается отдельно. Сначала средний дневной расход: =D2/30. Затем, если G2 содержит дневной расход, а H2 — текущий остаток, используйте =ЕСЛИ(G2>0;H2/G2;0). Например, 260 единиц при расходе 20 в день дают 13 дней покрытия. Именно этот показатель удобно сравнивать со сроком поставки и целевым запасом.

5. Практический пример: три SKU, точка заказа и деньги в излишках

Рассмотрим апрель из 30 дней. Товар A: расход 600 единиц, средний остаток 150, текущий остаток 260, себестоимость 800 ₽, срок поставки 7 дней, страховой запас 40 единиц. Оборачиваемость =600/150=4 оборота, дни хранения =30/4=7,5 дня, средний дневной расход =600/30=20 единиц, а текущее покрытие =260/20=13 дней.

Точка заказа товара A равна 20×7+40=180 единиц. Если целевой запас после пополнения задан как 10 дней спроса плюс страховка, рекомендуемый уровень равен 20×10+40=240 единиц. Текущий остаток 260 выше норматива на 20 единиц. При себестоимости 800 ₽ потенциальный излишек — 20×800=16 000 ₽. Это уже сумма, которую можно сопоставить с другими SKU и расставить приоритеты.

Товар B расходуется по 90 единиц в месяц, средний остаток 180, текущий — 210. Получаем 0,5 оборота и около 60 дней хранения. При дневном расходе 3 единицы покрытие составляет 70 дней. Если целевой запас — 20 дней плюс страховка 15 единиц, норматив равен 75 единиц, а потенциальный излишек — 135. При себестоимости 1 200 ₽ это 162 000 ₽ замороженных денег — гораздо важнее, чем 20 лишних единиц товара A.

Товар C расходуется по 300 единиц в месяц, то есть 10 в день. Текущий остаток — 55, срок поставки — 6 дней, страховка — 20. Точка заказа равна 10×6+20=80 единиц. Остаток уже ниже точки заказа на 25 единиц, хотя товар ещё физически есть на складе. Такой пример показывает, почему статус «есть/нет» недостаточен: риск дефицита появляется раньше нулевого остатка.

6. Рекомендуемый запас, точка заказа и размер пополнения

Простой норматив запаса можно задать как средний дневной расход × целевые дни + страховой запас. Если G2 — дневной расход, J2 — целевые дни, K2 — страховка, формула =G2*J2+K2. Целевые дни не должны быть одинаковыми для всего каталога: они зависят от частоты поставок, минимальной партии, надёжности поставщика и стоимости дефицита.

Точка заказа отвечает на другой вопрос — когда нужно инициировать пополнение. Формула: средний дневной расход × срок поставки + страховой запас. В Excel: =G2*L2+K2, где L2 — срок поставки в днях. Если остаток меньше или равен точке заказа, позицию стоит проверить на заказ. Простая текстовая формула статуса: =ЕСЛИ(H2<=M2;"Заказать";"Запаса достаточно"), где M2 — точка заказа.

Не путайте точку заказа с количеством заказа. Если точка равна 180, это не значит, что нужно заказать 180 единиц. Размер пополнения можно считать до целевого уровня: =МАКС(0;N2-H2+O2), где N2 — целевой уровень после поставки, H2 — доступный остаток, O2 — уже зарезервированный или ожидаемый расход до прихода поставки. В реальном файле также учитывайте товар в пути и минимальную партию поставщика.

7. Как находить излишки, дефицит и приоритет в рублях

Для статуса используйте понятные бизнес-условия. Например: =ЕСЛИ(H2<=M2;"Риск дефицита";ЕСЛИ(H2>N2*1,5;"Излишек";"Норма")), где H2 — текущий остаток, M2 — точка заказа, N2 — рекомендуемый уровень. Коэффициент 1,5 — не универсальная норма, а пример порога; его лучше хранить в отдельной настройке, а не зашивать в десятки формул.

Излишек в штуках: =МАКС(0;H2-N2). Стоимость излишка: =МАКС(0;H2-N2)*P2, где P2 — себестоимость единицы. Дефицит до рекомендуемого уровня: =МАКС(0;N2-H2). Для закупок полезно отдельно считать сумму потенциального заказа, но не смешивать её со стоимостью уже имеющегося запаса.

Сортируйте проблемные позиции не только по количеству, но и по деньгам. Сто лишних дешёвых деталей могут быть менее важны, чем десять дорогих единиц. Для ассортимента из сотен SKU дополните отчёт ABC/XYZ-анализом: группа A показывает экономически значимые позиции, а XYZ — стабильность спроса. Тогда риск дефицита AX и излишек CZ получают разный управленческий приоритет.

8. Типичные ошибки и контрольные проверки

Первая ошибка — смешивать единицы измерения: расход в штуках нельзя делить на запас в рублях. Вторая — использовать текущий остаток вместо среднего при расчёте оборотов. Третья — считать нулевые продажи слабым спросом, не проверив, был ли товар доступен. Если SKU отсутствовал две недели, статистика продаж уже ограничена наличием, а не реальным спросом.

Четвёртая ошибка — один норматив для всех SKU. Поставка из соседнего города за два дня и импортная поставка за 45 дней требуют разных точек заказа. Пятая — игнорировать товар в пути и резервы. Шестая — сравнивать коэффициенты за разные периоды без нормализации. Седьмая — вручную исправлять итоговый статус вместо исправления исходных данных или нормативов.

Добавьте контрольные проверки: расход и остатки не отрицательны без объяснимой корректировки; каждый SKU имеет срок поставки и себестоимость; средний остаток рассчитан; неизвестных артикулов нет; позиции с нулевым расходом вынесены в отдельный список; сумма стоимости текущего запаса совпадает с контрольной суммой склада. Условное форматирование используйте только для визуального сигнала — статус должен существовать отдельным текстовым полем.

9. Как превратить расчёт в регулярный отчёт по запасам

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

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

Если нужен готовый файл, в SmartXLSX можно описать товары, движения, сроки поставки и нужные нормативы обычными словами и получить персональный XLSX. Для более широкого контроля склада используйте соседнее руководство по складскому учёту, для приоритизации ассортимента — ABC/XYZ-анализ, а для понимания спроса — анализ продаж. Так оборачиваемость становится частью системы решений, а не отдельным коэффициентом в отчёте.