Введение

Аннотация.

Курс посвящён системному анализу логистических затрат и построению управляемых KPI эффективности доставки. В условиях роста цен на топливо, дефицита водителей и давления маркетплейсов на сроки доставки компании теряют маржу именно в логистике. Материал даёт практический инструментарий на базе Excel: от классификации затрат до расчёта unit-экономики last mile и построения дашбордов. Курс ориентирован на специалистов, которые ежедневно работают с цифрами и должны превращать сырые данные в решения по снижению стоимости доставки без потери сервиса.

Цель курса.

После прохождения курса вы сможете самостоятельно проводить полный анализ логистических затрат, рассчитывать ключевые KPI эффективности доставки в Excel и формировать обоснованные рекомендации по оптимизации на основе данных.

Для кого этот курс.

Курс создан для логистов, аналитиков цепочек поставок, руководителей транспортных отделов, финансовых контролёров и владельцев e-commerce, которые работают с Excel и хотят перейти от интуитивных решений к data-driven управлению. Особенно полезен тем, кто отвечает за P&L доставки или готовит отчёты для топ-менеджмента.

Курс не предназначен для новичков без базовых навыков Excel (сводные таблицы, формулы) и не заменяет специализированные WMS/TMS-системы. Если ваша задача — только оперативное управление курьерами без анализа цифр, материал будет избыточным.

Структура и классификация логистических затрат

Логистические затраты делятся на прямые и косвенные, постоянные и переменные. Прямые включают топливо, оплату водителей, амортизацию транспорта, упаковку. Косвенные — аренда склада, ИТ-системы, административный персонал. Для анализа логистических затрат критично разделять их по звеньям цепочки: inbound, склад, outbound, last mile, reverse logistics. В Excel создайте лист «Классификатор» с колонками: Код статьи, Наименование, Звено, Тип (постоянные/переменные), Драйвер (км, заказ, кг, час). Это позволит в дальнейшем автоматически распределять затраты по заказам. Типичная ошибка — относить все транспортные расходы к last mile, игнорируя магистральные перевозки. Используйте правило: если статья меняется пропорционально объёму — она переменная. Пример драйвера для топлива — пробег, для склада — количество паллето-мест.

Транспортные затраты — обычно 40–60 % от общей логистической себестоимости. Внутри них выделяют: топливо, ТО и ремонт, шины, страховку, лизинг/амортизацию, зарплату водителей с налогами, платные дороги. Для точного анализа логистических затрат в Excel собирайте данные по каждому ТС: пробег, расход л/100 км, фактический и нормативный. Формула удельного расхода: $\text{Расход} = \frac{\text{Литры}}{\text{Пробег}} \times 100$. Сравнивайте с нормой и выделяйте отклонения цветом через условное форматирование. Анти-паттерн — считать только среднюю стоимость километра по всему парку: разница между газелью и фурой может достигать 3–4 раз. Ведите отдельный лист «ТС» с атрибутами (тип, грузоподъёмность, год) и связывайте через ВПР или СУММЕСЛИМН.

Складские затраты включают аренду, коммунальные, персонал, технику (погрузчики, стеллажи), потери и порчу. Ключевой метрикой является стоимость хранения одной единицы или одного кубометра в сутки. Рассчитывайте: $\text{Стоимость хранения} = \frac{\text{Общие складские затраты за период}}{\text{Средний остаток} \times \text{Дни}}$. В Excel используйте сводную таблицу по номенклатуре и зонам склада. Практика показывает, что 20 % SKU генерируют 80 % складских затрат (ABC-анализ). Выделяйте медленно оборачиваемые позиции и переводите их на внешний склад или drop-shipping. Совет: добавляйте в модель коэффициент оборачиваемости и стоимость капитала, замороженного в запасах — это часто упускаемый драйвер.

