Большинство селлеров на маркетплейсах теряют деньги из-за неправильного управления запасами: либо товар кончается и есть потерянные продажи, либо накопляется мёртвый груз, который не продаётся. В этой статье разберемся, как используя Excel построить систему анализа спроса и планирования поставок.
Почему плохое планирование поставок убивает прибыль
Проблема 1: Дефицит товара (stock-out)
- Товар кончается на складе маркетплейса
- Вы теряете дни продаж в пиковый период
- Алгоритм маркетплейса снижает видимость товара
- Клиенты покупают у конкурентов
Проблема 2: Перезапас (overstock)
- Товар лежит на складе неделями или месяцами
- Маркетплейс взимает плату за хранение
- Товар может устаревает или повредиться
- Деньги заморожены в товаре, не работают в бизнесе
Проблема 3: Неравномерный спрос
- Сезонные пики (новый год, 1 сентября, 8 марта)
- Тренды и вирусные товары
- Пос влияние на спрос поправок маркетплейса
- Без анализа невозможно предсказать
Подготовка данных: что собирать
Сначала нужно собрать историческую информацию о продажах. Экспортируйте из API маркетплейса или выгрузите отчёты.
Нужные данные:
- Дата продажи
- Товар (артикул, название, категория)
- Количество проданных единиц
- Стоимость товара
- Остаток на складе (для каждого дня)
Минимум данных: 2-3 месяца история продаж. Чем больше — тем точнее прогноз.
Создайте таблицу в Excel:
`
Дата Артикул Товар Кол-во Цена Сумма Остаток
2024-01-15 SKU-001 Рубашка синяя 5 1000 5000 45
2024-01-15 SKU-002 Платье чёрное 3 2000 6000 12
2024-01-15 SKU-003 Куртка зимняя 1 3500 3500 8
2024-01-15 SKU-004 Чехол для телефона 12 500 6000 67
...
`
Базовый анализ: средние продажи и волатильность
Для каждого товара нужно рассчитать:
- Среднее количество продаж в день
- Стандартное отклонение (волатильность спроса)
- Коэффициент вариации (стабильность спроса)
`
Столбец A: Артикул (SKU)
Столбец B: Товар
Столбец C: Кол-во дней в выборке
Столбец D: Всего продано (шт)
Столбец E: Среднее в день = D / C
Столбец F: Стандартное отклонение = STDEV(диапазон дневных продаж)
Столбец G: Коэффициент вариации = F / E * 100%
Формулы:
E: =D/C
F: =STDEV(E:E) (диапазон зависит от того, где у вас дневные продажи)
G: =F/E*100
`
Интерпретация:
- CV < 30% — стабильный спрос, легко прогнозировать
- CV 30-60% — умеренная волатильность
- CV > 60% — высокая волатильность, прогнозы неточны
Пример для рубашки:
- Среднее продаж: 8 шт в день
- Стандартное отклонение: 3 шт
- CV: 37,5% (умеренная волатильность)
Метод 1: Простой прогноз (скользящее среднее)
Самый простой способ прогнозировать спрос — использовать среднее за последние N дней. Это даёт хороший результат для товаров с умеренной волатильностью.
`
Дата Продажи Скол. среднее (7 дней) Скол. среднее (14 дней)
2024-01-15 8
2024-01-16 6
2024-01-17 9
2024-01-18 7
2024-01-19 10
2024-01-20 6
2024-01-21 9 8,7
2024-01-22 8 8,1
2024-01-23 7 8,0
...
Формула скользящего среднего за 7 дней:
=AVERAGE(B16:B22) (где B16:B22 — продажи за последние 7 дней)
`
Преимущества: просто, требует мало вычислений
Недостатки: не учитывает тренды, плохо работает при резких изменениях
Метод 2: Экспоненциальное сглаживание (Exponential Smoothing)
Более продвинутый метод, который лучше реагирует на свежие данные.
Формула: Прогноз_новый = α × Продажи_актуальные + (1-α) × Прогноз_предыдущий
Где α (alpha) — коэффициент сглаживания (обычно 0,2-0,4).
`
Дата Продажи Прогноз (α=0,3) Ошибка прогноза
2024-01-15 8 8 0
2024-01-16 6 8,0 -2,0
2024-01-17 9 7,4 1,6
2024-01-18 7 7,98 -0,98
2024-01-19 10 7,68 2,32
2024-01-20 6 8,38 -2,38
Формула в Excel (для α=0,3):
Строка 2: =B2 (первое значение прогноза = первой продаже)
Строка 3: =0,3B3 + 0,7C2
Строка 4: =0,3B4 + 0,7C3
... (копируем формулу вниз)
`
Экспоненциальное сглаживание лучше справляется с товарами, у которых есть тренд.
Метод 3: FORECAST для улавливания тренда
Если спрос растёт или падает, используйте встроенную функцию FORECAST. Она строит линию тренда и экстраполирует её вперёд.
`
Дата День по порядку Продажи Прогноз на завтра
2024-01-15 1 8
2024-01-16 2 10
2024-01-17 3 12
2024-01-18 4 14
2024-01-19 5 16
2024-01-20 6 18 =FORECAST(7, B2:B7, A2:A7)
`
Результат: 20 (товар растёт на 2 шт в день)
Формула: =FORECAST(x, known_y, known_x)
Это хорошо работает, если данные за 2-3 недели показывают явный рост или падение.
ABC-анализ: какие товары приносят основной доход
ABC-анализ разделяет товары на три группы по вкладу в выручку.
A товары — 20% ассортимента, дают 80% выручки (звёзды, нужен частый завоз)
B товары — 30% ассортимента, дают 15% выручки (нормальный ассортимент)
C товары — 50% ассортимента, дают 5% выручки (редкие покупки, большой запас не нужен)
Как сделать ABC-анализ в Excel:
`
Столбец A: Артикул
Столбец B: Товар
Столбец C: Выручка за период
Столбец D: % от общей выручки = C / SUM($C:$C) * 100
Столбец E: Кумулятивный % (нарастающий итог)
Сортируем по столбцу C (выручка) в порядке убывания.
Далее смотрим кумулятивный %:
- Если < 80% — товар категории A
- Если 80-95% — товар категории B
- Если > 95% — товар категории C
Формулы:
D2: =C2/SUM($C$2:$C$500)*100
E2: =D2
E3: =E2+D3 (копируем вниз)
`
Пример для магазина одежды (выручка за месяц):
`
Товар Выручка % выручки Кум.% Категория
Платье чёрное (SKU-105) 125000 22% 22% A
Рубашка синяя (SKU-001) 95000 17% 39% A
Куртка зимняя (SKU-003) 78000 14% 53% A
Свитер серый (SKU-110) 65000 12% 65% A
Чёрные брюки (SKU-205) 55000 10% 75% A
Кофта спортивная 35000 6% 81% B
Юбка летняя 28000 5% 86% B
Носки (упаковка) 22000 4% 90% B
Платок шёлковый 18000 3% 93% B
Галстук 12000 2% 95% B
(остальное ~50 товаров) 28000 5% 100% C
`
На основе ABC планируем поставки:
- A товары: закупаем чаще (каждые 2-3 недели), держим мелкий запас, минимум дефицитов
- B товары: закупаем раз в месяц, держим средний запас
- C товары: закупаем редко (1-2 раза в квартал), держим большой запас (потому что завозить редко)
Расчёт страховки запаса (Safety Stock)
Safety Stock — это подушка запаса, которую нужно иметь, чтобы не было дефицитов.
Формула:
Safety Stock = Z-score × σ × √(Lead Time)
Где:
- Z-score — уровень сервиса (95% сервис = Z 1,65; 99% = Z 2,33)
- σ — стандартное отклонение спроса
- Lead Time — время поставки (сколько дней от заказа до поступления)
Пример для рубашки (SKU-001):
`
Среднее продаж в день: 8 шт
Стандартное отклонение: 3 шт
Lead time (время поставки): 14 дней
Желаемый уровень сервиса: 95% (Z = 1,65)
Safety Stock = 1,65 × 3 × √14
= 1,65 × 3 × 3,74
= 18,5 шт
Рекомендуемый запас на складе маркетплейса: минимум 19 шт
`
Это означает, что если у вас всегда есть 19+ рубашек на складе, вероятность дефицита менее 5%.
Расчёт точки переоформления (Reorder Point)
Reorder Point — количество товара, при котором пора делать новый заказ.
Формула:
Reorder Point = (Средние дневные продажи × Lead Time) + Safety Stock
Пример для рубашки:
`
Средние дневные продажи: 8 шт
Lead time: 14 дней
Safety stock: 19 шт
Reorder Point = (8 × 14) + 19
= 112 + 19
= 131 шт
Когда на складе остаётся 131 рубашка — делаете новый заказ.
`
Это гарантирует, что до прибытия нового заказа у вас не кончится товар (при условии нормального спроса).
Бюджет на закупки на основе прогноза
Теперь, когда вы знаете, какой спрос ожидается, можно составить бюджет закупок.
`
Товар Ежедневный спрос Дни недели Спрос на неделю Точка пересоказа Тек. запас К заказу
Рубашка синяя 8 7 56 131 75 56
Платье чёрное 12 7 84 185 100 84
Куртка зимняя 4 7 28 95 50 28
Чехол для тел. 15 7 105 210 120 105
Брюки чёрные 6 7 42 110 70 42
Всего к закупке (на неделю): 315 единиц товара
`
Теперь можно приблизительно подсчитать затраты на закупку и нужный оборотный капитал.
Отслеживание оборачиваемости товара (Inventory Turnover)
Оборачиваемость показывает, как часто товар полностью «перелопачивается» (продаётся и пополняется).
Формула:
Turnover = Выручка за период / Средний запас на складе
Или в единицах товара:
Turnover = Всего продано за период / Средний запас
Пример:
`
Месячная выручка по рубашкам: 8000 руб
Среднее количество на складе: 50 шт
Цена товара: 1000 руб
Оборачиваемость = 8000 / (50 × 1000) = 0,16 раза в месяц
= Один оборот каждые 6 месяцев
Или в днях:
Turnover days = 30 дней / Turnover = 30 / 0,16 = 187 дней
`
Это означает, что товар лежит на складе в среднем 187 дней перед продажей. Это плохо — товар должен оборачиваться быстрее.
Нормальные значения оборачиваемости:
- Одежда: 1-2 оборота в месяц (30-60 дней)
- Электроника: 2-3 оборота в месяц (10-15 дней)
- Продукты: 5-10 оборотов в месяц (3-6 дней)
Если оборачиваемость низкая — либо спрос не такой, как вы думали, либо цена слишком высокая.
Система мониторинга и автоматизация
Создайте простой дашборд в Excel, где можно быстро увидеть статус каждого товара:
`
Артикул Товар Уровень Спрос в день Точка пере. Статус
сейчас (прогноз)
SKU-001 Рубашка синяя 75 шт 8 131 ⚠️ ЗАКАЗАТЬ
SKU-002 Платье чёрное 180 шт 12 185 ✓ ОК
SKU-003 Куртка зимняя 42 шт 4 95 ❌ ДЕФИЦИТ!
SKU-004 Чехол для тел. 200 шт 15 210 ✓ ОК
`
Правила раскраски ячеек:
- Красный фон: текущий запас < точки переоформления (нужен срочный заказ)
- Оранжевый фон: текущий запас 80-100% от точки переоформления (заказ в течение дня)
- Зелёный фон: всё нормально, запас достаточный
Прогноз на сезонные всплески
Не забывайте про сезонность. Для товаров с явной сезонностью используйте данные за прошлые годы.
Пример: спрос на куртки по месяцам
`
Месяц 2022 2023 2024 Средний Индекс сезонности
Январь 85 92 89 88,7 1,8 (самый пик)
Февраль 78 81 80 79,7 1,6
Март 42 45 44 43,7 0,9
Апрель 25 28 26 26,3 0,5
...
Ноябрь 95 98 96 96,3 1,9
Декабрь 88 90 89 89,0 1,8
Средний спрос: 49,7
Индекс сезонности = Средний спрос в месяц / Средний спрос за год
Прогноз на май 2024:
Ожидаемый спрос = 49,7 × Индекс май = 49,7 × 0,4 = 19,9 ≈ 20 курток в месяц
`
Используйте это для планирования закупок на сезон.
Практические советы
- Начните с 5-10 товаров — отладите систему на них, потом масштабируйте
- Пересчитывайте прогнозы еженедельно — новые данные меняют прогноз
- Проверяйте точность — сравнивайте фактический спрос с прогнозом, улучшайте модель
- Работайте с поставщиками — согласуйте Lead time, минимальные партии, сроки оплаты
- Отслеживайте сезонность — даже для 2-3 лет данных можно увидеть паттерны
Заключение
Правильное планирование поставок экономит тысячи рублей:
- Избегаете дефицитов (не теряете продажи)
- Снижаете затраты на хранение (меньше мёртвого груза)
- Улучшаете алгоритмы маркетплейса (товар всегда в наличии)
Начните с простого скользящего среднего, потом добавляйте ABC-анализ и расчёт safety stock. Система не обязательно должна быть идеальной — главное, что она даёт вам видимость в спрос и помогает принимать решения на основе данных, а не интуиции.
Используйте наши продукты Планировщик поставок и Аналитика маркетплейса для автоматизации этих расчётов и экономии времени на планирование.