Syntha Lab EN

Excel для ритейла, часть II: ABC-анализ и бюджет закупки OTB в таблице

Вторая часть трека показывает, как собрать в таблице две рабочие модели байера и планировщика: ABC-анализ ассортимента (какие позиции дают основную выручку) и бюджет закупки OTB по месяцам (сколько можно заказать, не создав излишка). Документ даёт структуру листов, формулы со ссылками на ячейки, порядок построения и контрольные проверки; ту же логику считают расчёты Lab, а таблица нужна, когда модель нужно показать, править и передать по частям.

Финансы Бренд и ритейл Проверено: 2026-11-15

Открыть интерактивно →

Зачем этот документ

ABC и OTB дают байеру и планировщику два главных ответа: на чём держится выручка и сколько можно закупить. Таблицу выбирают, когда модель должен видеть и править коллега: понятно, откуда каждое число, можно поменять покрытие или снижения и сразу увидеть, как изменится бюджет. Риск в том же: скрытая ошибка в формуле тянет за собой решение о закупке, поэтому в документе есть контрольные проверки, а входные ячейки отделены от формул. Читают его байер, планировщик, финансовый директор и аналитик.

Коротко

Что это за документ и зачем он нужен?
Вторая часть трека показывает, как собрать в таблице две рабочие модели байера и планировщика: ABC-анализ ассортимента (какие позиции дают основную выручку) и бюджет закупки OTB по месяцам (сколько можно заказать, не создав излишка). Документ даёт структуру листов, формулы со ссылками на ячейки, порядок построения и контрольные проверки; ту же логику считают расчёты Lab, а таблица нужна, когда модель нужно показать, править и передать по частям.
Кто отвечает за документ и как часто его пересчитывать?
Ведёт планировщик или аналитик; ABC пересчитывается раз в сезон и после больших распродаж, OTB каждый месяц; файл сдаётся байеру и финансовому директору с контрольным листом.
Какие ошибки встречаются чаще всего?
Бренд: Считать ABC только по выручке: модель с большой выручкой и малой маржой попадает в A и забирает место у прибыльных. Бренд: Вписать число запаса на начало руками в каждый месяц: цепочка рвётся, и правка одного месяца не двигает остальные. Бренд: Считать «заказано» без подтверждений: OTB получается завышенным, а потом запуск упирается в нехватку материала.

Для кого

Бренд
Бренд считает ABC по моделям и каналам, а OTB в роли плана производства и закупки материалов: запас на конец берёт из прогноза продаж коллекции, а «заказано» — из подтверждённых заказов фабрике.
Ритейл
Ритейлер считает ABC по категориям, брендам и магазинам, а OTB по категориям и месяцам в рознице без НДС; запас на конец берёт из покрытия в неделях, а «заказано» — из подтверждённых заказов поставщикам.

Из чего состоит

Структура рабочей книги

  • Лист «Данные»: плоская таблица позиций с выручкой или маржой за период; один столбец ключа, один столбец значения, без итогов внутри.
  • Лист «ABC»: отсортированный список, доля, накопленная доля и класс; пороги классов лежат в отдельных ячейках сверху.
  • Лист «OTB»: по столбцу на месяц; строки: продажи, снижения, потери, запас на конец, запас на начало, заказано, OTB; входы выделены цветом, как в выгрузках Lab.
  • Лист «Контроль»: проверки сходимости, каждая должна давать нуль; лист открывают первым при любой правке.

ABC-анализ в таблице

  • Сортировка: Данные → Сортировка по значению по убыванию; формулы доли и накопленной доли после сортировки пересчитываются сами.
  • Доля: =значение / СУММ(все значения); накопленная доля: =предыдущая накопленная + доля; для первой строки накопленная равна доле.
  • Класс по накопленной доле до строки: =ЕСЛИ(накоплено до строки < порог A; «A»; ЕСЛИ(накоплено до строки < порог B; «B»; «C»)); границы 80 и 95 % условны и выносятся в ячейки.
  • Бренд: ABC считают по марже модели, а не только по выручке: модель с большой выручкой и малой маржой не должна попадать в A без оговорки.
  • Ритейл: ABC по категориям и по брендам читается вместе: бренд из класса A в категории класса C — сигнал пересмотреть место на полке.
  • XYZ добавляют коэффициентом вариации по месяцам: =СТАНДОТКЛОН(диапазон) / СРЗНАЧ(диапазон); связка ABC и XYZ описана в математическом документе.