Last mile — самый дорогой и волатильный сегмент. Затраты здесь: оплата курьеров (фикс + за заказ/км), упаковка, терминалы выдачи, возвраты, штрафы за срыв SLA. Unit-экономика last mile считается на один доставленный заказ: $\text{CPO} = \frac{\text{Все затраты last mile}}{\text{Количество успешных доставок}}$. В Excel создайте модель, где входные параметры (плотность заказов, среднее расстояние, процент возвратов) меняются, а выход — CPO и маржа. Типичный анти-паттерн — игнорировать стоимость неуспешных попыток доставки. Добавляйте коэффициент «попыток на успешную доставку» (обычно 1,15–1,4). Сравнивайте собственные курьеры vs аутсорс vs постаматы: при плотности выше 8–10 заказов на км² собственная доставка чаще выгоднее.

Обратная логистика (returns) может съедать 15–30 % прибыли в e-commerce. Затраты: приёмка возврата, проверка, ремонт/утилизация, повторная доставка или списание. Введите отдельный KPI «стоимость возврата на заказ» и отслеживайте причины (не тот размер, брак, отказ). В Excel свяжите данные из CRM/OMS с логистическими реестрами через номер заказа. Используйте сводную по причинам возврата и каналам. Рекомендация: внедрите порог — если стоимость возврата + потери превышает 40 % маржи товара, автоматически блокируйте бесплатный возврат для этой категории. Это снижает объём обратной логистики без потери лояльности.

Ключевые KPI эффективности доставки

Базовый набор KPI эффективности доставки включает: On-Time Delivery (OTD) — процент заказов, доставленных в обещанный интервал; First Attempt Success Rate (FASR) — доля успешных доставок с первой попытки; Cost Per Order (CPO); Average Delivery Time; процент возвратов. Формула OTD: $\text{OTD} = \frac{\text{Заказы в SLA}}{\text{Все доставленные заказы}} \times 100\%$. В Excel считайте через СЧЁТЕСЛИМН по статусу и дате. Целевые значения зависят от сегмента: для продуктовой доставки OTD ≥ 95 %, для fashion — ≥ 90 %. Важно разбивать KPI по городам, каналам и типам доставки, иначе средние цифры скрывают проблемные зоны.

Утилизация транспорта и курьеров — критический драйвер затрат. Коэффициент загрузки (Load Factor) = фактический вес/объём / номинальная грузоподъёмность. Для курьеров — количество заказов на смену или на час. В Excel собирайте данные из GPS/трекинга и табелей. Формула: $\text{Утилизация} = \frac{\text{Фактические заказы}}{\text{Плановая ёмкость}} \times 100\%$. Если утилизация ниже 70 %, пересматривайте маршруты и зоны. Практика: динамическое зонирование с помощью Excel Solver или простого VBA-скрипта позволяет поднять загрузку на 10–15 %. Анти-паттерн — планировать маршруты «на глаз» без учёта временных окон клиентов.

SLA и штрафы напрямую влияют на P&L. Введите метрику «штрафы на 1000 заказов» и «стоимость минуты просрочки». В Excel создайте лист штрафов с формулами: если фактическое время > обещанного + буфер, то штраф = тариф × минуты. Свяжите с договором перевозчика. Рекомендация: ежемесячно ранжируйте перевозчиков по индексу «цена + качество» (CPO × (1 − OTD)). Это даёт объективную основу для переторжки контрактов. Типичная ошибка — считать только прямые штрафы и игнорировать упущенную выручку от повторных заказов недовольных клиентов.

Customer Experience KPI: Net Promoter Score по доставке, процент жалоб на курьера, доля «доставка в руки». Эти показатели коррелируют с повторными покупками. В Excel объединяйте данные опросов с логистическими статусами через ID заказа. Стройте корреляционную матрицу: OTD vs NPS, FASR vs жалобы. Если корреляция высокая, инвестируйте в улучшение этих метрик в первую очередь. Практический совет: автоматизируйте сбор NPS через SMS/мессенджер сразу после доставки и подтягивайте ответы в Excel через Power Query.

Финансовые KPI логистики: доля логистических затрат в выручке (Logistics Cost Ratio), вклад логистики в маржу, ROI инициатив по оптимизации. Формула: $\text{LCR} = \frac{\text{Все логистические затраты}}{\text{Выручка}} \times 100\%$. Бенчмарк для e-commerce 8–15 %, для продуктового ритейла 4–7 %. В Excel стройте waterfall-диаграмму изменений LCR месяц к месяцу. Добавляйте сценарии: «если утилизация +5 %», «если возвраты −3 %». Это превращает отчёт в инструмент принятия решений, а не просто констатацию факта.

