Оперативные отчеты по продажам в Excel

Окончание табл. 2

Согласно Журналу учета продаж за 17.08.2019 торговая точка в г. Воронеж реализовала 22 комплекта школьной формы и один костюм элит-сегмента — Костюм мужской «Авангард». Продажи продукции под торговыми марками партнеров отсутствовали.

На школьную форму действует скидка 15 %. На продукцию ТМ «Рейтинг» элитного ценового сегмента скидки не распространяются. Выручка составила 53 880 руб. (44 880 + 9000), себестоимость реализации — 45 000 руб. (39 600 + 5400). Прибыль от продаж — 8880 руб.

Настройки Журнала учета продаж:

1. Наиболее просто настроить заполнение с помощью функции «ВПР», которая ищет значение в крайнем левом столбце таблиц-справочников (см. рис. 1) и выводит значение ячейки, находящейся в указанном столбце той же строки. По умолчанию справочник должен быть отсортирован по возрастанию. В графах табл. 2 заданы следующие ВПР-формулы:

•  «Регион продаж»: =ВПР(A2;Справочники!$D$1:$F$3;2). Аргументы функции «ВПР» представлены на рис. 2.

Важный момент: с помощью «$» закреплен диапазон, чтобы он не сдвигался при копировании. Если потребуется расширить справочники, здесь и далее соответствующим образом диапазон увеличивают.

•  «Торговая площадь»: =ВПР(A2;Справочники!$D$1:$F$3;3);
•  «Отчетный период»: =ВПР(F2;Справочники!$M$1:$N$54;2).

2. Функция «ВПР» имеет ограниченное применение, так как справочники часто сформированы текстовыми полями. Поэтому для следующих граф использована комбинация функций «ИНДЕКС» и «ПОИСКПОЗ»:

•  «Тип торговой марки» (рис. 3):

=ИНДЕКС(Справочники!$A$1:$B$5;ПОИСКПОЗ(H2;Справочники!$B$1:$B$5;0);1);

•  «Ценовой сегмент»:

=ИНДЕКС(Прайс!$A$1:$E$13;ПОИСКПОЗ(J2;Прайс!$B$1:$B$13;0);5);

•  «Цена продажи»:

 =ИНДЕКС(Прайс!$A$1:$E$13;ПОИСКПОЗ(J2;Прайс!$B$1:$B$13;0);3);

•  «Себестоимость»:

=ИНДЕКС(Прайс!$A$1:$E$13;ПОИСКПОЗ(J2;Прайс!$B$1:$B$13;0);4);

•  «Скидка»:

 =ИНДЕКС(Справочники!$H$1:$K$5;ПОИСКПОЗ(Q2;Справочники!$H$1:$H$5;0);2).

3. Номер недели сформирован функцией «НОМНЕДЕЛИ» согласно дате продажи.

4. Простой выпадающий список подключен к графам «Торговая марка» и «Маркетинговая акция». На вкладке Данные задают команду: Работа с данными → Проверка данных. В появившемся окне «Проверка вводимых значений» указывают тип данных Список, в поле «Источник» — диапазон ячеек соответствующего справочника.

5. Двухуровневый выпадающий список для графы «Модель» задают аналогично, только вместо диапазона ячеек указывают сложную формулу (рис. 4):

=СМЕЩ(Прайс!$A$1;ПОИСКПОЗ(H2;Прайс!$A$1:$A$13;0)-1;1;СЧЁТЕСЛИ(Прайс!$A$1:$A$13;H2);1).

Функция СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]) возвращает ссылку на диапазон ячеек, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов:

• ссылка в нашем случае — $A$1 — верхний левый угол табл. 1;
• смещ_по_строкам — ПОИСКПОЗ(Н2;$A$1:$A$13;0)-1 — номер строки со значением искомой группы (в нашем случае торговой марки — ячейка Н3) минус единица;
• смещ_по_столбцам — 1 — поскольку нам нужен столбец с подгруппами (моделями);
•  [высота] — СЧЁТЕСЛИ($A$1:$A$13;Н2) — количество подгрупп в искомой группе (количество моделей торговой марки Н2);
•  [ширина] — 1 — ширина нашего столбца с моделями.

Двухуровневый список в действии представлен на рис. 5. Доступный к выбору перечень моделей ТМ «Гамма» на этом рисунке можно сравнить со справочником в табл. 1.

Чтобы оценить эффективность продаж, Журнала учета по одному магазину недостаточно. Организуют консолидированную отчетность по всем торговым точкам. Для этого действуют следующим образом:

• форму отчета (см. табл. 2) рассылают для заполнения всем торговым точкам;
• устанавливают сроки сдачи отчета (в данном случае еженедельно);
• полученные по электронной почте оперативные отчеты по продажам консолидируют с помощью Power Query;
• сформированный в Power Query запрос используют для построения сводных таблиц согласно требованиям руководства и специфике бизнеса;
• оперативные отчеты, созданные с помощью сводных таблиц, анализируют, используют для контроля и выработки управленческих решений.