OTB в таблице

  • Формула месяца: OTB = продажи + снижения + потери + запас на конец − запас на начало − заказано, всё по рознице без НДС; ячейка OTB ссылается на строки выше.
  • Запас на конец месяца: =продажи следующего месяца × покрытие в месяцах; для последнего месяца плана продажи следующего вводят отдельной ячейкой.
  • Запас на начало: для первого месяца вводится фактический остаток, для остальных =запас на конец предыдущего столбца; так цепочка связана без ручных переносов.
  • Бренд: Строка «заказано» берёт подтверждённые заказы фабрике; OTB бренда означает, сколько ещё можно запустить без излишка к концу сезона.
  • Ритейл: OTB по рознице пересчитывают в себестоимость умножением на (1 − IMU категории); IMU своего сегмента, а не средний по сети.
  • Отрицательный OTB означает перезаказ: нужно отменить, сдвинуть или распродать, а не «сделать ноль».

Контроль модели

  • Сумма долей ABC равна 100 %, накопленная доля последней строки равна 100 %; сумма значений на листе ABC равна сумме на листе «Данные».
  • Запас на начало каждого месяца равен запасу на конец предыдущего; различие означает разорванную ссылку.
  • Бренд: Проверка допущения о покрытии: запас на конец, переведённый в недели, не выходит за границы категории и срока производства.
  • Ритейл: Сверка OTB с бюджетом закупки финансового плана: сумма по категориям не превышает утверждённый бюджет сезона.

Откуда берутся данные

Источник данныхПоля
Продажи по позициямВыручка или маржа по моделям, категориям, брендам за сезон или год, без НДС.
План продаж и покрытиеПродажи по месяцам плана, продажи следующего месяца, покрытие в месяцах, доля снижений и потерь.
Остатки и заказыФактический остаток на начало, подтверждённые заказы по месяцам поставки, IMU категории.

Как собрать по шагам

  1. Шаг 1: Соберите данные позиций на лист «Данные» плоской таблицей и проверьте итог с учётной системой.
  2. Шаг 2: Бренд: решите, по чему считать ABC: выручке, марже или штукам; для коллекции чаще берут маржу.
  3. Шаг 3: Отсортируйте позиции по убыванию, добавьте доли, накопленную долю и класс; пороги вынесите в отдельные ячейки.
  4. Шаг 4: Проверьте, что сумма долей равна 100 %, а число позиций классов в сумме равно числу строк.
  5. Шаг 5: Ритейл: введите продажи плана по месяцам, долю снижений и потерь, покрытие и продажи следующего месяца в отдельных ячейках.
  6. Шаг 6: Постройте строки: снижения = продажи × доля; запас на конец = продажи следующего месяца × покрытие; запас на начало ссылкой на предыдущий конец.
  7. Шаг 7: Бренд: добавьте строку «заказано» из подтверждённых заказов фабрике и посчитайте OTB как остаток возможного запуска.
  8. Шаг 8: Посчитайте OTB по формуле и переведите в себестоимость; суммы по месяцам и итог за сезон выведите на лист контроля.
  9. Шаг 9: Запустите сценарии: поменяйте покрытие или снижения в одной ячейке и запишите, как изменились итог и отрицательные месяцы.

Формулы

ПоказательФормулыПояснение
Доля и накопленная долядоля = значение ÷ сумма; накопленная = предыдущая накопленная + доляКласс присваивают по накопленной доле до строки: A до 80 %, B до 95 % (условные границы).
OTB месяцаOTB = продажи + снижения + потери + запас на конец − запас на начало − заказаноВсё по рознице без НДС; для перевода в себестоимость умножают на (1 − IMU).
Запас на конецзапас на конец месяца = продажи следующего месяца × покрытие в месяцахПокрытие задают ступенями по сезону: выше перед пиком, ниже к концу.
Коэффициент вариацииCV = стандартное отклонение ÷ среднее по месяцамДля XYZ: чем ниже CV, тем стабильнее спрос.