Сбор и подготовка данных в Excel

Качество анализа логистических затрат на 80 % зависит от качества данных. Источники: TMS, WMS, ERP, GPS-трекеры, табели, счета перевозчиков, CRM. В Excel используйте Power Query (Данные → Получить данные) для автоматической загрузки и очистки. Типовые проблемы: разные форматы дат, дубли номеров заказов, пустые поля статуса. Создайте единый «золотой» справочник заказов с ключом OrderID. Правило: никогда не копируйте данные вручную — только через запросы. Это исключает ошибки и позволяет обновлять модель одним кликом.

Очистка данных: удаление дублей (Данные → Удалить дубликаты), заполнение пропусков через ВПР или XLOOKUP, стандартизация статусов (например, «Доставлен», «Delivered», «Вручено» → единый код). В Power Query применяйте шаги: Replace Values, Split Column, Change Type. Для дат используйте формат ГГГГ-ММ-ДД. Совет: создайте лист «Правила очистки» с таблицей соответствий и применяйте его через Merge. Это делает процесс воспроизводимым и понятным для коллег.

Объединение таблиц — ключ к unit-экономике. Связывайте реестр заказов с реестром рейсов, табелем водителей и счетами через общие ключи (OrderID, TripID, DriverID). В Excel 365 используйте XLOOKUP или Power Pivot с моделью данных. Для старых версий — ВПР + СУММЕСЛИМН. Пример: стоимость конкретного заказа = топливо рейса × (вес заказа / общий вес) + оплата курьера / количество заказов в рейсе. Документируйте все связи на листе «Модель данных».

Календарь и измерения времени обязательны. Создайте таблицу дат с полями: Дата, Неделя, Месяц, Квартал, День недели, Праздник (да/нет). Свяжите её с фактовыми таблицами. Это позволит быстро считать KPI по любым периодам и сравнивать «как в прошлом году». В Power Pivot отметьте таблицу дат как Date Table. Анти-паттерн — считать всё через текстовые фильтры по месяцу: при смене года модель ломается.

Расчёт unit-экономики доставки в Excel

Unit-экономика начинается с полной себестоимости одного доставленного заказа (Fully Loaded CPO). Включайте: прямые затраты рейса, долю постоянных затрат склада и офиса, стоимость возвратов, амортизацию ИТ. Формула: $\text{CPO} = \frac{\text{Переменные} + \text{Постоянные} + \text{Возвраты}}{\text{Успешные доставки}}$. В Excel постройте модель на отдельном листе с входными ячейками (выделены синим) и расчётными (чёрным). Используйте имена диапазонов для читаемости. Сценарий «базовый / оптимистичный / пессимистичный» реализуйте через таблицу данных (Данные → Что-если → Таблица данных).

Распределение постоянных затрат — спорный момент. Методы: по количеству заказов, по выручке, по машино-часам, по кубометрам. Для доставки чаще используют количество заказов или машино-часы. В Excel создайте ключ распределения и применяйте СУММЕСЛИМН. Важно: фиксируйте метод в документации модели, иначе при смене аналитика цифры «поедут». Рекомендация: считайте два варианта (по заказам и по часам) и показывайте оба руководству — это повышает доверие к цифрам.

Маржинальность доставки = выручка от доставки (или наценка) − CPO. Если доставка «бесплатная» для клиента, маржа считается через вклад в общую маржу заказа. В Excel добавляйте колонку «Вклад доставки» = маржа товара × коэффициент − CPO. Это позволяет видеть, какие категории товаров «кормят» логистику, а какие её дотируют. Практика: при CPO выше 30 % от средней маржи товара запускайте программу оптимизации или повышайте порог бесплатной доставки.

