Формула прибыли: как автоматизировать расчеты юнит-экономики для тысяч товаров
Многие предприниматели до конца не понимают, что такое юнит-экономика и почему её расчет так важен. Постараюсь объяснить кратко и понятно. Юнит-экономика помогает разобраться в том, из чего складывается себестоимость вашего продукта — это все затраты, которые вы несёте на каждом этапе реализации товара.
Вот так обычно люди себя ведут при попытках рассчитать юнит-экономику
Я сам занимаюсь продажами на маркетплейсах, где контроль над издержками и оптимизация цен просто необходимы для выживания. Однако моё решение может быть полезно не только продавцам на маркетплейсах, но и другим предпринимателям.
Вы можете рассчитывать юнит-экономику в Excel, Google Sheets или даже в сложных программах вроде 1С. Но всё это может быть довольно трудоемко и дорого. В этой статье я расскажу, как разработал простое и эффективное решение, не вложив практически ничего в программное обеспечение.
Два дисклеймера и начинаем)
Подписывайтесь на мой Telegram-канал, где я делюсь опытом работы на маркетплейсах и записываю подкасты с предпринимателями. Там вы найдёте реальные истории провалов и успехов, а также я там делюсь своим опытом работы в своем проекте.
P.S. я здесь просто хочу поделиться своим решением проблемы, которая наверняка есть у многих предпринимателей. Я не продаю обучение или курс, не рекламирую чего либо, есть только ссылка на мой ТГ канал, если для кого-то это неприемлемо, то прошу не минусовать статью
Содержание:
В чем проблема, и как я ее решил?
Мое решение
Особенности и функционал таблицы
Технологическая реализация
Проблемы, которые решает таблица
Заключение
В чем проблема, и как я ее решил?
В моем магазине около 10 тысяч товаров, и каждый из них нужно продавать по оптимальной цене, учитывая все издержки. Однако я столкнулся с несколькими серьёзными проблемами:
Ручной пересчет. Обычно расчеты делают так — вводят все расходы в Excel, а затем вручную подбирают оптимальную цену, исходя из желаемой чистой прибыли. Это работает для малого количества товаров, но, когда их тысячи, этот подход становится крайне неэффективным.
Кучу времени занимает. Если речь идет о нескольких десятках товаров, то теоретически можно, уделить час-два этому процессу и просчитать стоимость продажи для все товаров методом подбора, но что делать, если товаров тысячи? Не очень хочется жизнь положить на этот просчет, и потом запасную жизнь тоже использовать в случае, если вдруг где-то маркетплейс поменяет условия работы
вот так обычно все рассчитывают цену продажи на товар. Есть все издержки и потом подбирают оптимальную стоимость продажи
Стандартные решения Экселя не подходят. Завязать все на формулы нереально, так как происходит зацикленность расчетов и выскакивает ошибка. Можно сделать это с помощью подбора параметра, но от ручного пересчета мы уходим к полуавтоматическому, и в любом случае пересчитывать ценник для множества товаров все равно будет нереально. Записать классический макрос тоже не получится.
Можно воспользоваться подбором параметров, но это с большим объемом это не подойдет
Мое решение
Я объединил все затраты в одну таблицу и добавил кнопку в Excel, при нажатии на которую запускается скрипт на языке программирования Basic. Скрипт запускает цикл, перебирающий значения, и выполняет расчеты на основе двух заданных условий. Например, я указываю, что маржинальность должна быть не ниже 10%, но и не менее 100 рублей с товара. Эти условия легко настраиваются прямо в таблице, без необходимости переписывать код.
1000 рублей обошлось мне программирование этого решения
Теперь расчеты занимают гораздо меньше времени, и система автоматически адаптируется под разные товары.
Вот так выглядит моя таблица. А теперь подробнее про нее
Особенности и функционал таблицы
На скриншоте выше я демонстрирую текущий вид своей таблицы. Я не утверждаю, что указанные в ней расходы идеально подойдут для расчета себестоимости любого товара в любом бизнесе — моя цель показать сам принцип работы. Важно понимать, что вы всегда можете легко добавить или убрать необходимые расходы под ваши нужды. Теперь давайте разберемся, из каких элементов состоит таблица (ниже идет подробное описание всех колонок, если вам кажется, что вам и так понятно, нажмите сюда, чтобы пролистать эту часть):
Колонка А) уникальный SKU товара
Колонка В и С) они у меня должны использоваться под артикул и штрихкод маркетплейса, но я это не использую, поэтому их скрыл, так как они в данном контексте лишние
Колонка D) Наименование
Колонка Е) Кратность товара. Здесь один из нюансов моей работы. Многие товары у меня продаются наборами. Например, в строке 406 указан товар «Вешалка-плечики». По определенным причинам мне не выгодно или невозможно продавать этот товар менее 6 штук за продажу. Поэтому в наименовании товара, клиент это видит и понимает, что получит товар кратно определенному количеству, и в колонке с кратностью я указываю минимальное количество, которое будет в заказе у покупателя.
Колонки F-I) Остатки на складах. У меня 4 склада в 4х городах. Остатки подгружаются в таблицу по API из системы поставщика. В ячейках F1-I1 находятся значения, меньше которых количество товара на складе обнуляется. Ночью, остатки отправляются уже на маркетплейс
Колонка J) Закупочная стоимость. Подгружается с системы поставщика на единицу товара.
Колонка К) Закупочная стоимость помноженная на кратность
Колонки L-N) Габариты товара
Колонка О) Объем товара. Это значение необходимо для расчета логистики. Вычисляется исходя из кратности и габаритов.
Колонка Р) Стоимость упаковки и отправки на маркетплейс единицы товара. Я принял, что у меня она будет 100 рублей за шт, данную стоимость взял с запасом, так как на каждый товар рассчитывать ее нет особого смысла. Указал это значение в Р1
Колонка Q) Комиссия по категории в процентах. В этой таблице значение комиссии подтягивается по API с маркетплейса
Колонка R) Комиссия по категории в деньгах
Колонка S) Стоимость эквайринга. Указана в S1
Колонка Т) Стоимость приемки на СЦ маркетплейса/ Указана в T1
Колонка U) Стоимость логистики.
Колонка V) Стоимость последней мили. Вычисляется, как процент от выручки по заказу. Процент указываю в V1
Колонка W) Процент на рекламу от стоимости продажи. Здесь у каждого товара этот процент может быть разный, поэтому я не стал завязывать его на определенную ячейку, как в колонке Р, например
Колонка Х) Рекламный бюджет, только в деньгах
Колонка Y) Себестоимость. Сумма всех издержек, которые я перечислил выше.
Колонка Z) Требуется пересчет. В процессе работы с таблицей я понял, что мне не нужно всегда пересчитывать все значения в таблице. Иногда мне нужно быстро понять, что произойдет с ценой продажи, если изменится хотя бы один из параметров. Так вот, чтобы не производить пересчет всей таблицы, я сделал так, чтобы можно было выбрать какую конкретно строку пересчитывать. Для этого я просто выбираю «Да»/«Нет», если да, то эта строка пересчитывает. Если нужно просчитать всю таблицу, то я везде проставляю «Да».
Колонка АА) Процент маржинальности после вычисления цены продажи
Колонка АВ) Сумма маржинальности после вычисления цены. Условия для вычисления этих двух значений у меня указаны в ячейках АА2 и АВ2.
Колонка АС) Стоимость, которая получается после пересчета. Она отправляется на маркетплейс по API.
Колонка AD) Цена до скидки. Все маркетплейсы просят указывать это значение. Мне до конца непонятно, зачем оно нужно, но надо, значит надо. Она отправляется на маркетплейс по API
Колонка AЕ) Минимальная цена. Полученное значение стоимости продажи умножается на 5%. Это значение влияет на то, насколько может маркетплейс прогнуть вашу цену. Она также отправляется на маркетплейс по API.
Колонка AF) Коэффициент. Это моя проверка правильности вычислений. Я принял для себя, что грязная наценка на мои товары должна быть минимум 1,5. То есть отношение стоимости продажи к закупочной стоимости. Если коэффициент ниже этого значения, то для меня это сигнал к тому, что в расчетах может быть ошибка и нужно проверить все
Кнопка «Подбор значений». Ключевая кнопка таблицы. После нажатия на эту кнопку происходит выполнение вычисления того простенького скрипта, который у меня хранится на сервере. В данном случае его переписали немного иначе, нежели для Экселя, но это сделано из-за моей специфики. О скрипте будет далее.
Google Sheets + вычисления на сервере уменьшили время расчета значений в десятки раз
Теперь, вычисление всех стоимостей происходит примерно за 5–7 минут. В Экселе такой расчет происходил бы примерно 2–3 часа в зависимости от мощности вашего компьютера, а без такой автоматизации, мне кажется, нужно быть кошкой с девятью жизнями, чтобы проделать такую работу вручную.
Примерно такая скорость вычислений скрипта на сервере в сравнении с Экселем на моем компьютере
Благодаря этому решению я могу загружать в таблицу и 10 и 20 и даже 30 тысяч товаров и спокойно работать с расчетом цены.
Технологическая реализация
Вот код на Visual Basic, который изначально выполнялся при нажатии на кнопку «Подбор Значения»
Private Sub CommandButton1_Click() ‘ объявляем переменные
Dim Строка As Integer
Dim Процент As Double
Dim Рубль As Double
Dim t As Double
Dim МИН As Integer
Dim СЕК As Integer
t = Timer
Строка = WorksheetFunction.CountA(Columns("V:V"))
Процент = Range("X1").Value ‘ значения условий
Рубль = Range("Y1").Value ‘ значение условий
k = 0
For n = 1 To Строка - 1 ‘ цикл for, который как раз перебирает значения
If Range("W" & n + 2) = "Да" Or Range("W" & n + 2) = "да" Or Range("W" & n + 2) = "ДА" Then ' Ищем ДА и проверяем, что стоимость закупки не 0
If Range("G" & n + 2) > 0 Then
Application.CutCopyMode = False
Range("X" & n + 2).GoalSeek Goal:=Процент, ChangingCell:=Range("Z" & n + 2) ' Подбор процентов
Значение = Range("Z" & n + 2).Value
Значение = Round(Значение, 0)
Range("Z" & n + 2) = Значение
If Range("Y" & n + 2) < Рубль Then
Application.CutCopyMode = False
Range("Y" & n + 2).GoalSeek Goal:=Рубль, ChangingCell:=Range("Z" & n + 2) ' Подбор рублей
Значение = Range("Z" & n + 2).Value
Значение = Round(Значение, 0)
Range("Z" & n + 2) = Значение
End If
ElseIf Range("G" & n + 2) <= 0 Then ' проверка, если стоимость закупки 0
Range("Z" & n + 2) = 0 ' присваиваем ячейке со стоимостью 0
End If
Else ' иначе
k = k + 1
End If
'Range("W" & n + 2) = "Нет"
Next n
' Расчёт времени
t = Timer - t
t = Round(t, 1)
Мп = t / 60
МИН = Round(Мп, 0)
Мр = МИН * 60
СЕК = t - Мр
СЕК = Round(СЕК, 0)
MsgBox "ГОТОВО!!!" & Chr(13) & "Количество подобранных значений: " & k & Chr(13) & "Время подбора значений: " & МИН & " мин. " & СЕК & " сек.", vbExclamation, "ИНФОРМАЦИЯ"
End SubЧтобы этот код заработал в вашей таблице, вам нужно сначала включить вкладку «Разработчик» в настройках Excel. Затем создайте аналогичную таблицу, как на скриншоте выше, но удалите столбцы G, H и I, и поменяйте местами первую и вторую строки. После этого добавьте прямоугольник и назначьте для него событие, которое запустит этот код.
должно выглядеть так
Код написан на Visual Basic, так как изначально работал в Excel. Однако, когда возникла необходимость в обменах данными через API между поставщиком, мной и маркетплейсом, я решил перейти на Google Sheets. Скрипт был перенесен на сервер, что позволило ускорить расчеты. Но это добавило и некоторые сложности — теперь я не могу вносить оперативные изменения, так как недостаточно хорошо разбираюсь в программировании и не могу легко редактировать код на сервере.
Проблемы, которые решает таблица
Помимо описанных выше сложностей, данная таблица помогла мне избавиться от одной крупной проблемы — ошибок в ценообразовании. В одной из статей я рассказывал о своей ошибке, когда из-за неверного обновления цен через шаблон Excel все цены на платформе «съехали», что привело к множеству заказов по слишком низким ценам. Отмена этих заказов обошлась мне примерно в 250 000 рублей. С тех пор, как я начал использовать эту таблицу, таких проблем больше не возникало.
Маркетплейсы после моих ошибок
Заключение
Автоматизация расчетов юнит-экономики не только упрощает работу с большим количеством товаров, но и помогает предпринимателям принимать более точные и эффективные решения. Моя таблица позволяет достаточно быстро рассчитывать цены продажи с учетом заданных условий маржинальности для десятков тысяч товаров, что невозможно при ручных расчетах.
Преимущества автоматизации:
Экономия времени: Вместо нескольких дней на расчеты ты можешь получить точные данные за считанные минуты.
Точность и надежность: Сведение к минимуму человеческих ошибок благодаря автоматическому расчету.
Масштабируемость: Таблица способна обрабатывать огромные объемы данных, не теряя в производительности.
Простота адаптации: код на Visual Basic легко переписывается на другие языки и адаптируется к конкретно вашим запросам.
Моя автоматизированная система стала настоящим прорывом в расчетах для моего бизнеса, и я надеюсь, что те способы решения проблемы, которые я показал в статье также могут помочь и вам оптимизировать свои процессы и достичь новых высот в работе
Благодарю вас за то, что прочли эту статью до конца. Если у вас есть вопросы или предложения, не стесняйтесь писать мне в личные сообщения. Я всегда открыт к общению и с радостью поделюсь дополнительными материалами. Подписывайтесь на мой Telegram-канал, там я беру интервью у предпринимателей и делюсь другими идеями и материалами из своего опыта.