Пример с числами

OTB на шесть месяцев по розничным ценам без НДС, тыс. ₽, условный пример

МесяцПродажиСниженияПотериЗапас на конецЗапас на началоЗаказаноOTB
Сентябрь9001891 8001 900300527
Октябрь1 20036122 2501 8002501 448
Ноябрь1 50075151 9502 2502001 090
Декабрь1 300156131 2001 9500719
Январь80014481 0501 2000802
Февраль7007071 1251 0500852
Итого за сезон6 400499641 1251 9007505 438

Условия: покрытие 1,5 месяца продаж следующего месяца, потери 1 % продаж, продажи месяца после плана 750 тыс. ₽, запас на начало первого месяца 1 900 тыс. ₽. Запас на начало каждого следующего месяца равен запасу на конец предыдущего. OTB за сезон 5 438 тыс. ₽ в рознице без НДС, то есть 2 066 тыс. ₽ по себестоимости при IMU 62 %. ABC на тех же условиях: из 10 позиций класс A составляют 5 и дают 82,5 % выручки, класс B 3 позиции (15,0 %), класс C 2 позиции (2,5 %). Все числа условные.

Рисунки

Доля позиции в выручке, % (условный ABC на десять позиций)
Столбчатая диаграмма: M-1 26,3, M-2 19,4, M-3 15,6, M-4 11,9, M-5 9,4, M-6 6,9051015202530M-1M-1Доля выручки: 26,326,3M-2M-2Доля выручки: 19,419,4M-3M-3Доля выручки: 15,615,6M-4M-4Доля выручки: 11,911,9M-5M-5Доля выручки: 9,49,4M-6M-6Доля выручки: 6,96,9M-7M-7Доля выручки: 55M-8M-8Доля выручки: 3,13,1M-9M-9Доля выручки: 1,91,9M-10M-10Доля выручки: 0,60,6%
Данные таблицей
ПоказательДоля выручки
M-126,3
M-219,4
M-315,6
M-411,9
M-59,4
M-66,9
M-75
M-83,1
M-91,9
M-100,6

Доля позиции в выручке, % (условный ABC на десять позиций). Позиции класса A (5) дают 82,5 % выручки; хвост из 2 позиций класса C даёт около 2,5 %.

Накопленная доля выручки, %
Линейный график: Накопленная доля: 26,3 → 100020406080100M-1M-3M-5M-7M-9M-10Накопленная доля, M-1: 26,3Накопленная доля, M-2: 45,6Накопленная доля, M-3: 61,3Накопленная доля, M-4: 73,1Накопленная доля, M-5: 82,5Накопленная доля, M-6: 89,4Накопленная доля, M-7: 94,4Накопленная доля, M-8: 97,5Накопленная доля, M-9: 99,4Накопленная доля, M-10: 100% выручкиПозиции по убыванию выручки
Данные таблицей
Позиции по убыванию выручкиНакопленная доля
M-126,3
M-245,6
M-361,3
M-473,1
M-582,5
M-689,4
M-794,4
M-897,5
M-999,4
M-10100

Накопленная доля выручки, %. Кривая быстро растёт на первых позициях: пересечение с 80 % и 95 % даёт границы классов A и B.

Из чего складывается OTB первого месяца, тыс. ₽
Мост: Продажи +900, Снижения +18, Потери +9, Запас на конец +1 800, Запас на начало −1 900, Заказано −300, OTB 52705001 0001 5002 0002 5003 000ПродажиПродажиПродажи: +900+900СниженияСниженияСнижения: +18+18ПотериПотериПотери: +9+9Запас на конецЗапас на конецЗапас на конец: +1 800+1 800Запас на началоЗапас на началоЗапас на начало: −1 900−1 900ЗаказаноЗаказаноЗаказано: −300−300OTBOTBOTB: 527527тыс. ₽
  • рост
  • снижение
  • итог
