Минимальная цена — граница, ниже которой товар продаётся в убыток. Для десяти товаров её можно посчитать на калькуляторе. Для пятисот — нужна таблица, которая пересчитывает всё сама, когда меняется закупка или тариф. Без такой таблицы минимальные цены в репрайсере быстро устаревают, и он начинает продавать в минус товары, которые считаются «защищёнными».
Ниже — как собрать такую таблицу в Excel или Google Таблицах: структура листов, формулы и проверки. Тарифы в примере условные — подставьте актуальные значения из кабинета.
Структура: три листа
- «Тарифы» — справочник: категория, комиссия в процентах, другие процентные расходы. Меняется, когда меняются условия площадки.
- «Параметры» — общие настройки: налог, эквайринг, целевая маржа, стоимость обратной логистики.
- «Товары» — по строке на товар: артикул, категория, себестоимость, логистика, процент выкупа и расчётные столбцы.
Разделение важно: когда маркетплейс меняет комиссию в категории, вы правите одну ячейку на листе «Тарифы», и пересчитываются все товары этой категории.
Лист «Тарифы»
| A: Категория | B: Комиссия |
|---|---|
| Бытовая техника | 15% |
| Дом и сад | 18% |
| Красота и здоровье | 20% |
Названия категорий на этом листе должны в точности совпадать с названиями на листе «Товары». Лучше всего сделать в столбце категории на листе «Товары» выпадающий список из этого справочника.
Лист «Параметры»
| Параметр | Ячейка | Значение |
|---|---|---|
| Налог | B1 | 6% |
| Эквайринг | B2 | 1,5% |
| Реклама (средняя доля) | B3 | 5% |
| Целевая маржа | B4 | 15% |
| Обратная логистика за невыкуп | B5 | 100 ₽ |
Лист «Товары»: исходные столбцы
| Столбец | Что в нём |
|---|---|
| A | Артикул |
| B | Категория |
| C | Себестоимость, ₽ |
| D | Логистика до покупателя, ₽ |
| E | Процент выкупа |
| F | Текущая цена, ₽ |
Расчётные столбцы
G — комиссия категории. Подтягивается из справочника:
=ВПР(B2; Тарифы!A:B; 2; ЛОЖЬ)
H — доля всех процентных расходов:
=G2 + Параметры!$B$1 + Параметры!$B$2 + Параметры!$B$3
I — расходы на невыкупы в расчёте на одну продажу. На каждую продажу приходится (1 − выкуп) / выкуп невыкупленных заказов, и за каждый вы платите доставку в обе стороны:
=(1 - E2) / E2 * (D2 + Параметры!$B$5)
J — фиксированные расходы на продажу:
=C2 + D2 + I2
K — безубыточная цена:
=ОКРВВЕРХ(J2 / (1 - H2); 10)
L — минимальная цена с целевой маржой — та, которую стоит загружать в репрайсер:
=ОКРВВЕРХ(J2 / (1 - H2 - Параметры!$B$4); 10)
Округление вверх до 10 рублей — чтобы минимальная цена никогда не оказалась на копейки ниже расчётной.
Пример на трёх товарах
| Товар | Категория | Себестоимость | Логистика | Выкуп | Доля % | Безубыточная | Минимальная (маржа 15%) | Текущая цена |
|---|---|---|---|---|---|---|---|---|
| Чайник | Бытовая техника | 1 050 ₽ | 120 ₽ | 95% | 27,5% | 1 630 ₽ | 2 060 ₽ | 2 490 ₽ |
| Органайзер | Дом и сад | 290 ₽ | 90 ₽ | 90% | 30,5% | 580 ₽ | 740 ₽ | 790 ₽ |
| Крем | Красота и здоровье | 520 ₽ | 70 ₽ | 97% | 32,5% | 890 ₽ | 1 140 ₽ | 1 290 ₽ |
Пересчитаем один товар вручную, чтобы проверить формулы. Органайзер: невыкупы на продажу — (1 − 0,9) / 0,9 × (90 + 100) ≈ 21 ₽. Фиксированные расходы — 290 + 90 + 21 = 401 ₽. Безубыточная цена — 401 / (1 − 0,305) ≈ 577 ₽, после округления 580 ₽. Минимальная с маржой 15% — 401 / (1 − 0,305 − 0,15) ≈ 736 ₽, после округления — 740 ₽.
Ручной расчёт совпал с таблицей. Делайте такую проверку на одном-двух товарах каждый раз, когда меняете формулы: это быстрее, чем потом искать ошибку на пятистах строках.
Что показывает таблица. У чайника текущая цена на 21% выше минимальной — запас большой, репрайсер может спокойно конкурировать. У крема запас 13%. А органайзер продаётся по 790 ₽ при минимальной цене 740 ₽ — запас меньше 7%. Это типичная картина для недорогих товаров: фиксированные расходы и невыкупы занимают большую долю цены, и любая скидка или акция быстро уводит товар ниже целевой маржи.
Проверки, которые стоит добавить
Запас до минимальной цены — сколько процентов текущая цена выше минимальной:
=F2 / L2 - 1
Условным форматированием выделите красным отрицательные значения (цена ниже минимальной) и жёлтым — запас меньше 5%.
Ошибки подстановки. Если категория не найдена в справочнике, ВПР вернёт ошибку #Н/Д. Не прячьте её через ЕСЛИОШИБКА с нулём: комиссия 0% даст заниженную минимальную цену, и ошибку никто не заметит. Пусть ошибка будет видна.
Слишком большая доля расходов. Если сумма процентных расходов и целевой маржи больше или близка к 100%, формула даёт огромную или отрицательную цену. Добавьте проверку:
=ЕСЛИ(H2 + Параметры!$B$4 >= 0,9; "Проверить"; "")
Пустые значения. Пустая себестоимость считается как ноль, и минимальная цена получится нереально низкой. Выделите строки с пустыми столбцами C, D и E.
Как поддерживать таблицу
- Себестоимость — обновляйте при каждой новой поставке. Удобно подтягивать её из учётной системы, а не вводить вручную.
- Логистику и выкуп — раз в месяц по фактическим данным из отчётов, а не по ожиданиям.
- Тарифы — при каждом изменении условий площадки, до даты их вступления в силу.
- Загрузку в репрайсер — сразу после пересчёта. Минимальная цена в таблице, которая не попала в репрайсер, ничего не защищает.
Отдельная минимальная цена для акций
Условия продажи в акциях отличаются от обычных: может не быть затрат на рекламу, зато вы сознательно соглашаетесь на меньшую маржу ради объёма. Поэтому удобно держать в таблице ещё один столбец — минимальную цену для акций, со своими параметрами.
Например, в акции вы не платите за рекламу (минус 5 пунктов процентных расходов) и готовы работать с маржой 10% вместо 15%. Для органайзера:
=ОКРВВЕРХ(J2 / (1 - (H2 - Параметры!$B$3) - 0,1); 10)
Получается 401 / (1 − 0,255 − 0,1) ≈ 622 ₽, после округления 630 ₽. Если Ozon предлагает участие в акции с ценой 650 ₽ — участвовать можно. Если 590 ₽ — товар в акции будет продаваться ниже вашей минимальной цены для акций, и решение об участии нужно принимать осознанно, а не автоматически.
Держите оба столбца рядом: обычную минимальную цену загружайте в репрайсер, а акционную используйте при решении об участии в акциях.
Итог
Таблица минимальных цен для всего каталога строится из трёх листов: тарифы по категориям, общие параметры и товары. Минимальная цена считается как фиксированные расходы на продажу, включая невыкупы, делённые на долю цены, которая остаётся после процентных расходов и целевой маржи. Добавьте проверки — запас до минимальной цены, ошибки подстановки, пустые значения — и пересчитывайте один-два товара вручную после каждого изменения формул. Тогда репрайсер будет получать минимальные цены, которым можно доверять.