Сценарный анализ «что если»: изменение плотности заказов, среднего чека, процента возвратов, тарифа перевозчика. В Excel используйте Диспетчер сценариев или просто копируйте блок расчёта и меняйте входные. Добавляйте чувствительность: таблица, где по строкам меняется один параметр, по столбцам — другой, в центре — CPO. Это наглядно показывает, на какие рычаги давить в первую очередь. Типичный вывод: снижение возвратов на 2 п.п. часто даёт больший эффект, чем переговоры о тарифе −5 %.

Построение дашбордов и визуализация KPI

Дашборд KPI эффективности доставки должен отвечать на три вопроса: где мы сейчас, почему так, что делать. Структура: верхняя строка — KPI-карточки (OTD, CPO, FASR, LCR) с условным форматированием (зелёный/жёлтый/красный). Ниже — тренды по неделям, разбивка по городам/каналам, топ-проблемные рейсы. В Excel используйте сводные таблицы + срезы (Slicers) + диаграммы. Обновление — через кнопку «Обновить всё» после загрузки новых данных Power Query.

Карточки KPI делайте через формулы с именованными ячейками. Пример: =ТЕКСТ(OTD,"0.0%") & " | цель 95%". Цвет меняйте правилом условного форматирования по значению. Добавляйте мини-спарклайны (Вставка → Спарклайны) для тренда за 8–12 недель. Это даёт мгновенное понимание динамики без лишних графиков. Совет: размещайте карточки в один ряд и фиксируйте область просмотра, чтобы при прокрутке они оставались видимыми.

Географическая визуализация: карта городов с размером пузыря = объём заказов, цветом = OTD или CPO. В Excel 365 можно использовать встроенные карты (Вставка → Карты). Для более старых версий — экспорт в Power BI или просто таблица с условным форматированием. Выделяйте города, где CPO выше среднего на 20 % и более — кандидаты на пересмотр модели доставки (постаматы, ПВЗ, другой перевозчик).

Drill-down обязателен. Пользователь должен иметь возможность кликнуть на город и увидеть разбивку по дням недели, типам доставки, перевозчикам. Реализуется через несколько сводных таблиц, связанных одними срезами. Или через Power Pivot + меры DAX (простые CALCULATE). Документируйте, какие фильтры влияют на какие блоки, чтобы коллеги не путались. Анти-паттерн — один огромный лист с 30 диаграммами без логики.

ABC-XYZ и сегментация для управления затратами

ABC-анализ по вкладу в логистические затраты: A — 80 % затрат (обычно 10–15 % объектов), B — 15 %, C — 5 %. Объекты могут быть SKU, клиенты, города, перевозчики. В Excel: отсортируйте по убыванию затрат, посчитайте накопительный процент, присвойте класс формулой ЕСЛИ. XYZ добавляет стабильность спроса/затрат (коэффициент вариации). Комбинация AX — жёсткий контроль, CZ — минимальное внимание. Это позволяет сфокусировать усилия аналитика на 20 % объектов, дающих 80 % эффекта.

Сегментация клиентов по стоимости обслуживания. Считайте CPO по каждому клиенту или сегменту (B2B/B2C, частота заказов, средний чек). В Excel используйте сводную с группировкой. Клиенты с высоким CPO и низкой маржой — кандидаты на изменение условий (платная доставка, минимальный заказ, другой SLA). Практика показывает, что 5–10 % клиентов генерируют 30–40 % убытков по доставке. Выявление таких сегментов — быстрый способ улучшить общую экономику.

Сегментация по географии и плотности. Разделите зоны на «высокая плотность» (CPO низкий), «средняя», «низкая» (CPO высокий). Для низкоплотных зон рассматривайте альтернативы: постаматы, ПВЗ, самовывоз, повышение тарифа, отказ от доставки. В Excel наложите данные заказов на сетку координат (если есть lat/lon) или просто на почтовые индексы. Рассчитайте плотность = заказы / км² и постройте матрицу решений.

Оптимизация на основе данных

После анализа логистических затрат формируется портфель инициатив. Типовые рычаги: повышение утилизации (динамические маршруты, кросс-докинг), снижение возвратов (улучшение описаний, фото, примерка), переговоры с перевозчиками на основе бенчмарков, перевод части потока на постаматы/ПВЗ, изменение порога бесплатной доставки. Каждую инициативу оценивайте по формуле: эффект (снижение CPO × объём) − затраты на внедрение − риски. Приоритизируйте по ROI и сроку окупаемости.

