Годовой отчет — важный документ для экономической службы. В нем нужно выделить приоритетные показатели и существенные отклонения от нормативов, чтобы руководитель обратил внимание на недочеты и достижения в работе организации. Письменного отчета и таблиц часто недостаточно — требуется информативная визуализация. В связи с этим рассмотрим, как с помощью нестандартных диаграмм в Excel привлечь внимание руководства к проблемным зонам и показать высокие результаты, которых достигла компания за отчетный год.
«ТОРНАДО» ПРИБЫЛИ
Руководство компании прежде всего заинтересует заработанная за год прибыль в разрезе точек продаж, каналов сбыта, категорий продукции, ценовых сегментов и видов деятельности.
Для руководителей компании необходимо подготовить информацию о плановой и фактической прибыли, сравнить показатели прибыли отчетного и прошлого года по каждому месяцу.
Когда нужно сравнить два показателя за несколько месяцев или лет, используют диаграмму типа «Торнадо» (рис. 1).
Диаграмма на рис. 1 позволяет оперативно сделать следующие выводы:
- наибольший приток прибыли в компанию обеспечивает ценовой сегмент «Стандарт». Однако даже в самые низкоприбыльные периоды прибыль от сегмента «Элит» не опускается ниже 400 тыс. руб. в месяц, что говорит о стабильных позициях компании в указанном сегменте;
- за отчетный год компания заработала максимальную прибыль в июле: «Стандарт» — 2100 тыс. руб., «Элит» — 1300 тыс. руб. Причины:
– сезонность;
– результаты профессиональной маркетинговой компании или активных продаж;
– заключение нового договора;
– оптовые поставки крупному, но непостоянному клиенту;
- минимальный приток прибыли с обоих сегментов в ноябре и апреле. Типичные причины:
– закрытие торговых точек;
– неудачная политика скидок;
– сезонный спад продаж;
– снижение цен в ответ на демпинг конкурентов в указанные периоды.
Построение диаграммы «Торнадо»
«Торнадо» в стандартных диаграммах Excel отсутствует, однако ее можно быстро построить в несколько шагов, взяв за основу обычную линейчатую диаграмму.
Шаг 1. Готовят таблицу с исходными данными.
В стандартной линейчатой диаграмме линии направлены в одну сторону. Чтобы линии в диаграмме расходились в разные стороны, нужно, чтобы один из столбцов имел отрицательные значения. На рис. 1 это сегмент «Элит». В таблицу 1 вносят данные по прибыли в столбцы «Сегмент “Стандарт”» и «Сегмент “Элит” Вспом.».
Для построения диаграммы используют зеленый столбец «Элит», где данные из вспомогательного столбца умножены на минус 1. Чтобы скрыть минусы, в Формате ячеек настроен тип «#;#;0» (рис. 2). Это позволит убрать минусы перед числами в таблице и диаграмме, представленной на рис. 1.
Шаг 2. Вставляют линейчатую диаграмму с накоплением. Для этого выполняют команду: Вставка → Диаграммы → Линейчатая с накоплением. По умолчанию диаграмма имеет вид, как на рис. 3.
Шаг 3. Форматируют нужный вид диаграммы.
Вертикальную ось с названиями месяцев переносят вправо. Для этого вызывают контекстное меню горизонтальной оси и выбирают Формат оси, а в Параметрах оси — обратный порядок значений (рис. 4). Кроме этого, в группе «Вертикальная ось пересекает» выбирают Максимальное значение по оси. Затем горизонтальную ось удаляют. Легенду смещают вниз.
Для увеличения ширины линеек выполняют команду: Формат ряда данных → Параметры ряда → Боковой зазор 40 %. Добавляют подписи данных. Настраивают шрифты и заливку цветом согласно корпоративным стандартам. Получают диаграмму, как на рис. 1.
КАСКАД ФАКТОРОВ
Рассмотрим еще одну проблему годового отчета. За отчетный год себестоимость выпускаемой продукции выросла с 1000 до 1730 тыс. руб. Это значительный рост затрат, который нужно объяснить руководству. Проводят факторный анализ.
Согласно факторному анализу себестоимость претерпевала значительные изменения под влиянием ряда факторов.
1. Факторы роста затрат:
- увеличение фонда оплаты труда на 380 тыс. руб.;
- рост цен на сырье и материалы добавил к затратам 240 тыс. руб.;
- рост цен на энергоносители увеличил затраты компании на 300 тыс. руб.;
- рост амортизационных отчислений составил 160 тыс. руб. Причины роста:
– в прошлом отчетном периоде ввели в эксплуатацию новую производственную линию № 5;
– завершили модернизацию оборудования и реконструкцию цеха № 2, что увеличило стоимость основных средств, а значит, и амортизационные отчисления;
- на улучшение качества затратили 400 тыс. руб. Применили новые материалы, добавили дополнительные технологические операции, усилили контроль показателей качества. Это положительно повлияло на конкурентоспособность продукции.
2. Факторы снижения себестоимости:
- снижение трудоемкости производственных операций позволило снизить себестоимость на 300 тыс. руб. Трудоемкость снижена в результате реализации мероприятий по внедрению бережливого производства в цехе № 1;
- экономия материалов снизила себестоимость на 200 тыс. руб. Снизить расход материалов удалось благодаря запуску новой производственной линии № 5;
- экономия по общепроизводственным расходам составила 250 тыс. руб. Экономию получили через оптимизацию численности инженерно-технического персонала цехов и общезаводских служб, автоматизацию рабочих мест мастеров, плановиков-диспетчеров, кладовщиков.
Все перечисленные факторы, которые отразились на отклонении фактической себестоимости от плановой, визуализированы на рис. 5.
Построение диаграммы «Каскад»
Чтобы построить «Каскад», важно правильно подготовить таблицу с диапазоном данных (табл. 2). Диаграмму строят на основании граф «Показатель», «Базис», «Рост» и «Снижение».
В таблицу 2 вносят «Себестоимость План» и заполняют показатели в графах «Рост» и «Снижение». Во вспомогательной графе рассчитана фактическая себестоимость (видно, как она изменялась под влиянием каждого фактора). Показатели в графе «Базис» выполняют функцию «подпорок» (невидимая часть диаграммы).
Формула для графы «Базис»: =ЕСЛИ(C4>0;E4-C4+D4;E4).
Формула для вспомогательной графы: =E3+C4-D4.
После подготовки диапазона А3:D12 на вкладке Вставка выбирают Гистограмму с накоплением. Получают стандартную диаграмму, как на рис. 6. Для темно-зеленых столбиков гистограммы (базиса) вызывают контекстное меню: Формат ряда данных → Заливка → Нет заливки. Делают их невидимыми.
Отдельно для первого и последнего невидимого столбца («Себестоимость План» и «Себестоимость Факт») задают цвет: вкладка Формат → Заливка фигуры. Убирают легенду, добавляют подписи данных, форматируют шрифт и цвета видимых блоков гистограммы согласно корпоративным стандартам. В результате получают диаграмму «Каскад» с результатами факторного анализа, как на рис. 5.
ОБЪЕМЫ ЗАПАСОВ НА «ШКАЛЕ»
Объем запасов компании зафиксирован в годовом балансе и пояснениях к бухгалтерскому балансу. Однако эти формы не дают представления о достаточном уровне запасов или их переизбытке. Показатели по запасам лаконично предоставляют руководству на диаграммах со «шкалой». Это следующий тип нестандартной, но информативной диаграммы. Рассмотрим три примера ее применения.
ПРИМЕР 1
На отчетный год был утвержден план завоза пигмента в количестве 2700 т (рис. 7, красная черта). Критический низкий объем завоза для компании составляет 2000 т и ниже (красная зона рис. 7). Средний уровень выполнения плана по завозу — это желтая зона от 2000 до 2500 т (2000 + 500 согласно табл. 3). Хорошее выполнение плана — от 2500 до 3000 т. Это зеленая зона. Допускается превышение плана на 300 т, что составляет 11 %.
Фактически за год служба снабжения обеспечила завоз в 2900 т пигментов (черный уровень).
Фактический показатель незначительно превышает план и находится в зеленой допустимой зоне — план завоза перевыполнен на 7,4 %.
Служба снабжения выполнила свою работу хорошо. Если бы уровень находился в красной зоне, для компании это означало бы нехватку сырья, сбои и простой производства. Выход за зеленую зону вреден для финансов компании. Он означал бы излишнюю закупку материалов, замораживание оборотных средств.
ПРИМЕР 2
Перед формированием годовой финансовой и налоговой отчетности в компании провели инвентаризацию по складу сырья. По учетным данным, на складе хранится 20 000 т сырья.
Специалисты определили уровни критичности (рис. 8, табл. 4). Объем ниже 18 000 т будет сигнализировать о значительной недостаче, от 18 000 до 22 000 т — о незначительных недостачах и излишках. Если инвентаризация покажет запас выше 22 000 т, это будет говорить о существенных излишках.
Установление зеленой зоны незначительных отклонений связано со следующими факторами, которые всегда имеют место при работе с запасами:
- естественная убыль;
- разница при пересчете остатков из-за разницы во влажности или иных физико-химических показателей;
- погрешности измерений (например, учет сырья ведут по результатам взвешивания крупнотоннажных автомобилей с сырьем на весах автомобильных или берут в учет вес по железнодорожной накладной);
- неточности и случайные ошибки в учете (например, завскладом забыл отписать передачу по накладной).
Согласно данным табл. 4 и рис. 8 инвентаризация выявила значительную недостачу, которую упомянутыми выше факторами не объяснить. Результат: 13 800 т находятся в критической красной зоне, на которую руководитель должен обратить внимание. Недостача составила 6200 т, или 31 % к учетным остаткам. Это высокорисковые результаты. Нужно разбираться с причинами. Распространенные причины — злоупотребления и хищения, заниженные нормы расхода, если списание на производство ведут по нормам.
В такой ситуации у компании заниженная себестоимость и завышенная прибыль. Необходимо срочно выверять нормы расхода сырья по основным видам продукции.
ПРИМЕР 3
Чтобы ускорить оборачиваемость оборотных средств, специалисты компании в отчетном году пронормировали остатки запасов сырья и готовой продукции, заделов незавершенного производства. В частности, запасы на конец года по бункеру № 3 должны составлять 10 000 т.
Одновременно рассчитали, что критическим для компании будет уровень запаса в этом бункере ниже 8000 т (рис. 9, табл. 5). Низкий запас может привести к остановке технологических линий при малейшем сбое в сроках завоза новых партий сырья.
Нормативный уровень запасов (желтая зона) определен в интервале от 8000 до 10 500 т. Запасы свыше 10 500 признают излишними. Если в компании уровень запаса достигнет зеленой зоны, то потребуется оперативно сокращать план завоза материалов, урезать авансы поставщикам.
Согласно данным табл. 5 и рис. 9 фактический уровень запасов в 10 250 т лишь незначительно превышает целевой ориентир в 10 000 т и находится в нормативной желтой зоне. Срочные корректирующие меры в данный период не нужны, остатки запасов на нормативном уровне.
Преимущества диаграмм типа «Шкала»:
- трехцветная фоновая заливка показывает зоны риска;
- горизонтальная красная линия показывает плановый (целевой, нормативный) уровень;
- черный центральный прямоугольник-уровень показывает достигнутый текущий уровень (факт).
Недостаток диаграммы в том, что она не сравнивает показатели за несколько лет. Этот недочет легко устранить, если расположить рядом «шкалы» за несколько отчетных периодов.
Построение диаграммы «Шкала»
Рассмотрим на примере табл. 5 и рис. 9, как построить диаграмму со «шкалой». Действуют последовательно:
Шаг 1. Готовят таблицу с исходными данными, где определяют уровни показателей (табл. 5).
Шаг 2. По заготовленному диапазону строят гистограмму с накоплением. На вкладке Конструктор выбирают Данные, а в Строка/Столбец выбирают расположение столбцов друг над другом. Настраивают поочередно цветовую заливку секторов по их смыслу: вкладка Формат → Заливка фигуры (рис. 10). Убирают легенду и название диаграммы. Изменяют размер — делают столбик узким.
Шаг 3. Настраивают черный «бегунок». Выделяют черный сектор (фактический остаток запасов). Вызывают через контекстное меню: Формат ряда данных → Параметры ряда → Построить ряд → По вспомогательной оси. Черный прямоугольник временно закроет другие секторы диаграммы. Далее в Параметрах ряда Боковой зазор устанавливают до максимума → Большой зазор (рис. 11).
Шаг 4. Форматируют нужный шрифт. Удаляют правую ось.
Шаг 5. Устанавливают плановый уровень. Чтобы получить в итоге красную планку, выделяют верхний красный сектор: Изменить тип диаграммы для ряда → Точечная. Сектор превратится в точку или знак «Ж» (рис. 12). Далее выполняют команду: Макет → Планки погрешностей → Планки погрешностей с относительными ошибками. Через контекстное меню вызывают Формат планок погрешностей. У точки появятся четыре планки. Вертикальные планки удаляют, а горизонтальные настраивают, чтобы получить черту-уровень.
В свойствах Горизонтальных планок погрешностей (диалоговое окно «Формат планок погрешностей») в разделе «Величина погрешности» выбирают «Фиксированное значение» и задают положительное и отрицательное значение ошибки («Все»), равное 0,2–0,5 (рис. 13). Здесь же увеличивают толщину линии и устанавливают красную заливку (Цвет линии и Тип линии). Маркер отключают через Формат ряда данных → Параметры маркера — Нет.
Другой способ настроить «уровень». Маркер не удаляют, а придают ему форму линии. Вызывают контекстное меню – Формат точки данных – Параметры маркера – Тип Линия. Размер подбирают так, чтобы перекрыть шкалу (толщину оставшихся секторов гистограммы). В результате получают диаграммы, как на рис. 7–9.
ЕЖЕДНЕВНАЯ ДИНАМИКА ПРОДАЖ
Производственные показатели и прибыль — достаточно узкие показатели. Руководителей больше интересуют объемы продаж («живые» деньги). При подведении итогов года по продажам суммы годовой выручки часто недостаточно. Требуется более подробная аналитика:
1) за каждый день года. Для анализа, привязанного к выборочным датам и определенным событиям: предпраздничный сезон, период скидок, агрессивная рекламная кампания, действия конкурентов, новая система мотивации менеджеров, открытие новых торговых точек, передача «активных» продаж на аутсорсинг и др. Если знать, как на интересующие события реагировали продажи в прошлом году, можно эффективно спланировать политику продаж и маркетинговый план на следующий год и таким образом увеличить выручку;
2) по регионам, торговым точкам, брендам, видам продукции, ценовым сегментам.
Сгруппировать все перечисленное в одной информативной диаграмме и не допустить перегруженности помогут интерактивные графики. Интерактивный график (рис. 14) каждый специалист может подстраивать под свои задачи:
- флажками включать и выключать отображение отдельных графиков. На выбор: все три графика, только две торговые марки, одну торговую марку или только общий объем продаж;
- подробнее рассмотреть показатели за укрупненный или короткий интервал времени (полгода или десять дней);
- выбрать любой период времени за прошлый год (январь, март или октябрь).
Создание интерактивного графика динамики продаж
Чтобы создать интерактивный график (см. рис. 14) на базе исходной таблицы, действуют поэтапно.
Этап 1. Строят вспомогательную таблицу на основании исходной (в нашем случае — на основании табл. 6). Структура и показатели обеих таблиц одинаковые. Отличие в следующем: во вспомогательную таблицу берут только те показатели (столбцы), которые выбраны «флажками».
Чтобы подключить флажки, используют вкладку «Разработчик». Если ее нет (в стандартных настройках отсутствует), открывают вкладку Файл → Параметры → Настройка ленты → Разработчик.
Далее выполняют команду: вкладка Разработчик → Вставить → Элементы управления формы → Флажок. Вставляют три флажка по количеству отображаемых на графике показателей (рис. 15). С помощью правой кнопки мыши выбирают команду «Изменить текст», подписывают флажки идентично столбцам в табл. 6.
Далее для каждого флажка определяют связанную ячейку. Нужно каждый флажок привязать к соответствующей зеленой ячейке над столбцом с выручкой (рис. 15). При включении флажка в связанную ячейку будет выводиться ИСТИНА, при выключении — ЛОЖЬ.
С помощью формул проверяют связанные ячейки и выводят во вспомогательную таблицу показатели выручки из исходной таблицы для построения графика или ошибку #Н/Д, чтобы график не визуализировался. Чтобы установить связь с ячейкой, вызывают контекстное меню → Формат объекта — Формат элемента управления → Связь с ячейкой (рис. 15).
После установки флажков настраивают автозаполнение формулой для вспомогательной таблицы из исходной при условии, что флажок включен и в связанной ячейке ИСТИНА. Формула для общего объема продаж (рис. 16):
=ЕСЛИ(H$1;ПродажиИ[@[Объем продаж - всего, тыс.руб.]];#Н/Д).
Копируем формулы на другие столбцы:
=ЕСЛИ(I$1;ПродажиИ[@[Объем продаж ТМ "Омега", тыс.руб.]];#Н/Д);
=ЕСЛИ(J$1;ПродажиИ[@[Объем продаж ТМ "Дельта", тыс.руб.]];#Н/Д).
Такой вид аргументов в формуле «ЕСЛИ» связан с тем, что обе таблицы отформатированы как «умные», им присвоены имена:
- исходная таблица «ПродажиИ»;
- вспомогательная таблица «ПродажиВ».
В формулах также выполнено частичное закрепление ссылки на зеленую ячейку (H$1). Она должна при копировании смещаться вправо, но не вниз (рис. 16).
Этап 2. Настраивают полосы прокрутки для масштабирования.
По аналогии с флажками добавляют две полосы прокрутки: Разработчик → Вставить → Элементы управления формы → Полоса прокрутки. Полосы прокрутки связывают каждую со своей ячейкой, куда будет выводиться числовое значение положения ползунка. Это нужно для определения масштаба и сдвига. Чтобы связать с ячейкой, действуют через Формат объекта (аналогично с флажками). Для первой полосы прокрутки назначают ячейку W16, для второй — W18 (рис. 17). При перемещении ползунков значения в связанных ячейках должны меняться в интервале от 1 до 366. Максимальное и минимальное значение интервала зависит от того, за какой период нужно визуализировать график.
Этап 3. Создают динамические именованные диапазоны.
Именованные диапазоны понадобятся на следующем этапе как исходные данные для построения графиков. Чтобы отображать на графике показатели только за определенный интервал времени, создают именованный диапазон, который ссылается только на нужные ячейки во вспомогательной таблице. Именованный диапазон характеризуется двумя параметрами:
- отступом от начала таблицы вниз на заданное количество строк. Это отступ по временной шкале начало-конец года (верхняя полоса прокрутки);
- количеством ячеек по высоте, то есть размером интервала (нижняя полоса прокрутки).
Для создания динамического диапазона используют функцию «СМЕЩ» из библиотеки функций «Ссылки и массивы». Константы в аргументах этой функции заменяют ссылками на ячейки с переменным содержимым — на значения в связанных ячейках W16 и W18 (рис. 18).
В первую очередь присваивают имя ячейкам W16 и W18: вкладка Формулы → Диспетчер имен → Создать. Имена Период и Масштаб соответственно (рис. 18).
Далее создают диапазон с именем ВОбщ (общий объем продаж). Этот диапазон ссылается с помощью функции «СМЕЩ» на показатели общего объема продаж за выбранный отрезок времени, используя диапазоны Период и Масштаб, а также ячейку G4 (шапка вспомогательной таблицы) в качестве точки отсчета (рис. 19). Перед именем диапазона проставляют имя текущего листа (в нашем случае имя листа — Динамика). Именованный диапазон доступен только в пределах текущего листа, а не всей книги. Это важно для построения графиков на следующем этапе.
Аналогично создают именованные диапазоны для продаж по отдельным торговым маркам и для столбца дат.
Все созданные диапазоны можно проконтролировать через «Диспетчер имен» на вкладке Формулы (рис. 19). Формулы диапазонов следующие:
- Дата (ВДата):=СМЕЩ($G$4;Период;0;Масштаб;1);
- Общий объем продаж (ВОбщ):=СМЕЩ(Динамика!G$4;Период;1;Масштаб;1);
- Продажи ТМ «Омега» (ВОмега):=СМЕЩ(Динамика!$G$4;Период;2;Масштаб;1);
- Продажи ТМ «Дельта» (ВДельта):=СМЕЩ(Динамика!$G$4;Период;3;Масштаб;1).
Этап 4. Вставляют график продаж.
Выделяют часть вспомогательной таблицы (табл. 6, рис. 20) и строят обычный график: вкладка Вставка → Диаграммы → График.
Если выделить одну из линий графика, то в строке формул появится функция «РЯД». Она задает диапазоны данных для выделенного графика. Заменяют статические диапазоны в ее аргументах (G5:G15) на динамические, которые создали на предыдущем этапе. В строке формул меняют.
Общая выручка:
=РЯД(Динамика!$H$4;Динамика!$G$5:$G$15;Динамика!$H$5:$H$15;1)
на =РЯД(Динамика!$H$4;Динамика!ВДата;Динамика!ВОбщ;1).
Аналогично по торговым маркам «Омега» и «Дельта»:
=РЯД(Динамика!$I$4;Динамика!ВДата;Динамика!ВОмега;2);
=РЯД(Динамика!$J$4;Динамика!ВДата;Динамика!ВДельта;3).
Форматируют цвет линий графика и размер диаграммы.
В работе с интерактивными графиками часто возникает проблема, когда после нескольких манипуляций прокрутка перестает работать, а диапазон в функции «РЯД» меняется с поименованного на базовый. Чтобы настройки не сбивались, в полосах прокрутки ставят максимальную длину на единицу больше. Так, за год вместо 365 в полосах прокрутки задано максимальное значение 366 (см. рис. 17).
Рассмотрим примеры, при которых динамические графики буду полезны (рис. 21–23).
ПРИМЕР 4
Годовой объем продаж продукции ТМ «Омега» превышает объем реализации ТМ «Дельта». Нужно сравнить эти торговые марки, поэтому график общей выручки с помощью флажка отключают (см. рис. 14). Получили на диаграмме два графика (рис. 21). Чтобы получить период февраль-май, изменяют положения бегунка в полосах прокрутки.
Перед экономистом лежит отчет об исполнении маркетингового бюджета. В период с конца февраля до середины мая проводили комплекс маркетинговых мероприятий по повышению продаж продукции ТМ «Дельта».
По расчетам маркетологов, объем продаж ввиду значительных скидок должен был превысить выручку по приоритетной продукции — ТМ «Омега». Как видно из графика на рис. 21, этого не произошло. Маркетинговая акция провальная. Важно обозначить причины провала в отчете об исполнении маркетингового бюджета. Наиболее распространенные причины: ошибка в целевой аудитории, акция совпала с активностью конкурентов на аналогичную продукцию.
В отдельные дни рассматриваемого периода незначительные пики продаж все-таки наблюдаются, однако их нельзя напрямую связать с маркетинговыми мероприятиями, так как продажи другой продукции также на высоком уровне.
ПРИМЕР 5
В сентябре при проведении следующего комплекса стимулирующих мер ранее допущенные ошибки (пример 4) проанализировали и устранили (рис. 22). В результате в августе-сентябре вышли на объем выручки ТМ «Дельта», запланированный в маркетинговом бюджете. Повышенные объемы продаж ТМ «Дельта» держались до конца октября. Это более длительный период, чем планировалось. Более того, еще около месяца продажи обеих торговых марок были на одном уровне. К концу года выручка от ТМ «Дельта» уже превышала ТМ «Омега» (рис. 22).
Данные маркетинговые мероприятия и политику продаж следует признать успешными, их нужно применить и на следующий год.
ПРИМЕР 6
Если просмотреть весь динамический график (с помощью прокруток сделать это удобно и быстро), то общий объем выручки соответствует динамике продаж ТМ «Омега» и ТМ «Дельта». Однако в отдельные периоды (рис. 14, 22 и 23), когда продажи от упомянутой продукции отсутствуют или на низком уровне, выручка в компанию продолжает поступать.
Нужно проанализировать группы товаров, что продавались в эти интервалы, и факторы роста продаж (вызваны рекламной поддержкой, сезонный спрос или иной фактор), а также выработать политику продвижения и продаж на следующий год.
Чтобы принять обоснованные решения, которые нужно заложить в плановые показатели на следующий год, анализ по категориям можно провести по методу АВС + XYZ.
ЗАКЛЮЧЕНИЕ
Информативная визуализация — лаконичный способ привлечь внимание к показателям, которые в письменных отчетах остаются без должного внимания ввиду высокой загруженности руководства. Корректно сформированный набор диаграмм уже сам по себе аналитический отчет. Важно не только показать произошедшие за год изменения, но и объяснить причины отклонения показателей, указать, какие меры реагирования были предприняты в середине года, оценить их достаточность.
Для физических лиц
Для юридических лиц
Подписка по каталогам