Расчёт себестоимости товара в Excel: формулы и пример

1. Что считать себестоимостью товара

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

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

2. Рекомендуемая структура Excel-файла

Для рабочего расчёта достаточно четырёх листов. На листе «Товары» храните SKU, название, цену продажи, закупочную цену, упаковку, логистику и ожидаемый объём. На листе «Ставки» — комиссии, налоги, рекламную долю и другие проценты с датой их актуальности. На листе «Расчёт» собирайте формулами полную экономику единицы. На листе «Итоги» показывайте выручку, валовую прибыль, маржинальную прибыль и товары с отрицательной или слишком низкой маржой.

Не вводите один и тот же тариф вручную в сотни строк. Например, ставку эквайринга 2,5% или комиссию площадки 17% лучше держать в отдельной ячейке или справочнике. Если ставка зависит от категории, используйте код категории и подтягивайте процент через ВПР/XLOOKUP. Это делает модель проверяемой: изменение тарифа происходит в одном месте, а все SKU пересчитываются автоматически.

Минимальный набор колонок на листе «Расчёт»: SKU, Цена, Закупка, Упаковка, Логистика, Комиссия %, Комиссия ₽, Реклама %, Реклама ₽, Налог %, Налог ₽, Переменные расходы, Прибыль на единицу, Маржа %, Наценка %, Цена безубыточности и План продаж. Для бизнеса с возвратами добавьте ожидаемую стоимость возвратов на единицу отдельной колонкой.

3. Формулы Excel для себестоимости, прибыли и маржи

Предположим, в строке 2 цена находится в B2, закупка — C2, упаковка — D2, логистика — E2, комиссия — F2, реклама — H2, налог — J2. Сумма комиссии считается как =B2*F2, реклама как =B2*H2, налог как =B2*J2. Если ставки записаны в формате процентов, Excel корректно пересчитает 17% в 0,17. Не вводите 17 вместо 17%, иначе расход окажется в сто раз выше.

Переменные расходы на единицу: =СУММ(C2:E2;G2;I2;K2), где G2, I2 и K2 — рассчитанные суммы комиссии, рекламы и налога. Прибыль на единицу: =B2-L2. Маржа: =ЕСЛИОШИБКА(M2/B2;0). Наценка: =ЕСЛИОШИБКА(M2/L2;0). Маржа отвечает на вопрос «какая доля выручки остаётся», а наценка — «на сколько прибыль увеличивает понесённые расходы»; эти показатели нельзя использовать как взаимозаменяемые.

Если закупочная цена хранится на отдельном листе, можно использовать =ЕСЛИОШИБКА(XLOOKUP(A2;Товары!A:A;Товары!D:D);0). Для старых версий Excel подойдёт ВПР. Важнее не конкретная функция, а принцип: исходные данные должны иметь один источник, а расчётные листы — ссылаться на него формулами.

4. Практический пример с числами

Возьмём товар с ценой продажи 2 490 ₽. Закупка стоит 900 ₽, упаковка — 45 ₽, логистика до покупателя — 180 ₽. Комиссия площадки составляет 17% от цены, реклама — 8%, налог с выручки — 6%. Комиссия равна 2 490 × 17% = 423,30 ₽; реклама — 199,20 ₽; налог — 149,40 ₽. Полные переменные расходы: 900 + 45 + 180 + 423,30 + 199,20 + 149,40 = 1 896,90 ₽.

Прибыль до постоянных расходов на одну продажу равна 2 490 − 1 896,90 = 593,10 ₽. Маржа — 593,10 / 2 490 = 23,82%. Если план продаж — 300 единиц в месяц, выручка составит 747 000 ₽, переменные расходы — 569 070 ₽, а маржинальная прибыль — 177 930 ₽. Именно эту сумму можно сравнивать с постоянными расходами бизнеса.

Предположим, на товар приходится 90 000 ₽ постоянных расходов в месяц. Тогда операционная прибыль по плану — 177 930 − 90 000 = 87 930 ₽. Если продажи упадут до 150 единиц, маржинальная прибыль станет 88 965 ₽ и уже не покроет распределённые постоянные расходы. Поэтому оценка только «593 ₽ прибыли с штуки» без объёма продаж даёт неполную картину.

