Excel для ритейла, часть II: ABC-анализ и бюджет закупки OTB в таблице
Вторая часть трека показывает, как собрать в таблице две рабочие модели байера и планировщика: ABC-анализ ассортимента (какие позиции дают основную выручку) и бюджет закупки OTB по месяцам (сколько можно заказать, не создав излишка). Документ даёт структуру листов, формулы со ссылками на ячейки, порядок построения и контрольные проверки; ту же логику считают расчёты Lab, а таблица нужна, когда модель нужно показать, править и передать по частям.
Зачем этот документ
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: Соберите данные позиций на лист «Данные» плоской таблицей и проверьте итог с учётной системой.
- Шаг 2: Бренд: решите, по чему считать ABC: выручке, марже или штукам; для коллекции чаще берут маржу.
- Шаг 3: Отсортируйте позиции по убыванию, добавьте доли, накопленную долю и класс; пороги вынесите в отдельные ячейки.
- Шаг 4: Проверьте, что сумма долей равна 100 %, а число позиций классов в сумме равно числу строк.
- Шаг 5: Ритейл: введите продажи плана по месяцам, долю снижений и потерь, покрытие и продажи следующего месяца в отдельных ячейках.
- Шаг 6: Постройте строки: снижения = продажи × доля; запас на конец = продажи следующего месяца × покрытие; запас на начало ссылкой на предыдущий конец.
- Шаг 7: Бренд: добавьте строку «заказано» из подтверждённых заказов фабрике и посчитайте OTB как остаток возможного запуска.
- Шаг 8: Посчитайте OTB по формуле и переведите в себестоимость; суммы по месяцам и итог за сезон выведите на лист контроля.
- Шаг 9: Запустите сценарии: поменяйте покрытие или снижения в одной ячейке и запишите, как изменились итог и отрицательные месяцы.
Формулы
| Показатель | Формулы | Пояснение |
|---|---|---|
| Доля и накопленная доля | доля = значение ÷ сумма; накопленная = предыдущая накопленная + доля | Класс присваивают по накопленной доле до строки: A до 80 %, B до 95 % (условные границы). |
| OTB месяца | OTB = продажи + снижения + потери + запас на конец − запас на начало − заказано | Всё по рознице без НДС; для перевода в себестоимость умножают на (1 − IMU). |
| Запас на конец | запас на конец месяца = продажи следующего месяца × покрытие в месяцах | Покрытие задают ступенями по сезону: выше перед пиком, ниже к концу. |
| Коэффициент вариации | CV = стандартное отклонение ÷ среднее по месяцам | Для XYZ: чем ниже CV, тем стабильнее спрос. |
Пример с числами
OTB на шесть месяцев по розничным ценам без НДС, тыс. ₽, условный пример
| Месяц | Продажи | Снижения | Потери | Запас на конец | Запас на начало | Заказано | OTB |
|---|---|---|---|---|---|---|---|
| Сентябрь | 900 | 18 | 9 | 1 800 | 1 900 | 300 | 527 |
| Октябрь | 1 200 | 36 | 12 | 2 250 | 1 800 | 250 | 1 448 |
| Ноябрь | 1 500 | 75 | 15 | 1 950 | 2 250 | 200 | 1 090 |
| Декабрь | 1 300 | 156 | 13 | 1 200 | 1 950 | 0 | 719 |
| Январь | 800 | 144 | 8 | 1 050 | 1 200 | 0 | 802 |
| Февраль | 700 | 70 | 7 | 1 125 | 1 050 | 0 | 852 |
| Итого за сезон | 6 400 | 499 | 64 | 1 125 | 1 900 | 750 | 5 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 %). Все числа условные.
Рисунки
Данные таблицей
| Показатель | Доля выручки |
|---|---|
| M-1 | 26,3 |
| M-2 | 19,4 |
| M-3 | 15,6 |
| M-4 | 11,9 |
| M-5 | 9,4 |
| M-6 | 6,9 |
| M-7 | 5 |
| M-8 | 3,1 |
| M-9 | 1,9 |
| M-10 | 0,6 |
Доля позиции в выручке, % (условный ABC на десять позиций). Позиции класса A (5) дают 82,5 % выручки; хвост из 2 позиций класса C даёт около 2,5 %.
Данные таблицей
| Позиции по убыванию выручки | Накопленная доля |
|---|---|
| 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 |
Накопленная доля выручки, %. Кривая быстро растёт на первых позициях: пересечение с 80 % и 95 % даёт границы классов A и B.
- рост
- снижение
- итог
Данные таблицей
| Шаг | Изменение | Уровень |
|---|---|---|
| Продажи | +900 | 900 |
| Снижения | +18 | 918 |
| Потери | +9 | 927 |
| Запас на конец | +1 800 | 2 727 |
| Запас на начало | −1 900 | 827 |
| Заказано | −300 | 527 |
| 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 для продаж в другую страну берут местную ставку и местные документы, проверьте их.