Данные таблицей
ШагИзменениеУровень
Продажи+900900
Снижения+18918
Потери+9927
Запас на конец+1 8002 727
Запас на начало−1 900827
Заказано−300527
OTB—527

Из чего складывается OTB первого месяца, тыс. ₽. Продажи, снижения, потери и запас на конец увеличивают бюджет, запас на начало и заказанное уменьшают его.

Особенности по категориям

Одежда
В одежде ABC считают по моделям, а покрытие в OTB ступенчатое: выше перед сезонным пиком и ниже перед распродажей. Снижения в зимней и летней ветках разные, а редкие размеры не должны давать ложный класс C; их смотрят отдельным срезом.
Обувь
В обуви покрытие выше, потому что нужен размерный ряд, а OTB считают по паре модель-размер там, где ряд ломается. Снижения глубже в конце сезона, а ABC по моделям скрывает дыры в размерах, поэтому рядом ставят сводную по размерам.
Аксессуары
В аксессуарах позиций много, а средний чек низок: ABC по количеству даёт другую картину, чем по выручке, и считают оба. Покрытие выше из-за минимальной выкладки, а подарочные пики сдвигают OTB на ноябрь и декабрь.
Детское
В детском OTB привязан к сезонам школы и садика и к росту ребёнка: размеры устаревают быстро, а снижения в конце сезона жёстче. ABC по возрастным группам и размерам показывает, какие группы кормят выручку, а какие остаются остатком.
Мужское
В мужском сегменте базовых позиций больше, а спрос стабильнее: ABC устойчив между сезонами, покрытие можно держать ниже. Снижения меньше, а OTB обновляют реже; XYZ полезен, чтобы отделить стабильные базовые позиции от сезонных.
Женское
В женском сегменте больше модных позиций и сильнее сезонность: ABC меняется быстрее, а доля снижений заметно выше. Покрытие по модным позициям ниже, по базовым выше; ABC и OTB считают по ветке, а не по категории целиком.

Типичные ошибки

  • Бренд: Считать ABC только по выручке: модель с большой выручкой и малой маржой попадает в A и забирает место у прибыльных.
  • Бренд: Вписать число запаса на начало руками в каждый месяц: цепочка рвётся, и правка одного месяца не двигает остальные.
  • Бренд: Считать «заказано» без подтверждений: OTB получается завышенным, а потом запуск упирается в нехватку материала.
  • Ритейл: Определять класс по накопленной доле после строки, а не до неё: последняя позиция, пересекающая 80 %, оказывается не в A.
  • Ритейл: Считать OTB в рознице с НДС: закупка оказывается завышена на ставку налога, а склад — на тот же процент выше плана.
  • Ритейл: Принимать отрицательный OTB за ноль: перезаказ остаётся в плане, а излишек всплывает на распродаже.

Особенности России

  • Ритейл: Розничные цены считают без НДС; общая ставка НДС в России 22 % (с 01.01.2026), для отдельных товаров ставка может отличаться; ставку по позиции проверьте. Норма: закон 425-ФЗ от 28.11.2025: Закон от 28.11.2025 № 425-ФЗ (изменения в части первую и вторую НК РФ; НДС 22 %).
  • Бренд: Подтверждённые заказы фабрике и остатки материалов в таблице должны совпадать с учётом: расхождение с бухгалтерией по запасам ищут до утверждения закупки.
  • Ритейл: Оценку запасов и резерв под снижение стоимости делают по правилам бухгалтерского учёта; OTB — управленческая модель и не заменяет учёт, проверьте с бухгалтером. Норма: закон 402-ФЗ от 06.12.2011: Закон от 06.12.2011 № 402-ФЗ «О бухгалтерском учёте».
  • Бренд: В странах ЕАЭС ставки НДС и правила оформления различаются: в OTB для продаж в другую страну берут местную ставку и местные документы, проверьте их.

Открыть интерактивно →