Переговоры с перевозчиками на цифрах. Подготовьте «зеркало» — сравнение вашего CPO с рыночными бенчмарками и с их конкурентами. Покажите, где они выходят за рамки SLA и сколько это стоит вам. В Excel соберите таблицу: перевозчик | объём | CPO | OTD | штрафы | индекс качества. Это превращает переговоры из торга в обсуждение фактов. Часто удаётся добиться −5–12 % без потери сервиса.

Динамическое ценообразование доставки. Если модель показывает, что в определённые дни/зоны CPO резко растёт, вводите динамический тариф или стимулируйте клиентов выбирать более дешёвые слоты (скидка за утро/день). В Excel можно прототипировать правила: если плотность < X и день = суббота, то тариф +Y %. Затем тестируйте на пилотной зоне и замеряйте влияние на конверсию и общую маржу.

Автоматизация рутинных расчётов. После того как модель в Excel отлажена, переносите критические KPI в Power BI или Google Data Studio для ежедневного мониторинга, а Excel оставляйте для глубокого ad-hoc анализа. Используйте VBA или Office Scripts для кнопки «Обновить модель и отправить отчёт». Это высвобождает время аналитика на поиск инсайтов, а не на копирование цифр.

Типовые ошибки и анти-паттерны

Ошибка №1 — считать только средние. Средний CPO по компании может быть 180 ₽, но в одном городе 95 ₽, в другом 340 ₽. Без разбивки решения будут неверными. Всегда требуйте drill-down до города/канала/перевозчика. В Excel это решается срезами и несколькими сводными.

Ошибка №2 — игнорировать качество данных. Если 15 % заказов без статуса или с неправильной датой, KPI OTD бессмысленны. Перед любым отчётом проверяйте completeness: процент заполненных ключевых полей. В Power Query добавляйте столбец-флаг «Данные полные» и фильтруйте.

Ошибка №3 — оптимизировать один KPI в ущерб другим. Снижение CPO за счёт удлинения сроков доставки убивает NPS и повторные покупки. Всегда смотрите на систему метрик: CPO + OTD + FASR + NPS. В дашборде размещайте их рядом и выделяйте конфликтующие зоны.

Ошибка №4 — разовые отчёты вместо системы. Анализ «раз в квартал» не позволяет управлять. Внедрите еженедельный цикл: данные → дашборд → 3 инсайта → 1–2 действия → замер эффекта. Excel-модель должна обновляться автоматически, а не собираться заново каждый раз.

Практический кейс: снижение CPO на 18 %

Исходная ситуация: e-commerce, 12 000 заказов/мес, CPO 220 ₽, OTD 87 %, возвраты 12 %. После построения модели выяснилось: 35 % заказов в низкоплотных зонах с CPO 380 ₽, возвраты по причине «не тот размер» дают 40 % стоимости обратной логистики, утилизация курьеров 62 %. План: перевод 20 % низкоплотного потока на постаматы, улучшение карточек товаров, динамическое зонирование. Через 4 месяца CPO 180 ₽ (−18 %), OTD 93 %, возвраты 8 %. Ключ успеха — еженедельный мониторинг и быстрые итерации.

Как воспроизвести кейс в Excel: 1) собрать 3 месяца данных заказов + рейсов + возвратов; 2) построить unit-экономику по зонам; 3) выделить сегменты с CPO > среднего × 1,5; 4) смоделировать перевод на альтернативный канал (постамат) с новыми параметрами; 5) посчитать эффект и риски; 6) запустить пилот на 2 зоны; 7) сравнить факт с моделью и скорректировать. Весь цикл занимает 2–3 недели работы аналитика.

Уроки кейса: данные важнее интуиции; маленькие изменения в нескольких местах дают большой суммарный эффект; без дашборда невозможно управлять в реальном времени; сопротивление операционного персонала снижается, когда цифры прозрачны и показывают выгоду для всех.