5. Как рассчитать цену безубыточности

Когда часть расходов фиксирована в рублях, а часть является процентом от цены, нельзя просто сложить расходы. Пусть фиксированные переменные затраты на единицу равны 1 125 ₽ (закупка + упаковка + логистика), а суммарные процентные расходы — 31% (17% комиссия + 8% реклама + 6% налог). Цена безубыточности определяется как 1 125 / (1 − 31%) = 1 630,43 ₽.

В Excel формула может выглядеть так: =ЕСЛИОШИБКА((C2+D2+E2)/(1-F2-H2-J2);0). Для контроля добавьте условное форматирование: если текущая цена ниже цены безубыточности — красный статус, если запас менее 10% — жёлтый. Это полезнее, чем обнаруживать отрицательную прибыль после закрытия месяца.

Если нужно получить целевую маржу, например 25%, знаменатель дополняется целевой маржой: Цена = фиксированные переменные затраты / (1 − процентные расходы − целевая маржа). При затратах 1 125 ₽, процентных расходах 31% и цели 25% цена должна быть не ниже 1 125 / (1 − 0,31 − 0,25) = 2 556,82 ₽.

6. Постоянные расходы и распределение на товар

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

Если требуется распределить постоянные расходы по SKU, заранее выберите базу: количество проданных единиц, выручка, маржинальная прибыль или трудоёмкость. Например, при постоянных расходах 300 000 ₽ и месячной выручке 3 000 000 ₽ можно распределять 10% выручки как управленческую нагрузку. Главное — не менять базу от месяца к месяцу только ради красивой прибыли.

Для сезонного бизнеса полезнее хранить постоянные расходы отдельным месячным блоком и строить итоговый P&L, чем искусственно прибавлять одинаковую сумму к каждой единице. Связать расчёт себестоимости с общей моделью можно через статью о P&L в Excel и бюджете компании.

7. Сценарии: цена, комиссия, реклама и возвраты

Хорошая модель должна отвечать не только на вопрос «сколько мы заработали», но и «что будет, если условия изменятся». Создайте три сценария: базовый, оптимистичный и стрессовый. В стрессовом варианте можно одновременно снизить цену на 5%, увеличить рекламу на 3 п.п. и логистику на 30 ₽. Сравнивайте не только новую маржу, но и абсолютную прибыль при ожидаемом объёме.

Возвраты лучше переводить в ожидаемый расход на успешную продажу. Например, если возврат происходит в 8% заказов и средняя невозмещаемая стоимость одного возврата — 250 ₽, ожидаемый расход составляет 20 ₽ на заказ. Формула: =Вероятность_возврата*Стоимость_возврата. Такой подход не делает каждый заказ «возвратным», но корректно учитывает риск на большом объёме.

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

8. Типичные ошибки в расчёте

Первая ошибка — сравнивать цену только с закупкой. Вторая — записывать процентные комиссии в рублях и забывать пересчитать их после изменения цены. Третья — смешивать НДС, налог с выручки и управленческую себестоимость без единой методики. Четвёртая — использовать среднюю логистику, когда товары сильно различаются по весу и габаритам.

Ещё одна ошибка — считать маржу и наценку одним показателем. При расходах 1 500 ₽ и цене 2 000 ₽ прибыль равна 500 ₽, маржа — 25%, а наценка — 33,3%. Ошибка в терминологии быстро превращается в неверное решение о скидке или минимальной цене.

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

9. Как контролировать модель каждый месяц

Раз в месяц обновляйте закупочные цены, комиссии и среднюю логистику по факту. Затем сравнивайте плановую себестоимость с фактической. Для этого можно хранить столбцы «План» и «Факт» и считать отклонение формулой =Факт-План, а процент отклонения — =ЕСЛИОШИБКА((Факт-План)/План;0). Сортировка по крупнейшему отрицательному отклонению быстро показывает, какие SKU требуют пересмотра цены или расходов.

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