КОНСОЛИДАЦИЯ ОТЧЕТОВ

Консолидация отчетов — важный этап создания системы оперативной отчетности. Чтобы консолидировать отчеты по продажам, используют надстройку Excel Power Query. Данная надстройка консолидирует данные из многочисленных разрозненных источников в целях подготовки данных для дальнейшего анализа в Excel, Power Pivot и др. Если надстройка не установлена, ее можно скачать для Excel 2010 и 2013 бесплатно (https://www.microsoft.com).

Консолидацию выполняют поэтапно:

Этап 1. Отчеты о продажах, которые согласно ранее настроенной форме-шаблону (см. табл. 2) торговые точки прислали по электронной почте, помещают в отдельную папку.

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

Этап 3. Проверяют, чтобы не было пустых строк. Используют сочетание клавиш Ctrl+End, которое перемещает пользователя на последнюю использованную ячейку листа. Это должна быть последняя ячейка отчета с данными. Если после нажатия Ctrl+End экономист попадает на пустую ячейку правее или ниже таблицы отчета, их удаляют и сохраняют файл.

Этап 4. Создают отдельную книгу Excel — файл с названием «Консолидированные отчеты». В него настраивают консолидированный запрос по отчетам магазинов и аналитические отчеты по продажам с помощью сводных таблиц. Открыв его, переходят на вкладку Power Query → Получение внешних данных → Из файла → Из папки. В появившемся окне указывают путь к папке и выполняют команду Изменить (см. рис. 6 в «Сервисе форм», код доступа - на с. 119).

Так как торговых точек две (на практике их будет больше), Power Query покажет следующие запросы (рис. 7). В окне редактора запросов разворачивают двойной стрелкой столбец Binary и выбирают лист, который нужно взять из каждого файла. Листы со справочниками нам не нужны, выбираем лист с именем «Отчет» и нажимаем кнопку «ОК» (см. рис. 8 в «Сервисе форм»). Далее в окне «Параметры запроса» → Свойства присваивают имя запросу: «Сводка и аналитика продаж». Запрос готов к анализу. Выполняют команду: Закрыть и загрузить в → Только создать подключение.

Этап 5. Запрос «Сводка и аналитика продаж» (рис. 9) используют для построения сводных таблиц. Выполняют команду: вкладка Вставка → Сводная таблица. В диалоговом окне «Создание сводной таблицы» выбирают Использовать внешний источник данных → Выбрать подключение. Из списка существующих подключений выбирают запрос «Сводка и аналитика продаж» → Открыть.

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

АНАЛИТИЧЕСКИЕ СПРАВКИ (ТОРГОВЫЕ МАРКИ И МОДЕЛИ)

Из консолидированного запроса получают информацию в аналитических разрезах, необходимых для принятия управленческих решений. Переходим к анализу и контролю отчетов по продажам, сформированных с помощью сводных таблиц, источником данных для которых выступает запрос в Power Query (содержание запроса — Журналы учета согласно табл. 2).

Функционирование компании напрямую зависит от платежеспособного спроса разных групп населения на мужскую и детскую одежду. Аналитика поможет проверить или опровергнуть следующие гипотезы:

• предлагаемые и приобретаемые на рынке швейные изделия в большинстве импортные, отсутствует достаточный спрос на аналогичную отечественную продукцию. Отчеты должны показать, какая продукция пользуется высоким спросом — модельный ряд, ценовой сегмент и т. д.;
• торгово-сбытовая сеть компании развита слабо, на швейной фабрике главенствует производственно-ориентированная концепция;
• приоритетная направленность компании на корпоративных клиентов в ущерб конечным потребителям (контрактное производство, пошив спецодежды);
• неэффективный менеджмент в компании.

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

Сильные торговые марки внушают доверие. Приобретая продукцию известных брендов, потребители минимизируют:

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

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

Преимущества развития собственных торговых брендов для компании-производителя:

• способствуют выделению и объединению продукции в группы;
• упрощают управление продукцией, контроль над ней, учет и мониторинг движения продукции на рынке;
• торговая марка гарантирует юридическую защиту уникальных характеристик выпускаемой продукции и прав интеллектуальной собственности;
• лояльность покупателей к качественным брендам обеспечивает предсказуемость и устойчивость спроса, препятствует появлению новых конкурентов.

Рассматриваемая компания ООО «Модный клуб» использует указанные выше преимущества для укрепления своих позиций на рынке. Торговая политика компании направлена на максимизацию продаж продукции под двумя собственными и двумя партнерскими торговыми марками.

Как видно из рис. 10, за весь анализируемый период наибольшую выручку компании принесла продукция собственной ТМ «Рейтинг» (447 636 руб.), лидер продаж — Костюм мужской «Классика». На втором месте также продукция собственной ТМ «Гамма» (224 200 руб.).

В целом положение дел соответствует стратегии компании: в торговых точках должны преобладать продажи продукции под собственными торговыми марками, что позволит обеспечить загрузку собственных производственных мощностей.

Однако структура продаж по регионам и отдельным периодам может не соответствовать указанной стратегии. Чтобы контролировать структуру продаж по торговым маркам, формируют табл. 3. Оперативно сформировать аналитику позволяет сводная таблица (см. рис. 10).

Список полей табл. 3:

• названия строк — торговая марка и модель;
• значения — количество продукции и сумма продаж с учетом скидки;
• названия столбцов — значения.

Для интерактивной фильтрации данных использованы срезы.

Как видно из табл. 3, продажи в торговой точке в Рязани за период 19.08–25.08.2019 отличаются от задекларированной стратегии. Преобладают продажи ТМ партнеров «Стиль» — 102 600 руб. Специалисты по продажам выясняют причины. Вероятные из них:

• обширная рекламная кампания ТМ «Стиль» привела к всплеску продаж костюмов мужских «Английский» и «Итальянский»;
• активизация конкурентов привела к снижению продаж собственной продукции;
• сезонный спрос;
• отсутствие продукции собственных торговых марок в магазине (в торговом зале, на складе).

Важный момент: чтобы избежать потерь, последний пункт нужно исключить и не допускать в будущем.

Анализ и контроль по торговым маркам дополняют анализом в разрезе регионов и моделей (рис. 11, табл. 4).

Список полей сводной табл. 4:

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

Применен срез для фильтрации по неделям продаж (доступны и другие). К сводной таблице можно применить дополнительные вычисления — % от общей суммы.

Предположим, руководством поставлена задача проанализировать весь объем продаж в структуре. Воспользуемся табл. 4.

Согласно данным табл. 4 наиболее успешные продажи в Рязани. Они принесли 52,04 % от общей выручки. Важно выяснить причину — торговая площадь и ассортимент торговой точки намного больше; магазин находится в удачном месте с высоким трафиком целевой группы покупателей; другие факторы.

В Рязани практически половина продаж приходится на ТМ партнеров — 24,62 из 52,04 %, в то время как в Воронеже только 6,77 из 47,96 %. Это в определенной степени противоречит стратегии компании: в торговых точках должны преобладать продажи продукции собственного производства, а продукцию компаний-партнеров реализуют, чтобы расширить ассортимент и обеспечить высокий приток покупателей, поддержать рентабельность магазинов.

Возможная причина высоких продаж партнерских ТМ — модный модельный ряд. Работа по совершенствованию модельного ряда согласно модным тенденциям и критериям удачной посадки изделий по фигуре становится как никогда актуальной.

Проведем мониторинг продаж по моделям собственных торговых марок. Лидерами продаж выступают:

• Костюм мужской «Классика» — 29,89 % (должны вызывать опасения низкие продажи этого изделия в Рязани — 9,64 %);
• школьная форма — 11,8 %. Согласно торговой политике в магазинах компании продажа школьной формы других производителей недопустима. Компания стремится утвердиться в данном сегменте. Низкие продажи в Рязани (1,8 %) обусловлены высокой конкуренцией;
• Пиджак для мальчика синий — 11,57 %.

Костюм мужской «Классика» и Пиджак для мальчика синий компания может планировать к запуску в производство, если на складе нет запасов данной продукции. Школьная форма является сезонным товаром, поэтому ее шьют с учетом фактора сезонности.

В группе партнерских ТМ лидирует по продажам Костюм мужской «Английский» (13,54 %). Компании важно проанализировать причины такого высокого спроса на эту модель — выгодное соотношение цена/качество, инновационная модель, хорошая посадка по фигуре, известный бренд и др. Специалистам компании необходимо разработать собственную конкурентоспособную модель со сходными конкурентными преимуществами.

Низкий спрос на Пальто мужское (2,37 % в Воронеже и 3,56 % в Рязани) объясняется преддверием сезона. Пик продаж этой продукции ожидают в октябре (согласно данным прошлого года).

Должен вызвать озабоченность низкий спрос на Костюм мужской «Владимир» (1,88 % по обеим торговым точкам) и Костюм мужской «Авангард» (2,6 % всего, в Воронеже только 0,87 %). Рассматривают вопрос о целесообразности снятия их с производства (если это не новинки), так как производство мелких партий продукции в условиях ООО «Модный клуб» убыточно.

На практике таких позиций может быть много, поэтому мониторинг консолидированной отчетности дополняют АВС- и XYZ-анализом. Цель анализа — выделить из большого количества однотипной продукции ту, на которой нужно сосредоточить внимание производственного и коммерческого персонала исходя из выбранной стратегии продаж компании.

Рекомендуем компании внедрять и развивать взаимосвязанный:

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

Этапы управления категорией товаров:

• определяют категорию в общем ассортименте продукции;
• собирают категории из отдельных видов товаров;
• устанавливают маркетинговые показатели категории;
• формулируют стратегию по категории;
• определяют тактику маркетинга;
• определяют задачи в процессе управления категорией;
• контролируют и анализируют категории.

Для производителя (поставщика) категорийный менеджмент является дополнением к бренду и продакт-менеджменту.