Управленческая отчетность и анализ продаж в Excel-модели с Power Query

Руководитель финансовой службы напрямую не связан с продажами, но может обеспечить финансовые механизмы для эффективности продаж управленческий учет, оперативную оценку финансового результата), которые обеспечат бизнесу плановую выручку и прибыль, минимизируют возможные риски. Как создать управленческие отчеты в сводных таблицах в Excel-модели, рассмотрим на примере мебельного бизнеса. Excel-модель, представленная в статье и «Сервисе форм» (код доступа — на с. 1), подойдет для любой сферы, достаточно корректно проработать справочники.

ОРГАНИЗАЦИЯ ПОЛУЧЕНИЯ ОПЕРАТИВНОЙ ИНФОРМАЦИИ ДЛЯ УПРАВЛЕНЧЕСКОЙ ОТЧЕТНОСТИ ПО ПРОДАЖАМ В EXCEL-МОДЕЛИ

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

• руководить казначейством;

• участвовать в формировании ценовой и ассортиментной политики компании;

• организовывать операционное управление — проводить экспертизу и согласование договоров, следить за бюджетными лимитами;

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

• вводить схемы личной мотивации продавцов и т. д.

Сложности в получении оперативной информации для управленческой отчетности могут возникнуть в следующих случаях:

• компания включает в себя несколько бизнес-единиц, юридически, а иногда территориально обособленных;

• учетные «классические» бухгалтерские документы для оперативного принятия решений неинформативны, поступают с задержкой;

• CRM в полноценном виде отсутствует или организован в разных базах данных.

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

На примере предприятия по производству мебели сформируем Excel-модель и установим порядок получения оперативной отчетности по продажам в следующем формате:

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

• менеджеры по продажам с установленной периодичностью (ежедневно, еженедельно) ведут и предоставляют в финансовую службу отчеты в формате Excel;

• полученные отчеты финансовый специалист помещает в специальную папку и консолидирует их с помощью надстройки Power Query;

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

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

СОЗДАНИЕ СПРАВОЧНИКОВ И НАСТРОЙКА ШАБЛОНА ОТЧЕТА О ПРОДАЖАХ

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

Как правило, проектируют справочники «Категории», «Ценовой сегмент», «Тип продаж», «Регион» и т. д. Все справочники форматируют как таблицы: вкладка Главная → Стили → Форматировать как таблицу. Далее переходят на вкладку Конструктор → Свойства и указывают имя таблицы (например, «Категории»).

Простые справочники задействуют для настройки шаблона Отчета и организации двухуровневых справочников (рис. 2).

Обратите внимание на структуру двухуровневого справочника торговой сети (см. рис. 2). Цель в следующем. Чтобы упростить внесение оперативной информации в Отчет, список дилеров (или Ф. И. О. менеджеров) будет открываться в зависимости от предварительно заданного типа продаж.

Чтобы при указании типа продаж «Дилер» открывался справочник дилерской сети, задают поименованный диапазон: вкладка Формулы → Определенные имена → Присвоить имя. Заданному диапазону присваивают в качестве имени тип продаж, как в столбце А на рис. 2 (Дилер, ГОЗ и т. д.). Увидеть уже присвоенные имена можно в «Диспетчере имен» во вкладке «Формулы».

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

В таблице 1 представлен двухуровневый справочник «Ассортимент».

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