Дашборд продавца: как пошагово настроить учет маржинальности товаров на Ozon в Excel

Каждый селлер на Ozon рано или поздно сталкивается с тем, что встроенной аналитики маркетплейса становится недостаточно. Комиссии меняются, затраты на логистику зависят от геопозиции складов, а реклама и скрытые расходы (вроде брака или упаковки) могут незаметно свести чистую прибыль к нулю.

Чтобы точно знать, какие товары приносят реальный доход, а какие генерируют убытки, необходимо построить собственную систему учета юнит-экономики. В этой статье мы максимально подробно разберем, как создать динамический дашборд в Excel для контроля маржинальности.

В качестве сквозного примера возьмем модель работы FBS (продажа со своего склада) с расчетом логистики для регионального селлера (например, при отправке из Сибири), оформленного как ИП на НПД (самозанятость).

Шаг 1. Проектируем архитектуру данных (База данных Excel)

Главная ошибка новичков — пытаться вести все расчеты в одной огромной таблице. Правильный подход — разделить данные на три составляющие:

1. Справочник товаров (Статические данные): себестоимость, габариты, фиксированные расходы.

2. Журнал продаж (Динамические данные): ежемесячные выгрузки отчетов о реализации из личного кабинета Ozon.

3. Дашборд (Визуализация): итоговое окно с графиками, срезами и ключевыми метриками.

Тетрадь 1. Справочник номенклатуры (Товары)

Создайте первый лист и назовите его Товары. Обязательно переведите диапазон в Умную таблицу (Ctrl + T).

Тетрадь 2. Журнал продаж и расходов (Транзакции)

На втором листе мы будем собирать данные о продажах. Структура таблицы должна совмещать данные из отчета Ozon и наши расчетные формулы.

Создайте таблицу со следующими столбцами:

• A: Дата

• B: SKU (Артикул)

• C: Цена реализации (фактическая цена, за которую купили товар)

• D: Комиссия Ozon

• E: Логистика Ozon (включая сборку, магистраль и последнюю милю)

• F: Затраты на рекламу (выделенные на единицу товара)

• G: Эквайринг

• H: Себестоимость (подтягивается автоматически)

• I: Налог

• J: Чистая прибыль (расчетное поле)

• K: Маржинальность, % (расчетное поле)

Шаг 2. Настраиваем автоматизацию и формулы

Теперь свяжем таблицы между собой с помощью формул, чтобы минимизировать ручной ввод.

1. Автоматическое подтягивание себестоимости и упаковки

Чтобы не вводить базовые затраты вручную для каждой продажи, используем современную функцию ПРОСМОТРX (XLOOKUP) или классическую ВПР (VLOOKUP).

В ячейку столбца H (Себестоимость) вставляем формулу:

=ВПР(B2; Товары!$A$2:$E$4; 3; 0) + ВПР(B2; Товары!$A$2:$E$4; 4; 0) + ВПР(B2; Товары!$A$2:$E$4; 5; 0)

Что делает формула: Ищет артикул из текущей строки в таблице Товары и суммирует все базовые затраты на производство и подготовку к отправке.

2. Расчет налога для ИП на НПД

При работе на данном налоговом режиме ставка составляет 4% при расчете с физическими лицами и 6% — с юридическими. Важно: налог на Ozon всегда рассчитывается от Выручки (Цены реализации до вычета комиссий маркетплейса), а не от суммы, пришедшей на счет.

В ячейку столбца I (Налог) вставляем (базовый расчет 4% для физлиц):

=C2 * 0,04

3. Расчет чистой прибыли (Net Profit)

Чистая прибыль — это то, что остается после оплаты всех расходов маркетплейса, налогов и себестоимости.

В ячейку столбца J (Чистая прибыль):

=C2 - D2 - E2 - F2 - G2 - H2 - I2

4. Расчет маржинальности (Margin)

Показывает, какую долю в выручке занимает чистая прибыль.

В ячейку столбца K (Маржинальность, %):

=ЕСЛИОШИБКА(J2 / C2; 0)

Важно: Задайте для этого столбца процентный формат ячеек. Функция ЕСЛИОШИБКА защитит таблицу от отображения #ДЕЛ/0!, если строка окажется пустой.

Шаг 3. Создание динамического Дашборда

Когда база данных настроена и заполнена тестовыми продажами, переходим к созданию главного экрана — Дашборда.

1. Выделение ключевых метрик (KPI-блоки)

В самом верху листа выделим крупные ячейки для общей картины за выбранный период. Используем функцию СУММЕСЛИМН.

• Общая выручка: =СУММ(Транзакции[Цена реализации])

• Чистая прибыль: =СУММ(Транзакции[Чистая прибыль])

• Средняя маржинальность бизнеса: =Ячейка_Чистой_Прибыли / Ячейка_Выручки

2. Сводная таблица по товарам (Глубокий анализ)

1. Выделите таблицу Транзакции.

2. Перейдите во вкладку Вставка -> Сводная таблица.

3. Поместите её на новый лист Дашборд.

4. Перетащите поля:

   • В Строки: SKU или Название товара

   • В Значения: Цена реализации (переименуйте в "Выручка"), Чистая прибыль (Сумма), Маржинальность, % (измените параметры поля на Среднее значение).

3. Внедрение условного форматирования (Сигнальная система)

Чтобы моментально видеть проблемные товары:

1. Выделите столбец со средней маржинальностью в Сводной таблице.

2. Перейдите в Главная -> Условное форматирование -> Правила выделения ячеек -> Меньше.

3. Введите 0,1 (то есть менее 10%) и выберите светло-красную заливку.

4. Создайте второе правило: Больше 0,3 (более 30%) — зеленая заливка.

Теперь, если логистика «съест» прибыль товара из-за дальней доставки (например, отправка FBS из Сибири в европейскую часть России зафиксирует повышенный расход на магистраль), строка товара сразу загорится красным.

4. Добавление Срезов (Фильтров)

Чтобы дашборд стал интерактивным:

1. Кликните на Сводную таблицу.

2. Откройте вкладку Анализ сводной таблицы -> Вставить срез.

3. Выберите Дата (или Месяц) и SKU.

4. Разместите появившиеся блоки управления рядом с KPI-метриками.

Теперь при клике на конкретный месяц или товар весь дашборд, графики и показатели чистой прибыли будут мгновенно пересчитываться.

Чек-лист для регулярной работы с дашбордом

1. Раз в неделю / месяц: Скачивайте «Отчет о реализации» и «Отчет о начислениях» из ЛК Ozon.

2. Копирование данных: Вставляйте новые строки в таблицу Транзакции. Заполняйте дату, артикул, цену, комиссии и логистику из отчета. Формулы себестоимости, налогов и прибыли протянутся автоматически.

3. Обновление: Перейдите на лист Дашборд, нажмите вкладку Данные -> Обновить все. Ваш дашборд актуализирован!

Этот инструмент позволит вам точно видеть финансовый результат вплоть до каждого рубля, вовремя выводить из ассортимента «минусовые» товары и масштабировать наиболее маржинальные позиции.

Вы всегда можете скачать файл с уже готовой структурой без ручного создания таблицы.