Информативные диаграммы в Excel к годовым отчетам

Как показать прибыль по месяцам и сегментам, используя диаграмму «Торнадо»?

Какой диаграммой сопроводить факторный анализ себестоимости продукции?

Как визуализировать объемы запасов и результаты годовой инвентаризации с помощью диаграммы «Шкала»?

Как построить интерактивный график динамики продаж за отчетный год?

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

«Торнадо» прибыли

Руководство компании прежде всего интересует заработанная за год прибыль в разрезе точек продаж, каналов сбыта, категорий продукции, ценовых сегментов и видов деятельности. Значит, нужно подготовить информацию о плановой и фактической прибыли, сравнить показатели прибыли отчетного и прошлого года по каждому месяцу. Когда нужно сравнить два показателя за несколько месяцев или лет, используют диаграмму типа «торнадо» (рис. 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. Факторы снижения себестоимости:

• в цехе № 1 внедрили бережливое производство. Это позволило снизить трудоемкость производственных операций. Как следствие, на 300 тыс. руб. уменьшилась себестоимость;

• в результате запуска новой производственной линии № 5 удалось сэкономить материалы и снизить себестоимость продукции на 200 тыс. руб.;

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

Все перечисленные факторы, которые привели к возникновению отклонений фактической себестоимости от плановой, визуализированы на рис. 5.

Как построить диаграмму «Каскад»?

Прежде чем строить «Каскад», подготовьте таблицу с диапазоном данных. Строить диаграмму будем на основании граф «Показатель», «Базис», «Рост» и «Снижение» (табл. 2).